Sequelize Paranoid Soft Deletes and the Unique Email Trap

Enabling paranoid: true in Sequelize feels safe until a user tries to re-register with an email they soft-deleted. The row is invisible in the app, but the database still enforces the unique index, so the new sign-up fails with a UniqueConstraintError. Paranoid gives you recoverability, but you must design the schema around it.

The practical takeaway is to treat soft deletion as a read-filter, not a delete. Queries hide rows, constraints do not.

How paranoid changes destroy and find

With paranoid: true and a deletedAt DATE column, instance.destroy() runs an UPDATE that sets deletedAt to now instead of issuing DELETE. Standard finders automatically add WHERE deletedAt IS NULL.

Model definition in a Node project:

const User = sequelize.define('User', {
  email: { type: DataTypes.STRING, allowNull: false },
  name: DataTypes.STRING
}, {
  paranoid: true,
  timestamps: true
});

Default reads exclude soft-deleted rows:

// returns null for a soft-deleted user
const u = await User.findByPk(id);

Include them explicitly with paranoid: false, restore with Model.restore(), or hard delete with force:

const all = await User.findAll({ paranoid: false });
await User.restore({ where: { id } });
await user.destroy({ force: true });

Run these in a script with DB write permission. Verify by checking the table directly with a raw query: the row should still exist with a non-null deletedAt after destroy(), and be gone after destroy({ force: true }).

The unique constraint conflict

The database does not know about Sequelize's filter. A unique index on email blocks a second row with the same value even if the first row is soft-deleted.

Workflow that breaks:

• User A creates [contact removed]
• User A calls destroy() → row gets deletedAt
• User B tries to create [contact removed] → DB rejects

Eager loads are also affected. Associations to soft-deleted parents are filtered by default, so includes can silently return missing related rows.

Worked example: verify behavior and restore

Create a migration first to add deletedAt before enabling paranoid, otherwise Sequelize will error at runtime.

// migration file, run with sequelize-cli db:migrate as a user with DDL rights
await queryInterface.addColumn('Users', 'deletedAt', {
  type: Sequelize.DATE,
  allowNull: true
});

Test script:

const user = await User.create({ email: '[contact removed]', name: 'Test' });
await user.destroy(); // sets deletedAt

const active = await User.findOne({ where: { email: '[contact removed]' } }); // null
const withDeleted = await User.findOne({ where: { email: '[contact removed]' }, paranoid: false }); // found

await User.restore({ where: { email: '[contact removed]' } }); // clears deletedAt

Expected check: active is null, withDeleted is an instance, and after restore a normal find returns it again. Risk: bulk operations like bulkDestroy can behave differently from instance methods, so confirm SQL via logging.

Trade-offs and limits

Two common schema fixes exist, each with trade-offs.

Partial unique index

PostgreSQL and SQLite support a filtered unique index that only applies to active rows:

-- migration, requires index creation privilege
CREATE UNIQUE INDEX unique_email_active ON "Users" (email) WHERE "deletedAt" IS NULL;

Pros: true uniqueness for active users. Cons: not portable to MySQL, and every read still carries the deletedAt IS NULL predicate.

Composite unique key

Include deletedAt in the unique key. Because deletedAt is NULL for active rows, only one NULL per email is allowed; a deleted row gets a timestamp and frees the email.

indexes: [{ unique: true, fields: ['email', 'deletedAt'] }]

Pros: works on MySQL. Cons: allows multiple soft-deleted rows with the same email, and NULL handling varies by DB.

Soft deletes also shift cost to reads: tables grow, and default queries always filter on deletedAt. Index deletedAt or use partial indexes at scale. Soft deletes are not compliance erasure; data subject to deletion requests may still need a true force: true removal.

Decide early whether you need email reuse. If yes, design the unique constraint for paranoid from the start, add the deletedAt column via migration, and test restore and force delete paths explicitly.