---
title: PostgreSQL Driver
description: PostgreSQL migration types, enum transitions, and transaction guarantees
icon: database
---

The migration client selects this renderer for PostgreSQL drivers. SQL targets
the driver's immutable `namespace` (default `public`), including catalog reads,
DDL, locks, and control tables. It never relies on `search_path`.

## Physical types

| Declaration | Default physical type |
| --- | --- |
| `s.string()` | `TEXT` |
| `s.int()` | `INTEGER` |
| `s.bigInt()` | `BIGINT` |
| `s.number()` | `DOUBLE PRECISION` |
| `s.decimal({ precision, scale })` | `NUMERIC(precision,scale)` |
| `s.boolean()` | `BOOLEAN` |
| `s.dateTime()` | `TIMESTAMPTZ(3)` |
| `s.dateTime().withoutTimezone()` | `TIMESTAMP(3)` |
| `s.date()` | `DATE` |
| `s.time()` | `TIME(3)` |
| `s.json()` | `JSONB` |
| `s.blob()` | `BYTEA` |
| `s.point()` | PostGIS `geography(Point,4326)` |
| `s.enum(["active", "inactive"])` | A native named enum |
| A supported `.array()` | The scalar's native type followed by `[]` |

Native overrides retain their typmods during introspection through
`format_type()`: precision, scale, length, temporal precision, and array member
types participate in comparison. Explicit `TIMETZ` remains a native declaration;
public `s.time()` values have no offset component.

DateTime defaults retain milliseconds. Zoned `.now()` uses
`CURRENT_TIMESTAMP`; naive `.now()` explicitly converts the current instant to
UTC. Date and Time `.now()` defaults also use UTC, and an explicitly zoned
Time declaration receives a zero-offset current time. Literal dates and timestamps preserve proleptic year zero using PostgreSQL's
BC spelling rather than asking the server to parse unsupported ISO year `0000`.

## Enums

```ts
import { s } from "viborm";

const user = s.model({
  id: s.int().id().increment(),
  status: s.enum(["active", "inactive"]).name("user_status"),
}).map("users");
```

Adding values, at the end or in the middle of the list, emits
`ALTER TYPE ... ADD VALUE ... [BEFORE ...]` in declared order. It only changes
the catalog: tables using the enum are not rewritten, and views, rules and
CHECK constraints over its columns do not block it.

PostgreSQL forbids using an added value before its transaction commits. When
the same push or migration may use a new value, VibORM instead replaces the
type in that transaction (see below). A use is a default naming the value, a
column converted to the enum, whose stored data may hold it, or a new
partial-index predicate naming the enum type or a column of it, however the
value is spelled and even when it compares only existing values. A predicate
over other columns, such as `title = 'archived'`, keeps the addition
catalog-only.
To keep the addition catalog-only, add the value in one migration and use it in
a later one: `apply`, `down` and `reset` commit after each migration that adds
enum values. Reset needs this too: before PostgreSQL 17, even a type created in
the same transaction refuses its added values until they commit.

Removing or reordering values replaces the type in one transaction: dependent
columns are cast through text, defaults are dropped first and restored after,
and every table using the enum is rewritten. Any replacement (a removal, a
reorder, or a new value used in the same migration) is blocked by a view, rule
or partial-index predicate over such a column; drop it first and recreate it
afterwards.

`push` keeps the CHECK constraints on those columns: it reads them from the
database, drops them before the columns become text and adds them back once
they are the enum again, under the same name and definition (`NOT VALID`
included), in the same transaction. The preview shows both statements. A CHECK
naming a value being removed cannot come back, so the plan is refused, naming
the constraint: drop or rewrite it first. `generate` reads no database and
cannot see a CHECK, so a generated replacement is still blocked by one; drop
it before applying and recreate it afterwards.

Removed values require explicit per-column mappings, including nullable columns.
`useNull()` is a deliberate decision. Enum arrays preserve their order and NULL
members. Renaming an enum casts through text to the destination enum. Reusing
one name with incompatible value lists refuses generation. Enum migration
preflight requires PostgreSQL 12 or later.

## Guarantees and limits

Every migration transaction opens with `BEGIN`, its limits and a
transaction-scoped advisory lock (`pg_try_advisory_xact_lock`), retried for at
most `lockWait` (ten seconds by default, see [Limits](#limits)); a
timeout refuses before migration effects. The marker, the ledger and the drift
check are read under that lock, in the transaction that then runs the first
group of the path, so two runners apply a migration once: the second sees the
first one's marker and reports `noop`, or refuses with `V11015`. A later group
(after an edge that adds an enum value, or a stepwise group after a
transactional one in `apply`, `down` or `reset`) locks again and re-reads the
marker before it acts: if another command moved it in between, the command
refuses with `V11015` and nothing of the rest of its path runs.

An `apply` with nothing to do is proven before that transaction, in two
statements: the control tables' definition, then the marker, the open attempts
and a digest of the managed schema that the server computes, read together and
only while `pg_try_advisory_xact_lock` succeeds (the statement releases it). A
marker at the target, nothing open and a matching digest answer `noop`;
anything else, another command holding the lock included, takes the locked
path above. The digest abstains where SQL cannot reproduce the schema exactly:
partial indexes (for example the soft-delete extension's partial unique
indexes) and some literal defaults. Such schemas keep the locked no-op and
introspect for the proof after a change, as in 1.1.0.

The lock ends with its transaction, so it cannot outlive the command, and a
transaction pooler such as PgBouncer in transaction mode may hand each
transaction to a different server session: `apply`, `push` and the other
locked commands run through one. Stepwise work (a manual
`execution: "stepwise"` transition, or an index statement run `CONCURRENTLY`) runs
outside a transaction and needs a session lock: it is taken inside the locked
transaction before that transaction commits, held to the end of the command and
released with `pg_advisory_unlock`. It needs a direct session. Cloudflare
Hyperdrive and Neon `-pooler` hosts are refused for effectful commands unless
you pass `migrationSessionAttestation: "dedicated-session"`. The lock key is
one per database: independent schema estates in one database serialize with
each other.

Transactional DDL, schema proof, marker compare-and-swap, and the append-only
ledger govern effectful operations. A native type swap cannot recover data
already deleted by an earlier migration.

PostGIS must already be installed. VibORM proves the required extension-owned
objects and never installs extensions. A managed-table list refuses dependencies
crossing its boundary, including an enum shared with an unselected table.

[Migration driver capabilities](/docs/migration/drivers) distinguish native
PostgreSQL connections from hosted transports that lack a proven session/lock
contract.


## Limits

Every locked command (`apply`, `down`, `reset`, `baseline`, `resolve`,
`verify` and `push`) runs on one reserved session, and VibORM bounds it:

| Limit | Default | What it bounds |
| --- | --- | --- |
| `lockTimeout` | 4 s | `lock_timeout`, with `SET LOCAL` in every migration transaction (and on the session during stepwise work): a DDL statement waiting for a table lock gives up instead of stalling every query queued behind it. |
| `statementTimeout` | 10 min | `statement_timeout`, likewise; the lock wait runs under it. A longer statement, such as a large table rewrite, fails with `V11001` whose `cause` is `V2002`: raise it for such migrations. Must be 0 or longer than `lockWait`. |
| `idleTimeout` | 30 s | `idle_in_transaction_session_timeout` in every migration transaction, and while the session holds the session lock (stepwise work; on Neki the whole command) `idle_session_timeout` on PostgreSQL 14+: the server ends a migration whose client stopped talking, and frees its migration lock with it. It also bounds the client's own pauses between two statements, so it stays well above them (1 s before 1.2.3). |
| `lockWait` | 10 s | How long the command retries for the migration lock another command holds. |
| `connectionWait` | 10 s | How long the command waits for its connection. Applies to MySQL too. |
| `largeTableRows` | 1,000,000 | A row count, not milliseconds: the planner's estimate above which a change that rewrites or scans a table under a lock that blocks it (a column type change, `SET NOT NULL`, a volatile `ADD COLUMN` default, a validated `FOREIGN KEY`/`CHECK`) is refused before any effect with `V11009`. See [Large tables](/docs/migration/migrate#large-tables). 0 admits every size. |

Pass any of them as `limits` (milliseconds, and rows for `largeTableRows`):

```ts
import { createMigrationClient } from "viborm/migrations";

const migrations = createMigrationClient(client, {
  storage,
  limits: { lockTimeout: 2000, statementTimeout: 30 * 60_000 },
});
```

`0` turns a server limit, or `largeTableRows`, off. `lockWait` and `connectionWait` must be at least
1, and no value may exceed 2147483647. On PostgreSQL 14+ stepwise work also
sets `client_connection_check_interval` to 1 s where the server supports it,
so a statement whose client disconnected stops within a second. Every setting
is named by a constant, and the command ends with one `RESET` for each setting
it set; a connection whose reset the server refuses is closed, never handed
back to the pool (`V11005`).

A command reads and decides inside its first migration transaction, so
`idleTimeout` also bounds the client's own pauses between two of those
statements. On PostgreSQL 14+ 1.1.0 bounded them too (`idle_session_timeout`);
on PostgreSQL 12 and 13 this is new. A `down` loss-approval callback is not
such a pause: `down` asks it with no transaction open and the lock let go, so
no limit bounds it. The rollback then takes the lock again and re-reads the
marker; if another command moved it meanwhile, `down` refuses with `V11015`
and runs nothing.

A limit that fires surfaces as a retryable error: `V5006` for `lock_timeout`,
`V2002` for `statement_timeout`, and `V1001` or `V1002` for a lost or
unanswered connection. On a statement the migration dispatches, `V5006` and
`V2002` arrive as the `cause` of the `V11001` it fails with. Nothing is retried for you; run the command again. It
re-reads the marker under the lock, so a transaction that committed before
its connection failed reports `noop`.

Transactional work sets nothing on the session: its limits end with each
transaction, which is what lets it run through a transaction pooler. Settings
stepwise work put on the session are reset before its connection goes back to
your pool, to the values the connection started with; a session whose reset
fails is never returned to the pool. A `CREATE [UNIQUE] INDEX CONCURRENTLY`,
`DROP INDEX CONCURRENTLY` or `REINDEX … CONCURRENTLY` statement runs with no
`lock_timeout` and no `statement_timeout`: it waits for older transactions by
design, without blocking anyone. Only the statement itself counts, after any
leading comment: the words in a comment or a string literal of another
statement leave its limits in place. PGlite gets
`statement_timeout` and `lock_timeout` only: an in-process database has no
client to lose.

When the connection is lost mid-command, VibORM sends nothing more on it: no
`ROLLBACK`, no unlock. PostgreSQL rolls back the open transaction and frees the
migration lock when the session ends. With postgres.js, a transport VibORM
created is then closed (in-flight queries on it get up to 5 s to finish); a
`client` you supplied is left to you: its reserved connection is abandoned,
never returned to its pool, and the thrown `V1001` records that as a `V1003`
in its `suppressedFailures` property.

### A dedicated migration pool

Settings VibORM does not manage (TCP keepalives, a `search_path`, a role) and
a pool your application cannot exhaust come from a client built only for
migrations. Every setting below reaches the migration session as its starting
value:

```ts
import { createClient } from "viborm/pg";

const migrationClient = createClient({
  schema,
  databaseUrl: process.env.DATABASE_URL,
  options: {
    max: 1,
    connectionTimeoutMillis: 10_000,
    keepAlive: true,
    lock_timeout: 4000,
    statement_timeout: 600_000,
    idle_in_transaction_session_timeout: 1000,
  },
});
```

`limits` still applies on top, and its reset restores these values.

## PlanetScale Postgres (Neki)

PlanetScale's Postgres (Neki) runs PostgreSQL 18 but refuses two shapes
standard PostgreSQL accepts, and VibORM emits neither:

- a whole-row reference to a relation, such as `to_jsonb(ix)` over `pg_index`
  (`0A000`). Catalog reads name their columns. `pg_index.indnullsnotdistinct`
  exists from PostgreSQL 15 on, so introspection first asks whether the server
  has it and reads every unique index as `NULLS DISTINCT` where it does not;
- a recursive CTE of more than two terms (`42P01`). A `recurse` query with
  `self: true` merges the starting row and its first hop into one
  non-recursive term.

Its connection URL carries `sslrootcert=system`, which `viborm/pg` and
`viborm/postgres` read as `sslmode=verify-full`.

Queries, introspection, `push`, `generate`, `apply`, `baseline`, `verify`,
`status`, `down` and `reset` run on Neki, partial indexes included. Two more
Neki differences shape how:

- Neki has no temporary views (`NK013`). VibORM reads a partial-index
  predicate back through a scratch view in the estate's schema, created, read
  and dropped in one transaction. The migration role therefore needs `CREATE`
  on that schema, which it already has to change the schema.
- A statement is planned against the schema committed before its
  transaction, and only the server it reaches sees the transaction's own
  changes. After `ALTER TABLE … ADD COLUMN z`, an `UPDATE` naming `z` in the
  same transaction fails (`42703`), a `SELECT *` silently leaves `z` out, and
  a column whose type changed is still planned with its old type. Schema
  statements and catalog reads see the transaction's changes, except that a
  table created or renamed in it cannot have a column retyped, renamed or
  dropped, nor be moved to another schema (`NK316`). Defaults, NOT NULL,
  CHECK and foreign keys, validated constraints and comments are enforced
  where the row is written and need nothing. A table, index or constraint
  created or dropped in the transaction does: a literal naming it, as in
  `to_regclass('…')` or `'…'::regclass`, still answers for the committed
  schema.

Some Neki routers go further (`checkTaint`, met on a production branch while
its `pre-prod` branch accepted the same reset): once a transaction changed the
schema, they refuse to plan any statement against any relation, VibORM's own
writes to `_viborm_migration_log` and `_viborm_migration_state` included
(`NK013`, "[817] Planning against relation … is unavailable: earlier DDL in
this transaction is not reflected in the router's schema view"). A schema
change and its record cannot then commit together, so since 1.5.1 every
migration command on Neki (`apply`, `down`, `reset`, `resolve`, `push`) runs
stepwise, on any router: no transaction is opened, each statement commits
alone, and each step's ledger record (announced before an unproven step,
answered after it) and the marker move run as statements of their own.

- A data statement after a schema change it depends on, such as a manual
  transition that adds a column and then fills it, therefore always sees the
  change. Between two statements the application can see the intermediate
  schema.
- If a statement fails, what committed stays: the marker stays on the
  previous state and the attempt stays open. `status()` reports
  `unfinished: true` and other commands refuse to start (`V11016`). A
  `reset()` resumes from its stored plan. For `apply()`, a manual statement
  the server refused whole is re-run by the next `apply()` (previewed by
  `apply({ dryRun: true })`), never what committed; any other failure (a
  generated statement, a check, a lost connection) is resumed with
  `resolve({ outcome: "retry" })`, or closed with
  `resolve({ outcome: "complete" })` when the destination already holds.
- Two statements of one dispatch (one `sql` fragment such as
  `ALTER TABLE …; UPDATE …`) run as one request, which no commit divides: a
  migration that needs a commit between them is refused before any effect
  (`V11021`, naming both). Write them as two `sql` fragments.
- An `execution: "transactional"` transition is run stepwise too: Neki cannot
  commit it with its record.
- `push()` keeps no ledger: a failed push leaves what committed, as a failed
  stepwise migration would without its resume.

```ts
await migrations.generate({
  name: "nickname",
  manualMigration: {
    transitions: [
      {
        from: previous.stateId,
        execution: "transactional",
        up: [
          sql`ALTER TABLE "accounts" ADD COLUMN "nickname" text`,
          sql`UPDATE "accounts" SET "nickname" = "email"`,
        ],
        rollback: { kind: "irreversible", reason: "derived data" },
      },
    ],
  },
});
await migrations.apply(); // each statement committed alone on Neki, one transaction elsewhere
```

Before 1.5.1, `apply()` ran states in transactions and committed only where a
statement needed it, recording each step in the transaction it ran in, and
`down`, `reset`, `resolve({ outcome: "retry" })` and `push` ran their program
in one transaction: on a router that enforces `checkTaint` they all failed at
their first record after a schema change. A `DO` block's own schema changes
cannot be read from its text: a column change of a table one created fails
(`NK316`).

Neki refuses a session advisory lock and a transaction advisory lock in the
same session (`NK013`, "Mixing session and transaction advisory locks is
unavailable"). Since 1.2.3 a command on Neki (it asks `version()` once) takes
the session lock once, outside any transaction, before its first read, waits
for it within `lockWait` as elsewhere, holds it to the end and never takes the
transaction lock; each transaction opens with its `SET LOCAL` limits only, and
the session carries `limits` until the command resets them. While `down()`'s
`resolve` callback answers, the command resets its limits and releases the
lock (since 1.2.4), so a callback longer than `idleTimeout` no longer ends the
session; it takes both again, within `lockWait`, before the rollback. Every
group still re-reads the marker. Stepwise transitions, including
`generate({ lowLock: true })`'s `CONCURRENTLY` steps, therefore run on Neki,
in the same `apply()` as transactional ones. 1.2.2 refused them with
`V11005` (`NK013`), and its session limits with `42501`.

A catalog read the server refuses fails with `V11001` and names itself and its
SQLSTATE, for example `PostgreSQL catalog query "indexes" failed with SQLSTATE
0A000`, instead of reporting control tables that cannot be authenticated. The
SQLSTATE is in `meta.providerSqlState`. The server's own message is redacted
unless the client handed to `createMigrationClient` was extended with the
[instrumentation](/docs/extensions/instrumentation) extension's
`diagnostics.includeProviderDetails`: since 1.5.1 a migration command discloses
its failures as its client's `diagnostics` allow, `includeSql` included.
PlanetScale Neki's own codes (`NK013`, `NK316`, …) are kept in
`meta.providerCode` like standard SQLSTATEs.

## Native index and constraint fidelity

Indexes with expressions, descending keys, INCLUDE columns, `NULLS NOT DISTINCT`,
nondefault opclasses or collations, or methods outside the declaration language
are declared with [`.rawIndex()`](/docs/schema/model#rawindexname-templates).
VibORM writes its marker as the index's comment (`COMMENT ON INDEX`, a statement
of its own with its own stepwise probe) and recognizes the index by it, whatever
its method or structure; the one-statement catalog digest projects it too. A
raw index is never built `CONCURRENTLY`.

Any other such index refuses synchronization while its table is managed.
VibORM preserves the existing artifact and refuses before effects; it does not
turn a hand-authored HNSW or trigram index into an ordinary index, nor adopt it
as a raw one (it carries no marker): drop it, and declare it. Excluding the table
through `migrations.tables` requires the same closed dependency boundary as any
selected scope.

Equivalent primary, unique and foreign-key constraints whose physical names
cannot be represented also refuse adoption before effects. Compound-key `name`
is a query selector name; no physical constraint-name declaration is available.
There is no importer for hand-made indexes.

Vector migrations require `pgvector: true` and prove every required visible
extension-owned vector type before effects. Install the extension yourself;
VibORM never installs it during migration.
