Exercises

Objectives

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

• Write safe, parameterized CRUD queries against a real table
• Wrap a multi-step operation in a transaction, with a correct rollback path
• Confirm a rolled-back transaction leaves data completely unchanged

⚠️ A note on verification: every command and every output in this lesson was actually run against a real PostgreSQL server.

Exercise: A Store Inventory

a) Set up. Create a products table (id, name, stock), and insert two products: Keyboard with stock = 5, and Mouse with stock = 10.

b) Safe search. Write a parameterized query that searches products by name:

const searchTerm = 'Keyboard';
const found = await pool.query('SELECT * FROM products WHERE name = $1', [searchTerm]);
console.log(found.rows);

[ { id: 1, name: 'Keyboard', stock: 5 } ]

c) Place an order, inside a transaction. Reducing stock is really two steps, check there’s enough stock, then reduce it, and they need to happen together:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  const productRes = await client.query('SELECT stock FROM products WHERE name = $1', ['Keyboard']);
  const currentStock = productRes.rows[0].stock;
  if (currentStock < 2) throw new Error('Not enough stock');
  await client.query('UPDATE products SET stock = stock - $1 WHERE name = $2', [2, 'Keyboard']);
  await client.query('COMMIT');
  console.log('Order committed');
} catch (e) {
  await client.query('ROLLBACK');
  console.log('Order rolled back:', e.message);
} finally {
  client.release();
}

Order committed

const afterOrder = await pool.query('SELECT name, stock FROM products ORDER BY name');
console.log(afterOrder.rows);

[ { name: 'Keyboard', stock: 3 }, { name: 'Mouse', stock: 10 } ]

d) Attempt an order that exceeds stock. Run the same transaction, but for 100 keyboards instead of 2:

Order rolled back: Not enough stock

const finalState = await pool.query('SELECT name, stock FROM products ORDER BY name');
console.log(finalState.rows);

[ { name: 'Keyboard', stock: 3 }, { name: 'Mouse', stock: 10 } ]

Confirm the stock is completely unchanged from part (c), the failed order left no trace.

e) A second resource. Add a customers table (id, name, email), and a place_order operation that, inside one transaction, reduces a product’s stock and inserts a row into an orders table (id, customer_id, product_id, quantity). Confirm that if the stock check fails, no orders row is inserted either.

f) Break it on purpose. Remove the try/catch/ROLLBACK from part (c)’s transaction entirely, and force the stock check to fail after the UPDATE has already run. Confirm the stock is left decremented anyway, and explain, in your own words, exactly what the transaction in part (c) prevented.

Recap

This module covered the full lifecycle of a raw SQL query in Node.js: SELECT, INSERT, UPDATE, and DELETE, always parameterized, never built from concatenated strings, and multi-step operations wrapped safely in transactions.

Next module: Prisma, an ORM that generates exactly this kind of SQL for you, starting with what a query engine and a client actually are.