@app-studio/qa-db

A test database per run — real Postgres or embedded PGlite — created, migrated, truncated with a verified postcondition, and dropped on the way out.

A database per run, with nothing to remember

The convention this replaces was "give each session its own database name", and it failed the way conventions do. Two suites sharing one database corrupt each other — phantom 401s, "record not found" on tests that pass alone — and the moment a second session in the same checkout forgets the rule, an audit has to be redone from scratch.

There is nothing to remember here. The default path is the safe one:

// tests/setup/test-db.ts
import { createTestDbGlobalSetup } from '@app-studio/qa-db';
import { execFileSync } from 'node:child_process';

export default createTestDbGlobalSetup({
  mode: 'postgres',
  prefix: 'myapp_test_run_',
  adminUrl: process.env.POSTGRES_URL ?? 'postgres://localhost:5432/postgres',
  applySchema: async ({ url }) => {
    execFileSync('pnpm', ['prisma', 'db', 'push'], { env: { ...process.env, DATABASE_URL: url } });
  },
});

// vitest.config.ts
export default { test: { globalSetup: ['tests/setup/test-db.ts'] } };

Each run gets myapp_test_run_<pid>_<timestamp>, dropped at teardown. Databases older than six hours are swept on the way in, so a killed run leaves debris for one afternoon rather than forever.

TEST_DATABASE_URL set in the shell still wins, which is how CI pins a service database. Deliberately read before any dotenv loading: a repository ships a shared default in .env for convenience, and honouring that would restore exactly the collision this prevents. TEST_DB_KEEP=1 keeps the database so a failure can be opened afterwards.

PGlite is a first-class mode, not a fallback

createTestDbGlobalSetup({
  mode: 'pglite',
  prefix: 'myapp_test_',
  applySchema: async ({ dataDir }) => { /* … */ },
  publish: ({ dataDir }) => ({ DB_TYPE: 'pglite', PGLITE_DATA_DIR: dataDir ?? '' }),
});

PGlite is Postgres compiled to WebAssembly, running in-process. pg_tables, TRUNCATE … RESTART IDENTITY CASCADE and SET LOCAL lock_timeout all behave as they do on a server, so the same tests run unchanged — with no Docker, on a laptop or in CI. This package's own reset tests run on it, which is the argument in practice rather than in principle.

Use it for development too, not only tests: an "ephemeral database" is a temporary directory, and there is no server to collide with.

Resetting, and proving it happened

import { fromPrisma, makeResetDatabase } from '@app-studio/qa-db';

const resetDatabase = makeResetDatabase(fromPrisma(prisma), { verifyTable: 'User' });

beforeEach(resetDatabase);

Two details are load-bearing.

The lock timeout. TRUNCATE needs an ACCESS EXCLUSIVE lock, and a real application legitimately issues fire-and-forget writes after a response — a session's lastSeenAt, an analytics row. Without a timeout, a race with one of those becomes an indefinite wait and the suite hangs rather than fails. Failing fast and retrying is strictly better: the same race resolves in milliseconds on the next attempt.

The postcondition. The reset verifies its own effect. A suite that starts against leftover rows does not fail here; it fails three tests later, in a way that reads as an application bug. Checking costs one query and turns a confusing failure into an obvious one.

Tables are discovered from the database rather than declared, and migration bookkeeping is left alone.

Any client

SqlExecutor is three methods, so the same reset works through Prisma, pg or PGlite:

import { fromPg, fromPglite, fromPrisma } from '@app-studio/qa-db';

Prisma is adapted structurally rather than imported — @prisma/client is generated per project, and depending on it here would pin every host to a version of a package that does not exist until they run a generator.

Exports

| Export | Purpose | | --- | --- | | createTestDbGlobalSetup | The vitest globalSetup, installed in one line. | | createEphemeralDatabase, sweepStaleDatabases | The same lifecycle, imperatively, for orchestration scripts. | | makeResetDatabase, tableNames | Truncate with a verified postcondition; discover what to truncate. | | fromPrisma, fromPg, fromPglite | Adapters onto SqlExecutor. | | explicitDatabaseUrl, withDatabase, ephemeralName | The naming and override rules, exposed for scripts that need them. |

Requirements

Node.js 20 or newer. pg and @electric-sql/pglite are optional peers — install whichever mode you use.