Database transactions in Next.js App Router with Drizzle ORM -- atomic multi-table writes for SaaS

When you create a user you often need to write to two tables at once: users and wallets, or users and orgMembers. If the first insert succeeds and the second fails, you have orphaned data. Drizzle ORM exposes PostgreSQL transactions through db.transaction() -- here is the pattern to use in a service layer.

The problem

In a service call like userService.register(), you might need to:

• Insert the user into users
• Create a default wallet in wallets
• Log the signup in auditLogs

If step 2 fails after step 1 succeeds, you have a user with no wallet. Subsequent API calls that assume the wallet exists will crash.

The fix: wrap all three writes in a transaction. If any step throws, Postgres rolls back all of them atomically.

Basic Drizzle transaction

// modules/user/user.service.ts
import { db } from "@/db/drizzle";
import { userTable, walletTable } from "@/db/schema";
 
export async function registerUser(email: string, passwordHash: string) {
  return db.transaction(async (tx) => {
    const [user] = await tx
      .insert(userTable)
      .values({ email, passwordHash })
      .returning();
 
    await tx.insert(walletTable).values({ userId: user.id, credits: 0 });
 
    return user;
  });
}

db.transaction() passes a tx object that shares the same connection and wraps all queries in BEGIN / COMMIT. Any unhandled throw triggers an automatic ROLLBACK.

Explicit rollback

Sometimes you want to roll back based on a business condition, not an exception. Use tx.rollback():

import { eq, sql } from "drizzle-orm";
 
export async function transferCredits(
  fromId: string,
  toId: string,
  amount: number
) {
  return db.transaction(async (tx) => {
    const [from] = await tx
      .select()
      .from(walletTable)
      .where(eq(walletTable.userId, fromId));
 
    if (from.credits < amount) {
      tx.rollback();
    }
 
    await tx
      .update(walletTable)
      .set({ credits: sql`${walletTable.credits} - ${amount}` })
      .where(eq(walletTable.userId, fromId));
 
    await tx
      .update(walletTable)
      .set({ credits: sql`${walletTable.credits} + ${amount}` })
      .where(eq(walletTable.userId, toId));
  });
}

tx.rollback() throws a special TransactionRollbackError that Drizzle catches and converts to a rollback -- it does not propagate to the caller as an unhandled error.

Passing tx down into the repository layer

When your service delegates DB writes to a repository, pass tx as an optional parameter instead of using the module-level db:

// modules/user/user.repo.ts
import { db } from "@/db/drizzle";
import type { PgTransaction } from "drizzle-orm/pg-core";
import type { NewUser } from "./user.types";
 
type Tx = PgTransaction<any, any, any>;
 
export async function insertUser(data: NewUser, tx?: Tx) {
  const client = tx ?? db;
  const [user] = await client.insert(userTable).values(data).returning();
  return user;
}

Then in the service:

// modules/user/user.service.ts
export async function registerUser(data: RegisterDto) {
  return db.transaction(async (tx) => {
    const user = await userRepo.insertUser(data, tx);
    await walletRepo.createWallet({ userId: user.id, credits: 0 }, tx);
    return user;
  });
}

This keeps repos independently testable while letting the service own the transaction boundary.

Savepoints (nested transactions)

If you need partial rollbacks within a transaction, Drizzle supports savepoints via nested tx.transaction() calls:

return db.transaction(async (tx) => {
  const [user] = await tx.insert(userTable).values(data).returning();
 
  // Attempt audit log -- do not fail the whole registration if it fails
  await tx
    .transaction(async (nested) => {
      await nested.insert(auditLogTable).values({
        userId: user.id,
        action: "register",
      });
    })
    .catch(() => {
      // savepoint rolled back; outer transaction continues
    });
 
  return user;
});

Use this sparingly. If the audit log is genuinely non-critical, insert it after the outer commit instead.

What belongs outside the transaction

Not every side effect should live inside db.transaction():

• Sending email -- Resend cannot be rolled back. If you hold the DB connection open waiting for the Resend API, you add latency and risk a timeout.
• Firing webhooks -- same reason.
• Single-table inserts -- one INSERT is already atomic without a transaction wrapper.

The right pattern: commit the DB writes first, then trigger side effects:

export async function registerUser(data: RegisterDto) {
  const user = await db.transaction(async (tx) => {
    const u = await userRepo.insertUser(data, tx);
    await walletRepo.createWallet({ userId: u.id, credits: 0 }, tx);
    return u;
  });
 
  // Outside the transaction -- runs after COMMIT
  await emailService.sendWelcomeEmail(user.email);
 
  return user;
}

If the email call fails, the user still exists in the DB. That is usually the right tradeoff -- you can retry the email; you cannot undo a failed rollback.

Takeaway

Wrap multi-table writes in db.transaction(), pass tx into repo functions so the transaction boundary stays in the service layer, and keep side effects like email and webhooks outside the commit block. That is the full pattern for atomic SaaS operations in Next.js App Router with Drizzle ORM -- no saga framework needed for the common case.