Node.js and PostgreSQL in Code · PostgreSQL

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

This Node.js example creates one shared Pool with the pg library, then runs a parameterised query where the values travel separately from the SQL text. The second half moves money between two accounts inside a transaction: if either UPDATE fails, ROLLBACK undoes both and the error is rethrown so it is not silently lost. Notice that client.release() sits in finally, so the connection always goes back to the pool, and that everything runs inside an async function, because a require-style script cannot use await at the top level.

const { Pool } = require("pg");

const pool = new Pool({
  host: "localhost",
  port: 5432,
  database: "myapp",
  user: "postgres",
  password: "password",
  max: 20
});

// A require-style (CommonJS) script cannot use await at the top level,
// so the work goes inside an async function.
async function main() {
  // Parameterised query (prevents SQL injection)
  const result = await pool.query(
    "SELECT * FROM users WHERE email = $1 AND is_active = $2",
    ["[email protected]", true]
  );
  console.log(result.rows);

  // Transaction
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [100, 1]);
    await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [100, 2]);
    await client.query("COMMIT");
  } catch (e) {
    await client.query("ROLLBACK");
    throw e; // undo the changes, then pass the error on instead of hiding it
  } finally {
    client.release();
  }
}

main()
  .catch((err) => console.error("Database error:", err.message))
  .finally(() => pool.end());