Testing the persistence layer

Database tests in this repo run against one of two backends behind a single harness API: pglite (in-process WASM Postgres 18 — Docker-free, fast, the repo-wide default) or real Postgres 18 on :55432. Two backends exist because each solves a different problem: pglite makes the everyday loop instant and dependency-free, while the real-Postgres lane catches what pglite silently ignores (grants, FORCE RLS, catalog invariants). This page is the how-to: which lane runs what, the exact commands, and the trap that bites everyone once.

The harness is packages/postgres-test-harness. A test calls acquireTestDatabase() and gets back the same handles regardless of backend:


import { acquireTestDatabase } from "@opsdna/postgres-test-harness"
 
const { backend, schemaPrefix, adminDb, appDb, stop } = await acquireTestDatabase()
// adminDb — superuser handle: migrations + seeding, bypasses RLS
// appDb   — non-superuser app-role handle: FORCE RLS keyed on app.tenant_id
//           (use this for anything asserting tenant isolation)
await stop()

Backend selection is OPSDNA_TEST_DB ("pglite" default, "postgres"). On pglite the database is cloned from a once-migrated in-memory baseline (src/pglite.ts — a pg.Pool shim over a single PGlite instance, with citext/btree_gist/uuid_ossp contrib modules and RLS enforced via SET ROLE). On Postgres, a template database is migrated once in vitest globalSetup and each test file gets its own CREATE DATABASE … TEMPLATE clone (src/template-database.ts) — no per-file migration replay, full isolation, safe file-parallelism (workers capped at 8 in vitest.integration.postgres.config.ts to stay under max_connections).

Commands

pglite (default)


# persistence unit suites (pglite; slow baseline-backed files run serially)
bun run --cwd packages/persistence test
 
# filtered
bun run --cwd packages/persistence test test/migrator.test.ts

real Postgres


# start local Postgres 18 (repo root docker-compose.postgres.yml → :55432)
docker compose -f docker-compose.postgres.yml up -d
 
# the real-Postgres integration lane (defaults OPSDNA_TEST_DB=postgres itself)
DATABASE_URL=postgres://opsdna:opsdna@localhost:55432/opsdna_dev \
  bun run --cwd packages/persistence test:integration
 
# suites that mutate cluster-global state / test the migrator itself
DATABASE_URL=postgres://opsdna:opsdna@localhost:55432/opsdna_dev \
  bun run --cwd packages/persistence test:integration:serial

single file


DATABASE_URL=postgres://opsdna:opsdna@localhost:55432/opsdna_dev \
  bun x vitest run --config packages/persistence/vitest.integration.postgres.config.ts \
  packages/persistence/test/<file>.integration.test.ts

Lane membership is a file marker

The integration runner (packages/persistence/test/run-integration-lane.ts) globs test/*.integration.test.ts and reads a // @lane: comment near the top of each file — adding a test never edits package.json:

Marker	Lane
(none) or `// @lane: postgres`	Default: parallel real-Postgres template-clone lane
`// @lane: postgres-serial`	Serial: cluster-global state or migration-mechanism tests (can’t clone/parallelize)
`// @lane: legacy-replay-postgres`	Frozen pre-cutover migration replay — nightly/path-triggered workflow only
`// @lane: quarantine`	Runs nowhere (tracked decay)

Note: within packages/persistence the pglite integration lane was retired — it was ~30% slower than the native clone lane and silently passed tests real Postgres rejects (it never enforced function EXECUTE grants). pglite remains the substrate for unit suites, the Acceptance Lab, and most other packages’ tests.

Useful gates when writing tests: integrationTestsEnabled() and concurrencyTestsEnabled() from packages/postgres-test-harness/src/test-database.ts. pglite is single-connection, so genuine multi-session concurrency (advisory-lock contention, SELECT … FOR UPDATE races) only runs on the Postgres backend — gate such cases with concurrencyTestsEnabled() ? it : it.skip.

bun run verify does NOT cover the real-Postgres lane. Its suite runs on pglite, which silently passes what real Postgres rejects: EXECUTE grants on SECURITY DEFINER functions, FORCE ROW LEVEL SECURITY behavior, and catalog invariants like the identity-spine rule (*_identity_id must be uuid). A green verify with a red postgres integration CI job is the classic tell that a schema change slipped the pglite lane. Whenever a change touches schema — a migration, an *_identity_id column, RLS, grants — run test:integration locally against :55432 before pushing.

The compose volume is Postgres-major-version-specific: PG18 cannot start on a PG16 data dir. If the container crash-loops after a version bump, recreate it (data is greenfield): docker compose -f docker-compose.postgres.yml down -v && docker compose -f docker-compose.postgres.yml up -d.

Raw-SQL tests that touch domain-schema tables (crm, kpi, collection, sharing) must use the returned schemaPrefix (via prefixedSql(...)) so they work on both backends — the parallel Postgres lane rewrites domain schemas per suite; pglite uses the literal names.

Related: Migrations for what the lanes are protecting, Roles & RLS for why grant bugs are Postgres-only, and Getting started for first-time environment setup.