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

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

Reference Guide

Consult these reference files for detailed documentation on specific topics:

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