pg (node-postgres)
PostgreSQL driver using node-postgres with connection pooling
Installation
pnpm add pg
Configuration
import { createClient } from "viborm/pg";
const client = createClient({
databaseUrl: process.env.DATABASE_URL,
schema,
});
Options
| Option | Type | Description |
|---|---|---|
pool |
Pool |
Existing pg Pool, or a pool with its shape such as a Neon WebSocket Pool |
options |
PoolConfig |
pg pool configuration |
databaseUrl |
string |
PostgreSQL connection URL |
namespace |
string |
PostgreSQL schema for VibORM-owned objects (default public) |
pgvector |
boolean |
Enable pgvector support |
postgis |
boolean |
Enable PostGIS support |
sharedRelationBodies |
boolean |
State each relation-filter body a statement repeats once, in a WITH (default true; false keeps the bodies inline) |
relationsPerStatement |
number |
The most relations (FROM items) one statement may reference (PlanetScale Neki: 256); a statement over it is rewritten to fit. See Neki |
migrationSessionAttestation |
"dedicated-session" |
Your claim, at your risk, that every connection of the pool is one server session for its whole life; lifts the migration refusal of Hyperdrive and Neon -pooler hosts (see Migrations through a pooler) |
namedStatements |
boolean | "explicit" | { max: number; auto?: boolean } |
Send statements as named prepared statements, at most max per connection (100 with true or "explicit"): the ones VibORM repeats, and the calls that pass named: true; with "explicit" or auto: false, only those calls. Off by default. See Named statements |
createClient refuses any other key with a ClientInitializationError that
names it.
Using Pool Options
import { createClient } from "viborm/pg";
const client = createClient({
options: {
host: "localhost",
port: 5432,
user: "postgres",
password: "password",
database: "mydb",
max: 20,
},
schema,
});
URL and Options Together
databaseUrl and options combine key by key: the URL supplies only the
connection keys it carries, and every key set in options wins over the URL’s.
A URL per tenant with the password from a secret works as written:
import { createClient } from "viborm/pg";
const client = createClient({
databaseUrl: "postgres://tenant_7@db.internal:5432/tenant_7",
options: { password: env.TENANT_DB_PASSWORD },
schema,
});
user,password,host,portanddatabaseinoptionsreplace the URL’s. Anssloption replaces the URL’sssl,sslmode,sslcert,sslkeyandsslrootcert; any other option replaces the query parameter of the same name.options.connectionString, when set, is used instead ofdatabaseUrl.sslrootcert=system, as PlanetScale’s and Neon’s URLs carry it, means TLS verified against the platform’s trust store (libpq’ssslmode=verify-full). node-postgres would read it as a file namedsystem, so VibORM rewrites it tosslmode=verify-full.- A password callback (node-postgres’s function form) cannot be merged into a
URL: pass it with
host,useranddatabaseinoptions, without a URL. Beside a URL (databaseUrloroptions.connectionString) it is refused at construction withClientInitializationError(V1004). - To merge, VibORM reads the URL with the WHATWG
URLparser. A form it cannot read, such as libpq’s/var/run/postgresql mydb, combined with connection options fails at construction withClientInitializationError.
databaseUrl: undefined or "" (usually an unset environment variable) with no
pool, host or connectionString fails at construction with
ClientInitializationError (V1004) instead of connecting wherever
node-postgres’s defaults point. Leave the key out to use those defaults (the
PG* environment variables, then localhost:5432).
Using Existing Pool
import { Pool } from "pg";
import { createClient } from "viborm/pg";
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
pool.on("error", (error) => {
console.error("PostgreSQL idle connection failed", error);
});
const client = createClient({
pool,
schema,
});
A supplied pool remains owned by your application. Install its idle-connection
error listener before passing it to VibORM and close it once with pool.end().
VibORM applies temporal parsers per command and leaves supplied pool listeners
and lifecycle under your control.
Neon WebSocket Pool
A Pool from @neondatabase/serverless is node-postgres’s pool over a
WebSocket, and runs as the supplied pool unchanged. viborm/pg still
imports pg, so a Worker installs pg next to @neondatabase/serverless and
enables the nodejs_compat compatibility flag:
import { Pool } from "@neondatabase/serverless";
import { createClient } from "viborm/pg";
const pool = new Pool({ connectionString: env.DATABASE_URL });
const client = createClient({ pool, schema });
pool accepts any pool with the part of node-postgres’s shape VibORM uses, so
Neon’s Pool type-checks whatever @types/pg your project installs (tested
with 8.16.0 and 8.23.1). It is a supplied pool: you own its error listener and
its end(), as above. It holds a session for interactive transactions and
migrations; run migrations through the direct endpoint, since a -pooler host
is refused for effectful migration commands (see
Migrations through a pooler).
It was qualified against hosted Neon (PostgreSQL 18) from a Worker on
2026-10-11: generate, apply, status, verify and log through the
direct endpoint, with no advisory lock left behind, and apply through the
-pooler URL refused with V8002 before any connection. For one-shot HTTP
queries, use Neon HTTP.
With pgvector
import { createClient } from "viborm/pg";
const client = createClient({
databaseUrl: process.env.DATABASE_URL,
pgvector: true,
schema,
});
With GeoPoint
Install PostGIS in the target database, then opt the driver into the exact GeoPoint protocol:
const client = createClient({
databaseUrl: process.env.DATABASE_URL,
postgis: true,
schema,
});
VibORM does not install the extension. A schema containing s.point() is
refused at client construction when postgis is absent. Live migrations also
prove the exact PostGIS-owned geography type and functions before effects. The
pg provider contract qualifies the full GeoPoint
tier, including GiST plans.
Namespace
namespace selects the PostgreSQL schema holding this driver’s tables,
junctions, indexes, ORM-managed enums, and migration tracking table. Omitted, it
is public.
import { createClient } from "viborm/pg";
const db = createClient({
databaseUrl: process.env.DATABASE_URL,
namespace: "billing",
schema,
});
Generated SQL is always qualified, so routing never depends on search_path —
including when pool options set one:
SELECT "user"."id" FROM "billing"."user" AS "user"
Because the pool is an ordinary constructor input, one externally owned Pool
can serve several schema-scoped clients at once, each with its own
namespace-bound migration estate:
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
pool.on("error", (error) => {
console.error("PostgreSQL idle connection failed", error);
});
const acme = createClient({ pool, namespace: "tenant_acme", schema: acmeModels });
const globex = createClient({ pool, namespace: "tenant_globex", schema: globexModels });
// the test or the application closes `pool` once, not each client
pg can pin one session, so it supports the full migration verb set against the
selected schema. Raw SQL is never rewritten and keeps ordinary search_path
semantics — see Namespaces.
Named statements
By default every statement is sent unnamed: the server parses and plans it each
time, and keeps nothing once it has run. namedStatements sends statements
under a name instead, so each connection parses and plans one once and then
only binds it:
import { createClient } from "viborm/pg";
const client = createClient({
databaseUrl: process.env.DATABASE_URL,
schema,
namedStatements: true, // or { max: 50 }, or "explicit"
});
Measured on a local PostgreSQL 18, a findUnique takes about 20 % less time
and 50 of them at once about 25 % less; a read of 1,000 rows gains less, as the
plan is a smaller part of it. node-postgres sends a statement in one round trip
named or not, so the number of round trips does not change.
What it does:
- With
true({ max: 100, auto: true }), a statement is named the second time VibORM sends it, under a name derived from its text, so a shape sent once never takes a place. Two clients sharing one pool give the same statement the same name. - Only statements VibORM writes, with values, are named. Raw SQL and statements without values are sent as before.
- A connection holds at most
maxnamed statements (100 withtrue). Each costs the server memory for the connection’s life: 11 kB for afindUniqueto 42 kB for afindManywith a relation as JSON, and 1.3 to 4.2 MB for a read of 141 to 251 relations, measured on PostgreSQL 18. - When the server no longer has a statement’s name (after
DEALLOCATEorDISCARD ALL), or the table under it changed (a column type, an enum a migration replaced), the statement is sent again once, unnamed, and answers as it would have without the option; it is named anew next time. Inside a transaction the server’s failure aborts the transaction, so that failure is what the transaction answers; the next transaction parses again. - The bound counts every name the client gave, stale ones included, since the
connections that did not lose a name still hold it. Once
maxnames were given, a statement whose name goes stale is sent unnamed for the client’s life. After migrations that change many tables, a new client (or a largermax) names them again.
Choosing per call: named
Every operation takes named, beside its payload:
// A heavy read that repeats: named from its first sending.
await client.document.findMany({ where: { teamId }, include: { blocks: true }, named: true });
// Never named, even in the automatic mode.
await client.user.findMany({ where: { email: { startsWith: q } }, named: false });
// Every statement of a nested write follows the option.
await client.order.create({ data: { ...order, lines: { create: lines } }, named: true });
named: truenames each statement of the call at its first sending, withinmax. The name is still derived from the text: a call writes different texts as its arguments vary (awhereleft out, anincludemore), and a name stands for one text.named: falsenever names the call’s statements.- Left out, the client’s mode decides: the second sending with
true, never with"explicit". - Without
namedStatements, it does nothing: every statement is sent exactly as without it. Every client accepts it, on every database, so code written for pg runs unchanged on PGlite, SQLite, MySQL, D1 or Neon HTTP, where it does nothing either. - It changes neither the SQL nor what an extension sees: not the arguments
of a
callor anaround, not the key of a$withCacheread. In$transaction([...])each operation carries its own; in an interactive transaction, each call.
With namedStatements: "explicit" (or { max, auto: false }), only the calls
that pass named: true are named: every place goes to what you chose, rather
than to the first shapes seen twice. A named: true call that finds every
place taken is sent unnamed, and VibORM writes a warning once per statement
(console.warn, starting VibORM (pg): a call passed named: true): raise
max, or name fewer calls.
Some statements are slower named. After five executions PostgreSQL may switch
a named statement to a generic plan, which cannot use the value it is bound
to: a prefix LIKE (startsWith) measured 26 % slower named on a plain
PostgreSQL 18. Pass named: false to such a call.
Workers and Hyperdrive
On Cloudflare Workers a client lives for one request, so the automatic mode
never sees a statement twice and names nothing. named: true is the only way
to name there:
- Hyperdrive passes VibORM’s names through to the database and keeps them on its own connections to it, from one session to the next; it prepares a name on a connection where it is missing by itself. Since the name is derived from the text, every isolate gives a statement the same name. Measured through Hyperdrive with a client per request: 5 parses for 80 executions.
- On a small read the gain is database CPU (0.06 to 0.13 ms per call), not latency. On heavy reads it is both: on PlanetScale Neki, reads of 141 to 251 relations took 25 to 70 ms less named (20 % to 30 %), through Hyperdrive with a new client per request as directly. Neki never switches to a generic plan.
- Memory is the cost. Hyperdrive keeps the names on connections the whole
fleet shares, and each heavy named read holds megabytes per connection. Name
the heavy reads that repeat, and keep
maxas the bound.
On Workers, prefer viborm/pg: postgres.js sends an unprepared statement with
values in two round trips (see postgres.js prepared statements).
Behind PgBouncer in transaction mode, named statements need
max_prepared_statements set (PgBouncer 1.21 or later; its default is 200
since 1.24). Set it to at least max. Without it, a transaction that lands on
another server connection does not find the name: each such statement fails
once, then runs unnamed. Other poolers have their own policy; leave the option
off unless yours keeps prepared statements.
Transactions
pg supports full transactions with savepoints for nested transactions — see Transactions.