A TypeScript/pnpm starting point 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.
The first application signs users in with an emailed eight-digit code, a one-use link, Google, or GitHub. Email stays in an in-memory inbox during development and testing. OAuth providers are optional; OAUTH.md covers credentials, callback URLs, account linking, and tests that use local provider endpoints.
Install Node 24 or newer, pnpm 10.30.1, and Docker with Compose. Start Docker, then:
pnpm install --frozen-lockfile
pnpm test
pnpm devNo database preparation is necessary. The first command that needs Postgres starts this checkout's containers and prepares a template. pnpm dev prints the application's assigned URL and local inbox URL. Request a code using any example address, open Local inbox, then type the code or click the email's link and confirm. No external email is sent.
Use PORT=3000 pnpm dev for a fixed port. Development uses real time so cooldowns elapse naturally, and chooses a fresh random seed because its database survives restarts. Tests instead start at January 1, 2020 with a repeatable seed. PGSTENCIL_TIME explicitly freezes development time and PGSTENCIL_SEED overrides its random seed; reset the development database before deliberately replaying an old seed.
| Piece | Implementation |
|---|---|
| Schema versioning | packages/auth/migrations/*.sql, 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 agents against listeners bound to 127.0.0.1:0 |
| OAuth | openid-client for Google OIDC and GitHub OAuth; local HTTP provider fixtures for tests |
| Time / randomness | Injected DevTime and DevRandom; production uses SystemTime and Node crypto |
Injected EmailSender, with EmailDev capture, waiting, unread checks and preview routes |
|
| Snapshots | Vitest file snapshots plus local JSON, response, HTML, Markdown and email captures |
The workspace contains pgstencil, @pgstencil/auth, and @pgstencil/stripe. They are not yet published to npm; PACKAGES.md explains consumption through compiled local tarballs. The core package's pgstencil/postgres export is the runtime connection layer; pgstencil/database and pgstencil/testing include local Docker infrastructure. examples/login is a complete consumer, using Node's HTTP server and native HTML forms. No frontend framework is required.
The complete application fixture is in tests/integration/helpers.ts. A smaller consumer can use the infrastructure directly:
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');
});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. 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 authentication 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 exactly at 24 hours. Cookie expiry attributes remain visible in the snapshot.
Run a focused test with pnpm test tests/integration/login.test.ts, or pnpm test:watch. Changes to the example's SQL files or Compose configuration trigger a full watch rerun. Type generation is explicit: rerun pnpm db:types after editing migrations.
pnpm test
pnpm snapshot:update tests/integration/login.test.ts
git diff -- tests/integration/snapshotsOrdinary 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 code-login snapshots and OAuth snapshots: original HTML, derived Markdown, response headers including every cookie, ordered database rows, 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 OAuth 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.
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:migrateThe schema dump and generated declarations are committed review artifacts, not schema inputs. 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 first runner's contract. SQL upgrade tests exercise existing data and rollback, as well as fresh initialization.
| Command | Result |
|---|---|
pnpm db:status |
List applied and pending SQL without applying it |
pnpm db:validate |
Verify applied SQL contents |
pnpm db:migrate |
Apply pending SQL |
pnpm db:types [--verify] |
Generate or verify Kysely declarations from a disposable clone |
pnpm db:schema [--verify] |
Generate or verify the schema dump from a disposable clone |
pnpm db:verify |
Verify both generated artifacts using one disposable clone |
pnpm db:reset |
Explicitly recreate the development database |
pnpm db:gc |
Clear cached test templates/databases when idle |
pnpm db:stop |
Stop this checkout's services when idle; retain its Postgres volume |
pnpm check |
Formatting, typechecking and all tests |
DATABASE_URL directs status/validate/migrate at an explicitly supplied persistent database. Otherwise they use pgstencil_dev. Schema/type commands always use a disposable migrated clone. Never point template management at a production cluster.
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.
The example implements browser-bound code/link challenges, confirmation POSTs that do not consume links during GET previews, 10-minute deadlines, five attempts per challenge, resend cooldowns, database-backed email/IP rate limits shared across instances, one-time atomic redemption, normalized email uniqueness, fixed 24-hour opaque sessions, session rotation/revocation, CSRF tokens, origin checks, and escaped HTML.
examples/login/src/production.ts composes SystemTime and SecureRandom, requires a public HTTPS origin and secret, uses __Host- Secure/HttpOnly/SameSite=Lax cookies, and omits inbox routes. It accepts an EmailSender implemented using a provider API such as Postmark; no real provider adapter or credentials are included. Deploy behind an HTTPS reverse proxy on the same host, apply migrations as a separate release step, and supply a high-entropy shared auth secret. The sample sender address is signin@example.test; configure your verified sender before real delivery.
Native forms require an actual Origin; strict-origin referrer policy preserves it while omitting URL paths and link tokens. no-referrer can make form POSTs send Origin: null, as MDN explains. Keep access logs from recording token-bearing query strings. Forwarded IP headers are deliberately not trusted; configure rate limiting at a trusted proxy before internet deployment, since the sample sees its direct peer's address.
Optional Google/GitHub login uses PKCE, browser-bound one-time attempts, verified provider identities, and explicit account linking. Pass oauth: oauthFromEnvironment(process.env) to the production wrapper to enable configured providers. OAUTH.md documents its security contract and remaining live-provider checks.
This is an example authentication application; email sign-in remains an available recovery method for the stored address. Provider delivery retries, expired-row retention, account recovery policy, passkeys/MFA, distributed deployment configuration and npm release automation remain extension work. Test helpers and deterministic random sources are not production dependencies to inject.
GitHub Actions installs with the frozen lockfile on Node 24/Linux, verifies formatting, generated schema/types, typechecks, and runs the complete Docker suite and verifies an independent consumer of the packed packages before removing that job's containers and volumes. Local editor typechecking works without Docker because generated types are committed.
See PLAN.md for architectural decisions and remaining expansion work, and LOGIN_FLOW.md for the login contract.
The example includes a card-required SaaS trial, monthly/yearly plans, and a local Stripe simulator. See BILLING.md for setup, entitlement rules, recovery and production configuration.
MIT, copyright 2026 DiffPlug.