Sequelize - Update FOREIGN KEY constraint win ONDELETE CASCADE

Question

I'm using Postgres with Sequelize and I need to update an existing ForeignKey Constraint with onDelete = Cascade.

I have updated my model, but the migration file comes up as a skeleton. I haven't found a way to update this constraint.

My model file:

export const Property = sequelize.define<PropertyModel, PropertyAttribute>('property', {
  id: {
    type: Sequelize.UUID,
    primaryKey: true,
    defaultValue: Sequelize.UUIDV4,
    allowNull: false
  },
  name: Sequelize.STRING,
  status: {
    type: Sequelize.BOOLEAN,
    defaultValue: true
  },
  publishedAt: Sequelize.DATE
});

Property.associate = models => {
  Property.belongsTo(models.User, {
    foreignKey: { allowNull: false },
    allowNull: false
  });
  Property.hasMany(
    models.PropertyService, {onDelete: "cascade"});

  Property.hasMany(
    models.Visitor, {onDelete: "cascade"});
};

My migration file:

module.exports = {
  up: (queryInterface, Sequelize) => {
      // How can I update a FK constraint??
  },

  down: (queryInterface, Sequelize) => {
      // How can I undo an FK update constraint??
  }
};

Any ideas on how to make this migration?

Answer 1

I found it simplest to first remove the existing constraint and then add it back with onDelete. Sequelize has queryInterface.removeConstraint and queryInterface.addConstraint methods for this.

Here is a contrived example:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    const transaction = await queryInterface.sequelize.transaction();
    try {
      await queryInterface.removeConstraint(
        'users',
        'users_address_id_fkey',
        { transaction }
      );
      await queryInterface.addConstraint('users', ['address_id'], {
        type: 'foreign key',
        name: 'users_address_id_fkey',
        references: {
          table: 'addresses',
          field: 'id',
        },
        onDelete: 'CASCADE',
        transaction
      });
      return transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  },

  down: async (queryInterface, Sequelize) => {
    const transaction = await queryInterface.sequelize.transaction();
    try {
      await queryInterface.removeConstraint(
        'users',
        'users_address_id_fkey',
        { transaction }
      );
      await queryInterface.addConstraint('users', ['address_id'], {
        type: 'foreign key',
        name: 'users_address_id_fkey',
        references: {
          table: 'addresses',
          field: 'id',
        },
        transaction
      });
      return transaction.commit();
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  },
};