---
title: postgres.js
description: Modern PostgreSQL driver using postgres.js
---

## Installation

```bash
pnpm add postgres
```

## Configuration

```typescript
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](/docs/extensions/permissions#providers) |
| `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](#prepared-statements) |

`createClient` refuses any other key with a `ClientInitializationError` that
names it.

### Using Options

```typescript
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:

```typescript
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

```typescript
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](#prepared-statements).

### With pgvector

```typescript
import { createClient } from "viborm/postgres";

const client = createClient({
  databaseUrl: process.env.DATABASE_URL,
  pgvector: true,
  schema,
});
```

### With GeoPoint

```typescript
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](/docs/schema/scalars/point) 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`.

```typescript
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:

```sql
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](/docs/drivers/namespaces).

## Transactions

postgres.js supports full transactions with automatic savepoints for nested transactions — see [Transactions](/docs/client/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`](/docs/drivers/postgresql/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):

```typescript
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](/docs/drivers/postgresql/pg#choosing-per-call-named). 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`](/docs/drivers/postgresql/pg#workers-and-hyperdrive).
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.
