pgstencil

A TypeScript/pnpm toolkit for Postgres websites with fast, isolated, deterministic tests. SQL files own the schema; IntegreSQL clones migrated templates; Kysely supplies typed queries. Supertest exercises real HTTP servers, and local capture functions produce readable Vitest snapshots.

Three packages are published to npm as 0.x releases:

Package	What it is
pgstencil	Migrations, isolated test databases, injected time/randomness/email, snapshots, and the database CLI
@pgstencil/auth	Sign-in built on Better Auth: email codes, five OAuth providers, sessions
@pgstencil/stripe	SaaS subscriptions on Stripe Checkout, with a local Stripe simulator for tests

PACKAGES.md covers installing them, their options and their dependencies. CHANGELOG.md lists what each release changed and how an application adapts.

Start

Install the pnpm version named in package.json's packageManager, and Docker with Compose. pnpm downloads the Node version pinned in devEngines.runtime and runs every script with it; the published packages need Node 24 or newer. Start Docker, then:

pnpm install --frozen-lockfile
pnpm test
pnpm dev

No database preparation is necessary. The first command that needs Postgres starts this checkout's containers and prepares a template. pnpm dev runs the example application and prints its URL, its local inbox and its billing page. Request a code using any example address, open Local inbox, and type the code. No external email is sent, and billing runs against a local Stripe simulator.

Use PORT=3000 pnpm dev for a fixed port. Development uses real time and the persistent pgstencil_dev database, and chooses a fresh random seed because that database survives restarts; PGSTENCIL_SEED overrides the seed. Tests instead start at January 1, 2020 with a repeatable seed.

The recipe

Piece	Implementation
Schema versioning	Ordered .sql files per source, node-pg-migrate, applied-file SHA-256 validation
Database isolation	IntegreSQL template per migration/configuration fingerprint; writable database lease per application
Local services	Testcontainers starts pinned Postgres/IntegreSQL Compose services with dynamic loopback ports
Queries	Kysely over pg; committed declarations generated by kysely-codegen
HTTP tests	Supertest against listeners bound to 127.0.0.1:0
Time / randomness	Injected DevTime and DevRandom; production uses SystemTime and Node crypto
Email	Injected EmailSender, with EmailDev capture, waiting and unread checks
Snapshots	Vitest file snapshots plus local JSON, response, HTML, Markdown and email captures
Authentication	Better Auth, with pgstencil's SQL, security policy and a deterministic test bundle around it
Billing	The official Stripe SDK, hosted Checkout and portal, a webhook ledger, and the StripeDev simulator
Workers	Hono and Cloudflare Workers through request-scoped Hyperdrive connections (WORKERS.md)

The core package's pgstencil/postgres export is the runtime connection layer. pgstencil/database and pgstencil/testing include local Docker infrastructure, and with pgstencil/snapshots they load optional peers that a production install leaves out.

Write a test

A test asks for a context and gets its own database, clock, random stream and outbox:

import { test, expect } from 'vitest';
import { createTestContext } from 'pgstencil/testing';
import { queryDatabase } from 'pgstencil/postgres';
import { stableJson } from 'pgstencil/snapshots';

test('empty users', async ({ onTestFinished }) => {
  const context = await createTestContext();
  onTestFinished(() => context.close());
  const rows = await queryDatabase(
    context.database.url,
    'SELECT email, created_at FROM users ORDER BY email',
  );
  expect(stableJson(rows)).toBe('[]\n');
});

Without arguments the context uses the migration sources in pgstencil.json. Pass { migrations: ['/absolute/path/to/migrations'], seed: 'scenario', now: '2020-01-01T00:00:00Z' } for another schema or scenario. Pass the returned database URL, clock, randomness and email sender into your application factory; tests/support/fixture-app.ts is the smallest application that does. Close HTTP servers and query pools before closing the context. The lease holds a database connection throughout its lifetime so IntegreSQL cannot reclaim an idle test's database.

Each context owns its clock, random stream and outbox. Advance time with context.time.advanceHours(23) and then advanceHours(1); there is no sleeping or timer monkey-patching. Applications must use the injected clock for deadlines and timestamps. PostgreSQL now() and browser clocks still use real time, so application SQL explicitly supplies relevant timestamps.

Supertest's request.agent(origin) manages ordinary cookies. For historical tests, capture the actual Set-Cookie headers and explicitly replay their name/value pairs: the client's cookie store uses today's machine clock. The 24-hour session test keeps the original cookie and proves that the server accepts it at 23 hours and rejects it once 24 hours have passed. Cookie expiry attributes remain visible in the snapshot.

A library that reads the global clock and crypto itself, as Better Auth does, cannot take an injected Time. Its tests bundle the application with a module that scopes Date, Web Crypto and outbound fetch to each application through AsyncLocalStorage; see Determinism and Workers.

Run a focused test with pnpm test tests/integration/database.test.ts, or pnpm test:watch. Changes to migration SQL or the Compose configuration trigger a full watch rerun.

Review snapshots

pnpm test
pnpm snapshot:update tests/integration/better-auth.test.ts
git diff -- tests/integration/snapshots

Ordinary runs fail on missing or changed baselines and do not rewrite them. The update command serializes update processes for this checkout. Use unique, explicit paths for each test/case/checkpoint and the test-local expect in concurrent tests.

See the snapshots: original HTML, derived Markdown, response cookies, ordered database rows, outgoing Stripe requests, and grouped email metadata/plaintext/Markdown/HTML. Capture functions preserve meaningful timestamps and tokens. They replace only the supplied application's exact origin (including its encoded form inside redirects) and omit transport-generated HTTP fields such as Date and Content-Length.

The Markdown lens selects the last .selfie element, otherwise main, otherwise body; it excludes .selfie-exclude, scripts, styles and hidden inputs. It renders fields/buttons as readable text and retains links, images, tables, embedded-content links and preformatted text. Full HTML remains a separate facet. Query capture preserves row order: use ORDER BY when order matters. pg keeps numeric/decimal values as strings; bigint values are labeled rather than coerced to imprecise numbers.

Change the schema

The pgstencil command ships in the core package. In this repository the pnpm db:* scripts run it from source; an application runs pnpm exec pgstencil <command>.

pnpm db:migration:create add_profile
# Edit the new SQL file's -- Up Migration section.
pnpm db:types
pnpm db:schema
pnpm typecheck
pnpm test
pnpm db:migrate

pgstencil.json at the project root configures it:

{
  "migrations": [
    "node_modules/@pgstencil/auth/better-auth-migrations",
    "node_modules/@pgstencil/stripe/migrations",
    "migrations"
  ],
  "schema": "schema.sql",
  "types": [{ "out": "src/db.generated.ts" }]
}

migrations is one directory or an ordered list, each inside the project root; it defaults to migrations. List a package's source by its installed path, which in a pnpm workspace is under the node_modules of the workspace package that depends on it (app/node_modules/@pgstencil/… when the project root is the workspace root). List your own directory last, since that is where create writes; it refuses a target inside node_modules. schema names the schema dump. Each types entry generates Kysely declarations to out, for the tables matching include (default public.*), minus exclude, with defaultSchema set when the tables live outside public; types needs the optional peer kysely-codegen. No other key is accepted. The project root is the working directory, or PGSTENCIL_PROJECT_ROOT.

The schema dump and generated declarations are committed review artifacts, not schema inputs, so editor typechecking works without Docker. Each new migration fingerprint gets its own template; unchanged fingerprints reuse a ready template without rerunning initialization. Failed initializers are discarded, and interrupted initializers without a ready marker are rebuilt. Template cache identity includes SQL filenames/content, the Compose configuration and a format version.

Persistent development/production databases record applied-file checksums. Editing or deleting applied SQL fails validation; add a new migration instead. Pending migrations run in one transaction through node-pg-migrate. Nontransactional changes such as CREATE INDEX CONCURRENTLY are intentionally outside this runner's contract. PACKAGES.md explains how several sources compose.

Command	pnpm script here	Result
pgstencil status	db:status	List applied and pending SQL without applying it
pgstencil validate	db:validate	Verify applied SQL contents
pgstencil migrate	db:migrate	Apply pending SQL
pgstencil create <name>	db:migration:create	Create the next numbered SQL file; --in <dir> picks the source
pgstencil types [--verify]	db:types	Generate or verify Kysely declarations from a disposable clone
pgstencil schema [--verify]	db:schema	Generate or verify the schema dump from a disposable clone
pgstencil verify	db:verify	Verify both generated artifacts using one disposable clone
pgstencil reset	db:reset	Explicitly recreate the development database
pgstencil gc	db:gc	Clear cached test templates/databases when idle
pgstencil stop	db:stop	Stop this checkout's services when idle; retain its Postgres volume

DATABASE_URL directs status, validate and migrate at an explicitly supplied persistent database. Otherwise they use pgstencil_dev. A DATABASE_URL that is set but empty is refused, because that is how a workflow sees a secret it lost. Those three commands also take:

• --also <dir>, repeatable: one more migration source after the configured ones, for this run only, such as SQL that only preview databases get. A database that has applied files from an --also directory needs the same option on every later run, or validation reports them missing.
• --require-database-url: fail when DATABASE_URL is unset instead of using the development database. Use it in every deployment script.

Every command takes --env-file <path> and --env-file-if-exists <path>, repeatable, loaded before pgstencil.json is read, so a file can set PGSTENCIL_PROJECT_ROOT and DATABASE_URL. Variables already in the environment win, and a later file wins over an earlier one. Node itself also notices these two flags: a missing --env-file stops in Node with exit code 9, and a missing --env-file-if-exists prints Node's own notice. reset refuses to run while DATABASE_URL is set, since it only ever recreates the development database. Schema/type commands always use a disposable migrated clone. Never point template management at a production cluster. pnpm check runs formatting, typechecking and all tests.

Local services run PostgreSQL 18. The 18 image keeps its data under /var/lib/postgresql/18/docker and refuses a volume holding a 17 cluster, so the volume is named postgres-18-data. A checkout that last ran PostgreSQL 17 starts a fresh development database; its old volume stays until removed with docker volume rm <project>_postgres-data, and docker volume ls lists it.

Resource lifecycle

Each checkout has a Compose project name derived from its absolute path, its own network, and a named Postgres volume. Local credentials and resolved ports are cached in ignored .pgstencil/services.json. Both exposed service ports bind to loopback. Change image versions in compose.yaml, then stop and restart the services; do not change Postgres major versions against an existing volume without a database upgrade.

Ordinary test runs retain the services and templates. Closing a fixture tells IntegreSQL to recreate its dirty clone. IntegreSQL's coordinator cache is in memory, so a coordinator restart rebuilds the template cache; the persistent development database survives db:stop. Cleanup commands refuse when they observe live application database connections. Run cleanup while test runners and development servers are stopped, never alongside a new allocation.

The configured pool supports 40 test databases per fingerprint, with Postgres capped at 200 connections and each Kysely pool capped at two. The suite exercises 20 simultaneous applications plus two independent Node processes. Tests assert unique ports/databases, deterministic identical outputs, isolated writes/time/randomness/email, and correct cleanup. pnpm test --reporter=verbose --disableConsoleIntercept exposes the concurrent fixture timing.

Docker must be running; pgstencil does not install or launch Docker Desktop. If initialization is interrupted, rerun the command. A stale process lock is removed when its owning PID is no longer alive. Do not delete .pgstencil while another process uses it.

Authentication

@pgstencil/auth wraps Better Auth with a fixed policy: eight-digit email codes, Google, Apple, Facebook, GitHub and Microsoft sign-in, 24-hour database sessions, explicit CSRF tokens, database-backed rate limits shared across instances, and explicit or same-email account linking. PACKAGES.md documents the options, the example the HTTP protocol and provider registration, and SECURITY.md every guarantee with the test that pins it.

Email is delivered through an injected EmailSender; @pgstencil/auth/postmark is the provided production adapter, and emailFrom and renderEmail set the sender and wording. Passkeys, MFA, account recovery beyond email sign-in, and retention of expired auth rows are the application's.

Billing

@pgstencil/stripe provides a card-required SaaS trial, named plans, entitlement as a SQL view, a webhook ledger and reconciliation. The example mounts it behind its sign-in. See BILLING.md for setup, entitlement rules, recovery and production configuration.

Verification

GitHub Actions installs with the frozen lockfile on Linux under the pinned Node, verifies formatting and the generated schema and types, typechecks, runs the complete Docker suite, builds the Workers example, and verifies an independent consumer of the packed packages before removing that job's containers and volumes.

SECURITY.md states what the packages guarantee, the test that pins each rule, and how to report a vulnerability privately. An automated audit runs those rules on every push to main and gates each npm release.

License

MIT, copyright 2026 DiffPlug.