pgsql-test (PostgreSQL Integration Testing)

pgsql-test provides a complete testing toolkit for PostgreSQL — from RLS policy verification and test seeding to snapshot utilities and complex multi-client scenario management. All tests run in transactions with savepoint-based isolation.

When to Apply

Use this skill when:

• Testing RLS policies: Verifying user isolation, role-based access, multi-tenant security
• Seeding test data: Loading fixtures with loadJson, loadSql, loadCsv
• Testing exceptions: Handling aborted transactions when operations should fail
• Snapshot testing: Deterministic assertions with pruneIds, pruneDates, etc.
• Building helpers: Reusable test functions, constants, assertion utilities
• JWT context: Simulating authenticated users with claims for RLS
• Complex scenarios: Multi-client patterns, transaction management, cross-connection visibility

Quick Start

pnpm add -D pgsql-test

import { getConnections, PgTestClient } from 'pgsql-test';

let pg: PgTestClient;  // Superuser (bypasses RLS)
let db: PgTestClient;  // App-level (enforces RLS)
let teardown: () => Promise<void>;

beforeAll(async () => {
  ({ pg, db, teardown } = await getConnections());
});

afterAll(async () => {
  await teardown();
});

beforeEach(async () => {
  await pg.beforeEach();
  await db.beforeEach();
});

afterEach(async () => {
  await db.afterEach();
  await pg.afterEach();
});

Core Concepts

Two Database Clients

Client	Purpose
pg	Superuser — setup/teardown, bypasses RLS
db	App-level — testing with RLS enforcement

Test Isolation

Each test runs in a transaction with savepoints:

• beforeEach() starts a savepoint
• afterEach() rolls back to savepoint
• Tests are completely isolated

Testing RLS Policies

// Set user context
db.setContext({
  role: 'authenticated',
  'request.jwt.claim.sub': userId
});

// Users only see their own records
const result = await db.query('SELECT * FROM app.posts');
expect(result.rows).toHaveLength(1);

Handling Expected Failures (Savepoint Pattern)

When testing operations that should fail, use savepoints to avoid "current transaction is aborted" errors:

it('rejects unauthorized access', async () => {
  db.setContext({ role: 'anonymous' });

  await db.savepoint('unauthorized_access');

  await expect(
    db.query('INSERT INTO app.private_data (secret) VALUES ($1)', ['hack'])
  ).rejects.toThrow(/permission denied/);

  await db.rollback('unauthorized_access');

  // Connection still works
  const result = await db.query('SELECT 1 as ok');
  expect(result.rows[0].ok).toBe(1);
});

Seeding Test Data

// Inline JSON (best for small datasets)
await pg.loadJson({
  'app.users': [
    { id: 'user-1', email: 'alice@example.com', name: 'Alice' }
  ]
});

// SQL files (best for complex data)
await pg.loadSql([path.join(__dirname, 'fixtures/seed.sql')]);

// CSV files (best for large datasets, uses COPY)
await pg.loadCsv({
  'app.categories': path.join(__dirname, 'fixtures/categories.csv')
});

Snapshot Testing

import { snapshot, IdHash } from 'pgsql-test/utils';

const result = await db.query('SELECT * FROM users ORDER BY email');
expect(snapshot(result.rows)).toMatchSnapshot();

// With ID tracking
const idHash: IdHash = {};
result.rows.forEach((row, i) => { idHash[row.id] = i + 1; });
expect(snapshot(result.rows, idHash)).toMatchSnapshot();

Default pruners: pruneTokens, prunePeoplestamps, pruneDates, pruneIdArrays, pruneUUIDs, pruneHashes, pruneIds.

JWT Context for RLS

// Authenticated user
db.setContext({
  role: 'authenticated',
  'jwt.claims.user_id': userId
});

// Organization context
db.setContext({
  role: 'authenticated',
  'jwt.claims.user_id': userId,
  'jwt.claims.org_id': orgId
});

// Anonymous
db.setContext({ role: 'anonymous' });

// Clear context
db.clearContext();

Reusable Test Helpers

export const TEST_USER_IDS = {
  USER_1: '00000000-0000-0000-0000-000000000001',
  USER_2: '00000000-0000-0000-0000-000000000002',
  ADMIN: '00000000-0000-0000-0000-000000000099',
} as const;

export function setAuthContext(db: PgTestClient, userId: string): void {
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': userId,
  });
}

Troubleshooting Quick Reference

Issue	Quick Fix
"current transaction is aborted"	Use savepoint pattern before expected failures
Data persists between tests	Ensure beforeEach/afterEach hooks are set up
RLS blocking test inserts	Use pg (superuser) for seeding, db for testing
Foreign key violations in seeding	Load parent tables before child tables
Tests interfere with each other	Every test file needs beforeEach/afterEach hooks

Reference Guide

Consult these reference files for detailed documentation on specific topics:

Reference	Topic	Consult When
references/rls.md	Testing RLS policies	SELECT/INSERT/UPDATE/DELETE policies, multi-user isolation, anonymous access
references/seeding.md	Seeding test databases	loadJson, loadSql, loadCsv, RLS-aware seeding, fixture organization
references/exceptions.md	Handling aborted transactions	Savepoint pattern for expected failures, constraint violations, permission errors
references/snapshot.md	Snapshot testing utilities	pruneIds, pruneDates, IdHash tracking, custom pruners, error formatting
references/helpers.md	Reusable test helpers	Constants, typed helpers, assertion utilities, test-utils organization
references/jwt-context.md	JWT claims and role context	setContext API, auth() helper, reading claims in SQL, context timing
references/scenario-setup.md	Complex test scenarios	Two-client pattern, transaction management, publish(), per-describe setup

Cross-References

Related skills (separate from this skill):

• pgpm (references/testing.md) — General pgpm test setup and seed adapters
• drizzle-orm-test — Testing with Drizzle ORM (uses pgsql-test utilities)
• constructive-safegres — Safegres authorization policies that RLS tests validate