Soft delete

[levani]

I'm developing an application where deleting records always means performing a soft delete instead of a hard delete. Typically, this involves adding a deleted_at timestamp or an is_deleted boolean flag to mark records as deleted while keeping them in the database.

However, I couldn't find much documentation or examples on how to properly implement soft deletes in Drizzle ORM. Are there any built-in mechanisms for this, or do we need to manually filter out deleted records in every query?

I'd appreciate any guidance or best practices for handling soft deletes efficiently with Drizzle.
Thanks! 🚀

[mentamarindos]

I landed here searching for the same thing... So far, I can tell that no progress has been made regarding this matter, and it doesn't look like any promises have been made for the future, although there are lots of related discussions:

hooks:

• [FEATURE]: Hooks like one's the lucid orm has #2266
• Hooks "on.before-query", "on.select-row" etc #1426
• [FEATURE]: Prisma's updatedAt behavior. onUpdate API suggestion #956
• [FEATURE]: CREATE TRIGGER functions #843

middlewares:

• Proposal for Extending `schema.ts` in Drizzle ORM with Runtime Middlewares #1513

signals

• [FEATURE]: Pre & Post Save Signals #755

[donywan]

+1

[kareem717]

+1

[weequan93]

interesting +1

[KevinMind]

You can do something like this

const timestamps = {
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  deletedAt: timestamp('deleted_at'),
};

export const users = pgTable("users", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar({ length: 255 }).notNull(),
  ...timestamps,
});

Now you can query for not deleted users.

const query = await db.select().from(users).where(isNull(users.deletedAt)).limit(10);

I added a query builder function that allows you to abstract this

export function notDeleted<
  T extends PgSelect,
  Schema extends TableWithDeletedAt
>(qb: T, table: Schema): T {
  return qb.where(isNull(table.deletedAt));
}

const query = db.select().from(users).$dynamic();
const results = await notDeleted(query, users);

This sorta works, but the type still returns Date | null for the field.. bummer. I would love to be able to do something more proper like defining a base model with methods and such...

[yooouuri]

Nice solution!

I don't think Drizzle should support this out of the box, this approach is perfectly fine!

[fenilsonani]

+1

[hpandelo]

My solution also add the username of who did the operation (for audit purpose)

export const timestamped = {
    createdAt: timestamp('created_at').notNull().defaultNow(),
    createdBy: varchar('created_by', { length: 50 }).notNull(),

    updatedAt: timestamp('updated_at').notNull().defaultNow().$onUpdate(() => new Date()),
    updatedBy: varchar('updated_by', { length: 50 }).notNull(),

    deletedAt: timestamp('deleted_at'),
    deletedBy: varchar('deleted_by', { length: 50 }),
};