Drizzle Transactions

Drizzle Transactions

Execute atomic Drizzle operations with db.transaction(), nested transactions, and rollback semantics

When to Use

• Performing multiple database operations that must succeed or fail atomically
• Implementing business logic with read-then-write patterns
• Ensuring consistency when modifying related records
• Using savepoints for partial rollback within a larger transaction

Instructions

1. Basic transaction — pass an async callback:

const result = await db.transaction(async (tx) => {
  const [sender] = await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} - ${amount}` })
    .where(eq(accounts.id, senderId))
    .returning();

  if (sender.balance < 0) {
    tx.rollback(); // Rolls back the entire transaction
  }

  const [receiver] = await tx
    .update(accounts)
    .set({ balance: sql`${accounts.balance} + ${amount}` })
    .where(eq(accounts.id, receiverId))
    .returning();

  return { sender, receiver };
});

2. Use tx for all queries inside the callback. Using db instead of tx runs queries outside the transaction.

3. Rollback — call tx.rollback() or throw an error:

await db.transaction(async (tx) => {
  await tx.insert(orders).values({ userId, total });
  const inventory = await tx.query.products.findFirst({
    where: eq(products.id, productId),
  });

  if (!inventory || inventory.stock < quantity) {
    throw new Error('Insufficient stock'); // Triggers rollback
  }

  await tx
    .update(products)
    .set({ stock: sql`${products.stock} - ${quantity}` })
    .where(eq(products.id, productId));
});

4. Nested transactions (savepoints):

await db.transaction(async (tx) => {
  await tx.insert(orders).values(orderData);

  try {
    await tx.transaction(async (tx2) => {
      await tx2.insert(notifications).values(notifData);
      // If this fails, only the inner savepoint rolls back
    });
  } catch {
    // Order still committed; notification failed silently
  }
});

5. Transaction configuration (PostgreSQL):

await db.transaction(
  async (tx) => {
    // ...
  },
  {
    isolationLevel: 'serializable',
    accessMode: 'read write',
    deferrable: true,
  }
);

6. Batch operations in a transaction:

await db.transaction(async (tx) => {
  for (const item of items) {
    await tx
      .update(inventory)
      .set({ stock: sql`${inventory.stock} - ${item.qty}` })
      .where(eq(inventory.sku, item.sku));
  }
});

Details

Drizzle transactions use the underlying database driver's transaction support. The tx object is a transaction-scoped Drizzle client with the same query API as db.

tx.rollback(): This throws a special error that Drizzle catches to trigger rollback. Do not catch this error inside the transaction callback. The transaction function returns never after tx.rollback().

Nested transactions use savepoints. On PostgreSQL and MySQL, nested tx.transaction() calls translate to SAVEPOINT / ROLLBACK TO SAVEPOINT. SQLite does not support savepoints in all drivers.

Isolation levels (PostgreSQL):

• read uncommitted — rarely used, same as read committed on PostgreSQL
• read committed (default) — each statement sees committed data as of statement start
• repeatable read — all statements see committed data as of transaction start
• serializable — full serializability, detects and aborts conflicting transactions

Connection handling: A transaction holds one database connection for its entire duration. Keep transactions short to avoid connection pool exhaustion. Avoid external HTTP calls, file I/O, or long computations inside transactions.

Trade-offs:

• tx.rollback() uses exceptions for control flow, which can be surprising — do not wrap it in try/catch inside the transaction
• Serializable isolation prevents anomalies but may abort transactions under contention — implement retry logic
• Nested transactions (savepoints) add complexity — use them sparingly for optional side effects

Source

https://orm.drizzle.team/docs/transactions

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.