Transactions
Run grouped statements on one borrowed client between BEGIN and COMMIT, and ROLLBACK if any of them fails.
const client = await pool.connect(); await client.query('BEGIN'); /* ... */ await client.query('COMMIT')Some changes only make sense together. Moving money between two accounts writes two rows, and if the second write fails the first must not survive. A transaction gives you that: a group of statements where either all of them take effect or none do.
Three commands do the work. BEGIN starts the transaction, COMMIT makes every change in it permanent, ROLLBACK discards them all. Until the commit, no other connection sees any of it.
How it works
A transaction belongs to one connection, so pool.query will not do: each call may land on a different connection from the pool. Borrow a client, run every statement on it, and release it in a finally block so it goes back on both the success and the failure path.
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query('UPDATE accounts SET cents = cents - $1 WHERE id = $2', [100, 'a']);
await client.query('UPDATE accounts SET cents = cents + $1 WHERE id = $2', [100, 'b']);
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}What trips people up
Keep transactions short. An open transaction holds locks on every row it has touched, and other requests wanting those rows queue behind it. Never call an HTTP API or wait on a person inside one.
After a failed statement Postgres aborts the whole transaction. Every further statement on that connection fails with current transaction is aborted until a ROLLBACK arrives, which is why the catch block rolls back before rethrowing.
Example
When to use it
- Insert an order and its line items so a mid-way failure leaves neither behind.
- Check a balance and deduct from it without two simultaneous requests overdrawing the account.
- Write a row and its audit-log entry as one unit.
- Keep a multi-table write off the pool's shared query path so every statement lands on one connection.
More examples
Two inserts where the second needs the first row's id
Without the transaction, a failure on the third item leaves an order with two of its lines, which no later request can detect. Use this shape whenever one logical write touches more than one table.
import { pool } from './db.js';
async function createOrder(userId, items) {
const client = await pool.connect();
try {
await client.query('BEGIN');
const order = await client.query(
'INSERT INTO orders (user_id) VALUES ($1) RETURNING id',
[userId],
);
const orderId = order.rows[0].id;
for (const item of items) {
await client.query(
'INSERT INTO order_items (order_id, sku, qty) VALUES ($1, $2, $3)',
[orderId, item.sku, item.qty],
);
}
await client.query('COMMIT');
return orderId;
} catch (err) {
await client.query('ROLLBACK');
throw err; // the caller turns this into a 500
} finally {
client.release();
}
}Locking a row so two requests cannot spend it twice
Read-then-write is only safe inside a transaction that locks the row it read. The lock is held until COMMIT or ROLLBACK, which is the strongest argument for keeping the block short.
import { pool } from './db.js';
// Two requests arriving at once would both read the old balance and both
// pass the check. FOR UPDATE makes the second one wait for the first.
async function withdraw(accountId, cents) {
const client = await pool.connect();
try {
await client.query('BEGIN');
const { rows } = await client.query(
'SELECT cents FROM accounts WHERE id = $1 FOR UPDATE',
[accountId],
);
if (rows.length === 0) throw new Error('no such account');
if (rows[0].cents < cents) throw new Error('insufficient funds');
await client.query(
'UPDATE accounts SET cents = cents - $1 WHERE id = $2',
[cents, accountId],
);
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}One helper so no caller writes BEGIN again
Hand-written try/catch/finally blocks are where a forgotten release creeps in. One helper means one place to get it right, and reviewers can see at a glance that a function taking a client is inside a transaction.
import { pool } from './db.js';
// Takes a function, hands it a client, and owns the BEGIN/COMMIT/ROLLBACK
// and the release. Every call site shrinks to the queries it cares about.
export async function withTransaction(fn) {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await fn(client);
await client.query('COMMIT');
return result;
} catch (err) {
await client.query('ROLLBACK');
throw err;
} finally {
client.release();
}
}
// Usage: note that every query goes to the client, never to the pool.
const id = await withTransaction(async (client) => {
const res = await client.query(
'INSERT INTO users (email) VALUES ($1) RETURNING id',
['[email protected]'],
);
await client.query(
'INSERT INTO audit (user_id, action) VALUES ($1, $2)',
[res.rows[0].id, 'signup'],
);
return res.rows[0].id;
});
Discussion