Mocks test how your application handles a result. They never test whether Postgres would have produced it. A mock will cheerfully return rows a row-level security policy should have filtered out, and it will never fire your trigger or check your foreign keys. That gap is why so much business logic ends up in application code even when the database is the better place to enforce it.

On 2026-09-22 the PostgreSQL news feed announced pgsql-test, an MIT-licensed harness that drops a real PostgreSQL database into the test runner you already use. Here is how to wire it up, seed it, and assert on the things only Postgres can enforce.

What pgsql-test does, and where pgTAP still wins

The harness creates an ephemeral, UUID-named database, seeds it once, and wraps every test in a transaction that is rolled back afterwards, so each test starts from the same known state. Assertions stay in your existing runner, and Postgres itself executes the policies, constraints, and triggers under test.

This is not the first way to test a database: pgTAP has done it in pure SQL for years, and it remains the right tool when the database is the whole system. pgsql-test aims one layer up, at developers who already have Jest or Mocha wired into CI and want database behaviour verified in the same pass. If your target is genuinely serverless, the sibling package pglite-test swaps the server for an in-process PGlite (WASM Postgres) instance — no service container in CI at all.

Step 1 — Install the harness

Pick one backend. This tutorial’s examples run unchanged on either.

# Server-backed: talks to a Postgres you can reach
npm install -D pgsql-test

# In-process alternative: no server, no service container
npm install -D pglite-test @electric-sql/pglite

Connection settings come from the environment where present. PGHOST, PGPORT, PGUSER, PGPASSWORD, and PGDATABASE override the defaults, and you can also pass them per call as getConnections({ pg: { host, port, user, password } }). Point them at a scratch role, never at production.

Step 2 — Give the harness a schema and a seed

Keep DDL and fixtures as ordinary SQL files. The harness runs them once before the suite, and that seeded state becomes the baseline every test rolls back to.

import path from 'path';
import { getConnections, seed } from 'pgsql-test';

const sql = (f: string) => path.join(__dirname, 'sql', f);

let db: any;
let teardown: () => Promise<void>;

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, [
    seed.sqlfile([sql('schema.sql'), sql('fixtures.sql')])
  ]));
});

afterAll(() => teardown());
beforeEach(() => db.beforeEach());  // BEGIN + SAVEPOINT
afterEach(() => db.afterEach());    // ROLLBACK TO SAVEPOINT + COMMIT

Seeding is composable: seed.sqlfile() for SQL files, seed.fn() for programmatic inserts, seed.json() and seed.csv() for fixtures, seed.loadPgpm() to deploy a migration project. One caveat worth knowing before you debug a phantom RLS failure: CSV and pgpm seeding do not apply a session context, so use JSON or SQL seeding when fixtures must be written under a role.

Step 3 — Test the policy in both directions

Take a multi-tenant documents table with an ownership policy. The DDL is plain Postgres, and testing it means checking what a user can reach and what they cannot.

CREATE TABLE app.documents (
  id       integer PRIMARY KEY,
  owner_id uuid NOT NULL,
  title    text NOT NULL
);

ALTER TABLE app.documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY documents_owner ON app.documents
  USING      (owner_id = current_setting('jwt.claims.user_id', true)::uuid)
  WITH CHECK (owner_id = current_setting('jwt.claims.user_id', true)::uuid);

GRANT USAGE ON SCHEMA app TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.documents TO authenticated;

The queries below carry no ownership filter on purpose. The policy is what has to filter them, including the read that probes a row by primary key.

const ALICE = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
const BOB   = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb';

test('Alice sees her document and not Bobs', async () => {
  db.setContext({ role: 'authenticated', 'jwt.claims.user_id': ALICE });

  const { rows } = await db.query('SELECT id FROM app.documents ORDER BY id');
  expect(rows).toEqual([{ id: 101 }]);
});

test('a direct lookup of another tenant row returns nothing', async () => {
  db.setContext({ role: 'anonymous' });

  const { rows } = await db.query('SELECT id FROM app.documents WHERE id = 202');
  expect(rows).toEqual([]);
});

test('WITH CHECK blocks a forged owner_id on insert', async () => {
  db.setContext({ role: 'authenticated', 'jwt.claims.user_id': ALICE });

  await expect(
    db.query(`INSERT INTO app.documents (id, owner_id, title) VALUES (303, $1, 'forged')`, [BOB])
  ).rejects.toMatchObject({ code: '42501' });   // row-level security violation
});

setContext() applies the role and the claims through SET LOCAL and set_config(..., true), so they are scoped to the current transaction and vanish when it rolls back. That mirrors production, where your middleware sets the same settings from a verified token; only the setup differs. Then write the negative cases, because they are the ones that catch a leak: assert an anonymous role sees zero rows, assert a primary-key probe against another tenant returns nothing, and assert the other user sees their row and not Alice’s. A policy that returns everything passes a one-way test.

Step 4 — Assert on the write path

Reads are only half the boundary. The WITH CHECK clause is what stops Alice inserting a row owned by Bob, and it raises a specific SQLSTATE you can assert on: 42501 for a policy violation, 23505 for a unique violation, 23503 for a foreign key. Assert on the code rather than the message text — wording changes between major versions, SQLSTATE does not.

Triggers are testable the same way, and this is where mocks fail hardest: insert a row as the application role, then read the audit table to prove the trigger fired. The harness hands you two clients — pg, connected as the root or superuser for setup and introspection, and db, the application-level client you run policy tests through. Keep that split honest, because a superuser bypasses RLS and an assertion made from pg proves nothing about your policy.

Step 5 — Five pitfalls that produce false confidence

1. The table owner bypasses RLS. If the client you assert from owns the table, every row is visible and your suite goes green for the wrong reason. Assert from a non-owner role, or set ALTER TABLE ... FORCE ROW LEVEL SECURITY so the owner is subject to the policy too.
2. setContext() is transaction-local. Inside a test body a transaction is already open, so context persists across queries. In beforeAll() it does not — wrap those calls in db.begin() and db.commit(), or the next query silently runs without the identity you set.
3. One session per test rules some things out. Advisory locks, cross-session visibility, concurrent transactions, autovacuum, and background workers need two real connections. Test those on a real cluster.
4. The in-process variant has limits. PGlite supports a subset of extensions, and commit-and-continue publishing is unavailable under its shared session. Standard roles are created for you, so setContext({ role: 'authenticated' }) works without manual DDL; extra roles must be created in your setup SQL.
5. Everything rolls back. That is what makes the suite fast and isolated, but it also means commit-time behaviour is not exercised unless you opt into publishing.

Scaffolding the loop, and keeping it fast

If you would rather not assemble the pieces, the pgpm workspace scaffold (pgpm init workspace) generates a project wired for pgsql-test, Jest, and GitHub Actions, where each schema change ships deploy, verify, and revert scripts. It pairs with a CI step that grades the deployed schema for security and performance regressions against a committed baseline, so a pull request cannot quietly lower the schema’s grade.

Speed is the point. A six-test suite covering policy enforcement, a direct-lookup probe, a forged-owner write, trigger firing, and rollback isolation booted and finished in about a second in our own smoke run against the in-process variant. When the loop is that fast, there is no argument left for shipping access rules untested.

Start with one table that already has a policy. Write the positive assertion, then the negative one, and let the harness run them on every commit.