Application code has fast testing loops: runners, fixtures, and red-green feedback in JavaScript, TypeScript, and Python. Postgres can be tested too, but database logic often sits outside those loops. Developers have to 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 end up there, even when Postgres is the better place to enforce them.
Mocks do not close the gap. They test how application code handles a result, not whether Postgres will produce it. A mock does not exercise a foreign key, fire a trigger, evaluate a row-level security policy, or verify the database role under which a query actually runs.
pgsql-test is an MIT-licensed harness that puts a real PostgreSQL database inside those loops. It spins up an ephemeral PostgreSQL database, seeds it once, and rolls every test back to that seeded state. Assertions run in the project’s existing test runner, and Postgres executes the constraints, functions, and policies under test. It is not the first way to test Postgres—pgTAP has long done it in pure SQL. pgsql-test targets the application layer instead, where most developers already work.
Row-level security (RLS) makes access rules enforceable by Postgres. Policies are pure database logic, invisible to mocks, and a wrong one can leak rows. Testing them means checking both what a user can access and what they cannot.
The pgsql-test harness provides an administrative client, pg, for setup and an application client, db, for testing grants and policies. Superusers bypass RLS, so tests need to exercise the database roles and permissions the application actually uses.
Suppose a project defines app.documents with an ownership policy, and its fixtures insert document 101 owned by Alice and document 202 owned by Bob. Each test runs inside a transaction that is rolled back afterwards, so every test starts from the seeded state:
import { getConnections } from 'pgsql-test';
let db, teardown;
beforeAll(async () => {
// create a fresh database and deploy the project's schema
({ db, teardown } = await getConnections());
});
afterAll(() => teardown());
beforeEach(() => db.beforeEach());
afterEach(() => db.afterEach());
test('Alice sees her document and not Bobs', async () => {
db.setContext({
role: 'authenticated',
'jwt.claims.user_id': 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
});
const result = await db.query(
'SELECT id FROM app.documents ORDER BY id'
);
expect(result.rows).toEqual([{ id: 101 }]);
});
The query has no ownership filter. The policy must make Alice’s document visible and keep Bob’s out of the result.
setContext() applies the role and identity settings through SET LOCAL and set_config(..., true), scoping them to the transaction. They supply the identity the policies read. Nothing validates a token. Test identities must match what the application’s policies expect. The RLS tutorial covers the setup.
The harness seeds through pgsql-seed, which loads SQL files, programmatic fixtures, CSV, JSON, or migrations.
The same harness runs under other stacks:
supabase-test supplies Supabase roles, schemas, and authentication defaults.
drizzle-orm-test runs Drizzle queries within the managed transaction.
pglite-test uses in-process PGlite, with no external database service when the schema and required extensions are supported.
graphile-test runs GraphQL against a PostGraphile schema backed by the test database. graphile-realtime-test adds subscription testing.
In one supabase-test run, noted by Supabase CEO Paul Copplestone, 246 tests across 44 databases completed in four seconds.
pgpm, Constructive’s package manager for modular PostgreSQL, scaffolds workspaces with pgsql-test, Jest, and GitHub Actions; getConnections() deploys the module’s plan by default. Start with pgpm init workspace, then add a schema change and a test.
Each change ships deploy, verify, and revert scripts. verify checks that the schema landed. The tests check that it behaves.
The scaffolded workspace extends the same feedback loop into CI with safegres. Tests verify that database behavior is correct, and safegres checks the deployed schema for security and performance regressions. Its CI job enforces a security threshold and compares performance findings against a committed baseline, so a change cannot lower the schema’s security grade or add performance findings.
Postgres enforces the rules. pgsql-test brings verification of those rules into the application development loop.
pgsql-test and its integrations are MIT licensed and available on npm and PyPI.
Get started: Tutorials · End-to-end testing course · npm · Source code