Transactions

Execute multiple operations atomically with transaction support.

Basic Usage

await db.transaction(async (tx) => {
  // All operations in this block are atomic
  await tx.insert(users).values({ name: 'Alice', email: 'alice@example.com' });
  await tx
    .update(accounts)
    .set({ balance: sql`balance - 100` })
    .where(eq(accounts.userId, 1));
  await tx
    .update(accounts)
    .set({ balance: sql`balance + 100` })
    .where(eq(accounts.userId, 2));
});

If any operation fails, all changes are rolled back.

Pooling & Transactions

When you create a database with connection pooling (drizzle(':memory:', { pool: { size: 4 } }) or the async connection-string form), transactions automatically pin a single pooled connection for their entire lifetime. BEGIN, all queries in the callback, and COMMIT/ROLLBACK run on that one connection to keep the transaction atomic. No extra configuration is required; pooling is still used for non-transactional queries.

With Return Value

const newUser = await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(users)
    .values({ name: 'Alice', email: 'alice@example.com' })
    .returning();

  await tx.insert(profiles).values({ userId: user.id, bio: 'Hello!' });

  return user;
});

console.log(newUser.id);

Manual Rollback

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'Alice' });

  const balance = await tx
    .select({ balance: accounts.balance })
    .from(accounts)
    .where(eq(accounts.userId, 1));

  if (balance[0].balance < 100) {
    tx.rollback(); // Aborts the entire transaction
    return;
  }

  await tx
    .update(accounts)
    .set({ balance: sql`balance - 100` })
    .where(eq(accounts.userId, 1));
});

Error Handling

try {
  await db.transaction(async (tx) => {
    await tx
      .insert(users)
      .values({ name: 'Alice', email: 'alice@example.com' });
    await tx.insert(users).values({ name: 'Bob', email: 'alice@example.com' }); // Duplicate email
  });
} catch (error) {
  // Transaction rolled back automatically
  console.error('Transaction failed:', error.message);
}

Important Limitation: No Savepoints

DuckDB Limitation

DuckDB 1.4.x does not support SAVEPOINT. The driver will try once per dialect instance; after the syntax error it marks savepoints unsupported and nested calls reuse the outer transaction.

What Happens with Nested Transactions

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'Alice' });

  // This "nested" transaction actually reuses the outer transaction
  await tx.transaction(async (innerTx) => {
    await innerTx.insert(users).values({ name: 'Bob' });

    // This rollback aborts THE ENTIRE TRANSACTION
    innerTx.rollback();
  });
});

// Result: Neither Alice nor Bob are inserted!

Workarounds

Option 1: Avoid nested transactions

// Don't do this
await db.transaction(async (tx) => {
  await tx.transaction(async (innerTx) => { ... });
});

// Do this instead
await db.transaction(async (tx) => {
  // Keep everything at one level
  await tx.insert(users).values({ name: 'Alice' });
  await tx.insert(users).values({ name: 'Bob' });
});

Option 2: Handle errors without rolling back

await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'Alice' });

  try {
    // This might fail
    await tx.insert(users).values({ name: 'Bob', email: duplicateEmail });
  } catch (error) {
    // Log but don't rollback - Alice is still inserted
    console.error('Failed to insert Bob:', error.message);
  }
});

Option 3: Use separate transactions

// First transaction
await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: 'Alice' });
});

// Second transaction (independent)
try {
  await db.transaction(async (tx) => {
    await tx.insert(users).values({ name: 'Bob' });
  });
} catch (error) {
  // Only Bob's transaction failed, Alice is committed
}

Transaction Patterns

All-or-Nothing

async function transferFunds(fromId: number, toId: number, amount: number) {
  await db.transaction(async (tx) => {
    // Deduct from source
    const [from] = await tx
      .update(accounts)
      .set({ balance: sql`balance - ${amount}` })
      .where(eq(accounts.id, fromId))
      .returning();

    if (from.balance < 0) {
      tx.rollback();
      throw new Error('Insufficient funds');
    }

    // Add to destination
    await tx
      .update(accounts)
      .set({ balance: sql`balance + ${amount}` })
      .where(eq(accounts.id, toId));
  });
}

Idempotent Operations

async function ensureUserExists(email: string, name: string) {
  return await db.transaction(async (tx) => {
    const existing = await tx
      .select()
      .from(users)
      .where(eq(users.email, email));

    if (existing.length > 0) {
      return existing[0];
    }

    const [newUser] = await tx
      .insert(users)
      .values({ email, name })
      .returning();

    return newUser;
  });
}

Batch with Validation

async function createOrderWithItems(
  userId: number,
  items: Array<{ productId: number; quantity: number }>
) {
  return await db.transaction(async (tx) => {
    // Validate all products exist and have stock
    for (const item of items) {
      const [product] = await tx
        .select()
        .from(products)
        .where(eq(products.id, item.productId));

      if (!product) {
        tx.rollback();
        throw new Error(`Product ${item.productId} not found`);
      }

      if (product.stock < item.quantity) {
        tx.rollback();
        throw new Error(`Insufficient stock for ${product.name}`);
      }
    }

    // Create order
    const [order] = await tx
      .insert(orders)
      .values({ userId, status: 'pending' })
      .returning();

    // Create order items and update stock
    for (const item of items) {
      await tx.insert(orderItems).values({
        orderId: order.id,
        productId: item.productId,
        quantity: item.quantity,
      });

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

    return order;
  });
}