Transactions

Objectives

By the end of this lesson, you should be able to:

• Explain what a transaction is, and why multi-step operations need one
• Run a transaction with BEGIN, COMMIT, and ROLLBACK
• Roll back a transaction correctly when a step fails

💡 Why this matters: Some operations are really multiple queries that all need to succeed together, or not happen at all. A bank transfer is the classic example, take money from one account, and the same operation crashes halfway, one account loses money that never arrives anywhere.

⚠️ A note on verification: every snippet and every result shown below was actually run against a real PostgreSQL server.

The Problem Without a Transaction

await pool.query('UPDATE accounts SET balance = balance - $1 WHERE name = $2', [200, 'Erin']);
// if the process crashes here, Erin's money is just gone
await pool.query('UPDATE accounts SET balance = balance + $1 WHERE name = $2', [200, 'Jordan']);

If anything goes wrong between those two queries, network failure, a bug, a crashed process, the first update already happened and the second never will. The database is left in an inconsistent state.

Running a Transaction

A transaction wraps multiple queries so they all commit together, or none of them do. It needs a single client, checked out from the pool (Module 1, Lesson 4), since every query in the transaction must run on the same connection:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE name = $2', [200, 'Erin']);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE name = $2', [200, 'Jordan']);
  await client.query('COMMIT');
  console.log('Transfer committed');
} catch (err) {
  await client.query('ROLLBACK');
  console.log('Transfer rolled back', err.message);
} finally {
  client.release();
}

Starting from Erin: 500, Jordan: 100:

const result = await pool.query('SELECT name, balance FROM accounts ORDER BY name');
console.log(result.rows);

Transfer committed
[ { name: 'Erin', balance: 300 }, { name: 'Jordan', balance: 300 } ]

Rolling Back on Failure

The real value shows up when something goes wrong mid-transaction. Here, a check runs after the first update, and finds a business rule was violated:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE name = $2', [1000, 'Erin']);

  const check = await client.query('SELECT balance FROM accounts WHERE name = $1', ['Erin']);
  if (check.rows[0].balance < 0) {
    throw new Error('Insufficient funds');
  }

  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  console.log('Rolled back:', err.message);
} finally {
  client.release();
}

const after = await pool.query('SELECT name, balance FROM accounts ORDER BY name');
console.log(after.rows);

Rolled back: Insufficient funds
[ { name: 'Erin', balance: 300 }, { name: 'Jordan', balance: 300 } ]

Even though the UPDATE already ran and subtracted 1000 from Erin’s balance, ROLLBACK undoes it completely, the balances end up exactly where they were before the transaction started, as if the failed attempt never happened at all.

The Pattern

Every transaction in this course, and in a real application, follows the same shape: check out a client, BEGIN, run the queries, COMMIT on success, ROLLBACK in a catch block on any failure, and always release() the client in a finally block so it returns to the pool either way.

Try It

1. Build the transfer example above, and confirm the balances after a successful transfer.
2. Build the failing example, and confirm the balances are completely unchanged after the rollback.
3. Remove the try/catch/ROLLBACK entirely, force an error partway through a transaction, and observe what state the data is left in. Explain why this is the exact problem transactions solve.
4. Add a third query to the successful transfer (for example, inserting a row into a transfer_log table), and confirm it commits together with the two balance updates.

Recap

• A transaction groups multiple queries so they all commit together, or none of them do.
• BEGIN starts one, COMMIT saves every change made since, ROLLBACK undoes every change made since, as if none of it happened.
• Transactions run on a single checked-out client (pool.connect()), never on pool.query() directly, since every step must share the same connection.

This is the final lesson of this module before exercises. Next module: introducing an ORM, and setting up Prisma on top of everything built so far.