Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

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, port and database in options replace the URL’s. An ssl option replaces the URL’s ssl, sslmode, sslcert, sslkey and sslrootcert; any other option replaces the query parameter of the same name.
  • options.connectionString, when set, is used instead of databaseUrl.
  • sslrootcert=system, as PlanetScale’s and Neon’s URLs carry it, means TLS verified against the platform’s trust store (libpq’s sslmode=verify-full). node-postgres would read it as a file named system, so VibORM rewrites it to sslmode=verify-full.
  • A password callback (node-postgres’s function form) cannot be merged into a URL: pass it with host, user and database in options, without a URL. Beside a URL (databaseUrl or options.connectionString) it is refused at construction with ClientInitializationError (V1004).
  • To merge, VibORM reads the URL with the WHATWG URL parser. A form it cannot read, such as libpq’s /var/run/postgresql mydb, combined with connection options fails at construction with ClientInitializationError.

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 max named statements (100 with true). Each costs the server memory for the connection’s life: 11 kB for a findUnique to 42 kB for a findMany with 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 DEALLOCATE or DISCARD 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 max names 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 larger max) 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: true names each statement of the call at its first sending, within max. The name is still derived from the text: a call writes different texts as its arguments vary (a where left out, an include more), and a name stands for one text.
  • named: false never 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 call or an around, not the key of a $withCache read. 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 max as 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.

Was this page helpful?