Choosing Sequelize's Paranoid Soft Delete: Constraints, Trade‑offs, and Implementation

Decision: Enable or Disable Paranoid?

When designing a Sequelize model you must choose whether a destroy() call should permanently delete a row or merely mark it as deleted. This decision affects data recovery, storage usage, and how foreign‑key constraints behave.

Useful takeaway: enabling the paranoid option adds a deletedAt timestamp column, converts destroy() into an UPDATE that sets this column, and provides a model.restore() method to reverse the soft delete.

Option Comparison

Setting	Behavior of destroy()	Restore capability	Typical use case
paranoid: false (default)	Issues a hard DELETE FROM statement	Not possible without external backup	Data that is truly obsolete, e.g., temporary logs
paranoid: true	Issues an UPDATE that sets deletedAt to current timestamp	Available via instance.restore() or Model.restore({ where: … })	Records that may need to be recovered, e.g., user accounts, orders

Trade‑offs

• Storage: Soft‑deleted rows remain in the table, increasing size until purged.
• Performance: Queries that ignore deletedAt must filter it out; an index on deletedAt speeds up active‑record scans but adds write overhead.
• Foreign keys: A soft‑deleted parent still exists, so ON DELETE triggers do not fire. If child rows should be allowed to remain, use ON DELETE SET NULL or handle manually.
• Complexity: Application code must remember to treat deletedAt as a visibility flag unless you rely on Sequelize’s default scope.

Implementation Example

1. Define the model with paranoid enabled

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('sqlite::memory:');

const User = sequelize.define('User', {
  username: DataTypes.STRING,
  email:    DataTypes.STRING,
}, {
  paranoid: true, // enables soft delete
  timestamps: true, // adds createdAt, updatedAt, and deletedAt
});

sequelize.sync({ force: true }).then(async () => {
  // 2. Create a record
  const user = await User.create({ username: 'alice', email: 'alice@example.com' });
  console.log('Created user id:', user.id);

  // 3. Soft delete
  await user.destroy(); // force: false by default
  console.log('After destroy, deletedAt:', user.deletedAt);

  // 4. Verify that the row is hidden from default finder
  const remaining = await User.findAll();
  console.log('Visible rows after soft delete:', remaining.length); // expects 0

  // 5. Restore the record
  await user.restore();
  console.log('After restore, deletedAt:', user.deletedAt);

  // 6. Verify the row appears again
  const visible = await User.findAll();
  console.log('Visible rows after restore:', visible.length); // expects 1
});

2. Verification steps you can run

1. Check the generated SQL: enable logging (logging: console.log) and confirm that destroy() produces an UPDATE statement setting deletedAt.
2. Inspect the table schema (SELECT * FROM sqlite_master WHERE type='table' AND name='Users';) to see the deletedAt column.
3. Add an index if desired: CREATE INDEX idx_users_deletedAt ON Users(deletedAt); and verify with EXPLAIN QUERY PLAN that the planner uses it for WHERE deletedAt IS NULL queries.
4. Attempt a hard delete by calling user.destroy({ force: true }) and confirm the SQL is a DELETE FROM statement.

Limitations and Practical Checks

• Soft‑deleted rows still consume disk space; schedule a periodic purge (User.destroy({ where: { deletedAt: { [Op.ne]: null }, force: true })) if retention policy allows.
• Foreign key constraints that reference the model will not cascade on soft delete; ensure they are defined with ON DELETE SET NULL or handle manually.
• Default scopes automatically filter out deletedAt IS NOT NULL. If you bypass scopes (Model.unscoped() or Model.scope(null)) you must add the filter yourself.

By following the table, trade‑off discussion, and the code example above you can make an informed decision about enabling Sequelize’s paranoid soft‑delete feature and validate that it behaves as expected in your environment.