PostgreSQL with pg: Pools and Transactions

pg is the client Node itself has no equivalent for — nothing in the standard library speaks Postgres’s wire protocol. This lesson uses its Pool to run everything else in this topic’s database work: a parameterised query next to the same query built with string concatenation, a transaction that commits and one that rolls back, and the leaked-client bug that quietly exhausts a pool. The SQL itself — BEGIN/COMMIT, isolation levels, GRANT/REVOKE — is covered in depth in Transactions and Database Security; this lesson is about what pg’s own API does with that SQL from Node.

Starting PostgreSQL on a Fixed IP

A client container needs a stable address to connect to, so the network this lesson creates gets an explicit --subnet, and the Postgres container gets a fixed --ip on it — never a Docker-assigned address that could change between runs:

$ docker network create --subnet 172.31.1.0/24 nodejs-pg-net
20a3653cc051f75766f5be82df12a6fdf1fcce41ec281765c2565ed85deaccf3
$ docker run -d --name nodejs-pg-db --network nodejs-pg-net --ip 172.31.1.10 -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=appdb postgres:18.6
6bb79e7419afb25aeceef5e59d609beac5a317b92f0ea4fe351d95d25cd60b7f
$ until docker exec nodejs-pg-db pg_isready -h localhost -U postgres -q; do sleep 0.3; done
$ docker exec nodejs-pg-db pg_isready -h localhost -U postgres
localhost:5432 - accepting connections

docker run -d returns the moment the container starts, not once Postgres is actually accepting connections — on first start the image also runs a short-lived server on a Unix socket only, to create the database, before the real server binds its TCP port. pg_isready -h localhost forces the check over TCP, the same path every client in this lesson uses, so the until loop only stops once the real server is the one answering.

Connecting with Pool and Seeding a Table

package.json pins the one dependency every example in this lesson uses:

{
  "name": "postgres-with-pg-pool",
  "version": "1.0.0",
  "type": "module",
  "dependencies": {
    "pg": "8.23.1"
  }
}

node_modules lives in its own named volume, nodejs-postgres-with-pg-pool-modules, for the same reason every other lesson in this topic uses one — a bind mount shares files with the Linux VM too slowly for node_modules’ thousands of small files:

$ docker run --rm -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app -e NPM_CONFIG_UPDATE_NOTIFIER=false node:26.13 npm install --no-audit --no-fund

added 14 packages in 2s

setup.js opens a Pool pointed at the Postgres container’s fixed IP, creates one table with a CHECK constraint this lesson’s transaction example relies on later, and seeds two rows:

// setup.js
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
});

await pool.query(`
  DROP TABLE IF EXISTS accounts;
  CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0)
  );
  INSERT INTO accounts (username, balance) VALUES ('alice', 100), ('bob', 200);
`);

const { rows } = await pool.query("SELECT * FROM accounts ORDER BY id");
console.log(rows);
await pool.end();

$ docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node setup.js
[
  { id: 1, username: 'alice', balance: 100 },
  { id: 2, username: 'bob', balance: 200 }
]

Two rows come back, alice with a balance of 100 and bob with 200 — pool.query opened a connection, ran the query, and returned it to the pool on its own; nothing here acquires a client by hand yet.

Parameterised Queries vs String Concatenation

The same lookup, written two ways, against the same attacker-controlled value — a payload designed to always be true once dropped into a quoted string:

// injection.js
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
});

const payload = "nobody' OR '1'='1";

const unsafeText = `SELECT * FROM accounts WHERE username = '${payload}'`;
console.log("unsafe query text:", unsafeText);
const unsafe = await pool.query(unsafeText);
console.log("unsafe row count:", unsafe.rows.length);

const safe = await pool.query("SELECT * FROM accounts WHERE username = $1", [payload]);
console.log("safe row count:", safe.rows.length);

await pool.end();

$ docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node injection.js
unsafe query text: SELECT * FROM accounts WHERE username = 'nobody' OR '1'='1'
unsafe row count: 2
safe row count: 0

The concatenated version turns payload into part of the SQL grammar: 'nobody' OR '1'='1' is a WHERE clause that’s true for every row, so it returns both — unsafe row count: 2. The parameterised version sends the exact same string to Postgres as one value to compare against username, which matches no real username, so safe row count: 0. Only the query-building style changed between the two calls; the payload itself is identical in both.

A Transaction That Commits, and One That Rolls Back

transfer() takes a client out of the pool, wraps two updates in BEGIN/COMMIT, and rolls back if either one fails — the table’s own CHECK (balance >= 0) constraint does the failing for an overdraft, with no application-level balance check needed:

// transfer.js
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
});

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 username = $2", [amount, from]);
    await client.query("UPDATE accounts SET balance = balance + $1 WHERE username = $2", [amount, to]);
    await client.query("COMMIT");
    console.log(`committed: moved ${amount} from ${from} to ${to}`);
  } catch (err) {
    await client.query("ROLLBACK");
    console.log(`rolled back: ${err.message}`);
  } finally {
    client.release();
  }
}

async function balances() {
  const { rows } = await pool.query("SELECT balance FROM accounts ORDER BY id");
  return JSON.stringify(rows.map((r) => r.balance));
}

console.log("starting:", await balances());

await transfer("alice", "bob", 9999); // more than alice has -> CHECK violation -> ROLLBACK
console.log("after failed transfer:", await balances());

await transfer("alice", "bob", 50); // within balance -> COMMIT
console.log("after successful transfer:", await balances());

await pool.end();

$ out=$(docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node transfer.js); echo "$out"
starting: [100,200]
rolled back: new row for relation "accounts" violates check constraint "accounts_balance_check"
after failed transfer: [100,200]
committed: moved 50 from alice to bob
after successful transfer: [50,250]
$ start=$(echo "$out" | grep '^starting:' | awk '{print $NF}'); failed=$(echo "$out" | grep '^after failed transfer:' | awk '{print $NF}'); success=$(echo "$out" | grep '^after successful transfer:' | awk '{print $NF}'); [ "$start" = "$failed" ] && echo SAME; [ "$failed" != "$success" ] && echo DIFFERENT
SAME
DIFFERENT

The first transfer asks for 9999 from an account with 100 — the first UPDATE, which subtracts from alice, is the one that would leave a negative balance, so the CHECK constraint rejects it right there and transfer catches the error and runs ROLLBACK before the second UPDATE ever runs. SAME confirms the balances after that failed attempt are identical to the starting ones: [100,200] both times. The second transfer asks for 50, which alice has, so both updates succeed and COMMIT keeps them — DIFFERENT confirms [50,250] is not [100,200]. Either way, client.release() in the finally block runs, so the same client goes back to the pool whether transfer committed or rolled back.

A Leaked Client Exhausts the Pool

A client taken from pool.connect() and never given back with .release() stays checked out forever — with max: 2, two leaked clients are the entire pool:

// leak.js
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
  max: 2,
  connectionTimeoutMillis: 1500,
});

const leak1 = await pool.connect();
console.log("acquired client 1 (leaked, never released)");
const leak2 = await pool.connect();
console.log("acquired client 2 (leaked, never released)");

console.log("pool stats before 3rd query:", { total: pool.totalCount, idle: pool.idleCount, waiting: pool.waitingCount });

const start = Date.now();
try {
  await pool.query("SELECT 1");
  console.log("3rd query succeeded (unexpected)");
} catch (err) {
  const elapsed = Date.now() - start;
  console.log("3rd query failed after", elapsed > 1000 ? "OVER_1000_MS" : "UNDER_1000_MS");
  console.log("error message:", err.message);
}

process.exit(0);

$ docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node leak.js
acquired client 1 (leaked, never released)
acquired client 2 (leaked, never released)
pool stats before 3rd query: { total: 2, idle: 0, waiting: 0 }
3rd query failed after OVER_1000_MS
error message: timeout exceeded when trying to connect

Both leaked clients succeed immediately — total: 2, idle: 0 means the pool is already full and nothing is free. The third call, a plain pool.query, needs a client of its own; with none idle and max already reached, it waits instead of erroring right away. OVER_1000_MS against connectionTimeoutMillis: 1500 confirms it actually waited past a full second rather than failing instantly, and the exact message pg’s pool produces is error message: timeout exceeded when trying to connect — not a Postgres error at all, because the query never got far enough to reach the server.

Giving the client back in a finally block, the way transfer() already does, removes the leak entirely:

// fixed.js
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
  max: 2,
  connectionTimeoutMillis: 1500,
});

async function readOne() {
  const client = await pool.connect();
  try {
    return (await client.query("SELECT 1 AS ok")).rows[0];
  } finally {
    client.release();
  }
}

console.log("query 1:", await readOne());
console.log("query 2:", await readOne());
console.log("query 3:", await readOne());
console.log("pool stats:", { total: pool.totalCount, idle: pool.idleCount, waiting: pool.waitingCount });

await pool.end();

$ docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node fixed.js
query 1: { ok: 1 }
query 2: { ok: 1 }
query 3: { ok: 1 }
pool stats: { total: 1, idle: 1, waiting: 0 }

All three queries succeed against the same max: 2 pool that leak.js exhausted with only two clients — total: 1, idle: 1 at the end shows the pool only ever opened one real connection, reusing it for every call because readOne always releases the client it took, success or failure.

Practice

1. pool.query("SELECT * FROM ranked ORDER BY $1", ["score"]) is an attempt to parameterise a column name. Confirm whether it actually sorts by score, or does nothing of the sort.

// ranked.js
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
});

await pool.query(`
  DROP TABLE IF EXISTS ranked;
  CREATE TABLE ranked (id SERIAL PRIMARY KEY, name TEXT NOT NULL, score INT NOT NULL);
  INSERT INTO ranked (name, score) VALUES ('bob', 200), ('alice', 100), ('carol', 150);
`);

const byId = await pool.query("SELECT name, score FROM ranked ORDER BY id");
console.log("by id:", byId.rows);

const attempted = await pool.query("SELECT name, score FROM ranked ORDER BY $1", ["score"]);
console.log("ORDER BY $1 with 'score':", attempted.rows);

await pool.end();

$ docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node ranked.js
by id: [
  { name: 'bob', score: 200 },
  { name: 'alice', score: 100 },
  { name: 'carol', score: 150 }
]
ORDER BY $1 with 'score': [
  { name: 'bob', score: 200 },
  { name: 'alice', score: 100 },
  { name: 'carol', score: 150 }
]

2. Raise leak.js’s max from 2 to 3 and confirm the third query — still against two leaked clients — now succeeds instead of timing out.

// leak-max3.js (max raised from 2 to 3)
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
  max: 3,
  connectionTimeoutMillis: 1500,
});

const leak1 = await pool.connect();
console.log("acquired client 1 (leaked, never released)");
const leak2 = await pool.connect();
console.log("acquired client 2 (leaked, never released)");

const start = Date.now();
try {
  const r = await pool.query("SELECT 1 AS ok");
  const elapsed = Date.now() - start;
  console.log("3rd query succeeded after", elapsed < 1000 ? "UNDER_1000_MS" : "OVER_1000_MS", "rows:", r.rows);
} catch (err) {
  console.log("3rd query failed:", err.message);
}

process.exit(0);

$ docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node leak-max3.js
acquired client 1 (leaked, never released)
acquired client 2 (leaked, never released)
3rd query succeeded after UNDER_1000_MS rows: [ { ok: 1 } ]

3. Run five queries concurrently with Promise.all, each acquiring and releasing its own client through try/finally, against the same max: 2 pool, and confirm all five succeed.

// concurrent.js
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
  max: 2,
  connectionTimeoutMillis: 1500,
});

async function readOne(n) {
  const client = await pool.connect();
  try {
    const r = await client.query("SELECT $1::int AS n", [n]);
    return r.rows[0].n;
  } finally {
    client.release();
  }
}

const results = await Promise.all([1, 2, 3, 4, 5].map(readOne));
console.log("results:", results);
console.log("pool stats:", { total: pool.totalCount, idle: pool.idleCount, waiting: pool.waitingCount });

await pool.end();

$ docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node concurrent.js
results: [ 1, 2, 3, 4, 5 ]
pool stats: { total: 2, idle: 2, waiting: 0 }

4. Remove transfer()’s finally block (call client.release() only after COMMIT, the way a first attempt at this function easily could) and confirm that two failed overdraft attempts in a row, against a max: 2 pool, exhaust it exactly like leak.js did.

// transfer-bug.js (release() only after COMMIT, not in a finally block)
import { Pool } from "pg";

const pool = new Pool({
  host: "172.31.1.10",
  port: 5432,
  user: "postgres",
  password: "secret",
  database: "appdb",
  max: 2,
  connectionTimeoutMillis: 1500,
});

async function transferBuggy(from, to, amount) {
  const client = await pool.connect();
  await client.query("BEGIN");
  await client.query("UPDATE accounts SET balance = balance - $1 WHERE username = $2", [amount, from]);
  await client.query("UPDATE accounts SET balance = balance + $1 WHERE username = $2", [amount, to]);
  await client.query("COMMIT");
  client.release(); // never reached when a query above throws
}

for (let i = 1; i <= 2; i++) {
  try {
    await transferBuggy("alice", "bob", 9999); // always violates the CHECK constraint
  } catch (err) {
    console.log(`attempt ${i} failed: ${err.message}`);
  }
}

console.log("pool stats after 2 failed attempts:", { total: pool.totalCount, idle: pool.idleCount, waiting: pool.waitingCount });

const start = Date.now();
try {
  await pool.query("SELECT 1");
  console.log("plain query succeeded (unexpected)");
} catch (err) {
  const elapsed = Date.now() - start;
  console.log("plain query failed after", elapsed > 1000 ? "OVER_1000_MS" : "UNDER_1000_MS");
  console.log("error message:", err.message);
}

process.exit(0);

$ docker run --rm --network nodejs-pg-net -v "$(pwd)":/app -v nodejs-postgres-with-pg-pool-modules:/app/node_modules -w /app node:26.13 node transfer-bug.js
attempt 1 failed: new row for relation "accounts" violates check constraint "accounts_balance_check"
attempt 2 failed: new row for relation "accounts" violates check constraint "accounts_balance_check"
pool stats after 2 failed attempts: { total: 2, idle: 0, waiting: 0 }
plain query failed after OVER_1000_MS
error message: timeout exceeded when trying to connect