Postgres.js

Apply these conventions when working with postgres.js (postgres package from porsager/postgres, current stable 3.4.x) in Node.js, Bun, Deno, or Cloudflare Workers projects.

Connection Setup

• Create the connection with postgres(url, options) or postgres(options).
• Falls back to psql-style environment variables when not specified: PGHOST, PGPORT, PGDATABASE, PGUSERNAME/PGUSER, PGPASSWORD, PGAPPNAME, PGIDLE_TIMEOUT, PGCONNECT_TIMEOUT.
• Key options: max (pool size, default 10; lower in Cloudflare Workers), idle_timeout, max_lifetime (randomized by default to recycle prepared statements), connect_timeout (default 30s), prepare (default true), fetch_types (default true), sslnegotiation: 'direct' when the server/provider requires direct SSL negotiation.
• PGBouncer transaction mode: use prepare: false unless the deployment is PGBouncer 1.21+ configured with max_prepared_statements for protocol-level named prepared statements.
• Multi-host HA: postgres('postgres://host1:5432,host2:5433', { target_session_attrs: 'primary' }).
• Dynamic auth: password option accepts an async function returning a token.

Query Syntax

• All queries use tagged template literals: sql`select * from users where age > ${age}`.
• Parameters are sent separately from the query string — this prevents SQL injection automatically.
• Never wrap interpolated values in quotes: sql`select * from users where name = '${name}'` is wrong. The correct form is sql`select * from users where name = ${name}`.
• Results are arrays of objects with column names as keys.