postgres.js
Modern PostgreSQL driver using postgres.js
Installation
pnpm add postgres
Configuration
import { createClient } from "viborm/postgres";
const client = createClient({
databaseUrl: process.env.DATABASE_URL,
schema,
});
Options
| Option | Type | Description |
|---|---|---|
client |
Sql |
Existing postgres.js client |
options |
Options |
postgres.js 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 |
namedStatements |
boolean | "explicit" | { max: number; auto?: boolean } |
Prepare 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 Prepared statements |
createClient refuses any other key with a ClientInitializationError that
names it.
Using Options
import { createClient } from "viborm/postgres";
const client = createClient({
options: {
host: "localhost",
port: 5432,
user: "postgres",
password: "password",
database: "mydb",
max: 10,
},
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 (host, port,
user, password, database, and any query parameter’s option) wins over the
URL’s. A URL per tenant with the password from a secret works as written:
import { createClient } from "viborm/postgres";
const client = createClient({
databaseUrl: "postgres://tenant_7@db.internal:5432/tenant_7",
options: { password: env.TENANT_DB_PASSWORD },
schema,
});
This is postgres.js’s own precedence; VibORM passes both through, except
sslrootcert=system (in PlanetScale’s and Neon’s URLs), which postgres.js would
send to the server as a setting it refuses: VibORM rewrites it to
sslmode=verify-full, the TLS verification it stands for.
databaseUrl: undefined or "" (usually an unset environment variable) with no
client, host or hostname fails at construction with
ClientInitializationError (V1004) instead of connecting wherever
postgres.js’s defaults point. Leave the key out to use those defaults (the
PG* environment variables, then localhost:5432).
Using Existing Client
import postgres from "postgres";
import { createClient, vibormTypes } from "viborm/postgres";
const databaseUrl = process.env.DATABASE_URL;
if (!databaseUrl) throw new Error("DATABASE_URL is required");
const sql = postgres(databaseUrl, { types: vibormTypes });
const client = createClient({
client: sql,
schema,
});
VibORM checks supplied temporal parser configuration before using the client.
vibormTypes keeps PostgreSQL dates and timestamps as text so typed queries
decode them consistently, including years before 100 and timezone offsets.
$disconnect() leaves this supplied client open; its owner calls sql.end().
Its statements stay unprepared unless you pass namedStatements (or
options: { prepare: true }) beside it and the client keeps postgres.js’s
default prepare: true; see Prepared statements.
With pgvector
import { createClient } from "viborm/postgres";
const client = createClient({
databaseUrl: process.env.DATABASE_URL,
pgvector: true,
schema,
});
With GeoPoint
const client = createClient({
databaseUrl: process.env.DATABASE_URL,
postgis: true,
schema,
});
The target must already contain PostGIS; VibORM never installs it. Without the option, a point schema is refused at client construction. postgres.js is qualified for the full GeoPoint tier, including live polygon, fixed-radius distance, GiST planning, and migration preflight.
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/postgres";
const db = createClient({
databaseUrl: process.env.DATABASE_URL,
namespace: "billing",
schema,
});
Generated SQL is always qualified, so routing never depends on the connection’s
search_path — including a search_path set through postgres.js options:
SELECT "user"."id" FROM "billing"."user" AS "user"
A supplied Sql client is bound the same way; the namespace comes from the
option, never from inspecting the client. postgres.js reserves a connection with
reserve(), 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.
Transactions
postgres.js supports full transactions with automatic savepoints for nested transactions — see Transactions.
When a physical connection failure rejects BEGIN before the callback enters,
VibORM preserves that failure and drains the provider’s stale reconnect response
through at most two acquisitions from the same pool, within one cleanup deadline
using the transaction acquisition budget (five seconds by default). It releases
each lease without running caller SQL. It never
replays the callback or closes a supplied pool. If the server is still
unreachable, the transaction rejects with that retryable connection error and
the client stays usable: no live session remains, so the next query or
transaction reconnects once the server is back, without $disconnect().
Prepared statements
Generated and raw statements are unprepared by default, for a client built from
databaseUrl or options and for a supplied one. postgres.js sends an
unprepared statement that has parameters in two round trips: it parses and
describes it, waits for the server’s parameter types, then binds and executes.
The unnamed statement leaves nothing on the server, so transaction-mode poolers
accept it. viborm/pg (node-postgres) sends the
same unnamed statement in one round trip.
Where the endpoint keeps prepared statements, opt in with namedStatements,
also beside a supplied client that keeps postgres.js’s default
prepare: true (postgres.js prepares only when both agree):
const client = createClient({
databaseUrl: process.env.DATABASE_URL,
schema,
namedStatements: true, // or { max: 50 }, or "explicit"
});
A statement VibORM writes, with values, is prepared the second time it is sent
(with true), or at its first sending when its call passes named: true, and
a connection holds at most max of them (100 with true). With "explicit"
(or { max, auto: false }), only the calls that pass named: true are
prepared. A call that passes named: false is never prepared, not even under
options: { prepare: true }. The option works as on pg, warning included: see
choosing per call. Each is then
parsed once per connection and costs one round trip after that: a transaction
of eight inserts takes 10 round trips instead of 18. On a local PostgreSQL 18,
where a round trip costs little, a findUnique already takes about 40 % less
time, and a create or an update about 20 % less. Raw SQL is not prepared.
postgres.js itself sends again, once, a statement whose name the server lost.
options: { prepare: true } still prepares every statement, raw ones included,
with no bound. Opting in, either way, means:
- After a migration that replaces an enum type (removing or reordering values,
or using a value the same migration adds), a statement with a value of that
type no longer finds the old type. With
namedStatements, it is sent again once unprepared (in a transaction, the failure is the transaction’s answer) and never prepared again by that client. Withoptions: { prepare: true }, restart or reconnect every running client: until then those statements fail withV2001. - The server keeps one prepared statement per distinct query shape (each
createManyrow count, for example) per connection until the connection reaches itsmax_lifetime: at mostmaxwithnamedStatements, all of them withoptions: { prepare: true }. Each costs 11 kB to 42 kB of server memory, measured on PostgreSQL 18. - Behind PgBouncer in transaction mode, set
max_prepared_statements(its default is 200 since 1.24) to at leastmax.
On Cloudflare Hyperdrive, prefer viborm/pg.
postgres.js names its prepared statements per client, so a new client per
request never reuses one, and it describes every statement with values it has
not prepared on its connection before running it: two round trips, for every
such statement under the default prepare: false, and for the first sending
of each text in a request otherwise. A page of three reads measured 116 ms
through Hyperdrive on postgres.js, 61 ms on pg.