postgres
Postgres.js, published on npm as `postgres`, is a PostgreSQL client for Node, Deno, Bun, and Cloudflare Workers built around ES6 tagged template literals. You call postgres() once to get an sql function, then write queries as sql`select * from users where id = ${ id }`. Any value you interpolate is pulled out and sent to the server as a bound parameter, so parameterization happens whether you remember it or not. The same sql function doubles as a helper: passing it an object builds an insert or update column list, passing it an array builds a value list, and passing it another sql fragment nests a piece of a query inside a larger one. It has zero runtime dependencies, connects lazily, manages its own pool, and creates prepared statements automatically for anything that looks static. It is a driver, not an ORM: no models, no migrations, no schema.
For hand-written SQL in a modern Node, Bun, or Workers service, Postgres.js has the nicest API of any Postgres client and the zero-dependency install is a real advantage. Go in knowing you own the type story, that numeric and bigint arrive as strings, and that the project is one person shipping patch releases rather than a roadmap.
Use it if
- You write SQL by hand and want the safe path to also be the easy path. The tagged template makes string concatenation awkward on purpose, so accidental injection takes real effort
- You want a data layer with no transitive dependencies at all. The package installs one thing and pulls in nothing else, which shortens your audit surface considerably
- You are deploying somewhere other than plain Node. It ships separate export conditions for Bun and for Cloudflare's workerd runtime, and works with Hyperdrive by passing the binding's connection string
- You need Postgres features that most clients bury: LISTEN and NOTIFY with automatic reconnect, logical replication via sql.subscribe(), and COPY exposed as normal Node readable and writable streams
- You are tired of writing insert helpers. sql(user, 'name', 'age') builds the column list and placeholders, and passing an array of objects builds a multi-row insert in one statement
- You want conditional query building without a query builder library. Nesting sql`` fragments inside a query lets you add a where clause or an order by based on a flag, still parameterized
- You need the widest possible tool compatibility. `pg` is the reference client for Node, and plenty of migration tools, admin utilities, and ORMs only speak its interface. Drizzle and Kysely both have Postgres.js adapters, but check before you commit
- You expect numbers back from numeric columns. count(*) returns a string because it is a bigint, and numeric and decimal always come back as strings, since JavaScript has no safe representation for them. You opt into BigInt with a custom type, and there is no built-in answer for decimals at all
- You want the compiler to know your schema. TypeScript support is a generic you assert yourself, sql<User[]>`...`, with nothing checking that the select list matches. If typed rows are the goal, put Kysely or Drizzle on top
- You hand user input anywhere near a query shape. Values are safe, but sql(someString) means identifier, and it will happily quote and insert whatever column or table name arrives from a request body. The README warns about this and it is still the easiest way to get burned here
- You sit behind PgBouncer in transaction pooling mode without prepared statement support configured. Automatic prepared statements are on by default and will produce intermittent errors until you set prepare: false, which also gives up part of the performance argument
- You want a project moving forward. It is essentially one maintainer, the 3.4 line has only received patch releases since 2023 (3.4.8 in January 2026, 3.4.9 in April 2026), and there are 226 open issues (270 counting PRs). It is stable rather than stagnant, but features requested in issues are not arriving
- Your legal review has an allowlist of licenses. This is released under the Unlicense, a public domain dedication rather than a conventional permissive license, and some corporate policies do not accept it
Setup reality
npm install postgres installs exactly one package with no dependencies, and there is no native code or build step. It is ESM first with a CommonJS fallback in the exports map, plus dedicated bun and workerd conditions. The behaviour that catches people is that connecting is lazy: const sql = postgres(...) opens nothing, so a wrong password or an unreachable host does not surface until the first query, which is usually deep inside a request handler rather than at boot. If you want fail-fast startup, run a select 1 yourself. Then read the defaults before shipping. max is 10 connections per sql instance, so count your worker processes; idle_timeout is 0, meaning idle connections are held open forever, which is wrong for Lambda and for any provider that reaps idle sessions; max_lifetime defaults to a randomized interval derived from 60 * (30 + Math.random() * 30) seconds, and the README describes that range inconsistently in two different places, so treat the formula as the source of truth. fetch_types is on and issues a pg_catalog query on first connect, which fails if you have revoked catalog access. Two errors are specific to this library and both show up on day one: UNDEFINED_VALUE, because passing undefined as a parameter is rejected rather than coerced to null unless you set transform.undefined, and UNSAFE_TRANSACTION, because a begin and commit issued as separate queries can land on different pooled connections and so must go through sql.begin() instead. Finally, call await sql.end({ timeout: 5 }) on shutdown or the process will hang.
Patterns
Create the client and run a queryconnect-and-query
import postgres from 'postgres'
const sql = postgres(process.env.DATABASE_URL, {
max: 10,
idle_timeout: 20,
connect_timeout: 10,
ssl: 'require',
})
const users = await sql`
select id, email from users where age > ${ 21 }
`
users[0].email
users.count // rows affected, useful for insert/update/delete
users.command // 'SELECT'
// TypeScript: the generic is an assertion, nothing validates it
const typed = await sql<{ id: number }[]>`select id from users`The result is an Array subclass, so users.length works and JSON.stringify(users) gives a plain array. Queries are lazy promises that only run when awaited, which is how nested fragments are distinguished from top-level queries; call .execute() if you need it sent in the current tick.
Insert objects without writing the column listdynamic-inserts
const user = { name: 'Murray', age: 68, secret: 'nope' }
// pick the columns explicitly
const [row] = await sql`
insert into users ${ sql(user, 'name', 'age') } returning *
`
// many rows in one statement
const users = [{ name: 'Ada', age: 36 }, { name: 'Alan', age: 41 }]
await sql`insert into users ${ sql(users, 'name', 'age') }`
// upsert
await sql`
insert into users ${ sql(user, 'name', 'age') }
on conflict (name) do update set age = excluded.age
`Calling sql(user) with no column names inserts every key on the object, so a request body passed straight in lets a caller set columns you never intended. Always name the columns. For the array form, every object must have the same keys or the generated value tuples will not line up.
Update only the fields that changeddynamic-updates
const patch = { name: 'Murray', age: 68 }
const columns = Object.keys(patch).filter(k => allowed.has(k))
await sql`
update users set ${ sql(patch, columns) }
where id = ${ userId }
`
// multiple rows in one round trip
const rows = [[1, 'John', 34], [2, 'Jane', 27]]
await sql`
update users set name = u.name, age = (u.age)::int
from (values ${ sql(rows) }) as u (id, name, age)
where users.id = (u.id)::int
`The values form of a bulk update sends everything as text, so each column needs an explicit cast on the way out or Postgres complains about the type. Filter the column list against an allowlist before passing it in; sql() quotes identifiers but does not restrict which ones you can name.
Interpolate values, identifiers, and lists correctlyvalues-vs-identifiers
const ids = [68, 75, 23]
const table = 'users'
const column = 'created_at'
await sql`select * from users where age in ${ sql(ids) }`
// -> where age in ($1, $2, $3)
await sql`select * from ${ sql(table) } order by ${ sql(column) } desc`
// -> from "users" order by "created_at" desc
// wrong: quoting a value turns the placeholder into a literal string
// await sql`select * from users where name = '${ name }'` // '$1'Four different meanings depending on what you interpolate: a plain value becomes a bound parameter, sql(string) becomes a quoted identifier, sql(array) becomes a value list, and a nested sql`` becomes raw SQL. Wrapping a placeholder in quotes is the classic mistake and produces a query comparing against the literal text $1.
Build a query from optional filtersconditional-query
const filters = [
minAge != null && sql`and age >= ${ minAge }`,
country && sql`and country = ${ country }`,
].filter(Boolean)
const rows = await sql`
select * from users
where deleted_at is null
${ filters.length ? filters.reduce((a, b) => sql`${ a } ${ b }`) : sql`` }
order by id
limit ${ limit }
`An empty sql`` fragment is the no-op, which is what makes the ternary pattern work. Fragments are lazy and only execute as part of the outer query, so never await one on its own or you will run a broken partial statement.
Run a transaction and roll back on errortransactions
const [user, account] = await sql.begin(async sql => {
const [user] = await sql`
insert into users (name) values (${ name }) returning *
`
const [account] = await sql`
insert into accounts (user_id) values (${ user.id }) returning *
`
return [user, account]
})
// isolation level and savepoints
await sql.begin('read write, isolation level serializable', async sql => {
await sql.savepoint(async sql => {
await sql`insert into audit (msg) values ('maybe')`
}).catch(() => { /* savepoint rolled back, transaction continues */ })
})Use the sql passed into the callback, not the outer one; the outer instance takes a different pooled connection and its statements land outside the transaction. Issuing begin and commit as separate queries throws UNSAFE_TRANSACTION rather than silently doing the wrong thing, which is the one guardrail here.
Iterate a big result set with a cursorstream-large-results
// one row at a time
for await (const [row] of sql`select * from events`.cursor()) {
await handle(row)
}
// batches of 500
await sql`select * from events`.cursor(500, async rows => {
await bulkHandle(rows)
})
// stop early
await sql`select * from events`.cursor(row => {
return done ? sql.CLOSE : undefined
})The async iterator yields an array even in single-row mode, which is why the destructuring [row] is there. The callback form waits for your promise before requesting more rows, so it applies backpressure for free. Throwing inside the callback stops the cursor and rejects the outer promise.
Bulk load and export with COPYcopy-streams
import { pipeline } from 'node:stream/promises'
import { createWriteStream } from 'node:fs'
import { Readable } from 'node:stream'
const rows = Readable.from(['Murray\t68\n', 'Walter\t80\n'])
const writable = await sql`copy users (name, age) from stdin`.writable()
await pipeline(rows, writable)
const readable = await sql`copy (select name, age from users) to stdout`.readable()
await pipeline(readable, createWriteStream('users.tsv'))This is raw protocol plumbing with no type safety: the tab-delimited text has to match the column list exactly, and a mismatch surfaces as a Postgres error mid-stream. COPY ignores on conflict, so load into a temporary table first if you need upsert behaviour.
React to Postgres notificationslisten-notify
await sql.listen(
'jobs',
payload => run(JSON.parse(payload)),
() => sql`select * from unfinished_jobs()`.forEach(run),
)
await sql.notify('jobs', JSON.stringify({ id: 7 }))listen() opens its own dedicated connection outside the pool and reconnects with backoff on its own. The third argument runs on every connect and reconnect, which is the hook for replaying whatever you missed while disconnected; without it a network blip silently drops notifications.
Handle bigint, numeric, and undefinedbigint-and-numeric
const sql = postgres(url, {
types: { bigint: postgres.BigInt }, // opt in to real BigInt
transform: { undefined: null }, // undefined becomes NULL
})
const [{ count }] = await sql`select count(*) from users`
// without the types option above: count is the string '42'
// numeric / decimal always arrive as strings
const [{ total }] = await sql`select sum(amount) as total from orders`By default bigint columns and count(*) come back as strings, because BigInt does not survive JSON.stringify. numeric and decimal have no safe JavaScript representation at all and stay strings whatever you configure, so parse them with a decimal library rather than Number(). Passing undefined as a parameter throws UNDEFINED_VALUE unless you set transform.undefined.
Map snake_case columns to camelCasecamel-case-columns
import postgres from 'postgres'
const sql = postgres(url, { transform: postgres.camel })
await sql`insert into users ${ sql([{ firstName: 'Ada' }]) }`
const rows = await sql`select ${ sql('firstName') } from users`
// rows -> [{ firstName: 'Ada' }]
// one direction only
postgres(url, { transform: postgres.toCamel }) // results only
postgres(url, { transform: postgres.fromCamel }) // parameters onlyThe transform does not touch the static text of your template, only values passed through the sql() helper. So select first_name still returns first_name, and you have to write select ${ sql('firstName') } for the mapping to apply. This surprises everyone at least once.
Size the pool and shut down cleanlypooling-and-shutdown
const sql = postgres(url, {
max: 10, // per sql instance, per process
idle_timeout: 20, // seconds; default 0 keeps sockets open forever
max_lifetime: 60 * 30,
prepare: false, // required behind PgBouncer transaction pooling
})
// isolate work on one connection (session settings, advisory locks)
const reserved = await sql.reserve()
try {
await reserved`set local statement_timeout = 5000`
await reserved`select pg_advisory_lock(1)`
} finally {
reserved.release()
}
process.on('SIGTERM', async () => {
await sql.end({ timeout: 5 })
})Connections open lazily, so max is a ceiling and not a startup cost, but four workers at max: 10 is still forty sessions. idle_timeout: 0 is the wrong default for serverless and for providers that reap idle connections. Skipping sql.end() leaves the event loop with open handles and the process never exits.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| pg | npm | You want the client with the broadest third-party support, or a tool in your stack expects the node-postgres interface specifically |
| drizzle-orm | npm | You want typed queries and migrations derived from a schema, with Postgres.js still doing the talking underneath |
| slonik | npm | You like the tagged template style but want runtime validation of result shapes with Zod and stricter query safety rules |