Implementing Soft Deletions with Drizzle ORM and PostgreSQL

Soft deletion is a technique used in databases to mark records as deleted without actually removing them. This method is especially valuable in applications requiring audit trails, undo capabilities, or strict compliance with data retention policies.

The technique is typically implemented by adding a deleted_at timestamp column to your tables. Instead of executing a SQL DELETE operation, this field is updated to the current timestamp. While some high-level ORMs, such as Laravel’s Eloquent, provide native support for soft deletion, Drizzle ORM, at the time of writing, requires developers to implement it manually.

Implementation Guide

Let's walk through an example of implementing soft deletion using Drizzle ORM and TypeScript.

Define your Schema

To enable soft deletions in your database, you’ll need to add a deletedAt field to the relevant table. This field is typically a nullable timestamp that indicates when the row was marked as deleted. If the value is NULL, the row is considered active. The following code shows an example user entity:

schema.ts

export const users = pgTable(
  'users',
  {
    id: uuid().primaryKey().defaultRandom(),
    username: varchar({ length: 255 }).notNull(),
    deletedAt: timestamp(),
  },
  (table) => [
    uniqueIndex('users_username_idx')
      .on(sql`lower(${table.username})`)
      .where(sql`${table.deletedAt} IS NULL`),
  ],
);

Partial Indexes

One challenge with soft deletions is enforcing uniqueness. If a field (like username) must be unique, soft-deleted rows can interfere unless handled properly.

PostgreSQL supports partial indexes, which allow you to enforce uniqueness only when a condition is met. In this case, we only want to enforce uniqueness for rows where deleted_at is NULL. This ensures soft-deleted records don't violate unique constraints and allows users to re-register with the same username, for example.

It's important to note that the partial unique index above only covers active rows. Once a record is soft-deleted, it falls out of the index entirely. This means queries that filter by username on deleted records (e.g. an admin view of recently deleted accounts or an undo/restore flow) won't benefit from the index and may result in full table scans. If you need to query soft-deleted records by indexed columns, consider adding a second partial index with the inverse condition:

schema.ts

export const users = pgTable(
  'users',
  {
    // same as above
  },
  (table) => [
    uniqueIndex('users_username_idx')
      .on(sql`lower(${table.username})`)
      .where(sql`${table.deletedAt} IS NULL`),
    index('users_username_deleted_idx')
      .on(sql`lower(${table.username})`)
      .where(sql`${table.deletedAt} IS NOT NULL`),
  ],
);

Depending on your query patterns, a single non-partial index on username could also work, for example, if you have an admin search that needs to return users regardless of their deletion status. However, this would index all rows, meaning active users get indexed twice. Two partial indexes avoid this redundancy.

Example Queries

Deleting the entity

To soft-delete the user, we can simply update the deletedAt column to the current timestamp.

const deleteUser = async (id: string) => {
  const affected = await db
    .update(users)
    .set({ deletedAt: sql`now()` })
    .where(and(eq(users.id, id), isNull(users.deletedAt)))
    .returning({ id: users.id });

  if (affected.length === 0) {
    throw new Error('User not found')
  }
}

To permanently delete a record, you can require that the user be soft-deleted first:

const hardDeleteUser = async (id: string) => {
  const affected = await db
    .delete(users)
    .where(and(eq(users.id, id), isNotNull(users.deletedAt)))
    .returning({ id: users.id });

  if (affected.length === 0) {
    throw new Error('User not found')
  }
}

A common pattern is to schedule a background job that permanently removes soft-deleted records older than a specified number of days.

Selecting or Updating the entity

When querying for users, ensure soft-deleted records are excluded by checking that deletedAt is null:

const listUsers = async () => {
  return db.select().from(users).where(isNull(users.deletedAt));
}

Easier Querying with Views

To avoid repeating deleted_at IS NULL in your queries, you can create a view that abstracts this logic:

schema.ts

export const activeUsers = pgView("active_users").as((qb) =>
  qb.select().from(users).where(isNull(users.deletedAt)));

This simplifies your application logic and reduces the risk of accidentally including deleted records.

Conclusion

While Drizzle ORM does not offer soft deletion as a built-in feature, the implementation is still pretty straightforward. Combined with PostgreSQL Partial Indexes and Views, you can maintain clean and secure code.

Learn More

• Drizzle ORM: Documentation

• Drizzle ORM: (Partial) Indexes

• Drizzle ORM: Views

• PostgreSQL: Partial Indexes