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

PostgreSQL Driver

PostgreSQL migration types, enum transitions, and transaction guarantees

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

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); 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 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. 0 admits every size.

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

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:

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.
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 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(). 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.

Was this page helpful?