node-postgres (`pg`)

Q: What should max be on the pool?

Start with 10 per Node process. Tune against Postgres max_connections and replica count. See Connection Pool Tuning.

Q: Does pg work with ESM on Node 24?

Yes. import pg from "pg" with "type": "module" in package.json.

Q: Can I use pg with NestJS?

Provide Pool as a custom provider or use TypeORM/Prisma adapters that use pg under the hood.

Q: How do I handle BIGINT IDs?

Postgres returns bigint as string in JS. Use ::text cast or map to string in TypeScript types.

Q: What about LISTEN/NOTIFY?

Dedicated long-lived Client, not the pool. Rare in stateless APIs; prefer queues for events.

Q: How do I test repositories?

Testcontainers Postgres for integration tests; mock Pool with jest.mock only for trivial cases.

Q: Connection string SSL params?

Prefer explicit ssl option in code over ?sslmode=require only when CA verification matters.

Q: Batch inserts?

Use multi-row INSERT ... VALUES ($1,$2), ($3,$4) or COPY for bulk loads.

Production patterns for node-postgres (pg): pooling, safe queries, and transactions in TypeScript on Node 24.

Recipe

Quick-reference recipe card - copy-paste ready.

import pg from "pg";
 
export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
});
 
export async function findOrder(id: string) {
  const { rows } = await pool.query(
    "SELECT id, total_cents FROM orders WHERE id = $1",
    [id]
  );
  return rows[0] ?? null;
}

When to reach for this:

You want full SQL control without ORM magic
Reporting queries need CTEs, window functions, or Postgres-specific features
Team already ships migrations with SQL files (dbmate, flyway, goose)

Working Example

// src/db/pool.ts
import pg from "pg";
 
export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: Number(process.env.PG_POOL_MAX ?? 10),
  ssl: process.env.NODE_ENV === "production" ? { rejectUnauthorized: true } : undefined,
});
 
// src/db/orders.ts
import type { Pool, PoolClient } from "pg";
import { pool } from "./pool";
 
export type Order = { id: string; userId: string; totalCents: number };
 
export async function createOrderWithItems(
  userId: string,
  items: { sku: string; qty: number; priceCents: number }[]
): Promise<Order> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const orderRes = await client.query<{ id: string }>(
      "INSERT INTO orders (user_id, total_cents) VALUES ($1, $2) RETURNING id",
      [userId, items.reduce((s, i) => s + i.qty * i.priceCents, 0)]
    );
    const orderId = orderRes.rows[0].id;
    for (const item of items) {
      await client.query(
        "INSERT INTO order_items (order_id, sku, qty, price_cents) VALUES ($1, $2, $3, $4)",
        [orderId, item.sku, item.qty, item.priceCents]
      );
    }
    await client.query("COMMIT");
    return { id: orderId, userId, totalCents: items.reduce((s, i) => s + i.qty * i.priceCents, 0) };
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}
 
// src/routes/orders.ts (Express 5)
import express from "express";
import { createOrderWithItems } from "../db/orders";
 
const router = express.Router();
 
router.post("/", async (req, res, next) => {
  try {
    const { userId, items } = req.body;
    const order = await createOrderWithItems(userId, items);
    res.status(201).json(order);
  } catch (err) {
    next(err);
  }
});
 
export default router;

What this demonstrates:

Single pool import shared across modules
Multi-statement transaction with explicit BEGIN/COMMIT/ROLLBACK
Parameterized inserts for every value
Express route stays thin; SQL lives in db/ or repository adapters

Deep Dive

Pool vs Client

API	Use when
`pool.query(sql, params)`	One-off SELECT/INSERT/UPDATE
`pool.connect()` + `client.query`	Multi-query transaction on same connection
`pool.end()`	Process shutdown

pool.query checks out a client internally and releases it
Long transactions hold a client and reduce effective pool size

Parameterized Queries

// Safe
await pool.query("SELECT * FROM users WHERE email = $1", [email]);
 
// Unsafe - never do this
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

pg sends parameters separately from the SQL text
For dynamic IN lists, use = ANY($1::uuid[]) with an array param

TypeScript Typing

const res = await pool.query<{ id: string; email: string }>(
  "SELECT id, email FROM users WHERE id = $1",
  [id]
);

Generic on query<T>() types rows only; validate at boundaries with Zod when needed

Prepared Statements

pg supports unnamed prepared statements per query automatically
For identical queries at very high QPS, named prepared statements can help - measure first

Observability

pool.on("error", (err) => {
  console.error({ msg: "idle client error", err: err.message });
});

Track query duration in repository layer
Alert when pool.waitingCount stays high (pool exhaustion)

Gotchas

New pool per request - exhausts Postgres max_connections. Fix: module-level singleton pool.
Forgotten client.release() - pool starvation. Fix: try/finally around every connect().
HTTP calls inside transactions - locks rows for seconds. Fix: commit before enqueueing side effects.
String-built SQL for filters - injection risk. Fix: whitelisted sort columns or query builder.
Ignoring SSL in production - MITM on managed Postgres. Fix: ssl: { rejectUnauthorized: true } with CA if required.

Alternatives

Alternative	Use When	Don't Use When
Prisma	Schema migrations and DX priority	Heavy custom SQL every endpoint
Drizzle	SQL-first with TypeScript inference	Team wants zero SQL visibility
Knex	Legacy codebase already on Knex	Greenfield with Drizzle/Prisma options
`@vercel/postgres` / Neon serverless driver	Edge/serverless without pooler	Long-lived worker with local pool

FAQs

What should `max` be on the pool?

Start with 10 per Node process. Tune against Postgres max_connections and replica count. See Connection Pool Tuning.

Does `pg` work with ESM on Node 24?

Yes. import pg from "pg" with "type": "module" in package.json.

How do I run migrations?

Use dbmate, flyway, goose, or Drizzle/Prisma migrate. Never apply ad hoc DDL in production shells.

Can I use `pg` with NestJS?

Provide Pool as a custom provider or use TypeORM/Prisma adapters that use pg under the hood.

How do I handle `BIGINT` IDs?

Postgres returns bigint as string in JS. Use ::text cast or map to string in TypeScript types.

What about LISTEN/NOTIFY?

Dedicated long-lived Client, not the pool. Rare in stateless APIs; prefer queues for events.

How do I test repositories?

Testcontainers Postgres for integration tests; mock Pool with jest.mock only for trivial cases.

Read replica routing?

Separate pools for read and write URLs. Route SELECTs to replica pool in repository methods explicitly.

Connection string SSL params?

Prefer explicit ssl option in code over ?sslmode=require only when CA verification matters.

Batch inserts?

Use multi-row INSERT ... VALUES ($1,$2), ($3,$4) or COPY for bulk loads.

Databases Basics - SQL vs document
Connection Pool Tuning - PgBouncer and serverless
Prisma - ORM alternative
Graceful Shutdown - pool.end()
Databases Best Practices - migrations policy

npm Official Website & Registry - Package search, publishing, and documentation.
Node.js Best Practices (GitHub) - Curated production best practices.
DigitalOcean Node.js Tutorials - Production-oriented how-tos.
Node.js Design Patterns, 4th Edition - Production-grade application patterns.
Node.js Cookbook, 5th Edition - Practical server-side recipes.

Stack versions: This page was written for Node.js 24.18.0 (Active LTS), npm 10+, TypeScript 5.6+, Express 5, Fastify 5, and NestJS 11.

Recipe

Quick-reference recipe card - copy-paste ready.

import pg from "pg";
 
export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
});
 
export async function findOrder(id: string) {
  const { rows } = await pool.query(
    "SELECT id, total_cents FROM orders WHERE id = $1",
    [id]
  );
  return rows[0] ?? null;
}

When to reach for this:

You want full SQL control without ORM magic
Reporting queries need CTEs, window functions, or Postgres-specific features
Team already ships migrations with SQL files (dbmate, flyway, goose)

Working Example

// src/db/pool.ts
import pg from "pg";
 
export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: Number(process.env.PG_POOL_MAX ?? 10),
  ssl: process.env.NODE_ENV === "production" ? { rejectUnauthorized: true } : undefined,
});
 
// src/db/orders.ts
import type { Pool, PoolClient } from "pg";
import { pool } from "./pool";
 
export type Order = { id: string; userId: string; totalCents: number };
 
export async function createOrderWithItems(
  userId: string,
  items: { sku: string; qty: number; priceCents: number }[]
): Promise<Order> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const orderRes = await client.query<{ id: string }>(
      "INSERT INTO orders (user_id, total_cents) VALUES ($1, $2) RETURNING id",
      [userId, items.reduce((s, i) => s + i.qty * i.priceCents, 0)]
    );
    const orderId = orderRes.rows[0].id;
    for (const item of items) {
      await client.query(
        "INSERT INTO order_items (order_id, sku, qty, price_cents) VALUES ($1, $2, $3, $4)",
        [orderId, item.sku, item.qty, item.priceCents]
      );
    }
    await client.query("COMMIT");
    return { id: orderId, userId, totalCents: items.reduce((s, i) => s + i.qty * i.priceCents, 0) };
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}
 
// src/routes/orders.ts (Express 5)
import express from "express";
import { createOrderWithItems } from "../db/orders";
 
const router = express.Router();
 
router.post("/", async (req, res, next) => {
  try {
    const { userId, items } = req.body;
    const order = await createOrderWithItems(userId, items);
    res.status(201).json(order);
  } catch (err) {
    next(err);
  }
});
 
export default router;

What this demonstrates:

Single pool import shared across modules
Multi-statement transaction with explicit BEGIN/COMMIT/ROLLBACK
Parameterized inserts for every value
Express route stays thin; SQL lives in db/ or repository adapters

Deep Dive

Pool vs Client

API	Use when
`pool.query(sql, params)`	One-off SELECT/INSERT/UPDATE
`pool.connect()` + `client.query`	Multi-query transaction on same connection
`pool.end()`	Process shutdown

pool.query checks out a client internally and releases it
Long transactions hold a client and reduce effective pool size

Parameterized Queries

// Safe
await pool.query("SELECT * FROM users WHERE email = $1", [email]);
 
// Unsafe - never do this
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

pg sends parameters separately from the SQL text
For dynamic IN lists, use = ANY($1::uuid[]) with an array param

TypeScript Typing

const res = await pool.query<{ id: string; email: string }>(
  "SELECT id, email FROM users WHERE id = $1",
  [id]
);

Generic on query<T>() types rows only; validate at boundaries with Zod when needed

Prepared Statements

pg supports unnamed prepared statements per query automatically
For identical queries at very high QPS, named prepared statements can help - measure first

Observability

pool.on("error", (err) => {
  console.error({ msg: "idle client error", err: err.message });
});

Track query duration in repository layer
Alert when pool.waitingCount stays high (pool exhaustion)

Gotchas

New pool per request - exhausts Postgres max_connections. Fix: module-level singleton pool.
Forgotten client.release() - pool starvation. Fix: try/finally around every connect().
HTTP calls inside transactions - locks rows for seconds. Fix: commit before enqueueing side effects.
String-built SQL for filters - injection risk. Fix: whitelisted sort columns or query builder.
Ignoring SSL in production - MITM on managed Postgres. Fix: ssl: { rejectUnauthorized: true } with CA if required.

Alternatives

Alternative	Use When	Don't Use When
Prisma	Schema migrations and DX priority	Heavy custom SQL every endpoint
Drizzle	SQL-first with TypeScript inference	Team wants zero SQL visibility
Knex	Legacy codebase already on Knex	Greenfield with Drizzle/Prisma options
`@vercel/postgres` / Neon serverless driver	Edge/serverless without pooler	Long-lived worker with local pool

FAQs

What should `max` be on the pool?

Start with 10 per Node process. Tune against Postgres max_connections and replica count. See Connection Pool Tuning.

Does `pg` work with ESM on Node 24?

Yes. import pg from "pg" with "type": "module" in package.json.

How do I run migrations?

Use dbmate, flyway, goose, or Drizzle/Prisma migrate. Never apply ad hoc DDL in production shells.

Can I use `pg` with NestJS?

Provide Pool as a custom provider or use TypeORM/Prisma adapters that use pg under the hood.

How do I handle `BIGINT` IDs?

Postgres returns bigint as string in JS. Use ::text cast or map to string in TypeScript types.

What about LISTEN/NOTIFY?

Dedicated long-lived Client, not the pool. Rare in stateless APIs; prefer queues for events.

How do I test repositories?

Testcontainers Postgres for integration tests; mock Pool with jest.mock only for trivial cases.

Read replica routing?

Separate pools for read and write URLs. Route SELECTs to replica pool in repository methods explicitly.

Connection string SSL params?

Prefer explicit ssl option in code over ?sslmode=require only when CA verification matters.

Batch inserts?

Use multi-row INSERT ... VALUES ($1,$2), ($3,$4) or COPY for bulk loads.

Databases Basics - SQL vs document
Connection Pool Tuning - PgBouncer and serverless
Prisma - ORM alternative
Graceful Shutdown - pool.end()
Databases Best Practices - migrations policy

npm Official Website & Registry - Package search, publishing, and documentation.
Node.js Best Practices (GitHub) - Curated production best practices.
DigitalOcean Node.js Tutorials - Production-oriented how-tos.
Node.js Design Patterns, 4th Edition - Production-grade application patterns.
Node.js Cookbook, 5th Edition - Practical server-side recipes.

Stack versions: This page was written for Node.js 24.18.0 (Active LTS), npm 10+, TypeScript 5.6+, Express 5, Fastify 5, and NestJS 11.

node-postgres (pg)

Search Documentation

node-postgres (pg)

node-postgres (`pg`)

node-postgres (`pg`)