Soft deletes in Next.js App Router with Drizzle ORM -- keep records without losing them

In most SaaS products, you never truly delete a record. You mark it as deleted and filter it from queries. This pattern -- called a soft delete -- lets you recover records, audit history, and keep foreign key references intact without touching your schema constraints.

Here is how to implement it cleanly in a Next.js App Router project with Drizzle ORM.

Add a deletedAt column

Add a nullable deleted_at timestamp to any table you want to soft-delete. Include a partial index at the same time -- it keeps active-record queries fast as the table grows:

// modules/post/post.schema.ts
import { pgTable, text, timestamp, uuid, index } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
 
export const postTable = pgTable(
  'posts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    title: text('title').notNull(),
    content: text('content').notNull(),
    authorId: uuid('author_id').notNull(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
    deletedAt: timestamp('deleted_at'),
  },
  (t) => [
    index('posts_active_idx').on(t.id).where(sql`${t.deletedAt} IS NULL`),
  ]
);

The partial index tells Postgres to only index rows where deleted_at is null, so list queries scan only active records.

Run the migration after the schema change:

npm run db:generate && npm run db:migrate

Repository layer

Every read query filters deleted records by default. Expose a separate helper only when you need to see soft-deleted rows -- for example, when restoring a record:

// modules/post/post.repo.ts
import { db } from '@/db/drizzle';
import { postTable } from './post.schema';
import { eq, isNull, and } from 'drizzle-orm';
import type { Post } from './post.types';
 
export const postRepo = {
  findAll(): Promise<Post[]> {
    return db
      .select()
      .from(postTable)
      .where(isNull(postTable.deletedAt));
  },
 
  findById(id: string): Promise<Post | undefined> {
    return db
      .select()
      .from(postTable)
      .where(and(eq(postTable.id, id), isNull(postTable.deletedAt)))
      .then((rows) => rows[0]);
  },
 
  findByIdIncludeDeleted(id: string): Promise<Post | undefined> {
    return db
      .select()
      .from(postTable)
      .where(eq(postTable.id, id))
      .then((rows) => rows[0]);
  },
 
  softDelete(id: string): Promise<void> {
    return db
      .update(postTable)
      .set({ deletedAt: new Date() })
      .where(eq(postTable.id, id))
      .then(() => undefined);
  },
 
  restore(id: string): Promise<void> {
    return db
      .update(postTable)
      .set({ deletedAt: null })
      .where(eq(postTable.id, id))
      .then(() => undefined);
  },
};

Service layer

The service enforces ownership before touching any record. Restoration uses findByIdIncludeDeleted because the standard findById filters deleted rows out:

// modules/post/post.service.ts
import { postRepo } from './post.repo';
import { HttpError } from '@/lib/errors';
 
export const postService = {
  async delete(id: string, requesterId: string): Promise<void> {
    const post = await postRepo.findById(id);
    if (!post) throw new HttpError(404, 'Post not found');
    if (post.authorId !== requesterId) throw new HttpError(403, 'Forbidden');
    await postRepo.softDelete(id);
  },
 
  async restore(id: string, requesterId: string): Promise<void> {
    const post = await postRepo.findByIdIncludeDeleted(id);
    if (!post) throw new HttpError(404, 'Post not found');
    if (post.authorId !== requesterId) throw new HttpError(403, 'Forbidden');
    await postRepo.restore(id);
  },
};

Route handlers

Both the delete and restore routes follow the same thin shape -- extract user, call service, return response:

// app/api/posts/[id]/route.ts
import { NextResponse, type NextRequest } from 'next/server';
import { getUserFromRequest } from '@/lib/auth';
import { postService } from '@/modules/post';
import { handleError } from '@/lib/errors';
 
export async function DELETE(
  req: NextRequest,
  { params }: { params: { id: string } }
) {
  try {
    const user = await getUserFromRequest(req);
    await postService.delete(params.id, user.id);
    return NextResponse.json({ success: true });
  } catch (error: unknown) {
    return handleError(error);
  }
}

// app/api/posts/[id]/restore/route.ts
import { NextResponse, type NextRequest } from 'next/server';
import { getUserFromRequest } from '@/lib/auth';
import { postService } from '@/modules/post';
import { handleError } from '@/lib/errors';
 
export async function POST(
  req: NextRequest,
  { params }: { params: { id: string } }
) {
  try {
    const user = await getUserFromRequest(req);
    await postService.restore(params.id, user.id);
    return NextResponse.json({ success: true });
  } catch (error: unknown) {
    return handleError(error);
  }
}

SWR hook for the delete action

On the client side, wire up useSWRMutation to call the delete endpoint and invalidate the list cache:

// hooks/api/usePosts.ts
import useSWRMutation from 'swr/mutation';
import { deleter } from '@/lib/fetcher';
import { useSWRConfig } from 'swr';
 
export function useDeletePost(id: string) {
  const { mutate } = useSWRConfig();
  return useSWRMutation(`/api/posts/${id}`, deleter, {
    onSuccess: () => mutate('/api/posts'),
  });
}

Calling trigger() from this hook sends the DELETE request and refreshes the post list -- the deleted item disappears from the UI immediately because the server now filters it out.

What the partial index does

Without the index, every WHERE deleted_at IS NULL query does a full table scan. With a partial index, Postgres maintains a separate, smaller index that only includes active rows. At 10k rows it makes no difference; at 500k rows it is the difference between a 2 ms query and a 200 ms query.

You can confirm the index exists in Drizzle Studio or by running:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'posts';

The takeaway

Three things to get right with soft deletes in Drizzle ORM:

• Add isNull(table.deletedAt) to every read query in the repo by default -- never let deleted records leak into list views
• Keep the ownership check and soft-delete call in the service layer, not the route
• Add the partial index before the table has any real volume -- retrofitting it later requires a migration and a brief lock

This gives you recoverable data, a built-in audit trail, and no changes to your foreign key constraints or referential integrity.