Postgres

forge-orm's most feature-rich dialect. This page documents the Postgres-specific behavior: drivers, type round-trip, extensions, transaction quirks, and the patterns that make Postgres-with-forge production-ready.

Driver matrix — pg, postgres.js, Neon HTTP, Hyperdrive
DDL forge emits — types, defaults, sequences, IDENTITY, ENUM emit
Type round-trip — every f.* type → Postgres column → JS value
JSONB — JSONB vs JSON, GIN indexes, path queries
Arrays — native text[] / integer[], gotchas
Extensions — pgvector, postgis, pgcrypto, citext, ltree, hstore
UUID generation — gen_random_uuid(), uuid_generate_v4, UUIDv7 patterns
Transactions and isolation — READ COMMITTED vs REPEATABLE READ vs SERIALIZABLE, advisory locks
CONCURRENTLY index creation
LISTEN / NOTIFY
Partitioned tables and row-level security
Replication and read replicas
Per-statement timeouts
Connection pooling — PgBouncer caveats
Common errors and fixes
Cross-references

Driver matrix

forge's Postgres adapter talks to a normalized port, PostgresDriver, defined in src/adapters/postgres/driver.ts. The executor only ever calls query(sql, params), so any client that can be wrapped into the port is a valid backend. Four wrappers are interesting in practice: pg, postgres.js, @neondatabase/serverless (Pool and HTTP), and clients sitting behind Cloudflare Hyperdrive.

Wrapper	Module	Connection string	Pooling	Streams	Transactions	Best at
`pgDriver` (default)	`pg`	`postgres://…`	`pg.Pool` (process-local)	Server-side cursor (`DECLARE … CURSOR`)	Pinned `PoolClient`, `BEGIN`/`COMMIT`	Long-lived Node, real cursors, advisory locks
`postgresJsDriver`	`postgres` (porsager)	`postgres://…`	Built-in, `max` on `postgres()`	None (no `stream?`)	`sql.begin(fn)`	Faster simple queries on Node 20+, binary protocol bulk inserts
`pgDriver` over Neon WebSocket Pool	`@neondatabase/serverless`	`postgres://…neon.tech/…`	Mimics `pg.Pool`	Falls back to OFFSET/LIMIT	Yes — WebSocket session	Serverless functions with transactions
`neonHttpDriver` (custom wrap)	`@neondatabase/serverless` (`neon()`)	`postgres://…neon.tech/…`	None — per-request HTTP	Falls back to OFFSET/LIMIT	Throws — HTTP is single-shot	Edge runtime reads (Workers, Vercel Edge)
`pgDriver` behind Hyperdrive	`pg`	`postgres://user:pass@<hyperdrive-id>.hyperdrive.cf`	Hyperdrive multiplexes	Yes (Hyperdrive forwards)	Yes — Hyperdrive holds a session-mode pool	Cloudflare Workers with full PG features

pg is the default the adapter picks from the URL prefix. If you don't construct a driver and just pass url, forge calls loadDriver('postgres', url) which requires the pg package, opens a Pool, and runs a SELECT 1 probe so auth/host errors surface at connect(), not at the first query.

// Default — URL only.
import { createDb } from 'forge-orm';
await createDb({ url: process.env.DATABASE_URL!, schema });

// Custom pool sizing — own the pool, hand it to pgDriver.
import { Pool } from 'pg';
import { createDb, pgDriver } from 'forge-orm';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 8, min: 0,
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
  statement_timeout: 10_000,
});
export const db = await createDb({ schema, driver: pgDriver(pool) });

postgres.js (porsager)

import postgres from 'postgres';
import { createDb, postgresJsDriver } from 'forge-orm';

const sql = postgres(process.env.DATABASE_URL!, {
  max: 10,
  idle_timeout: 30,
  // PgBouncer transaction-pooling needs prepared statements off — see below.
  prepare: false,
});
export const db = await createDb({ schema, driver: postgresJsDriver(sql) });

Wire-compatible. The compiler emits the same SQL; the wrapper translates { rows, rowCount } into and out of postgres.js's array-with-.count result shape. Transactions delegate to sql.begin(fn) — different implementation, identical contract.

Neon

Neon ships two surfaces. The WebSocket Pool matches pg.Pool directly:

import { Pool } from '@neondatabase/serverless';
import { createDb, pgDriver } from 'forge-orm';

const pool = new Pool({ connectionString: process.env.NEON_DATABASE_URL! });
export const db = await createDb({ schema, driver: pgDriver(pool) });

The tagged-template HTTP transport — neon(connStr) — is per-request, no socket. It can't hold a transaction across calls, so the wrap throws from transaction:

import type { PostgresDriver } from 'forge-orm';
import { neon } from '@neondatabase/serverless';

export function neonHttpDriver(sql: ReturnType<typeof neon>): PostgresDriver {
  return {
    kind: 'postgres',
    query: async (text, params) => {
      const r = await sql.unsafe(text, (params ?? []) as any[]);
      return { rows: r as any[], rowCount: (r as any).rowCount ?? (r as any[]).length };
    },
    transaction: async () => {
      throw new Error('[forge] neon HTTP transport does not support transactions');
    },
    close: async () => { /* stateless */ },
  };
}

Use it for single-round-trip reads on the edge; switch to the WebSocket Pool the moment you need a tx.

Cloudflare Hyperdrive

Hyperdrive is a session-mode connection pool fronting any Postgres. From the Worker it looks like a regular Postgres URL — point pg at it and the WebSocket layer takes care of routing.

import { Pool } from 'pg';
import { createDb, pgDriver } from 'forge-orm';

export default {
  async fetch(_req, env: { HYPERDRIVE: { connectionString: string } }) {
    const pool = new Pool({ connectionString: env.HYPERDRIVE.connectionString, max: 5 });
    const db = await createDb({ schema, driver: pgDriver(pool) });
    try { /* … */ } finally { await pool.end(); }
  },
};

Hyperdrive runs in session mode, so prepared statements, server- side cursors, LISTEN/NOTIFY, and transactions all work. The one constraint is per-isolate connection lifetime: open the pool inside the handler, close before return. Don't pin globals at module level — isolates outlive individual requests, not connections.

DDL forge emits

buildSchemaDDL(schema) (in src/adapters/postgres/ddl.ts) walks the schema in three passes and produces a list of DDLStatement objects — each is a single SQL string with a deterministic name the migrator can reconcile against pg_class / pg_constraint / pg_indexes.

Pass 1: tables (CREATE TABLE IF NOT EXISTS …). Pass 2: per-field unique constraints, composite uniques, foreign keys, enum CHECK constraints, indexes, FTS GIN indexes for .searchable() fields. Pass 3: views (CREATE OR REPLACE VIEW / CREATE MATERIALIZED VIEW IF NOT EXISTS).

Every constraint and index name is prefixed forge_<table>_<kind>_<parts> and hash-collapsed when it exceeds Postgres's 63-byte identifier limit. That naming convention is what makes drift detection idempotent — the migrator looks up names, not SQL bodies.

Defaults

`FieldDef`	Emitted default
`.default('now')`	`DEFAULT now()`
`.default(42)`	`DEFAULT 42`
`.default('foo')`	`DEFAULT 'foo'` (single-quoted, escaped)
`.default(true)`	`DEFAULT TRUE`
`.default({ a: 1 })`	`DEFAULT '{"a":1}'::jsonb`
`f.uuid({ default: 'gen_random_uuid' })`	`DEFAULT gen_random_uuid()`
`f.embedMany(…)` (non-optional)	`DEFAULT '[]'::jsonb`

autoId defaults emit nothing — forge generates ObjectId-flavour ids in the wrapper before insert.

Primary keys

Three idType strategies. forge picks the column type per kind:

`f.id()` form	Postgres column	Default emitted	Sequence?
`f.id()` (default `'auto'`)	`text`	none — wrapper generates	no
`f.id({ type: 'uuid' })`	`uuid`	none (declare with `f.uuid({ default: 'gen_random_uuid' })` for DB-side)	no
`f.id({ type: 'bigserial' })`	`bigserial`	implicit `nextval(…)`	yes — implicit sequence

bigserial expands to BIGINT NOT NULL DEFAULT nextval('<table>_<col>_seq'::regclass) plus the sequence. The DDL builder recognises this and skips the usual NOT NULL/default rendering — appending them again is a syntax error. The sequence object is dropped on DROP TABLE … CASCADE.

IDENTITY columns

forge currently emits bigserial, not GENERATED BY DEFAULT AS IDENTITY. For pure-PG schemas where you want IDENTITY semantics — e.g. the OVERRIDING SYSTEM VALUE opt-out for backfills — declare the column as f.bigint() and ride a manual sequence created via raw SQL:

await db.$executeRaw`
  ALTER TABLE invoices
  ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 10000)
`;

Drift detection treats sequence ownership as orthogonal to the column declaration — running forge push after this raw step will not try to rewrite the column.

ENUM emit

forge does not emit Postgres native CREATE TYPE … AS ENUM. f.enumOf(['DRAFT', 'PUB']) becomes text NOT NULL with a CHECK:

sql

ALTER TABLE "posts"
  ADD CONSTRAINT "forge_posts_enum_status"
  CHECK ("status" IN ('DRAFT', 'PUB'));

Why: native enums need ALTER TYPE … ADD VALUE to grow, which can't run inside a transaction. Adding a value via the schema would require a multi-step migration; the CHECK approach grows in-place inside the same forge push. Tradeoff: native enums are slightly cheaper to store (4 bytes vs. text), and they show up in \dT+. The CHECK form wins on ergonomics; reach for raw SQL if you specifically need a native enum.

GENERATED columns

.dbgenerated('"price" * "qty"') emits a GENERATED ALWAYS AS (…) STORED column. The expression governs nullability — forge never adds an explicit NOT NULL or default to a generated column. Use these for computed totals, derived bounding boxes (ST_Envelope(geom)), or extracted JSONB paths kept in their own indexed column.

total: f.decimal().dbgenerated('"unit_price" * "qty"'),
// →  ALTER TABLE … "total" numeric GENERATED ALWAYS AS ("unit_price" * "qty") STORED

Materialized views

A model with view: { sql: '…', materialised: true } emits CREATE MATERIALIZED VIEW IF NOT EXISTS. Refresh is a REFRESH MATERIALIZED VIEW <name> issued via adapter.refreshView(model, { concurrently? }). CONCURRENTLY needs a unique index on the matview, so it's opt-in — declare a uniques on the model so the index exists before turning it on.

Type round-trip

Every f.* builder maps to one Postgres column type. The round-trip back to JS is whatever the pg (or postgres.js) type parser hands you, optionally massaged in decodeOutbound. For Postgres specifically, forge's decodeOutbound is a pass-through — pg's parsers already give sensible JS types — but numeric values come back as strings to preserve precision, which is by design.

Builder	Postgres column	JS read shape	Notes
`f.id()`	`text` PRIMARY KEY	`string`	wrapper-generated, ObjectId-flavour token
`f.id({ type: 'uuid' })`	`uuid` PRIMARY KEY	`string`	`pg` parses to a string
`f.id({ type: 'bigserial' })`	`bigserial` PRIMARY KEY	`number`	wraps to string if > `MAX_SAFE_INTEGER` — set `pg.types.setTypeParser(20, …)` to force bigint
`f.objectId()`	`text`	`string`	foreign-key style; pure-PG schemas use `uuid` instead
`f.string()`	`text`	`string`	PG `text` is unlimited; no `VARCHAR(N)` ceiling
`f.text()`	`text`	`string`	identical on PG (the distinction is MySQL-only)
`f.int()`	`integer`	`number`	32-bit, fits in `Number`
`f.float()`	`double precision`	`number`	64-bit IEEE
`f.decimal({ precision, scale })`	`numeric(p, s)`	`string`	preserves exact precision; pass `decimalNumbers: true` on `pg.Pool` to convert to JS `number` (loses precision past 15 digits)
`f.decimal()` (no p/s)	`numeric`	`string`	unbounded numeric
`f.uuid()`	`uuid`	`string`	`pg` parses to lowercase hex
`f.bigint()`	`bigint`	`string` by default	`pg` parses bigint as string to avoid `Number` precision loss; install a custom parser to get `BigInt`
`f.bool()`	`boolean`	`boolean`
`f.dateTime()`	`timestamptz`	`Date`	UTC-stored; `pg` parses through `Date` constructor
`f.json()`	`jsonb`	object/array/scalar	parsed by `pg`'s JSON parser
`f.enumOf([...])`	`text` + `CHECK`	`string`	CHECK constraint enforces membership
`f.embed(() => …)`	`jsonb`	object	shape derived from the embed definition
`f.embedMany(() => …)`	`jsonb` DEFAULT `'[]'::jsonb`	array	optional embedManys stay nullable
`f.stringArray()`	`text[]`	`string[]`	native array — see Arrays
`f.intArray()`	`integer[]`	`number[]`	native array
`f.geoPoint()`	`geography(Point, 4326)`	`{ lng, lat }`	needs PostGIS; falls back to `jsonb` with `{ fallback: true }`
`f.geoPoint({ dims: 3 })`	`geography(PointZ, 4326)`	`{ lng, lat, alt }`	3D point on the WGS84 sphere
`f.geoPoint({ srid: 3857 })`	`geometry(Point, 3857)`	`{ lng, lat }`	non-WGS84 falls off `geography` onto `geometry`
`f.vector({ dims: 1536 })`	`vector(1536)`	`number[]`	needs `CREATE EXTENSION vector`

Numeric returning as string

This is the single biggest type-round-trip surprise on Postgres. The pg driver's default numeric parser returns a string, because the underlying value can carry more precision than IEEE-754 affords:

const { unit_price } = await db.product.findUniqueOrThrow({ where: { id } });
// unit_price is a string like "19.99", NOT a number.
unit_price * 1.07;  // NaN

Three ways out:

Use decimal.js or BigNumber.js — the right answer for money. forge keeps the field typed as string so this is the obvious path.
Cast at read time — Number(unit_price) if you know the value fits in 15 digits.
Override the parser — early in startup:
ts
```
import pg from 'pg';
pg.types.setTypeParser(1700, (v) => parseFloat(v));  // 1700 = NUMERIC
```
Affects every numeric column the pool reads. Don't do this for money.

Date / timestamptz

f.dateTime() emits timestamptz, which Postgres stores as UTC and returns adjusted to the session timezone. pg parses via new Date(…), which lands a UTC instant. Two things to watch:

Session timezone matters at write time for now() defaults that flow through the session's SET TIME ZONE. Default is the cluster default (usually UTC); override per-pool with process.env.PGTZ = 'UTC' or SET TIME ZONE 'UTC' as the first statement.
Precision — timestamptz defaults to 6 fractional digits. JS Date is millisecond-precise, so the last 3 digits are zero on reads. Use bigint epoch ns if you need sub-millisecond precision.

JSONB

forge picks jsonb for every JSON-shaped field — f.json(), f.embed(), f.embedMany(). The dialect's columnType (src/adapters/postgres/dialect.ts) returns the string 'jsonb' for all three.

Why JSONB and not JSON: JSONB is parsed once at insert, stored as a binary tree, and supports GIN indexes. The only thing plain JSON preserves that JSONB drops is exact whitespace and key order — neither is relevant once the data is in the database.

When you'd want plain JSON

Round-trip preservation of a third-party payload that you'll later hand back verbatim. Sign-event webhooks, audit logs of raw vendor JSON. forge has no first-class switch for plain json — declare a f.text() column and serialise yourself. The cost of forfeiting JSONB indexing should be a deliberate decision.

GIN indexes on JSONB

GIN is the index method for "is this JSONB containing / matching X". For a JSONB column you query with @> or ?, add a GIN index via the schema's index list with method: 'gin':

defineModel({
  collection: 'events',
  fields: { id: f.id(), payload: f.json() },
  indexes: [
    { keys: { payload: 1 }, method: 'gin' },
  ],
});

That compiles to:

sql

CREATE INDEX IF NOT EXISTS "forge_events_idx_payload"
  ON "events" USING gin ("payload")

For path-targeted queries (payload->>'user_id' = $1), an expression index over the path is cheaper:

indexes: [
  { expression: `("payload"->>'user_id')`, name: 'forge_events_user_id_idx' },
],

Path queries

forge's JSON path operator (where: { payload: { path: ['user_id'] } }) compiles per-dialect via jsonPathExpr. On Postgres the read is (col->'a'->'b'->>'c')::<cast>, where the cast depends on the operand type — text comparisons stay as text, numeric comparisons cast to numeric, boolean to boolean. See JSON-PATH for the full operator reference.

Arrays

f.stringArray() and f.intArray() map to native Postgres array types (text[] and integer[]). The wrapper hands the array straight through to pg's array binder; the driver returns a JS array.

const u = await db.user.findUniqueOrThrow({ where: { id } });
u.tags         // string[] from text[]
u.scores       // number[] from integer[]

Gotchas

No element-level uniqueness or NOT NULL inside an array. The Postgres type system doesn't carry constraints into array elements. If you need "no empty strings in this array", validate in application code or add a CHECK using array_position(tags, '').
Operator coverage is limited. forge supports equals and basic containment on arrays. For tags @> '{x,y}' (does the array contain every value), reach for $queryRaw:
ts
```
const events = await db.$queryRaw`
  SELECT * FROM events WHERE tags @> ${['security', 'p0']}::text[]
`;
```
Empty arrays vs. NULL. ''::text[] is a syntax error; the empty array literal is '{}'::text[]. forge passes [] through to pg which handles the boundary correctly. Reads of NULL arrays come back as null, not []. Declare .optional() if NULL is meaningful; otherwise initialise to [] on insert.
GIN index for @> queries. Same recipe as JSONB: { keys: { tags: 1 }, method: 'gin' }. Without it, @> table-scans.

When to use JSONB instead

text[] is right for "an unordered set of tags". JSONB wins when the elements have structure ([{ key, value }]), when you want path queries into the elements, or when the array might mix scalar types. Arrays are the cheaper choice when the elements are uniform scalars and you query with ANY/ALL/@>.

Extensions

forge doesn't CREATE EXTENSION for you. The runtime adapter expects the extensions it uses (pgvector, PostGIS) to be present, and surfaces SQLSTATE 42704 (type "vector" does not exist) etc. as a P2022 if they aren't. The doctor probe (see DOCTOR) reports the extensions installed in the current database so you can spot the gap before pushing.

The list in practice:

Extension	Why forge needs it	`CREATE EXTENSION` ordering
`pgcrypto`	`gen_random_uuid()` for `f.uuid({ default: 'gen_random_uuid' })`. Postgres 13+ ships `gen_random_uuid` in core, so this is only needed on older clusters.	Before any `forge push` that creates a uuid-defaulted column
`vector` (pgvector)	`vector(N)` column type, distance operators, HNSW index	Before tables that declare `f.vector()`; HNSW operator classes register on `CREATE EXTENSION`
`postgis`	`geography`/`geometry` types, `ST_*` functions	Before tables that declare `f.geoPoint()`
`citext`	Case-insensitive text columns. forge doesn't emit `citext` directly — declare a column as raw SQL via `dbgenerated` or post-migrate `ALTER COLUMN TYPE`.	Before columns that use it
`ltree`	Hierarchical labels (forum categories, file paths). No forge primitive — raw `ltree` columns via `$executeRaw` DDL.	Before columns that use it
`hstore`	Legacy key-value column. JSONB is the modern answer; hstore is kept around for compatibility with older schemas.	Before columns that use it
`pg_trgm`	Trigram similarity indexes for fuzzy text search. Pair with a GIN index using `gin_trgm_ops`.	Before the trigram index

Bootstrapping extensions

Run extensions before forge push:

import { Pool } from 'pg';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
await pool.query(`CREATE EXTENSION IF NOT EXISTS pgcrypto`);
await pool.query(`CREATE EXTENSION IF NOT EXISTS vector`);
await pool.query(`CREATE EXTENSION IF NOT EXISTS postgis`);

On managed Postgres (RDS, Neon, Supabase) the cluster owner has to allowlist the extension first — CREATE EXTENSION from a non-owner fails with SQLSTATE 42501. adapter.doctor() reports the missing extensions for the schema you're about to push; pair with forge push --plan to dry-run.

UUID generation

Three layers stack here: f.id({ type: 'uuid' }) (the primary-key strategy), f.uuid({ default: 'gen_random_uuid' }) (the DB-side default on any uuid column), and application-side UUIDv7 generation when you want sortable ids.

App-generated UUIDs

f.id({ type: 'uuid' }) declares a uuid PRIMARY KEY column with no default — the wrapper generates the id app-side. The trade-off vs. DB- side is that the id is known before insert (which simplifies write- ahead logging) at the cost of importing a generator:

import { v4 as uuidv4 } from 'uuid';
await db.user.create({ data: { id: uuidv4(), email: 'me@x.co' } });

DB-generated UUIDs (`gen_random_uuid`)

f.uuid({ default: 'gen_random_uuid' }) emits the column with DEFAULT gen_random_uuid(). Postgres 13+ ships gen_random_uuid in core; older clusters need CREATE EXTENSION pgcrypto. forge keys off the uuidDefault field flag in the DDL builder:

fields: {
  id: f.uuid({ default: 'gen_random_uuid' }),
  // …
}
// →  "id" uuid NOT NULL DEFAULT gen_random_uuid()

The caller can omit id on insert; the database fills it in. On read, pg parses the value to a lowercase hex string.

uuid-ossp provides the older uuid_generate_v4() — functionally equivalent. Pick gen_random_uuid for new schemas; it's in core.

UUIDv7 — sortable ids

UUIDv7 packs a millisecond timestamp into the high bits, so values generated on the same machine sort lexicographically by creation time. That makes them dramatically friendlier to B-tree indexes than v4 (no random scatter across the page tree).

Postgres doesn't ship uuid_generate_v7() yet (as of PG 16). Two paths:

App-side generation — import { v7 } from 'uuid' (uuid v9.0+) and pass the value yourself. The simplest answer.
DB-side function — install a community uuidv7() (e.g. postgres-uuidv7) and ALTER TABLE … ALTER COLUMN id SET DEFAULT uuidv7() after push. The differ doesn't compare default expressions on uuid columns, so the override survives subsequent pushes.

UUIDv7's win on index size and write throughput is large enough that it's the default recommendation for new uuid-keyed tables expecting more than a few million rows.

Transactions and isolation

db.$transaction(fn) opens a pgDriver.transaction(fn) call, which checks a client out of the pool, runs BEGIN, calls fn with a PgQueryable bound to that client, and COMMITs on success or ROLLBACKs on throw. Every executor call inside the callback receives that queryable via ExecOpts.session, so reads and writes inside the tx land on the same connection.

await db.$transaction(async (tx) => {
  const acct = await tx.account.findUniqueOrThrow({ where: { id } });
  await tx.account.update({ where: { id }, data: { balance: acct.balance - 100 } });
});

The three isolation levels

PG's default is READ COMMITTED. forge does not override it.

Level	What you get	When to use
`READ COMMITTED` (default)	Each statement sees the latest committed snapshot. Reads inside the tx can change between statements.	Most CRUD; default for HTTP handlers
`REPEATABLE READ`	The whole tx sees one snapshot, taken at the first non-transaction-block statement. No phantoms inside the tx. Serialization failures on write conflicts.	Reports that read multiple tables and need a consistent view
`SERIALIZABLE`	True serializability — predicates and all. Concurrent transactions that would conflict get one rolled back with SQLSTATE `40001`.	Money movements, inventory reservations, anything where "two simultaneous reads + writes both succeeding" is incorrect

Override per-transaction with a SET TRANSACTION as the first statement:

await db.$transaction(async (tx) => {
  await tx.$executeRaw`SET TRANSACTION ISOLATION LEVEL SERIALIZABLE`;
  // …
});

For REPEATABLE READ or SERIALIZABLE, code defensively: forge maps SQLSTATE 40001 to P2034. Wrap the call in a small retry that backs off on P2034 (and 40P01 deadlock) before giving up — see Common errors and fixes below.

The aborted-transaction trap

Postgres marks the whole tx as failed after any statement error and the next statement gets current transaction is aborted, commands ignored until end of transaction block. Forge rolls back cleanly and reports the original error — but catch-and-continue inside a tx body doesn't work the way it does outside one:

// Wrong — second .create runs on an aborted tx.
await db.$transaction(async (tx) => {
  try { await tx.user.create({ data: { email } }); }
  catch (e) { /* swallow */ }
  await tx.user.create({ data: { email: alt } });   // throws aborted-tx
});

Two workarounds:

Branch before the failing call. Check existence with findUnique, or use upsert (ON CONFLICT DO UPDATE) so the second path is the same statement.

Wrap the risky statement in a savepoint. PG savepoints survive inner errors:

await db.$transaction(async (tx) => {
  await tx.$executeRaw`SAVEPOINT sp1`;
  try { await tx.user.create({ data: { email } }); }
  catch { await tx.$executeRaw`ROLLBACK TO SAVEPOINT sp1`; }
  await tx.user.create({ data: { email: alt } });   // works
});

Advisory locks

PG advisory locks are session- or transaction-scoped 64-bit lock ids that aren't tied to any row. Use them for "only one process should run this leader task" patterns. forge uses them internally for migrations — applyMigration takes pg_advisory_xact_lock($1, $2) with the constant pair 0x6f6f7267 / 0x65000001 (the bytes spell forge\0\0\1) so two concurrent forge push runs serialise instead of trampling each other.

In application code:

await db.$transaction(async (tx) => {
  const [{ locked }] = await tx.$queryRaw<[{ locked: boolean }]>`
    SELECT pg_try_advisory_xact_lock(${LEADER_LOCK_ID}) AS locked
  `;
  if (!locked) return;       // another worker has it; bail
  await doExclusiveWork(tx);
  // lock released at COMMIT — pg_advisory_xact_lock is tx-scoped
});

Two flavours:

pg_advisory_lock(id) / pg_advisory_unlock(id) — session-scoped, manually released. Survives across transactions on the same connection.
pg_advisory_xact_lock(id) — transaction-scoped, released at COMMIT / ROLLBACK. Safer default.

Both have two-int variants (pg_advisory_lock(hi, lo)) for cross-driver compatibility — some clients can't transmit a 64-bit int parameter.

CONCURRENTLY index creation

CREATE INDEX CONCURRENTLY builds an index without an ACCESS EXCLUSIVE lock on the table. Writers keep working through the build. The cost: the operation can't run inside a transaction, takes two passes over the table, and a failure leaves an invalid index behind that you have to drop manually.

forge does not emit CONCURRENTLY by default. The reason is that forge push wraps the whole DDL batch in BEGIN … COMMIT (so a single failure rolls back a single statement via savepoint, while the rest of the batch commits). CONCURRENTLY can't run inside that. For initial push against an empty table the regular CREATE INDEX IF NOT EXISTS is fast (no data) and the locking trade-off doesn't matter.

For adding an index to a live table after the fact, take it outside the schema and run it as raw SQL on its own connection:

await pool.query(`
  CREATE INDEX CONCURRENTLY IF NOT EXISTS "events_user_created_idx"
    ON "events" ("user_id", "created_at" DESC)
`);

After the manual creation, declare the matching index in the schema so drift detection treats it as already-present. The migrator looks up indexes by name in pg_indexes — if the name matches what buildIndex would have emitted, the next forge push skips it.

Naming convention: forge_<table>_idx_<col1>_<col2> (or forge_<table>_idx_expr for expression indexes). The DDL builder reproduces this so the runtime and manual paths agree.

Materialized views can also be refreshed concurrently (see DDL forge emits) — adapter.refreshView(model, { concurrently: true }) issues REFRESH MATERIALIZED VIEW CONCURRENTLY and needs a unique index on the matview to do so.

LISTEN / NOTIFY

Postgres's pub/sub: any session can NOTIFY channel, 'payload' and every session that has LISTEN'd to that channel receives the message when its connection is idle. Lightweight (the broadcast goes through shared memory), but with caveats: payloads are capped at 8000 bytes and the channel queue holds messages only while a session is listening.

forge doesn't surface LISTEN/NOTIFY as a first-class API — it's an application pattern, not an ORM feature. Wire it up with raw SQL on a dedicated long-lived connection outside the pool:

import { Client } from 'pg';

const listener = new Client({ connectionString: process.env.DATABASE_URL });
await listener.connect();
await listener.query('LISTEN events');

listener.on('notification', (msg) => {
  if (msg.channel === 'events') handleEvent(JSON.parse(msg.payload!));
});

// In the writer path — emits inside any tx
await db.$executeRaw`SELECT pg_notify('events', ${JSON.stringify(payload)})`;

Three rules:

Listener is its own client, not pool-borrowed. A pool returns the client between queries; the LISTEN registration is lost.
NOTIFY inside a transaction queues until COMMIT. Listeners don't see uncommitted notifications. This is the right semantics for "tell the world after I successfully wrote the row".
Don't put the listener behind PgBouncer. Transaction-mode pooling recycles the connection — the LISTEN evaporates. Connect direct, or use Hyperdrive (session-mode) for the listener and Hyperdrive (or PgBouncer) for the rest of the app.

For higher-volume pub/sub with replay and at-least-once delivery, write to an outbox table and tail it from a poll-and-mark-consumed loop. See BACKEND for the full pattern.

Partitioned tables and row-level security

forge's IR doesn't model partitions or RLS policies directly. Both are fully supported — they live in DDL that the schema doesn't generate but the runtime queries against transparently.

Partitioned tables

Declarative partitioning (PARTITION BY RANGE / LIST / HASH) is a Postgres-side concern. forge's CREATE TABLE emits a non-partitioned table by default; to make it partitioned, add the partition clause via raw SQL before the first push:

sql

CREATE TABLE events (
  id        text PRIMARY KEY,
  org_id    text NOT NULL,
  ts        timestamptz NOT NULL DEFAULT now(),
  payload   jsonb NOT NULL
) PARTITION BY RANGE (ts);

CREATE TABLE events_2026_06 PARTITION OF events
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Then declare the matching model with defineModel({ collection: 'events', … }). The runtime queries events and the partition router does its work transparently. The DDL builder's CREATE TABLE IF NOT EXISTS is idempotent on the parent — it sees the table exists and moves on.

Caveat: the migrator can't add a PARTITION BY to a table that already exists. Partitioning has to be decided before first push, or applied via a CREATE TABLE … PARTITION BY …; INSERT … SELECT; DROP …; ALTER … RENAME manual cutover.

Row-level security

RLS policies are independent of the schema declaration. Push the schema, then enable RLS and add policies via raw SQL:

await db.$executeRaw`ALTER TABLE events ENABLE ROW LEVEL SECURITY`;
await db.$executeRaw`
  CREATE POLICY events_org_isolation ON events
    USING (org_id = current_setting('app.org_id', true))
`;

The policy keys off a session variable. In the request handler, set the variable before the query:

await db.$transaction(async (tx) => {
  await tx.$executeRaw`SET LOCAL app.org_id = ${orgId}`;
  return tx.event.findMany({ where: { type: 'login' } });
  //                                  ↑ Postgres adds: AND org_id = current_setting('app.org_id')
});

SET LOCAL lives only for the current transaction, so it doesn't leak across pool borrows. This is the cleanest tenant-isolation pattern on Postgres — it works with any forge query because the policy is enforced below the SQL forge emits.

See BACKEND for the full pattern, including a recipe for swapping current_setting for a JWT claim via set_config().

Replication and read replicas

Postgres replication is operational, not schema-level. forge's pattern: two adapters, two pools, one schema — a primary for writes and one or more replicas for reads.

import { createDb, pgDriver } from 'forge-orm';
import { Pool } from 'pg';

const writer = await createDb({
  schema,
  driver: pgDriver(new Pool({ connectionString: process.env.PG_PRIMARY })),
});
const reader = await createDb({
  schema,
  driver: pgDriver(new Pool({ connectionString: process.env.PG_REPLICA })),
});

// Write goes to primary
await writer.user.create({ data: { email } });
// Read can go to replica
const u = await reader.user.findUnique({ where: { email } });

The trap is read-your-own-writes. Streaming replication is asynchronous; a read against the replica milliseconds after the write on the primary can miss the row. Three options:

Force reads after writes onto the primary. Easiest pattern is "everything inside this HTTP handler hits the writer", which is the default if you only pass the writer to request-scoped code.

Wait for replay. pg_last_wal_replay_lsn() on the replica reports how far it's caught up. Bound by an upper LSN you remember from the write:

const { rows } = await writer.$queryRaw`SELECT pg_current_wal_lsn() AS lsn`;
const targetLsn = rows[0].lsn;
// poll the replica until it has crossed targetLsn

Synchronous replication. Configure synchronous_commit = on and synchronous_standby_names on the primary. Writes don't return until one replica acknowledges. Most operators leave this off — the latency cost is real.

Connection routing usually lives above forge — either explicit ("read paths use reader") or a custom driver that branches on SELECT vs. write (fragile, rarely worth it). On Neon, read-replica branches expose their own connection string; same pgDriver. On Supabase, pooler vs. direct serves a related role (pooler is transaction-mode; direct is session-mode).

Per-statement timeouts

Three timeouts to set on every production pool. They're independent — statement_timeout doesn't cover lock waits, lock_timeout doesn't cover query execution, idle_in_transaction_session_timeout covers neither:

Setting	Bounds	Recommended default
`statement_timeout`	Total wall-clock time for a single statement	10s for HTTP, 5min for jobs
`lock_timeout`	Time waiting to acquire a lock before giving up	5s — enough to ride out usual contention, short enough to fail fast on a stuck transaction
`idle_in_transaction_session_timeout`	Time a tx can sit idle before being killed	60s — kills runaway BEGIN-and-forget

Set them per-pool:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  statement_timeout: 10_000,
  lock_timeout: 5_000,
  idle_in_transaction_session_timeout: 60_000,
});

Or per-transaction with SET LOCAL:

await db.$transaction(async (tx) => {
  await tx.$executeRaw`SET LOCAL statement_timeout = 30000`;
  await runReport(tx);
});

When the timeout fires, Postgres throws SQLSTATE 57014 (query canceled). forge maps it to P2024. Catch and degrade:

try {
  return await report();
} catch (e: any) {
  if (e.code === 'P2024') return { degraded: true, partial: cachedSnapshot };
  throw e;
}

Connection pooling — PgBouncer caveats

For a single Node process talking to a single managed Postgres, pg.Pool is enough. The math is "vCPUs × 2 connections per replica" against the managed cluster's connection ceiling (RDS gives max_connections, Neon gives 100 on free tier, etc.). Past ~8 replicas × 8 connections = 64 you're approaching the wall on most managed plans.

For Lambda / serverless / many-instance fan-out, you need PgBouncer in transaction mode (or Hyperdrive, or Neon's pooler endpoint — all the same shape). Transaction-mode pooling multiplexes many short-lived client connections onto a small server-side pool.

forge is compatible with transaction-mode pooling out of the box, but two settings on the driver have to be turned off because they require session-pinned state that transaction pooling doesn't preserve:

Prepared statements

pg's default sends queries via the extended-query protocol with implicit server-side prepared statements (keyed by the SQL text). The prepared-statement handle lives on the server side and PgBouncer's transaction-mode recycle would orphan it.

The fix differs by driver:

Driver	How to disable prepares
`pg`	No client switch — but `pg` only prepares parameterised queries that go through `client.query(text, params)`, and forge always passes params, so each query is a one-shot prepare-bind-execute-deallocate. As long as forge owns every call, transaction-mode pooling works. Don't manually call `client.query({ text, name: 'foo' })` — naming a query turns on caching
`postgres.js`	Pass `prepare: false` to `postgres(url, …)` or `pgbouncer=true` on the URL. Disables the cache that postgres.js maintains client-side
`@neondatabase/serverless` (Pool)	No prepares — Neon's transport doesn't use them

Server-side cursors

pgDriver.stream uses DECLARE … CURSOR FOR <sql> inside a transaction to stream large result sets. Cursors are session-scoped — transaction- mode pooling can recycle the connection mid-stream and your FETCH 200 FROM cursor returns "cursor does not exist".

The stream wraps everything in BEGIN … COMMIT, so the cursor lives exactly as long as the surrounding transaction. PgBouncer hands the same server connection to the client for the whole transaction, so as long as the cursor read happens inside the BEGIN/COMMIT, transaction-mode is safe. forge's built-in stream does this — but custom streams that keep a cursor open across transactions break.

LISTEN / NOTIFY

Already covered above — don't put the listener behind PgBouncer transaction-mode. Either direct connect, or use session-mode pooling for that one client.

Session variables

SET app.org_id = … (no LOCAL) persists for the session; PgBouncer transaction-mode loses it on connection recycle. Always use SET LOCAL inside an explicit transaction for tenant tagging, isolation overrides, timeouts. The RLS pattern above is correct because of this.

When to use which mode

Mode	What's allowed	Best for
Session	Everything	Long-lived Node, Hyperdrive, single-process API
Transaction	Everything inside a transaction; no LISTEN/NOTIFY/SET	Lambda, Vercel Functions, Cloudflare Workers behind PgBouncer or Neon pooler
Statement	One statement per connection — no transactions, no prepares	Almost never; transaction mode is strictly more useful

Common errors and fixes

forge maps Postgres SQLSTATE codes to its uniform DbKnownError shape (src/adapters/postgres/errors.ts). The mapping table covers the codes that matter in practice:

SQLSTATE	forge code	What it means	Fix
`23505`	`P2002`	Unique constraint failed	Branch with `findUnique` before the insert, or use `upsert`
`23503`	`P2003`	Foreign key constraint failed	Insert the parent first; or check `parent_id` exists before insert
`23502`	`P2011`	NOT NULL violation	Required field missing — declare `.optional()` if it should be nullable
`23514`	`P2004`	CHECK constraint failed	Value outside enum / range; validate before insert
`42P01`	`P2021`	Table does not exist	Run `forge push` to apply DDL
`42703`	`P2022`	Column does not exist	Run `forge push`; if intentional, regenerate types
`40P01`	`P2034`	Deadlock	Retry the transaction (see below)
`40001`	`P2034`	Serialization failure	Retry the transaction
`57014`	`P2024`	Query timeout	Increase `statement_timeout` or partition the query
`08000`/`08006`	`P1001`	Connection error	Check network, host, pool exhaustion
`28P01`	`P1010`	Authentication failed	Check password, role grants

Deadlock and serialization-failure retry

40P01 (deadlock) and 40001 (serialization failure under REPEATABLE READ / SERIALIZABLE) are not bugs — they're the database telling you "these two transactions couldn't both win, redo yours". The right response is small-jittered exponential backoff:

async function withRetry<T>(fn: () => Promise<T>, max = 3): Promise<T> {
  for (let i = 0; i < max; i++) {
    try { return await fn(); }
    catch (e: any) {
      if (e.code !== 'P2034' || i === max - 1) throw e;
      await new Promise((r) => setTimeout(r, 50 * 2 ** i + Math.random() * 25));
    }
  }
  throw new Error('unreachable');
}

await withRetry(() => db.$transaction(async (tx) => { … }));

Don't retry indefinitely — three attempts catches transient contention; beyond that the contention is a design issue (lock ordering, hot rows).

Unique violation: constraint vs index

PG enforces UNIQUE via a unique index. forge emits these as constraints (ADD CONSTRAINT … UNIQUE (…)), which Postgres internally backs with a unique index. The constraint and index share a name — forge_<table>_uq_<col>.

The trap: a manually-added CREATE UNIQUE INDEX and a forge-managed UNIQUE constraint can collide on names, and \d <table> shows them identically. If you've manually created a unique index that overlaps a column the schema declares as .unique(), drift detection will try to add the constraint and fail with 42P07 (duplicate object). Either:

Drop the manual index, let forge add the constraint, or
Rename the manual index out of the forge_* namespace so the migrator skips it.

Partial unique indexes (UNIQUE (email) WHERE deleted_at IS NULL) don't have an ADD CONSTRAINT … UNIQUE WHERE form. forge emits them as indexes, not constraints:

indexes: [
  { keys: { email: 1 }, unique: true, where: `"deleted_at" IS NULL` },
],
// →  CREATE UNIQUE INDEX … ON … ("email") WHERE "deleted_at" IS NULL

Aborted transaction

Already covered under Transactions and isolation. The fix is upsert, branch-before, or savepoint — not try/catch.

`numeric` as string

Already covered under Type round-trip. The fix is decimal.js, not a parser override.

"current transaction is aborted, commands ignored until end of transaction block"

The error after the error. The original error is the one to fix; the aborted-tx error is the symptom. Forge's wrapper rethrows the first SQLSTATE it saw, so the actual root cause is usually already in the stack trace.

Cross-references

DRIVERS — the full driver-port reference; how pgDriver, postgresJsDriver, and custom wrappers all share one port shape
QUERIES — operator-per-dialect emit table; the where: { col: { search: q } } PG path uses to_tsvector / plainto_tsquery
INDEXES — every IndexDef field; PG-specific INCLUDE, partial indexes, text_pattern_ops, GIN / GIST / BRIN / HNSW
JSON-PATH — the JSON path operator's per-dialect compilation, including the Postgres cast rules
GEO — f.geoPoint, PostGIS geography vs geometry, non-WGS84 SRIDs, MultiPolygon withinPolygon
VECTOR — f.vector, pgvector HNSW with cosine / L2 / inner-product opclasses, <=> / <-> / <#> operators
MIGRATIONS — forge push, drift detection, applyDrift, the runtime db.$migrate() API on supported drivers
TRANSACTIONS — $transaction, savepoints, isolation levels, deadlock retry, the AsyncLocalStorage HTTP pattern
BACKEND — pool sizing, PgBouncer recipe, RLS-based tenant isolation, the outbox pattern that subsumes LISTEN/NOTIFY for durability
DOCTOR — the runtime probe that reports installed extensions and missing capabilities before they bite at query time
DIFF — the introspection differ; what's compared, what's deliberately not (default expressions, comments, ownership)

Postgres ​

Contents ​

Driver matrix ​

postgres.js (porsager) ​

Neon ​

Cloudflare Hyperdrive ​

DDL forge emits ​

Defaults ​

Primary keys ​

IDENTITY columns ​

ENUM emit ​

GENERATED columns ​

Materialized views ​

Type round-trip ​

Numeric returning as string ​

Date / timestamptz ​

JSONB ​

When you'd want plain JSON ​

GIN indexes on JSONB ​

Path queries ​

Arrays ​

Gotchas ​

When to use JSONB instead ​

Extensions ​

Bootstrapping extensions ​

UUID generation ​

App-generated UUIDs ​

DB-generated UUIDs (gen_random_uuid) ​

UUIDv7 — sortable ids ​

Transactions and isolation ​

The three isolation levels ​

The aborted-transaction trap ​

Advisory locks ​

CONCURRENTLY index creation ​

LISTEN / NOTIFY ​

Partitioned tables and row-level security ​

Partitioned tables ​

Row-level security ​

Replication and read replicas ​

Per-statement timeouts ​

Connection pooling — PgBouncer caveats ​

Prepared statements ​

Server-side cursors ​

LISTEN / NOTIFY ​

Session variables ​

When to use which mode ​

Common errors and fixes ​

Deadlock and serialization-failure retry ​

Unique violation: constraint vs index ​

Aborted transaction ​

numeric as string ​

"current transaction is aborted, commands ignored until end of transaction block" ​

Cross-references ​

Postgres

Contents

Driver matrix

postgres.js (porsager)

Neon

Cloudflare Hyperdrive

DDL forge emits

Defaults

Primary keys

IDENTITY columns

ENUM emit

GENERATED columns

Materialized views

Type round-trip

Numeric returning as string

Date / timestamptz

JSONB

When you'd want plain JSON

GIN indexes on JSONB

Path queries

Arrays

Gotchas

When to use JSONB instead

Extensions

Bootstrapping extensions

UUID generation

App-generated UUIDs

DB-generated UUIDs (`gen_random_uuid`)

UUIDv7 — sortable ids

Transactions and isolation

The three isolation levels

The aborted-transaction trap

Advisory locks

CONCURRENTLY index creation

LISTEN / NOTIFY

Partitioned tables and row-level security

Partitioned tables

Row-level security

Replication and read replicas

Per-statement timeouts

Connection pooling — PgBouncer caveats

Prepared statements

Server-side cursors

LISTEN / NOTIFY

Session variables

When to use which mode

Common errors and fixes

Deadlock and serialization-failure retry

Unique violation: constraint vs index

Aborted transaction

`numeric` as string

"current transaction is aborted, commands ignored until end of transaction block"

Cross-references