Run an application transaction

You know transactions from the SQL side: BEGIN, some statements, COMMIT. From application code there is one extra rule that changes everything.

A transaction belongs to one connection. BEGIN starts it on whichever connection carried that statement, and only statements on that same connection are inside it. A pool, by design, spreads queries across connections. Those two facts collide.

Check out one client and use it for every statement:

const client = await pool.connect()

try {
  await client.query('BEGIN')

  const note = await client.query(
    'INSERT INTO app.notes (title) VALUES ($1) RETURNING id',
    ['Plan the week']
  )

  await client.query(
    'INSERT INTO app.note_tags (note_id, tag_id) VALUES ($1, $2)',
    [note.rows[0].id, 1]
  )

  await client.query('COMMIT')
} catch (error) {
  await client.query('ROLLBACK')
  throw error
} finally {
  client.release()
}

pool.connect() reserves one connection for you until you release it. Every client.query() rides that connection, so all four statements share the transaction.

The shape of the try/catch/finally is not boilerplate to trim. COMMIT on success. ROLLBACK on any error, so the connection goes back to the pool clean instead of carrying a broken transaction to its next borrower. And client.release() in finally, so the connection returns even when things go wrong.

The mistake that looks fine in testing

Do not call pool.query() inside this transaction. The pool may choose another connection. That statement then runs outside the transaction: it can commit on its own while your transaction rolls back, or read data your uncommitted transaction cannot see. Under low traffic the pool often reuses the same connection, so the bug hides through development and appears under production load.

The other classic failure is forgetting release() on some code path. Each forgotten client shrinks the pool by one until requests start timing out waiting for a connection.

Verify from another session

While the code between BEGIN and COMMIT runs, the server shows the reserved connection:

SELECT pid, state, query
FROM pg_stat_activity
WHERE application_name = 'notes-web';

A row with state idle in transaction is a transaction that is open while the application does non-database work. Brief is normal. Minutes long means some code path forgot to commit, roll back, or release, and it is holding locks the whole time.