pg

What it is

pg (often called node-postgres) is the canonical pure-JavaScript PostgreSQL client for Node. It speaks the Postgres wire protocol directly, exposes a Promise-based API, and ships a connection pool (pg.Pool) and a single-connection client (pg.Client). Optional pg-native swaps the implementation for libpq bindings, but the pure-JS driver is the default and the one most projects use.

Reach for pg when you want low-level SQL with full control over parameters, transactions, prepared statements, and Postgres-specific features (LISTEN/NOTIFY, COPY, advisory locks). Pair it with pg-format for safe identifier interpolation, or layer a query builder (kysely) or ORM (prisma, drizzle) on top when the project warrants.

Install

npm install pg

Output: added pg to dependencies

pnpm add pg

Output: added 1 package, linked from store

yarn add pg

Output: added pg

bun add pg

Output: installed pg

TypeScript projects need the type package separately:

npm install --save-dev @types/pg

Output: added @types/pg to devDependencies

Versioning & Node support

Current line is pg@8.x. The 8.x line is the long-term stable across recent Node releases.

• pg@8 — Node 16+. Native Promise support, AbortController integration, IPv6 fixes.
• pg@7 — historical; do not start new work here.
• pg-native — optional add-on backed by libpq. Slightly faster on heavy result sets but adds a C build dependency. Most projects skip it.

The package follows semver carefully; minor releases add features without breaking existing query interfaces. Pin minor ("pg": "8.x") in production.

Package metadata

• Maintainer: Brian C. + node-postgres collective (brianc/node-postgres)
• Project home: github.com/brianc/node-postgres
• Docs: node-postgres.com
• npm: npmjs.com/package/pg
• License: MIT
• First released: 2010
• Downloads: ~8 million+ weekly downloads.

Peer dependencies & extras

pg has no peer dependencies. The companion packages are published as siblings under the same maintainer:

• pg-pool — bundled in pg, exposed as pg.Pool. No separate install needed for pg@8.
• pg-cursor — streaming cursor for large result sets
• pg-copy-streams — COPY FROM / COPY TO streaming
• pg-format — safe interpolation of identifiers (%I) and literals (%L)
• pg-listen — robust LISTEN/NOTIFY wrapper with reconnection
• pg-types — type-parser hooks (already transitive)
• pg-protocol — wire protocol implementation (transitive)
• pg-native — optional libpq binding

For type-safe queries on top: kysely, drizzle-orm, slonik, or prisma (which can use the same connection string).

Alternatives

Real-world recipes

Pool setup

pg.Pool is the workhorse. It manages a fixed set of clients, reusing them across requests. Always use a pool — never new Client() per request, which is the most common production performance bug.

import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  ssl: process.env.NODE_ENV === "production" ? { rejectUnauthorized: true } : false,
});

const { rows } = await pool.query("SELECT NOW() AS now");
console.log(rows[0].now);

await pool.end();

Output: 2026-05-31T12:34:56.789Z (or current server time). Pool drains on end().

Parameterized query

Never interpolate user input into SQL — always pass parameters via the $1, $2 placeholders. Postgres uses the prepared-statement path automatically.

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

async function findUser(email) {
  const { rows } = await pool.query(
    "SELECT id, email FROM users WHERE email = $1",
    [email]
  );
  return rows[0] ?? null;
}

console.log(await findUser("alice@example.com"));

Output: { id: 'u-123', email: 'alice@example.com' } if matched, otherwise null. SQL injection impossible because email is bound, not interpolated.

Transaction

A transaction needs a single dedicated client checked out from the pool. The BEGIN / COMMIT / ROLLBACK pattern must release the client even on error.

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

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 id = $2", [amount, from]);
    await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, to]);
    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Output: both updates apply atomically, or neither does. Releasing the client returns it to the pool.

LISTEN / NOTIFY

Postgres supports a lightweight pub/sub via NOTIFY / LISTEN. A dedicated long-lived client subscribes; other clients publish.

import pg from "pg";

const client = new pg.Client({ connectionString: process.env.DATABASE_URL });
await client.connect();

client.on("notification", (msg) => {
  console.log(`channel=${msg.channel} payload=${msg.payload}`);
});

await client.query("LISTEN job_added");

// elsewhere:
// await otherClient.query("NOTIFY job_added, 'job-123'");

Output: every NOTIFY job_added, '<payload>' triggers the listener with payload as a string. For production use, prefer pg-listen — it handles reconnection.

Bulk insert with COPY

COPY is Postgres's fast bulk-load path. pg-copy-streams integrates it with Node streams.

import pg from "pg";
import { from as copyFrom } from "pg-copy-streams";
import { pipeline } from "node:stream/promises";
import { Readable } from "node:stream";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

const rows = Array.from({ length: 100_000 }, (_, i) => `${i}\tuser-${i}\n`);
const source = Readable.from(rows);

const client = await pool.connect();
try {
  const ingest = client.query(copyFrom("COPY users (id, name) FROM STDIN"));
  await pipeline(source, ingest);
} finally {
  client.release();
}

Output: 100k rows loaded in seconds — orders of magnitude faster than batched INSERT. COPY assumes tab-delimited input by default.

Production deployment

The connection pool is the deployment knob that matters most.

Pool sizing

The pool's max should be ≤ Postgres's max_connections ÷ replicas. A single small app server with 10 concurrent requests rarely needs more than 10 connections.

const pool = new pg.Pool({
  max: 10,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 2_000,
});

Output: at most 10 simultaneous connections; idle connections close after 30s; checkouts fail after 2s if pool is exhausted.

PgBouncer in front

For multi-process or serverless deployments, run PgBouncer in transaction-pooling mode and connect Node to PgBouncer rather than Postgres directly. PgBouncer multiplexes thousands of client connections onto a small Postgres pool.

Caveat: transaction-pooling breaks session-level features (prepared statements cached on the connection, SET LOCAL, advisory locks held across statements). For Node, this is fine because pg does not cache plans across pooled checkouts.

TLS / SSL

Production Postgres should require SSL. The Node TLS layer accepts a ca PEM string.

import fs from "node:fs";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync(process.env.PG_CA_CERT).toString(),
  },
});

Output: TLS handshake fails if the server cert doesn't chain to the supplied CA — the right default for managed Postgres (RDS, Cloud SQL, Supabase).

Graceful shutdown

Drain the pool on signal:

for (const sig of ["SIGTERM", "SIGINT"]) {
  process.on(sig, async () => {
    await pool.end();
    process.exit(0);
  });
}

Output: in-flight queries finish; new checkouts fail; process exits cleanly.

Performance tuning

• Prepared statements via name. Pass name to pool.query({ text, name, values }) and Postgres caches the plan. Use for hot queries with stable shapes.
• Avoid SELECT *. Always project explicit columns; the driver parses every returned cell.
• Type parsers cost CPU. pg-types parses bigints to strings (avoiding precision loss) and timestamps to Date (slow). Override for hot paths: pg.types.setTypeParser(1114, (v) => v) returns ISO strings instead of Date.
• rowMode: "array". Returns each row as an array, skipping column-name lookup. Twice as fast for wide rows that you never inspect by name.
• Cursor for large result sets. Don't SELECT * FROM big_table into memory; use pg-cursor to stream.
• Pool max capped by latency. Doubling pool size only helps if connections are saturated. Measure with pool.totalCount / pool.idleCount / pool.waitingCount.
• Disable JIT for short queries. Set jit_above_cost = 1e9 in postgresql.conf if planning dominates; saves milliseconds.

Version migration guide

The 8.x line has been stable for years. Migration from 7 is mostly removing callback wrappers.

Security considerations

• Always parameterize. Never use string concatenation or template literals to build SQL. Pass [] values.
• Identifier interpolation requires pg-format. If you must inject a column or table name dynamically, use pg-format's %I specifier. Never bind identifiers via $1.
• Reject self-signed certs in production. ssl: { rejectUnauthorized: false } disables the very check that makes TLS meaningful.
• Connection string in env, not source. Use DATABASE_URL with secret management; never commit credentials.
• Least-privilege roles. The app should connect as a role with only the rights it needs — migrations run as a separate, more-privileged role.
• statement_timeout and idle_in_transaction_session_timeout. Set in Postgres or per-session to bound runaway queries.
• Audit pg-format boundaries. Identifier interpolation is sometimes unavoidable (dynamic columns); audit those code paths.
• Don't log query.values. Bound parameters contain PII / secrets in many apps. Mask in middleware.

Testing & CI integration

For unit tests, options range from real Postgres in Docker to in-process emulation.

pg-mem — pure-JS in-memory Postgres-compatible store. Fast (no docker), but does not implement every Postgres feature (no JSONB indexing, partial CTE coverage, no extensions).

import { newDb } from "pg-mem";

const db = newDb();
const { Pool } = db.adapters.createPg();
const pool = new Pool();

await pool.query("CREATE TABLE users (id text, email text)");
await pool.query("INSERT INTO users VALUES ($1, $2)", ["u-1", "alice@example.com"]);
const { rows } = await pool.query("SELECT * FROM users");
console.log(rows);

Output: [{ id: 'u-1', email: 'alice@example.com' }]. Fast tests; some Postgres features unsupported.

testcontainers — spin up a real Postgres container per test suite. Slower but full fidelity.

import { GenericContainer } from "testcontainers";

const container = await new GenericContainer("postgres:16")
  .withEnvironment({ POSTGRES_PASSWORD: "test" })
  .withExposedPorts(5432)
  .start();
const url = `postgres://postgres:test@${container.getHost()}:${container.getMappedPort(5432)}/postgres`;

Output: ephemeral Postgres usable for any feature, torn down after the suite.

CI workflow:

services:
  postgres:
    image: postgres:16
    env: { POSTGRES_PASSWORD: test }
    options: >-
      --health-cmd pg_isready --health-interval 10s --health-timeout 5s --health-retries 5
    ports: ["5432:5432"]

Ecosystem integrations

Troubleshooting common errors

Connection terminated unexpectedly — long-lived connection dropped by the server (idle timeout, network blip). The pool will replace it on the next checkout, but in-flight queries fail. Catch and retry idempotent reads.

password authentication failed — credentials wrong or role lacks LOGIN. Verify with psql "$DATABASE_URL".

SSL connection has been closed unexpectedly — TLS misconfiguration or proxy dropping the connection. Test with psql sslmode=require.

no pg_hba.conf entry for host — Postgres rejects the client IP / SSL mode. Adjust pg_hba.conf or supply sslmode=require.

too many connections for role — pool max too high or PgBouncer not in front. Reduce max; deploy PgBouncer.

canceling statement due to statement timeout — query exceeded statement_timeout. Optimize or raise the timeout for this session via SET LOCAL statement_timeout = '60s'.

invalid input syntax for type ... — a parameter doesn't coerce to the column type. Inspect the value; cast explicitly or convert client-side.

column does not exist — typo or unquoted camelCase identifier. Postgres folds unquoted identifiers to lowercase; either rename to snake_case or quote: SELECT "createdAt".

deadlock detected — two transactions waiting on each other's locks. Postgres aborts the later one. Retry with backoff.

Pool exhausted (queries hang) — too many checkouts not released. Common cause: forgetting client.release() in a finally. Audit with pool.totalCount vs pool.idleCount.

When NOT to use this

• Edge runtimes. pg uses Node's net module and won't run on Cloudflare Workers / Vercel Edge directly. Use Neon's @neondatabase/serverless (HTTP-over-Postgres) or PostgresJS over Tcp/WS via a connection pooler.
• You need an ORM, not a driver. If you don't want to write SQL, jump to Prisma / Drizzle. pg is purposely low-level.
• You don't actually use Postgres. pg only speaks the Postgres protocol; MySQL → mysql2, SQLite → better-sqlite3, Mongo → mongoose.
• You need built-in connection multiplexing for serverless. Serverless platforms open new processes per request. Use PgBouncer or a serverless-aware driver (@neondatabase/serverless, postgres.js with prepare: false).
• You're more comfortable with tagged-template SQL. postgres (porsager) is a smaller, faster, modern alternative with sql`SELECT ...` ergonomics.

See also

• JavaScript: Node runtime — process model, streams, async
• Concept: async — promises, queues, backpressure
• Concept: api — driver vs ORM design boundaries