Paranoid

Sequelize supports the concept of paranoid tables. A paranoid table is one that, when told to delete a record, it will not truly delete it. Instead, a special column called deletedAt will have its value set to the timestamp of that deletion request.

This means that paranoid tables perform a soft-deletion of records, instead of a hard-deletion.

Defining a model as paranoid

To make a model paranoid, you must pass the paranoid: true option to the model definition. Paranoid requires timestamps to work (i.e. it won’t work if you also pass timestamps: false).

You can also change the default column name (which is deletedAt) to something else.

  1. class Post extends Model {}
  2. Post.init({ /* attributes here */ }, {
  3.   sequelize,
  4.   paranoid: true,
  6.   // If you want to give a custom name to the deletedAt column
  7.   deletedAt: 'destroyTime'
  8. });

Deleting

When you call the destroy method, a soft-deletion will happen:

  1. await Post.destroy({
  2.   where: {
  3.     id: 1
  4.   }
  5. });
  6. // UPDATE "posts" SET "deletedAt"=[timestamp] WHERE "deletedAt" IS NULL AND "id" = 1

If you really want a hard-deletion and your model is paranoid, you can force it using the force: true option:

  1. await Post.destroy({
  2.   where: {
  3.     id: 1
  4.   },
  5.   force: true
  6. });
  7. // DELETE FROM "posts" WHERE "id" = 1

The above examples used the static destroy method as an example (Post.destroy), but everything works in the same way with the instance method:

  1. const post = await Post.create({ title: 'test' });
  2. console.log(post instanceof Post); // true
  3. await post.destroy(); // Would just set the `deletedAt` flag
  4. await post.destroy({ force: true }); // Would really delete the record

Restoring

To restore soft-deleted records, you can use the restore method, which comes both in the static version as well as in the instance version:

  1. // Example showing the instance `restore` method
  2. // We create a post, soft-delete it and then restore it back
  3. const post = await Post.create({ title: 'test' });
  4. console.log(post instanceof Post); // true
  5. await post.destroy();
  6. console.log('soft-deleted!');
  7. await post.restore();
  8. console.log('restored!');
  10. // Example showing the static `restore` method.
  11. // Restoring every soft-deleted post with more than 100 likes
  12. await Post.restore({
  13.   where: {
  14.     likes: {
  15.       [Op.gt]: 100
  16.     }
  17.   }
  18. });

Behavior with other queries

Every query performed by Sequelize will automatically ignore soft-deleted records (except raw queries, of course).

This means that, for example, the findAll method will not see the soft-deleted records, fetching only the ones that were not deleted.

Even if you simply call findByPk providing the primary key of a soft-deleted record, the result will be null as if that record didn’t exist.

If you really want to let the query see the soft-deleted records, you can pass the paranoid: false option to the query method. For example:

  1. await Post.findByPk(123); // This will return `null` if the record of id 123 is soft-deleted
  2. await Post.findByPk(123, { paranoid: false }); // This will retrieve the record
  4. await Post.findAll({
  5.   where: { foo: 'bar' }
  6. }); // This will not retrieve soft-deleted records
  8. await Post.findAll({
  9.   where: { foo: 'bar' },
  10.   paranoid: false
  11. }); // This will also retrieve soft-deleted records