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

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. With options: { prepare: true }, restart or reconnect every running client: until then those statements fail with V2001.
  • The server keeps one prepared statement per distinct query shape (each createMany row count, for example) per connection until the connection reaches its max_lifetime: at most max with namedStatements, all of them with options: { 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 least max.

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.

Was this page helpful?