Transactions and Safe SQL

Table of Contents

1. The Half-Finished Checkout
2. ACID in Plain Words
3. BEGIN, COMMIT, ROLLBACK
4. A Safe Checkout
5. When Something Fails: Rollback in Action
6. Race Conditions and FOR UPDATE
7. Isolation Levels in Brief
8. SQL Injection
9. Parameterized Queries with Node.js
10. A Checkout Script with a Transaction
11. Least Privilege: Roles and GRANT
12. Backups with pg_dump
13. Wrap-Up: What You Built
14. Try It Yourself

1. The Half-Finished Checkout

A customer clicks "Place order". Behind that click, your code must do three things:

1. Create a row in orders.
2. Insert a row in order_items for each product bought.
3. Subtract the quantities from products.stock.

Each is a separate SQL statement. Now imagine the server loses power after step 2 and before step 3. The order exists, the customer was charged, but the stock was never reduced. You will sell the same last keyboard twice. Or the reverse: stock decreased but the order was never saved, and the customer gets nothing.

Any time one logical action needs several statements, there is a window where the data is half-changed. A transaction closes that window. It groups statements so that they either all happen or none do.

2. ACID in Plain Words

Relational databases promise four properties for transactions, remembered as ACID:

The constraints you wrote in chapters 3 and 6 are the "C". This chapter is mostly about "A" and "I".

3. BEGIN, COMMIT, ROLLBACK

Until now, every statement you typed in psql ran in its own tiny automatic transaction (called autocommit). To group several, you open one yourself:

BEGIN;
-- ... several statements ...
COMMIT;      -- make everything permanent

or, to throw everything away:

BEGIN;
-- ... several statements ...
ROLLBACK;    -- undo everything since BEGIN

Try it on shopdb. This is safe because we roll back:

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
SELECT id, name, stock FROM products WHERE id = 1;
ROLLBACK;
SELECT id, name, stock FROM products WHERE id = 1;

 id |      name      | stock
----+----------------+-------
  1 | Wireless Mouse |    39
(1 row)

 id |      name      | stock
----+----------------+-------
  1 | Wireless Mouse |    40
(1 row)

Inside the transaction you saw 39. After ROLLBACK, it is back to 40, as if nothing happened. Other sessions never saw the 39 at all.

While a transaction is open, psql shows a * in the prompt (shopdb=*#). After an error inside a transaction, the prompt shows ! (shopdb=!#), meaning the transaction is aborted, and the only way forward is ROLLBACK.

4. A Safe Checkout

Let us write the real checkout. Vuthy (customer 4) wants 2 Coffee Mugs (product 7, price 6.00) and 1 Notebook Pack (product 8, price 9.50). Save this as shopdb/queries/checkout.sql:

BEGIN;

-- 1. Create the order
INSERT INTO orders (customer_id, status)
VALUES (4, 'paid');

-- 2. Add the items, copying the current price as a snapshot (ch. 10)
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT currval(pg_get_serial_sequence('orders', 'id')),
       p.id, v.quantity, p.price
FROM (VALUES (7, 2), (8, 1)) AS v(product_id, quantity)
JOIN products p ON p.id = v.product_id;

-- 3. Reduce stock
UPDATE products SET stock = stock - 2 WHERE id = 7;
UPDATE products SET stock = stock - 1 WHERE id = 8;

COMMIT;

Two things to note. currval(pg_get_serial_sequence('orders', 'id')) returns the id that the INSERT just generated in this session, so you never hard-code an order number. And the VALUES list joined to products lets us pull each price from the table, so the snapshot cannot be mistyped.

Run it and check the result:

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  < queries/checkout.sql

SELECT id, name, stock FROM products WHERE id IN (7, 8);
SELECT * FROM order_items WHERE order_id = 6;

 id |     name     | stock
----+--------------+-------
  7 | Coffee Mug   |    78
  8 | Notebook Pack|    59
(2 rows)

 order_id | product_id | quantity | unit_price
----------+------------+----------+------------
        6 |          7 |        2 |       6.00
        6 |          8 |        1 |       9.50
(2 rows)

(Typical output, assuming you started from the seed data so this is the sixth order.) Because everything sat in one transaction, no observer can ever see an order without its items or an item without its stock change.

5. When Something Fails: Rollback in Action

Now the important part. Maly (customer 5) wants the Desk Lamp, product 6. Its stock is 0. Our CHECK (stock >= 0) constraint should block it:

BEGIN;

INSERT INTO orders (customer_id, status) VALUES (5, 'paid');

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (currval(pg_get_serial_sequence('orders', 'id')), 6, 1, 22.00);

UPDATE products SET stock = stock - 1 WHERE id = 6;

BEGIN
INSERT 0 1
INSERT 0 1
ERROR:  new row for relation "products" violates check constraint "products_stock_check"
DETAIL:  Failing row contains (6, Desk Lamp, 22.00, -1, 3).

The first two inserts worked. The third statement failed. Now the transaction is aborted, and psql refuses everything until you end it:

SELECT count(*) FROM orders;

ERROR:  current transaction is aborted, commands ignored until end of transaction block

ROLLBACK;
SELECT count(*) FROM orders;

ROLLBACK
 count
-------
     6
(1 row)

Six orders: the five from the seed plus Vuthy's. Maly's half-created order is gone, along with its item. The database is exactly as it was before BEGIN. That is atomicity, with the CHECK constraint acting as the guard: it refused to oversell, and the transaction undid everything else.

One small curiosity: the failed attempt still used up an order id. The next successful order will be 7, not 6. Identity sequences never roll back, and gaps in ids are normal and harmless.

If you run a script with psql -f and want it to stop at the first error rather than plowing on, add -v ON_ERROR_STOP=1:

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  -v ON_ERROR_STOP=1 < queries/checkout.sql

6. Race Conditions and FOR UPDATE

Transactions protect you from failures. They do not, by themselves, protect you from two customers acting at the same time. Consider the last Mechanical Keyboard (suppose its stock is 1). Two customers click buy within the same second. A naive program does:

1. Read the stock: SELECT stock FROM products WHERE id = 3.
2. If stock is at least 1, UPDATE products SET stock = stock - 1.

Both sessions read 1 at step 1, both decide it is fine, both go ahead. Without the CHECK constraint, you would sell two keyboards you do not have. This is a race condition: the result depends on the timing.

(In this particular case, stock = stock - 1 plus our CHECK actually saves you, since the second update fails. But the pattern of "read a value, decide in your code, then write" is dangerous for anything without such a guard.)

The fix is to lock the row while you decide, using SELECT ... FOR UPDATE:

BEGIN;

SELECT stock FROM products WHERE id = 3 FOR UPDATE;
-- Row 3 is now locked. Other transactions that try to
-- UPDATE it or SELECT ... FOR UPDATE it will wait here.

-- Your code checks the stock and decides.
UPDATE products SET stock = stock - 1 WHERE id = 3;

COMMIT;   -- releases the lock

You can watch it with two psql windows. In window A run BEGIN; then the SELECT ... FOR UPDATE. In window B run the same pair. Window B hangs at its SELECT, waiting. When you COMMIT in window A, window B immediately continues and sees the new stock value. The two checkouts are now forced to happen one after the other.

Keep these locking habits:

• Keep transactions short. Locks are held until COMMIT or ROLLBACK.
• Never wait for a user (a payment page, a prompt) while a transaction is open.
• Lock rows in a consistent order (for example by product id) so two transactions do not wait on each other forever, a situation called a deadlock. PostgreSQL detects it and cancels one of them with an error, and your app should be ready to retry.

7. Isolation Levels in Brief

How much should one transaction see of another's work? That is the isolation level. PostgreSQL offers three that matter:

(PostgreSQL also accepts READ UNCOMMITTED, but treats it as READ COMMITTED: it never shows you uncommitted data.)

You choose per transaction:

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- reports that need a consistent view of the data
COMMIT;

For most application code the default plus careful use of FOR UPDATE is right. Reach for the higher levels when you generate a report across many tables and need every query to see the same moment, or when the logic is too complex to lock by hand, and remember that code using them must be ready to retry on a serialization failure.

8. SQL Injection

Now the other half of "safe SQL": protecting the database from the people using your application.

Imagine your Node.js server looks up a customer by email. A natural way to write it, and a terrible one:

// DO NOT DO THIS
const email = req.query.email;   // comes from the user
const sql = "SELECT * FROM customers WHERE email = '" + email + "'";
const result = await db.query(sql);

With a friendly input, dara@example.com, the string becomes:

SELECT * FROM customers WHERE email = 'dara@example.com'

Fine. Now a user types this as the email:

' OR '1'='1

and the SQL becomes:

SELECT * FROM customers WHERE email = '' OR '1'='1'

'1'='1' is always true, so the query returns every customer. The attacker changed the meaning of your query because their text was pasted straight into the SQL. This is SQL injection, one of the oldest and still most damaging web vulnerabilities.

It gets worse. An input like '; DROP TABLE customers; -- can try to end your statement and start a new one, and the -- comments out the rest of your SQL. Whether that exact attack works depends on the driver, but the lesson holds: if user text is part of the SQL string, the user is writing your SQL.

Never build SQL by concatenating or interpolating (${email}) user input. Not for emails, not for ids, not for sort columns. There is no safe amount.

9. Parameterized Queries with Node.js

The fix is a parameterized query (also called a prepared statement or bind variables). You send the SQL text and the values separately. The database parses the SQL first with placeholders ($1, $2), then plugs the values in as pure data, so a value can never become SQL.

Set it up in the project folder. The pg package is the standard PostgreSQL client for Node.js:

cd shopdb
npm init -y
npm install pg

Safe version of the lookup, saved as shopdb/find-customer.mjs:

import pg from "pg";

const pool = new pg.Pool({
  host: "localhost",
  port: 5432,
  user: "postgres",
  password: "postgres",
  database: "shopdb",
});

const email = process.argv[2];   // untrusted input

const result = await pool.query(
  "SELECT id, name, email FROM customers WHERE email = $1",
  [email]
);

console.log(result.rows);
await pool.end();

node find-customer.mjs dara@example.com

[ { id: 2, name: 'Dara Kim', email: 'dara@example.com' } ]

Now try the attack:

node find-customer.mjs "' OR '1'='1"

[]

An empty array. The whole attack string was treated as one literal email that nobody has, because the driver sent it as a value, never as SQL.

Rules for parameters:

This is the single most important habit for any developer who talks to a database. The planned security series on this site covers injection and the rest of the OWASP Top 10 more broadly.

10. A Checkout Script with a Transaction

Now combine both ideas: a transaction and parameters, in Node.js. Because a Pool hands out different connections per query, a transaction needs one dedicated client. Save as shopdb/checkout.mjs:

import pg from "pg";

const pool = new pg.Pool({
  host: "localhost",
  port: 5432,
  user: "postgres",
  password: "postgres",
  database: "shopdb",
});

async function checkout(customerId, items) {
  const client = await pool.connect();   // one connection for the whole txn
  try {
    await client.query("BEGIN");

    const order = await client.query(
      "INSERT INTO orders (customer_id, status) VALUES ($1, 'paid') RETURNING id",
      [customerId]
    );
    const orderId = order.rows[0].id;

    for (const { productId, quantity } of items) {
      // Lock the product row so two checkouts cannot race
      const product = await client.query(
        "SELECT price, stock FROM products WHERE id = $1 FOR UPDATE",
        [productId]
      );
      if (product.rows.length === 0) {
        throw new Error("Unknown product " + productId);
      }
      const { price, stock } = product.rows[0];
      if (stock < quantity) {
        throw new Error("Not enough stock for product " + productId);
      }

      await client.query(
        `INSERT INTO order_items (order_id, product_id, quantity, unit_price)
         VALUES ($1, $2, $3, $4)`,
        [orderId, productId, quantity, price]
      );
      await client.query(
        "UPDATE products SET stock = stock - $1 WHERE id = $2",
        [quantity, productId]
      );
    }

    await client.query("COMMIT");
    return orderId;
  } catch (err) {
    await client.query("ROLLBACK");      // undo everything
    throw err;
  } finally {
    client.release();                    // always give the connection back
  }
}

try {
  const id = await checkout(3, [
    { productId: 1, quantity: 1 },
    { productId: 6, quantity: 1 },       // Desk Lamp, stock is 0
  ]);
  console.log("Created order", id);
} catch (err) {
  console.error("Checkout failed:", err.message);
} finally {
  await pool.end();
}

Run it:

node checkout.mjs

Checkout failed: Not enough stock for product 6

The mouse was added to the order before the lamp failed, but the ROLLBACK in the catch block removed it, and the stock of product 1 is untouched. Change the lamp to product 5 (Clean Code) and run again to see an order created. The pattern is always the same: BEGIN, work, COMMIT, with ROLLBACK on any error, and release() in finally.

The FOR UPDATE here makes the "check stock, then subtract" logic safe against the race from section 6, and every value travels as a parameter.

11. Least Privilege: Roles and GRANT

So far everything connected as postgres, the all-powerful superuser. If your web app connects as postgres, then one injection bug lets an attacker drop every table. The principle of least privilege says: give each program only the permissions it needs.

Create a role for the application with a login and limited rights:

CREATE ROLE app_user LOGIN PASSWORD 'change_me_locally';

GRANT CONNECT ON DATABASE shopdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Read-only on reference data
GRANT SELECT ON categories, products, customers TO app_user;

-- The checkout needs these
GRANT INSERT ON orders, order_items TO app_user;
GRANT UPDATE (stock) ON products TO app_user;
GRANT SELECT ON orders, order_items TO app_user;

-- Identity columns use sequences
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

Notice GRANT UPDATE (stock) ON products: the app can change only the stock column, not prices. The password above is a throwaway for your local container. In a real deployment, store secrets in environment variables or a secrets manager, never in your code or repository.

Test it by connecting as that role:

docker exec -it shopdb-pg psql -U app_user -d shopdb

shopdb=> DROP TABLE customers;
ERROR:  must be owner of table customers

shopdb=> DELETE FROM orders;
ERROR:  permission denied for table orders

The same bug that would have been a disaster as postgres is now blocked by the database itself. Have your Node.js script connect as app_user (it needs SELECT on products plus the grants above, which we gave). Admin work such as migrations should use a separate, more powerful role.

To remove the privileges later, REVOKE mirrors GRANT.

12. Backups with pg_dump

Transactions protect you from failures inside the database. They do not protect you from a deleted disk, a DROP TABLE that was committed, or a bad migration. For that you need backups.

pg_dump writes the entire database as a SQL script:

docker exec shopdb-pg pg_dump -U postgres shopdb > shopdb-backup.sql

The output file contains CREATE TABLE and INSERT-style statements. To restore into a fresh database:

docker exec shopdb-pg psql -U postgres -c "CREATE DATABASE shopdb_restored;"
docker exec -i shopdb-pg psql -U postgres -d shopdb_restored \
  < shopdb-backup.sql

Good backup habits:

• Automate them (a scheduled job), and keep copies off the machine running the database.
• A backup you have never restored is a hope, not a backup. Test the restore.
• Back up before any risky migration.
• Managed database services offer automatic backups and point-in-time recovery. That is a big reason people pay for them.

13. Wrap-Up: What You Built

Look at what you have after twelve chapters:

• A PostgreSQL database in Docker that you can rebuild from schema.sql and seed.sql with a single command.
• A well-designed schema: five tables, keys, constraints, and indexes on the foreign keys.
• A library of queries: filters, joins, aggregates, CTEs, window functions, views.
• A safe checkout: an atomic transaction with row locking, called from Node.js with parameterized queries, running under a limited role.
• Backups, and the vocabulary to reason about normalization, indexes, and concurrency.

A short recap of the habits that matter most:

14. Try It Yourself

1. Prove atomicity. In psql, BEGIN, insert a new customer, then ROLLBACK. Check SELECT count(*) FROM customers. Repeat with COMMIT and compare. What happens to the identity counter in each case?
2. Break it on purpose. Write a transaction that inserts an order and then an order_items row with a product_id that does not exist. What error do you get, and how many orders exist after ROLLBACK?
3. Two windows. Open two psql sessions. In both, run BEGIN and SELECT ... FOR UPDATE on the same product. Describe exactly what window B does, and when. Then replace FOR UPDATE with a plain SELECT and explain what changes.
4. Add a total. Extend checkout.sql so that after inserting the items it prints the order total (a SELECT sum(quantity * unit_price) for the new order). Run it twice and confirm stock decreases each time.
5. Attack your own code. Write the unsafe string-concatenated version of find-customer.mjs and run it with the ' OR '1'='1 input. Count the rows returned. Then switch to $1 and run it again.
6. Least privilege. Connect as app_user and try to UPDATE products SET price = 1. What happens? Then try UPDATE products SET stock = stock. Why is the second allowed?

Where to go next. You have finished the series: you can design a relational schema, query it, make it fast, and change it safely. The natural next step is to use a database from a real backend. The NestJS Crash Course builds an API framework you can connect to PostgreSQL, putting every idea from this series (parameterized queries, transactions, migrations) to work, and the Backend track on Start Here lays out the rest of the path. Keep shopdb around as a playground, and keep asking questions of your data in SQL.