pg
pg (node-postgres) is the standard low-level PostgreSQL client for Node.js: a non-blocking driver that speaks the Postgres wire protocol in pure JavaScript, with optional native libpq bindings behind the same API. It gives you parameterized queries, connection pooling via the bundled pg-pool, LISTEN/NOTIFY, COPY support, and extensible type coercion between JS and Postgres. It is deliberately light on abstractions: you write SQL strings, it moves rows. Nearly every higher-level Postgres tool in the Node ecosystem, from Knex to many ORM drivers, sits on top of it, and the docs also work on Bun, Deno, and Cloudflare (via pg-cloudflare).
The boring, correct choice for talking to Postgres from Node when you are comfortable writing SQL and reading the pooling docs. If you want types, migrations, or query building, put Drizzle or Knex on top rather than replacing it.
Use it if
- You want to write actual SQL against Postgres with parameterized queries and no ORM layer in the way
- You need Postgres-specific features like LISTEN/NOTIFY for pub/sub or COPY FROM/TO for bulk loads, which most ORMs hide or skip
- You are building a library or tool on Postgres; pg is the ecosystem's common denominator and what most tooling already depends on
- You need connection pooling that you control directly, including checkout, transactions on a dedicated client, and pool sizing
- You want type-safe queries or schema migrations out of the box; pg has neither, and Drizzle or Prisma will save you from hand-rolled string SQL and runtime type surprises
- You will never read the pooling docs; the failure modes are real, from leaked clients that exhaust the pool to an unhandled pool 'error' event that crashes the process when an idle connection drops
- Every value coming back as the right JS type matters to you without config; bigint and numeric columns arrive as strings by default and you must register type parsers yourself
- You want a friendlier query-composition layer; pg-promise or a query builder like Knex handles multi-row inserts, named params, and query helpers that raw pg makes verbose
Setup reality
npm install pg and it just works: pure JavaScript, no native compilation, Node 16+. Configuration is via standard PG* environment variables, a connection string, or an options object. The annoying parts are operational, not install-time: you must attach a pool.on('error') handler or a dropped idle connection can take down the process; transactions require checking out a client and releasing it in a finally block, and a missed release() quietly starves the pool; SSL against managed Postgres (RDS, Supabase, Neon) usually needs an ssl option that the connection string alone does not fully express; and streaming or cursors mean installing the sibling packages pg-query-stream or pg-cursor from the same monorepo.
Patterns
Query through a shared poolpool-query
import pg from 'pg'
const { Pool } = pg
const pool = new Pool() // reads PGHOST, PGUSER, PGPASSWORD, PGDATABASE
const { rows } = await pool.query('SELECT NOW()')
console.log(rows[0])Create one Pool per process and share it; a new Pool per request destroys the point of pooling and exhausts Postgres connections.
Parameterized query (no SQL injection)parameterized-query
const { rows } = await pool.query(
'SELECT * FROM users WHERE email = $1 AND active = $2',
[email, true]
)Placeholders are $1, $2 (not ?), and parameters only work for values, never for table or column names.
Transaction on a dedicated clienttransaction
const client = await pool.connect()
try {
await client.query('BEGIN')
await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, from])
await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, to])
await client.query('COMMIT')
} catch (e) {
await client.query('ROLLBACK')
throw e
} finally {
client.release()
}pool.query cannot do transactions because each call may use a different connection; forget release() and the pool eventually starves.
Handle errors on idle clientspool-error-handling
pool.on('error', (err) => {
console.error('idle client error', err)
})Without this handler, a backend disconnect on an idle pooled connection emits an unhandled 'error' event and crashes Node. Non-negotiable in production.
Connection string and SSL configconnection-config
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: { rejectUnauthorized: false }, // many managed PG hosts need this or a CA cert
max: 10,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 5000
})rejectUnauthorized: false accepts any cert; prefer ssl: { ca } with your provider's CA bundle when you can.
Pub/sub with LISTEN/NOTIFYlisten-notify
const client = new pg.Client()
await client.connect()
client.on('notification', (msg) => {
console.log(msg.channel, msg.payload)
})
await client.query('LISTEN job_updates')
// elsewhere: await pool.query("NOTIFY job_updates, 'done'")Use a dedicated long-lived Client, not the pool; a pooled connection can be recycled and silently stop listening.
Get bigint and numeric back as numberstype-parsing
import pg from 'pg'
// int8 (OID 20) and numeric (OID 1700) arrive as strings by default
pg.types.setTypeParser(20, (val) => parseInt(val, 10))
pg.types.setTypeParser(1700, (val) => parseFloat(val))The string default exists because JS numbers lose precision past 2^53; only override if your values are safely in range.
Insert many rows in one statementbulk-insert
const values = users.flatMap(u => [u.name, u.email])
const placeholders = users
.map((_, i) => `($${i * 2 + 1}, $${i * 2 + 2})`)
.join(', ')
await pool.query(
`INSERT INTO users (name, email) VALUES ${placeholders}`,
values
)pg has no built-in bulk helper; for very large loads use COPY FROM via the pg-copy-streams package instead of giant VALUES lists.
Named prepared statement with plan cachingprepared-statement
const result = await pool.query({
name: 'fetch-user',
text: 'SELECT * FROM users WHERE id = $1',
values: [id]
})The name caches the parsed plan per connection; keep names unique per SQL text or you get a prepared-statement conflict error.
Stream large result setsstreaming-rows
import QueryStream from 'pg-query-stream'
const client = await pool.connect()
const stream = client.query(new QueryStream('SELECT * FROM big_table'))
stream.on('end', () => client.release())
for await (const row of stream) {
process(row)
}pg-query-stream is a separate install from the same monorepo; without it a huge SELECT buffers every row in memory.
One-off script without a poolsingle-client-script
import pg from 'pg'
const client = new pg.Client({ connectionString: process.env.DATABASE_URL })
await client.connect()
const { rows } = await client.query('SELECT COUNT(*) FROM users')
await client.end()Forgetting client.end() leaves the process hanging at exit; for anything serving traffic, use a Pool instead.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| postgres | npm | You want a faster, dependency-free client with tagged-template SQL instead of $1 placeholders |
| drizzle-orm | npm | You want typed schema, migrations, and SQL-shaped queries while keeping pg underneath |
| knex | npm | You want a query builder and migrations without a full ORM |
| pg-promise | npm | You want raw SQL but with promise-centric helpers, named params, and multi-row insert generation |