pgt postgres_test v0.7.1

A guided tour for someone new to pgt: the problem it solves, the one idea you need to hold in your head, how you write and run a test, and the few behaviors that make a run safe to point at a real database. For the exhaustive reference — every flag, every assertion in the catalog — see the README and the design docs; this guide is the "why" and the "how it fits together."

The problem it solves

You want to test PostgreSQL the way it deserves: assertions written in SQL, living next to the schema they exercise, so a trigger's behavior or a function's contract is checked in the language it's written in, against a real database. That's pgTAP's one durable idea, and it's the right one.

But pgTAP carries a Perl-era apparatus around it. Its assertions emit TAP — a flat text stream — that a Perl harness (pg_prove) parses back into pass/fail. The result is text, so a failed comparison is a string you read with your eyes; there's no structured expected/got to diff. And pg_prove is a Perl runtime you have to install and keep alongside your database tooling.

pgt keeps the good idea and drops the apparatus. Assertions still live in SQL, but each one returns a typed composite value instead of TAP text. A single static Rust binary reads those values straight off the wire into a struct — no parsing, no Perl, no pg_prove. It's a net-new, independent tool, not a port: the call sites and names are designed on their own merits.

The one idea: the assertion is the result type

Everything follows from one decision about the boundary between the database and the harness. An assertion is a SQL function that returns one row of a composite type, test.test_result:

create type test.test_status as enum ('ok', 'not_ok', 'skip', 'todo');

create type test.test_result as (
    name        text,
    status      test.test_status,
    expected    jsonb,        -- captured only on failure, for the diff
    got         jsonb,        -- "
    diagnostic  text,         -- free-form overflow for non-value failures
    duration_ms float8,       -- stamped by the harness
    location    text          -- stamped by the harness (file:line)
);

So a test file is just a script of SQL statements, and the harness tells assertions from setup by the type of what each statement returns:

• A statement whose result is a test.test_result is an assertion — collected, stamped, and reported.
• Anything else — your inserts, your setup selects — just runs. You mark nothing; the type is the signal.

This is what removes the ceremony. There's no plan(n)/finish() — the harness counts the result rows it collected. There's no per-file registration — a file is found on disk and run. And one more consequence worth internalizing: comparisons are decided with native typed operators, not in jsonb. A pass/fail uses is not distinct from, the operator you asked for, or a catalog lookup; expected/got are captured as jsonb only on failure, purely so the harness can render a structural diff. (Comparing in jsonb would lie — 1.0 and 1.00 are numeric-equal but jsonb-distinct.) Decide native, report jsonb.

Getting started

Two installs: the binary on your machine, and the library into each database you test.

# the binary
just build && just install              # -> /usr/local/bin/pgt

# the PL/pgSQL assertion library, into a database (idempotent)
pgt install -d mydb

pgt install creates a dedicated test schema and the assert_* functions over an ordinary connection — no superuser, no files dropped into the server's extension directory. That's deliberate: pgt runs against any Postgres, including managed services (RDS, Cloud SQL, Aurora) where you can't install a compiled extension. It's idempotent (create schema if not exists + create or replace), so re-running it to pick up a newer library is safe. pgt uninstall -d mydb drops the schema again.

Connection flags follow libpq — --host, -p/--port, -U/--user, -d/--dbname — and anything you omit falls through to the PG* environment, so an existing PGHOST/PGDATABASE setup just works.

Writing a test

A test is a .sql file: select assert_*(...) calls with ordinary setup statements between them. The description is always the last argument and always optional — omit it and a sensible default is generated.

-- tests/users.sql

-- a behavior assertion: this statement runs without raising
select assert_runs(
    $$ insert into app.users (email) values ('a@b.com') $$,
    'a fresh email inserts cleanly');

-- a value assertion: null-safe equality (is not distinct from)
select assert_equal(
    (select count(*) from app.users), 1::bigint,
    'one user after insert');

-- a behavior assertion: this statement raises a specific error
select assert_raises(
    $$ insert into app.users (email) values ('a@b.com') $$,
    'unique_violation',
    'a duplicate email is rejected');

-- a delta assertion pgTAP lacks: the action moves the measure by exactly delta
select assert_changes(
    $$ insert into app.users (email) values ('c@d.com') $$,
    $$ select count(*) from app.users $$,
    1,
    'inserting a user grows the table by one');

A few things to note from that file:

• Assertions that take SQL (assert_runs, assert_raises, assert_empty, assert_changes, assert_set_equal, …) take it as text, which they execute internally and bracket with exception handling. Dollar-quoting ($$ … $$) keeps that SQL readable.
• assert_changes measures a delta. The action runs, the scalar measure is read before and after, and the assertion checks the difference equals delta — first-class, where most suites today fake it with a do block and an emptiness check.
• The harness fills in where and how long: it submits one statement at a time, so it already knows each assertion's source file:line and wall-clock time, and stamps location/duration_ms client-side. The database only decides the verdict.

The assertion catalog, at a glance

The surface is curated and behavior-focused — a sharp set, not pgTAP's hundreds — and leads with behavior because that's what real suites are mostly about. The core, built and verified against PostgreSQL, covers:

The structural / privilege / RLS tail (existence checks, constraints, indexes, grants, RLS) and the convenience assertions are designed and landing incrementally. The full catalog — every signature, including the not-yet-built ones — is docs/design/assertions.md.

The everyday loop

Point pgt at a file or a directory (directories are recursed for *.sql):

$ pgt -d mydb tests/
Running 1 test file(s)...
Files=1  Tests=4  Failed=0  Skipped=0  FailedFiles=0  (0.1s)
Result: PASS

The verbosity and machine-readable modes are the harness's own — it owns its output, with build/progress on stderr and results on stdout so a pipe sees only results:

Exit is non-zero if any assertion fails or any file errors, so pgt drops into CI unchanged.

For a bigger suite, run files concurrently and shuffle the order to surface inter-file dependencies:

$ pgt -d mydb -j 8 tests/            # 8 files at a time, across 8 connections
$ pgt -d mydb --random tests/        # shuffle, and print the seed it used
$ pgt -d mydb --seed 12345 -j1 tests/  # replay that order, fully reproducibly

What makes it safe to run against a real database

The single most important runtime behavior: every test file is bracketed in a transaction that rolls back. The harness does, per file:

1. begin
2. split the file into statements with a dollar-quote-, comment-, and string-literal-aware splitter (never a naive split(';')), tracking each statement's line offset;
3. submit each statement in order, decoding any test.test_result it sees (matched by type OID, looked up once at connect) and stamping it;
4. rollback — discarding everything the file did.

The results were already read in step 3, before the rollback, so nothing is lost — and the target database is left pristine. This is what makes "run against an existing, even shared, database" safe: a run never pollutes it. Because there's no per-session protocol state, the harness can reuse connections across files and run them in parallel; a transient deadlock or serialization failure under -j is retried with a short backoff.

Two rules follow for the test author:

• Don't issue your own commit in a test file — it would defeat the rollback. Savepoints inside a file are always fine.
• If a test genuinely must cross a commit boundary, --no-rollback is the explicit escape hatch (and then you own the cleanup).

One thing pgt deliberately does not do: create databases, apply schema, or load fixtures. Its scope is run-only, against an existing database. Standing the database up is the job of your test setup or a migration tool such as pgsm.

How it fits the rest of the tools

pgt is one of the postgres_tools family — pgsf (formatter), pgstd (extractor), pgsm (schema-management engine), and pgt (this runner). The harness is exposed as a library, not just a binary: its run_report returns the structured report with no output of its own, so pgsm test drives this same runner to execute suites while keeping its own database-build pipeline. You write tests once, in SQL, and either tool can run them.

Where to go next

• The README — the quick reference: install, the CLI surface, the result type, and the relationship to the other tools.
• docs/design/architecture.md — the authoritative design: the wire protocol, the script-style authoring model, and the harness (splitter, per-file transaction, type-OID assertion detection, parallelism, output modes, and the PL/pgSQL install).
• docs/design/assertions.md — the full assertion catalog: every signature, the naming conventions, and the build order, with an implementation-status section marking what's live today.