Part 8 · 3 chapters · ~18 min

Databases from Node

How pg and mysql2 speak the wire protocols, connection pool sizing and the total-connections trap, prepared statements through a pool, pipelining and batching, failover and retry safety, transactions across async boundaries with AsyncLocalStorage, query optimisation from the app side, and in-process versus Redis caching.

Drivers and the wire protocol

A database driver is a protocol implementation over a socket. pg speaks the PostgreSQL frontend/backend protocol: a startup message, authentication (SCRAM-SHA-256), then either the simple query flow (one Query message, text results) or the extended flow (Parse, Bind, Describe, Execute, Sync) used for parameters and prepared statements. mysql2 speaks the MySQL protocol, including the binary protocol for prepared statements.

code

// parameters travel separately from SQL text (no injection, and the plan can be reused)
const { rows } = await pool.query('SELECT id, balance_kobo FROM accounts WHERE owner_id = $1', [ownerId]);

// a named prepared statement: parsed once per connection
await pool.query({ name: 'acct-by-owner', text: 'SELECT ... WHERE owner_id = $1', values: [ownerId] });

// pipelining: send several queries without waiting for each result (postgres.js does this by default)
const [a, b] = await Promise.all([sql`select 1`, sql`select 2`]);   // same connection, one round trip

topic	rule
prepared statements through a pool	a named statement lives on one connection; each pooled connection prepares it on first use. Behind PgBouncer transaction mode, check your version supports it.
batching	insert many rows with one multi-row INSERT or `COPY`, not N round trips
failover	after a primary failover, existing connections error; the pool must discard them and reconnect to the new primary (DNS or a proxy)
retry safety	retry only idempotent reads automatically; a write that timed out may have committed. Use idempotency keys and unique constraints.

Pooling, transactions and async context

code

// a transaction must use ONE connection for every statement
import { AsyncLocalStorage } from 'node:async_hooks';
const txStore = new AsyncLocalStorage();

export async function withTx(fn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const out = await txStore.run(client, fn);      // anything called inside sees this client
    await client.query('COMMIT'); return out;
  } catch (e) { await client.query('ROLLBACK'); throw e; }
  finally { client.release(); }                    // forgetting release() leaks a connection forever
}
export const db = () => txStore.getStore() ?? pool;   // repositories call db().query(...)

await withTx(async () => { await debit(a, 500); await credit(b, 500); });

The classic leak: an early return or a thrown error before release(). After max leaks, every request waits for a connection forever. Always release in finally, set connectionTimeoutMillis so waiting fails loudly, and export pool metrics (total, idle, waiting).

THE TOTAL-CONNECTIONS TRAP

pool size per process times processes times replicas, against the database limit

swipe the figure sideways, or tap expand for full screen

1/5

the multiplication

Each Node process opens its own pool. Twelve pods with a pool of 20 is 240 connections; Postgres was configured for 200. The thirteenth pod during a deploy fails to connect.

12 pods × 20 = 240 > 200deploys and autoscaling make it worse

Query optimisation and caching from the app side

app-side problem	symptom	fix
N+1 queries in a loop	hundreds of tiny queries per request	one query with `WHERE id = ANY($1)`, or a DataLoader batching per tick
`SELECT *` over wide rows	large payloads, no index-only scans	select the columns you use
offset pagination	slow deep pages	keyset: `WHERE (created_at, id) < ($1, $2) ORDER BY ... LIMIT 50`
long transactions around HTTP calls	locks held, connections held, bloat	never call external services inside a transaction; use the outbox

code

// in-process LRU in front of Redis in front of the DB, with request coalescing
import { LRUCache } from 'lru-cache';
const local = new LRUCache({ max: 10_000, ttl: 5_000 });
async function getRate(pair) {
  return local.fetch(pair, { fetchMethod: async () =>               // coalesces concurrent misses
    JSON.parse(await redis.get(`rate:${pair}`) ?? 'null') ?? loadAndCache(pair) });
}

In-process caches are fastest and free but inconsistent across replicas and lost on restart; Redis is shared but a network hop. Use local caches for seconds-long, read-heavy, tolerant data; Redis for shared state; and never cache what authorises money (Distributed Systems part 11).