Sequelize-typescript: Update with associations

[doovers]

With the following models & associations, how can I perform an update with this JSON?

I've spent a day trying to do this and don't know why I can't figure it out!

{
    "firstName": "firstName1",
    "lastName": "lastName1",
    "company": "company1",
    "email": "email1",
    "isActive": false,
    "notes": "notes1",
    "addresses": [{
        "street": "street1",
        "city": "city1",
        "country": "country1",
        "region": "region1",
        "postalCode": "postalCode1"
    },
    {
        "street": "street2",
        "city": "city2",
        "country": "country2",
        "region": "region2",
        "postalCode": "postalCode2"
    }],
    "phones": [{
        "number": 1234,
        "extension": 1234,
        "type": "Cell"
    },
    {
        "number": 1234,
        "extension": 1234,
        "type": "Home"
    }]
}

import { Table, Column, Model, HasOne, HasMany } from 'sequelize-typescript';
import Address from './address.model';
import Phone from './phone.model';

@Table({
  tableName: 'customer',
  timestamps: true
})
export default class Customer extends Model<Customer> {

  @Column company: string;
  @Column email: string;
  @Column firstName: string;
  @Column isActive: boolean;
  @Column lastName: string;
  @Column notes: string;
  @HasMany(() => Address, { onDelete: 'cascade' }) addresses: Address[];
  @HasMany(() => Phone, { onDelete: 'cascade' }) phones: Phone[];

}

import { Table, Column, Model, ForeignKey, BelongsTo } from 'sequelize-typescript';
import Customer from './customer.model';

@Table({
  tableName: 'address',
  timestamps: true
})
export default class Address extends Model<Address> {

  @Column street: string;
  @Column city: string;
  @Column country: string;
  @Column region: string;
  @Column postalCode: string;

  @ForeignKey(() => Customer)
  @Column customerId: number;

  @BelongsTo(() => Customer, { onDelete: 'cascade' })
  customer: Customer;
}

import { Table, Column, Default, DataType, Model, BelongsTo, ForeignKey } from 'sequelize-typescript';
import Customer from './customer.model';

@Table({
  tableName: 'phone',
  timestamps: true
})
export default class Phone extends Model<Phone> {

  @Column number: string;
  @Column extension?: number;
  @Column type: string;

  @ForeignKey(() => Customer)
  @Column customerId: number;

  @BelongsTo(() => Customer, { onDelete: 'cascade' })
  customer: Customer;
}

[RobinBuschmann]

Hey @doovers did you this example: https://stackoverflow.com/questions/33918383/sequelize-update-with-association

Regarding this issue https://github.com/sequelize/sequelize/issues/7703, this feature doesn't seem to be implemented with Model.update

[doovers]

@RobinBuschmann Yes I saw that post. I have been successful in updating a HasOne association like this:

  updateItem() {
    let requestBody = this.parseRequest();

    Employee
      .findById(this.event.pathParameters.id, { include: [Address, Phone] })
      .then(employee => {
        employee.set(requestBody);

        const address = Address.build(requestBody.address);
        const setAddress = employee.$set('address', address);

        const phone = Phone.build(requestBody.phone);
        const setPhone = employee.$set('phone', phone);

        Promise.all([
          employee.save(),
          setAddress,
          setPhone
        ])
          .then(value => value[0])
          .then(this.successHandler)
          .catch(this.errorHandler);
      })
      .catch(this.errorHandler);
  }

But my customer model has HasMany associations for phone and address so I'm unsure how to do this. My goal is that the array of addresses and phones is updated with the customer object.

[RobinBuschmann]

Unfortunately you will need to do it manually. Ideally within a db transaction(?)

[doovers]

@RobinBuschmann Is it a case of deleting the existing address and phone objects and adding the new? Do you have an example of how to do this?

[MaheshCasiraghi]

@doovers your code actually builds and inserts a new record into db. It kind of works, but leaves back old instances of associated models, now no longer associated. Here an example of how to make it working, assuming User has a hasOne relationship with Country:

First export the Providers and import them in modules:

export const usersProviders = [ { provide: 'UsersRepository', useValue: User, }, ];

export const countriesProviders = [ { provide: 'CountriesRepository', useValue: Country, }, ];

then inject Repositories them into service:

@Inject('UsersRepository') public readonly usersRepository: typeof Model, @Inject('CountriesRepository') public readonly countriesRepository: typeof Model,

and then you can run:

this.countriesRepository.update({"countryAttribute" : "countryValue"}, {where: {userId: id}});

which updates your associated model, without creating an new record.

If something structure-wise is not clear in my example, refer to this: https://docs.nestjs.com/recipes/sql-sequelize

[doovers]

@MaheshCasiraghi I think I'm achieving the same thing with this code for a HasOne:

  updateItem() {
    let requestBody = this.parseRequest();

    Employee
      .findById(this.event.pathParameters.id, { include: [Address, Phone] })
      .then(employee => {

        const setAddress = Address
          .findById(requestBody.address.id)
          .then(address => {
            address.updateAttributes(requestBody.address);
          });

        const setPhone = Phone
          .findById(requestBody.phone.id)
          .then(phone => {
            phone.updateAttributes(requestBody.phone);
          });

        employee.set(requestBody);

        Promise.all([
          setAddress,
          setPhone
        ])
          .then(() => employee.save())
          .then(this.successHandler)
          .catch(this.errorHandler);
      })
      .catch(this.errorHandler);
  }

But how would I achieve something similar for a HasMany? This is my current approach:

  updateItem() {
    let requestBody = this.parseRequest();

    Customer
      .findById(this.event.pathParameters.id, { include: [Address, Phone] })
      .then(customer => {

        // Remove old addresses
        customer.addresses.forEach(address => {
          address.destroy();
        });

        // Create new addresses
        let addresses = [];
        requestBody.addresses.forEach(addressData => {
          let addressReq = Address.create(addressData)
            .then(address => {
              address.$set('customer', customer);
              address.save();
            });
          addresses.push(addressReq);
        });

        // Remove old phones
        customer.phones.forEach(phone => {
          phone.destroy();
        });

        // Create new phones
        let phones = [];
        requestBody.phones.forEach(phoneData => {
          let phoneReq = Phone.create(phoneData)
            .then(phone => {
              phone.$set('customer', customer);
              phone.save();
            });
          phones.push(phoneReq);
        });

        // Update customer
        customer.set(requestBody);

        Promise.all([
          addresses,
          phones
        ])
          .then(() => customer.save())
          .then(this.successHandler)
          .catch(this.errorHandler);
      })
      .catch(this.errorHandler);
  }

@RobinBuschmann Is the above equivalent to what you were suggesting regarding the transactions?

[RobinBuschmann]

@doovers What I mean with transactions are db transactions. See http://docs.sequelizejs.com/manual/tutorial/transactions.html

[MikeDabrowski]

Do I understand it correctly: in order to update associated entities I need to remove them and use create to add them back?

[RobinBuschmann]

@MikeDabrowski No, this is not necessary.

@doovers & @MikeDabrowski What about this:

const customerData = req.body; 
const addressData = customerData.addresses;
const phoneData = customerData.phones;
const customer = await Customer.findById(req.params.id, { include: [Address, Phone] });
if(!customer) {
  throw new NotFoundError();
}
await sequelize.transaction(async transaction => {
  const {addresses, phones} = customer;

  // update addresses
  await Promise.all([
    addressData.map(async addressData => {
      const address = addresses.find(_address => _address.id === addressData.id);

      if(address) {
        // if exists, update address
        await address.update(addressData, {transaction});
      } else { 
        // if not create new address and associate to customer
        await customer.$create('address', addressData, {transaction});
      }
    })
  ])
  // same as addresses for phones goes here...

  // finally update customer
  await customer.update(customerData, {transaction});
});

The req object is more or less pseudo code based on express.

(I haven't tested it yet)

Update: Notice, if needed, a deletion of removed addresses or phones could be implemented as well.

[doovers]

@RobinBuschmann Nice, thanks for that. I now have a working solution:

async updateItem() {
  let requestBody = this.parseRequest();

  // Fetch customer
  const customer = await Customer.findById(this.event.pathParameters.id, { include: [Address, Phone] });
  if (!customer) {
    throw new NotFoundError();
  }

  // Get deltas
  const addressDelta = this.getDelta(customer.addresses, requestBody.addresses);
  const phoneDelta = this.getDelta(customer.phones, requestBody.phones);

  // Start transaction
  await this.sequelize
    .transaction(async transaction => {

      // Update addresses
      await Promise.all([
        addressDelta.added.map(async address => {
          await customer.$create('address', address, { transaction });
        }),
        addressDelta.changed.map(async addressData => {
          const address = customer.addresses.find(_address => _address.id === addressData.id);
          await address.update(addressData, { transaction });
        }),
        addressDelta.deleted.map(async address => {
          await address.destroy({ transaction });
        })
      ]);

      // Update phones
      await Promise.all([
        phoneDelta.added.map(async phone => {
          await customer.$create('phone', phone, { transaction });
        }),
        phoneDelta.changed.map(async phoneData => {
          const phone = customer.phones.find(_phone => _phone.id === phoneData.id);
          await phone.update(phoneData, { transaction });
        }),
        phoneDelta.deleted.map(async phone => {
          await phone.destroy({ transaction });
        })
      ]);

      // Finally update customer
      return await customer.update(requestBody, { transaction });
    })
    .then(customer => Customer.findById(customer.id, { include: [Address, Phone] }))
    .then(this.successHandler)
    .catch(this.errorHandler);
}

getDelta(source: Array<T>, updated: Array<T>): Delta<T> {

    let added = updated.filter(updatedItem => source.find(sourceItem => sourceItem.id === updatedItem.id) === undefined);
    let changed = source.filter(sourceItem => updated.find(updatedItem => updatedItem.id === sourceItem.id) !== undefined);
    let deleted = source.filter(sourceItem => updated.find(updatedItem => updatedItem.id === sourceItem.id) === undefined);


  const delta: Delta<T> = {
    added: added,
    changed: changed,
    deleted: deleted
  };

  return delta;
}

[mschipperheyn]

@doovers nice. But you should not put await customer.$create etc. That kills the parallel behavior of Promise.all. It should imho be

    await Promise.all([
        ...phoneDelta.added.map(async phone => customer.$create('phone', phone, { transaction })),
        ...phoneDelta.changed.map(async phoneData => {
          const phone = customer.phones.find(_phone => _phone.id === phoneData.id);
          return phone.update(phoneData, { transaction });
        }),
        ...phoneDelta.deleted.map(async phone => phone.destroy({ transaction }))
      ]);

[blankstar85]

I figured out an error in my code, i was defining the HasMany with an array HasMany .... model[].
Once I did that, the functions i was looking for finally showed in my ide,

So now i'm just trying to figure out the typing in the get delta function and where/how Delta is defined. Thanks!