@liam-public/node-postgres

Node.js Postgres helpers for pooling, transactions, and migrations. A thin, schema-neutral layer over pg and node-pg-migrate whose main job is to make connection policy explicit instead of inherited.

Exports

• createPool(databaseUrl, options?) → pg.Pool with bounded waits by default.
• withTransaction(pool, fn) — BEGIN / COMMIT / ROLLBACK around fn, with the client destroyed rather than pooled if it cannot be unwound.
• resolveCommitFailurePolicy() — reads and validates POSTGRES_COMMIT_FAILURE_POLICY.
• waitForDatabase(databaseUrl, { retries, delayMs, logger }) — startup readiness poll.
• runMigrations(databaseUrl, migrationsDir, { migrationsTable }) — node-pg-migrate runner with stale-lock recovery. Services sharing a database MUST pass a scoped migrationsTable.

Timeouts are bounded by default

pg's own defaults are "wait forever" for connection acquisition, statements, and queries. A Postgres that errors surfaces promptly; one that hangs — a failover mid-transaction, a network blackhole, a saturated instance — makes every caller wait indefinitely and exhausts the pool. createPool therefore applies bounded defaults, all overridable:

| Option | Default | Where it is enforced | | --- | --- | --- | | connectionTimeoutMillis | 5_000 | Client-side, on pool acquisition | | statementTimeoutMillis | 30_000 | Server-side (statement_timeout) | | queryTimeoutMillis | 30_000 | Client-side, per query | | idleTimeoutMillis, max | pg's defaults | Pool | | idleInTransactionSessionTimeoutMillis, applicationName, keepAlive, keepAliveInitialDelayMillis | unset | See below |

Keep both statement and query timeouts. They fail differently and neither subsumes the other: statement_timeout is enforced by the server and cancels the work, but only helps while the server is alive and executing. queryTimeoutMillis is enforced client-side and is the only one that fires when the connection is a black hole. For that last case, keepAlive: true is worth adding — TCP keepalive detects a blackholed socket that no query-level deadline can observe.

Set applicationName in every service. Without it pg_stat_activity.application_name reads node for every consumer of this package, and "which service is holding these connections" is the first question anyone asks during an incident.

The defaults are exported as POOL_DEFAULTS, so a consumer can read or widen one without restating the number.

What 0 means

0 disables a timeout, with one caveat worth stating precisely. connectionTimeoutMillis: 0 and queryTimeoutMillis: 0 mean "no timeout". statementTimeoutMillis: 0 sends no statement_timeout in the startup packet, so the server's own configured statement_timeout applies — usually unlimited, but not guaranteed to be. If you need certainty, set the value explicitly.

Timeouts are per pool, so use more than one pool

A single blanket statement_timeout cannot serve both a request path that must fail in milliseconds and a nightly rollup that legitimately runs for minutes. It does not have to: statement_timeout, idle_in_transaction_session_timeout, and application_name are negotiated in each connection's startup packet, so two pools against the same database with different timeouts is a supported pattern and the two do not interfere.

// Request path — fail fast, so a hung database rejects rather than hangs.
const pool = createPool(config.databaseUrl, {
  connectionTimeoutMillis: 2_000,
  statementTimeoutMillis: 5_000,
  queryTimeoutMillis: 5_000,
  keepAlive: true,
  applicationName: 'checkout',
})

// Background jobs — long statements are expected and correct.
const jobPool = createPool(config.databaseUrl, {
  connectionTimeoutMillis: 5_000,
  statementTimeoutMillis: 300_000,
  applicationName: 'checkout:jobs',
})

Do not run migrations through a request-path pool. DDL such as creating an index on a large table will blow past a 30-second statement_timeout and be cancelled part-way. runMigrations opens its own untimed connections and is unaffected.

Transactions

const order = await withTransaction(pool, async (client) => {
  const { rows } = await client.query('INSERT INTO orders (total) VALUES ($1) RETURNING id', [total])
  await client.query('INSERT INTO order_lines (order_id) VALUES ($1)', [rows[0].id])
  return rows[0]
})

If fn throws, the transaction is rolled back and the original error is rethrown — not the rollback error, which is a symptom rather than the cause. If the rollback also fails, the client is destroyed instead of returned to the pool: pg rejects a query that hits query_timeout without closing the socket, so the server may still be executing on that connection and reusing it risks picking up the previous query's results.

Note that fn receives a single checked-out client. Do not use pool inside it — those queries run on a different connection and outside the transaction.

POSTGRES_COMMIT_FAILURE_POLICY

A failed COMMIT is ambiguous: the connection may be dead, or it may be perfectly healthy and the server may simply have refused the transaction. The two readings call for opposite handling, so the choice is configurable.

| Value | Behaviour on a failed COMMIT | | --- | --- | | rollback (default) | Issue a ROLLBACK and keep the client if it succeeds; destroy it if the rollback fails too. | | destroy | Discard the client immediately, without attempting a rollback. |

Default to rollback. A commit rejected for a server-side reason — a serialization failure under REPEATABLE READ, a deferred constraint firing at commit time — leaves a healthy connection, and discarding it makes retry-heavy workloads pay for a reconnect every time. Choose destroy when you would rather not reason about the state of a connection whose commit failed, and can afford the reconnect.

This governs the commit path only. A throwing callback is always rolled back, whatever the policy — an open transaction has to be unwound.

An unrecognised value throws rather than falling back to the default, so a typo cannot silently leave the wrong policy in force. Call resolveCommitFailurePolicy() during startup to surface a bad value at boot rather than at the first failed commit.

waitForDatabase

await waitForDatabase(process.env.DATABASE_URL!, {
  retries: 10,      // default: 10, so up to 11 attempts
  delayMs: 2_000,   // default: 2_000
  logger: console,  // optional { warn(msg, err) }
})

Polls SELECT 1 on a fresh single-connection pool per attempt (5s connect timeout) and rethrows the last error once retries are exhausted. For start-up ordering when the database may not be accepting connections yet.

runMigrations

await runMigrations(process.env.DATABASE_URL!, './migrations', {
  migrationsTable: 'pgmigrations_orders',
})

Runs node-pg-migrate up to the latest migration.

Services sharing a database must pass a scoped migrationsTable. node-pg-migrate's checkOrder compares tracked migrations positionally against the service's own migration files, so interleaved entries from a sibling service break it. The default stays pgmigrations for backward compatibility. The name must be a plain SQL identifier ([A-Za-z_][A-Za-z0-9_]*) — it is validated, because it is interpolated into the lock-clearing statement below.

Stale-lock recovery. If node-pg-migrate reports Another migration is already running, this waits 90 seconds, clears <migrationsTable>_lock, and retries once. The wait is what makes it safe: a migration genuinely in flight elsewhere has time to finish and drop the lock itself, so what gets cleared is a lock left behind by a pod that died mid-migration — the case that otherwise wedges a deploy until someone clears it by hand. A migration that legitimately runs longer than 90 seconds should not use this path.

Tests

pnpm test runs the unit suite: fast, no Docker, and it covers the wiring — option translation, defaulting, and which release path a transaction takes.

pnpm test:integration runs the suite that needs a real server, via testcontainers. These cover the properties the unit tests cannot observe: that statement_timeout is genuinely negotiated on the connection, that an overrunning statement comes back as 57014 query_canceled from the server, that a saturated pool rejects instead of queueing, that 0 disables rather than fires immediately, and that two pools on one database keep their own timeouts. The unresponsive-connection case is exercised through a TCP relay that stops forwarding mid-session, which is the one scenario a server-side timeout can never catch.