Solution & Challenge: Node.js and PostgreSQL · PostgreSQL

Ch 1 · PostgreSQL Connection with Node.js — card 4 of 4.

Compare your code with the worked solution, which wraps the query and the transaction in reusable async functions and rethrows errors after rolling back. The challenge then asks you to build a full create, read, update and delete API with Express.js on top of PostgreSQL. A good answer shares a single pool, parameterises every query and uses transactions for multi-step operations.

const { Pool } = require("pg");
const pool = new Pool({ connectionString: "postgresql://localhost/myapp" });

async function getUser(email) {
  const { rows } = await pool.query("SELECT * FROM users WHERE email = $1", [email]);
  return rows[0];
}

async function transfer(from, to, amount) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, from]);
    await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, to]);
    await client.query("COMMIT");
  } catch (e) {
    await client.query("ROLLBACK");
    throw e;
  } finally {
    client.release();
  }
}