Disposable PostgreSQL

How does a test get a real PostgreSQL database, and how do you know it is gone afterwards? PGlite covers most tests. This covers the rest, the ones that need a server your built app reaches over a URL.

import { createDisposablePostgres } from "@questpie/testing/scenario";
import { createAppForRuntime } from "#questpie/app-factory";

const database = await createDisposablePostgres({
	adminUrl: "postgres://postgres:postgres@127.0.0.1:5432/postgres",
	migrate: async (databaseUrl) => {
		const app = await createAppForRuntime({
			app: { url: "http://questpie.test" },
			db: { url: databaseUrl },
		});
		await app.migrations.up();
		await app.destroy();
	},
});

// hand database.url to the server or app under test
await database.dispose();

Running the CLI works too. Spawn questpie migrate:up with DATABASE_URL set to the url you were handed.

What you get back

runId is stable for the life of the run. Use it to tag artifacts, log lines or a bucket prefix so a failure points back at one database.

The lease

Setup opens one admin connection and takes a session advisory lock keyed on the database name. That lock is the lease, and it is held on that same connection until you dispose. It is what makes the sweep below safe to run concurrently.

dispose() is memoized, so two calls, or two concurrent calls, do one teardown. It drops the database WITH (FORCE), so a connection your app forgot to close does not block it. Teardown failures collect into DisposablePostgresCleanupError, and .errors holds each one.

Sweeping what crashed

A killed test run leaves its database behind. Every createDisposablePostgres call therefore sweeps first, and you can also sweep on its own:

import { sweepStalePostgresDatabases } from "@questpie/testing/scenario";

const dropped = await sweepStalePostgresDatabases({
	adminUrl: "postgres://postgres:postgres@127.0.0.1:5432/postgres",
});

It returns the names it dropped. A database has to clear three gates first.

1. The name matches the qp_harness_ pattern exactly.
2. Its timestamp is older than staleAfterMs, 30 minutes by default.
3. pg_try_advisory_lock on its name succeeds.

Gate 3 is why a live run survives a sweep from another run. It holds the lease, the try fails, and the sweep moves on. Gate 1 is why nothing else on the server is ever a candidate. A name it cannot parse, or a timestamp that does not round trip, is skipped rather than guessed at.

Options

adminUrl is checked before anything connects. It must parse, use the postgres: or postgresql: protocol, and name a database. Naming a qp_harness_ database is rejected, because PostgreSQL cannot drop the database its own connection is using. A bad value throws a TypeError.

When setup fails

Every failure path cleans up before it throws. You get a DisposablePostgresSetupError carrying three things.

A migrations phase failure means the database was created and then dropped again. Your migration error is on .cause.

Related

• Testing for the in-process PGlite harness.
• Production server for the built server that connects to this database.
• Migrations for what migrations.up() applies.