import { Pool, PoolClient, Client, QueryResult, QueryResultRow } from 'pg';
const MAX_LIFETIME_MS = 30 * 60 * 1000;
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: Number(process.env.PG_POOL_MAX ?? 10),
idleTimeoutMillis: 30_000,
connectionTimeoutMillis: 5_000,
});
const bornAt = new WeakMap<Client, number>();
pool.on('connect', (client: Client) => {
bornAt.set(client, Date.now());
});
pool.on('error', (err) => {
console.error('idle pg client error', err);
});
function expired(client: PoolClient): boolean {
const t = bornAt.get(client as unknown as Client);
return t !== undefined && Date.now() - t > MAX_LIFETIME_MS;
}
export async function query<T extends QueryResultRow>(
text: string,
params?: unknown[],
): Promise<QueryResult<T>> {
const client = await pool.connect();
try {
return await client.query<T>(text, params);
} finally {
client.release(expired(client));
}
}
export async function withTransaction<T>(
fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
const client = await pool.connect();
try {
await client.query('BEGIN');
const result = await fn(client);
await client.query('COMMIT');
return result;
} catch (err) {
await client.query('ROLLBACK').catch(() => undefined);
throw err;
} finally {
client.release(expired(client));
}
}
import { Request, Response } from 'express';
import { pool, query } from './db';
export async function readiness(_req: Request, res: Response): Promise<void> {
try {
await query<{ ok: number }>('SELECT 1 AS ok');
res.status(200).json({
status: 'ready',
pool: {
total: pool.totalCount,
idle: pool.idleCount,
waiting: pool.waitingCount,
},
});
} catch (err) {
res.status(503).json({
status: 'unavailable',
error: err instanceof Error ? err.message : 'unknown',
});
}
}
import type { Server } from 'http';
import { pool } from './db';
export function installGracefulShutdown(server: Server): void {
let closing = false;
async function close(signal: string): Promise<void> {
if (closing) return;
closing = true;
console.log(`received ${signal}, draining connections`);
server.close(async () => {
try {
await pool.end();
console.log('pg pool closed');
process.exit(0);
} catch (err) {
console.error('error closing pool', err);
process.exit(1);
}
});
setTimeout(() => process.exit(1), 10_000).unref();
}
process.on('SIGTERM', () => void close('SIGTERM'));
process.on('SIGINT', () => void close('SIGINT'));
}
This snippet shows how to wrap node-postgres (pg) into a small, reliable pool module that recycles connections after a maximum lifetime — a detail the built-in pool does not handle on its own. The pg.Pool keeps a set of live TCP connections around so requests avoid the cost of a fresh handshake and authentication on every query. That reuse is exactly what makes long-lived connections dangerous: a socket can stay attached to a backend that has drifted (a failed-over primary, a pgbouncer that rotated servers, or a Postgres process holding memory from a large query) for hours. Capping the lifetime forces periodic renewal so the pool self-heals instead of accumulating stale sockets.
In db.ts, the pool is configured with max, idleTimeoutMillis, and connectionTimeoutMillis, and a WeakMap (bornAt) records when each physical Client was created. The connect event stamps every new client, and the query helper checks that stamp against MAX_LIFETIME_MS. When a client is older than the cap it is destroyed with client.release(true), which tells pg to discard rather than return it, so the next checkout builds a fresh connection. A generic <T> return type keeps call sites type-safe without leaking pg internals.
The withTransaction helper acquires a single client with pool.connect(), runs BEGIN/COMMIT, and rolls back on any error. Crucially it releases the client in a finally, and passes true when the client is too old, applying the same recycling logic inside transactions. Leaking a client here — forgetting the finally — is the classic way a pool silently exhausts itself under load.
healthController.ts exposes a readiness probe that runs SELECT 1 and reports pool.totalCount, idleCount, and waitingCount, the three numbers that reveal saturation: a persistently high waitingCount means max is too low or queries are too slow.
shutdown.ts wires SIGTERM/SIGINT to pool.end() so in-flight queries finish and sockets close cleanly, which matters under rolling deploys where an abrupt exit would leave server-side connections lingering until Postgres times them out. The trade-off of lifetime capping is slightly more reconnect churn; setting MAX_LIFETIME_MS to tens of minutes keeps that negligible while bounding staleness.
Related snips
export interface RetryOptions {
retries: number;
baseMs: number;
maxMs: number;
signal?: AbortSignal;
onRetry?: (attempt: number, delay: number, err: unknown) => void;Exponential backoff with jitter for retries
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
# Vulnerable: user input is concatenated directly into SQL.
email = params[:email]
password = params[:password]
sql = "SELECT * FROM users WHERE email = '#{email}' AND password_hash = '#{password}'"
user = ActiveRecord::Base.connection.execute(sql).firstSQL injection prevention with unsafe and safe query patterns
export type Settled<R> =
| { status: 'fulfilled'; value: R }
| { status: 'rejected'; reason: unknown };
export interface ConcurrencyOptions {
limit: number;Simple concurrency limiter for batch operations
import { randomBytes, createHash } from "crypto";
import jwt from "jsonwebtoken";
import { RefreshTokenStore } from "./store";
const ACCESS_SECRET = process.env.ACCESS_SECRET!;
const ACCESS_TTL = "15m";JWT access + refresh token rotation (conceptual)
Share this code
Here's the card — post it anywhere.
