Run database transactions correctly with node-postgres

Approach

A transaction must run on one client checked out from the pool. Write a withTransaction helper that does BEGIN, runs your callback with that client, COMMITs, ROLLBACKs on error and always releases the client. Lock rows in a consistent order (SELECT ... FOR UPDATE) to avoid deadlocks, and retry serialization failures.

Solution

import { Pool, type PoolClient } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export async function withTransaction<T>(fn: (client: PoolClient) => Promise<T>, retries = 2): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err: any) {
    await client.query("ROLLBACK");
    if (retries > 0 && ["40001", "40P01"].includes(err.code)) return withTransaction(fn, retries - 1);
    throw err;
  } finally {
    client.release();
  }
}

export function transfer(fromId: string, toId: string, cents: number) {
  return withTransaction(async (c) => {
    const [first, second] = [fromId, toId].sort();               // consistent lock order
    await c.query("SELECT id FROM accounts WHERE id = ANY($1) ORDER BY id FOR UPDATE", [[first, second]]);
    const { rowCount } = await c.query(
      "UPDATE accounts SET balance_cents = balance_cents - $2 WHERE id = $1 AND balance_cents >= $2", [fromId, cents]);
    if (rowCount === 0) throw new Error("Insufficient funds");
    await c.query("UPDATE accounts SET balance_cents = balance_cents + $2 WHERE id = $1", [toId, cents]);
  });
}

Pitfalls

• pool.query('BEGIN') then pool.query(...) — each may use a different connection!
• Forgetting client.release() (pool exhaustion).
• Calling external APIs inside the transaction.

Variations

• ORMs: Prisma $transaction, Drizzle db.transaction, Knex knex.transaction.

Further reading: node-postgres: Transactions