Seed a disposable Postgres for integration tests

The container comes up in four seconds. That is the easy part, and it is where most integration-test guides stop: a docker compose up, a health check, a DATABASE_URL, and a green pipeline.

Then the suite runs against a database with no rows in it. Or against the three fixture rows someone committed in 2024. Or against a fresh random dataset every time, so a test that failed on Tuesday cannot be made to fail again on Wednesday. The tests pass, and the thing they were supposed to verify, which is how your application behaves against data, is never exercised at all.

A disposable database has two requirements that pull in different directions. It has to be cheap enough to destroy on every run, and its contents have to be reproducible enough that a failure means something. Getting both is the whole job.

Starting Postgres is the easy half

One container is enough, and it does not need a source database sitting next to it.

services:
  postgres:
    image: postgres:18-alpine
    environment:
      POSTGRES_USER: weavori
      POSTGRES_PASSWORD: weavori
      POSTGRES_DB: app
    ports:
      - "5434:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U weavori -d app"]
      interval: 2s
      timeout: 3s
      retries: 20

Two details matter more than they look. The health check is not decoration: depends_on waits for the container to start, not for Postgres to accept connections, so a readiness loop or a healthcheck is the difference between a reliable suite and a race. And the image tag should be pinned. postgres:18-alpine behaves differently from postgres:latest on the day a new major ships, and an integration test that changes its database because someone else released software is not a test.

Fill it from the schema, not from a dump

The instinct is to load a pg_dump of staging. That works until the dump is three weeks stale, or it contains customer rows, or it is 40 GB and the pipeline spends longer restoring it than running the tests.

The schema is the only input a generator actually needs. Weavori reads the same CREATE TABLE statements a migration would apply and writes rows into the container in foreign-key order, parents before children:

There is no source database in that command, which means no production credentials in CI and no network path to a real cluster. The container is empty, the schema file is in the repository, and the target tables are created as part of the run (--create-tables is on by default). If you would rather have your real migrations build the schema, run them first and pass --create-tables=false, so the test exercises the same DDL path production does.

This is also the honest reason to generate instead of dump: the data has no PII in it by construction, and it is never stale relative to the schema in the same commit. When a test genuinely needs production's exact rows, a dump or a masked slice of production is the right tool, and neither belongs in a job that any pull request can trigger.

Reproducibility is what makes the container disposable

Here is the failure that costs a day. A test fails once in twenty runs. It passes locally. It passes when you re-run the job. Nobody can reproduce it, so it gets marked flaky and skipped, and whatever it was catching stays in the code.

The cause is usually the database, not the test. If each run draws fresh random values, the one run that produced a specific collision or a specific ordering is gone forever. A container you can throw away is not the same thing as a database whose contents you can reproduce, and a test suite needs the second property.

--seed 42 makes the run byte-identical: the same seed against the same schema produces the same rows, in the same order, with the same derived values. A failure becomes an input you can replay instead of a story about Tuesday. And the seed does not have to be memorized, because every run prints it, including the runs where you let it pick randomly. The line looks like this:

Seed: 4217933 (random, replay with --seed 4217933)

That line is the difference between "it failed once" and a command you can paste into a terminal. In CI, log it. The seed in the job output is the reproduction step.

The CI mechanics that actually bite

The generation itself is one command. What breaks a pipeline is the environment around it.

• Authentication has no keyring. A CI runner cannot open a browser or unlock an OS keychain, so use WEAVORI_API_KEY (or --api-key). An absent credential exits 2 rather than falling back to an interactive prompt that hangs the job.
• Machine-readable output. --output plain gives RFC 3339 timestamps and no terminal styling; --output json emits JSONL. Both are safe to pipe into logs and log aggregators, where pretty produces noise.
• Branch on exit codes, not on message text. Auth is 2, quota is 4, and a general failure is 1, so a retry policy for a quota error does not accidentally retry an auth error forever. The full contract is in the generate reference.
• Licenses validate offline. A runner on a restricted network does not need an outbound call to generate. That matters in air-gapped or egress-filtered environments, where a tool that phones home fails for reasons unrelated to your code.
• Cache, and know how to bypass it. The schema cache skips re-introspection between runs. When you are debugging a schema change and the output looks stale, --no-cache forces a fresh read.

A GitHub Actions step ends up small, because the container service does the heavy lifting:

- name: Generate synthetic data
  env:
    WEAVORI_API_KEY: ${{ secrets.WEAVORI_API_KEY }}
  run: |
    weavori generate --input schema.sql --input-format ddl \
      --target "postgres://weavori:weavori@localhost:5432/app" \
      --rows 1000 --seed 42 --yes --output plain

Compose or Testcontainers

They solve different lifecycle problems, and the deciding question is who owns the container.

Neither changes the data problem. With Testcontainers, the test process starts Postgres and owns its lifetime, and you run the same generation command against the mapped DSN before the suite begins. With Compose, the pipeline starts the database once and every step shares it. Pick on lifecycle, not on which one has a nicer README, and make the seed deterministic in both.

Assert on the data, not only on the application

An integration suite that only asserts HTTP status codes leaves the database unverified. The cheap addition is a set of assertions about the data itself, run with ON_ERROR_STOP so a failure fails the job:

DO $$
DECLARE orphans integer; bad_emails integer;
BEGIN
  SELECT count(*) INTO orphans
  FROM projects p LEFT JOIN users u ON u.id = p.owner_id
  WHERE u.id IS NULL;
  SELECT count(*) INTO bad_emails
  FROM users WHERE email !~ '@example\.com$' OR email <> lower(email);
 
  IF orphans > 0 THEN
    RAISE EXCEPTION '% project(s) reference missing owners', orphans;
  END IF;
  IF bad_emails > 0 THEN
    RAISE EXCEPTION '% email(s) not derived from the formula', bad_emails;
  END IF;
END $$;

Two things are being tested here that application code cannot test. The first is that generation produced a referentially closed dataset, so the suite is not passing because the tables happened to be empty. The second is that a formula still derives what it is supposed to derive, which catches the day someone changes the derivation and every downstream assumption silently shifts.

What this does not do

Summary

1. The container is the easy half. Readiness and a pinned image tag are the two details that keep it reliable.
2. Generate from the schema file. No source database, no production credentials, no stale dump, and no PII by construction.
3. A disposable database still has to be reproducible. --seed makes a failed run replayable, and the seed is printed on every run so you never have to guess.
4. The environment is where pipelines break. API key, plain/json output, exit codes, offline license validation, and --no-cache when the cache lies to you.
5. Assert on the data. Row counts, orphan checks, and derived-value checks catch the failures application tests structurally cannot see.

The full runnable version, including the compose file and the GitHub Actions workflow, is cookbook recipe 09. For the data itself, Realistic synthetic data is more than valid foreign keys covers the distribution and coherence controls that decide whether the rows behave like a population instead of a random draw.