Application code has long enjoyed tight red-green loops—runners, fixtures, and instant feedback in JavaScript, TypeScript, and Python. Database logic often sits outside those loops: developers provision state, manage transactions, or fall back to a pasted query, a browser refresh, or a manual check. That gap shapes architecture. When rules are easier to test in application code than in Postgres, they tend to migrate there, even when Postgres is the better place to enforce them. Mocks do not close the gap, because they test how application code handles a result, not whether Postgres will produce it.
pgsql-test is an MIT-licensed harness that puts a real PostgreSQL database inside those loops. The concrete behavior:
- It spins up an ephemeral PostgreSQL database for the test run, so no shared or persistent instance is required.
- The database is seeded once, then every test rolls back to that seeded state, giving tests a known baseline without re-provisioning.
- Assertions run in the project's existing test runner, so JavaScript, TypeScript, and Python suites keep their normal fixtures and feedback cycle.
- Postgres itself executes the objects under test: constraints, functions, triggers, row-level security policies, and the database role under which a query actually runs.
- It is not the first way to test Postgres—pgTAP has long done it in pure SQL—but pgsql-test targets developers working inside application-language test loops rather than SQL-only harnesses.
For developers, the implication is architectural as much as tactical. If foreign keys, triggers, and RLS policies can be asserted as easily as application logic, teams are less likely to push database rules into application code just to make them testable. Keeping enforcement in Postgres means tests verify the real engine's behavior—not a mock's approximation of it—while still fitting the fast feedback loop developers already use.