Prevent sequelize from soft deleting rows that are referenced somewhere else

Question

I am using Sequelize.js with paranoid mode set to true.

My issue is that when I try to soft delete a row using the Model.destroy method, it does not check if this row is referenced before soft deleting it. Before I started using paranoid, sequelize would throw an error when trying to destroy a row that was referenced, and I would like to keep this behaviour now that I am using paranoid.

I found two similiar questions about this matter:

The 1st one did not have a solution, but it had a restriction I do not have: touching the DB.

The 2nd one has the following solution:

User.hasMany(Roles, { foreignKey: "whatever", onDelete: 'restrict', onUpdate: 'restrict'});

It is definitely a solution but would result in a lot of work because I need to have this behaviour for all my tables and many of them are referenced by N other tables.

Is there a way to make sequelize always check for references before soft deleting? I don't have any restrictions as for touching the DB or running performance expensive queries on the beforeBulkDelete hook.

score 0 · Answer 1 · answered Feb 05 '20 at 14:16

So I came up with a solution that I found here.

I'm not sure if it's a good way of doing it, but at least it did the job. I would appreciate if someone could evaluate this.

I created the two functions suggested in the answer previously mentioned and used them inside a beforeBulkDelete permanent hook:

sequelize.addHook("beforeBulkDestroy", async options => {
      const query = await sequelize.query(`
      declare @RowId int = ${options.where.Id}
      declare @TableName sysname = '${options.model.name}'

      declare @Command varchar(max) 

      select @Command = isnull(@Command + ' union all ', '') + 'select ''' + object_name(parent_object_id) + 
          ''' where exists(select * from ' + object_name(parent_object_id) + 
          CASE
              WHEN EXISTS(select object_name(object_id) from sys.columns col where name = 'deletedAt' and object_id = parent_object_id) 
                  THEN ' where ' + col.name+ ' = ' + cast(@RowId as varchar) +' and deletedAt IS NULL '
              when dbo.ParentIdFromTable(object_name(parent_object_id)) <> ''
                  then ' inner join ' + dbo.ParentIdFromTable(object_name(parent_object_id)) + ' on id = ' + dbo.PrimaryKey(object_name(parent_object_id))
                      +' where ' + col.name+ ' = ' + cast(@RowId as varchar) +' and deletedAt IS NULL '
              else 
                  ' where ' + col.name+ ' = ' + cast(@RowId as varchar) 
            END
          + ')' 
      from sys.foreign_key_columns fkc
          join sys.columns col on
              fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
      where object_name(referenced_object_id) = @TableName

      PRINT @Command
      execute (@Command)
      `,
        { type: sequelize.QueryTypes.SELECT, logging: false }
      );

      if (query.length !== 0) throw new Error();
    });

Prevent sequelize from soft deleting rows that are referenced somewhere else

1 Answers1