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](https://www.npmjs.com/package/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. ### Testing row-level security 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](https://constructive.io/learn/e2e-postgres-testing/testing-rls-policies) covers the setup. The harness seeds through [**`pgsql-seed`**](https://www.npmjs.com/package/pgsql-seed), which loads SQL files, programmatic fixtures, CSV, JSON, or migrations. ### One harness, many platforms The same harness runs under other stacks: - [**`supabase-test`**](https://www.npmjs.com/package/supabase-test) supplies Supabase roles, schemas, and authentication defaults. - [**`drizzle-orm-test`**](https://www.npmjs.com/package/drizzle-orm-test) runs Drizzle queries within the managed transaction. - [**`pglite-test`**](https://www.npmjs.com/package/pglite-test) uses in-process PGlite, with no external database service when the schema and required extensions are supported. - [**`graphile-test`**](https://www.npmjs.com/package/graphile-test) runs GraphQL against a PostGraphile schema backed by the test database. [**`graphile-realtime-test`**](https://www.npmjs.com/package/graphile-realtime-test) adds subscription testing. In one `supabase-test` run, [noted by Supabase CEO Paul Copplestone](https://x.com/kiwicopple/status/1998059097728385292), 246 tests across 44 databases completed in four seconds. ### Scaffolding the loop with pgpm [**pgpm**](https://www.postgresql.org/about/news/introducing-pgpm-a-package-manager-for-modular-postgresql-3196/), 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`**](https://www.npmjs.com/package/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. ### Availability `pgsql-test` and its integrations are MIT licensed and available on [npm](https://www.npmjs.com/package/pgsql-test) and [PyPI](https://pypi.org/project/pgsql-test/). **Get started:** [Tutorials](https://constructive.io/learn) · [End-to-end testing course](https://constructive.io/learn/e2e-postgres-testing) · [npm](https://www.npmjs.com/package/pgsql-test) · [Source code](https://github.com/constructive-io/constructive/tree/main/postgres/pgsql-test)
