Snapshot Testing with pgsql-test

Use snapshot utilities from pgsql-test/utils to create deterministic, reproducible test assertions. These helpers replace dynamic values (IDs, UUIDs, dates, hashes) with stable placeholders.

When to Apply

Use this skill when:

• Writing snapshot tests for database queries
• Need deterministic output from queries with UUIDs or timestamps
• Testing API responses that include database-generated values
• Comparing query results across test runs

Core Utilities

Import from pgsql-test/utils or drizzle-orm-test/utils:

import { 
  snapshot,
  prune,
  pruneIds,
  pruneDates,
  pruneUUIDs,
  pruneHashes,
  pruneTokens,
  composePruners,
  createSnapshot
} from 'pgsql-test/utils';

Basic Usage

snapshot()

The main utility that applies all default pruners recursively:

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

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

Output transforms dynamic values to stable placeholders:

// Before snapshot()
{
  id: '550e8400-e29b-41d4-a716-446655440000',
  name: 'Alice',
  created_at: '2024-01-15T10:30:00.000Z',
  password_hash: '$2b$10$...'
}

// After snapshot()
{
  id: '[ID]',
  name: 'Alice',
  created_at: '[DATE]',
  password_hash: '[hash]'
}

With Drizzle ORM

import { drizzle } from 'drizzle-orm/node-postgres';
import { snapshot } from 'drizzle-orm-test/utils';
import { users } from './schema';

const drizzleDb = drizzle(db.client);
const result = await drizzleDb.select().from(users);
expect(snapshot(result)).toMatchSnapshot();

Individual Pruners

pruneIds()

Replaces id and *_id fields with [ID]:

import { pruneIds } from 'pgsql-test/utils';

pruneIds({ id: 123, user_id: 'abc-123', name: 'Alice' });
// { id: '[ID]', user_id: '[ID]', name: 'Alice' }

pruneDates()

Replaces Date objects and ISO date strings in *_at or *At fields:

import { pruneDates } from 'pgsql-test/utils';

pruneDates({ 
  created_at: '2024-01-15T10:30:00.000Z',
  updatedAt: new Date(),
  name: 'Alice'
});
// { created_at: '[DATE]', updatedAt: '[DATE]', name: 'Alice' }

pruneUUIDs()

Replaces UUID values in uuid and queue_name fields:

import { pruneUUIDs } from 'pgsql-test/utils';

pruneUUIDs({ uuid: '550e8400-e29b-41d4-a716-446655440000' });
// { uuid: '[UUID]' }

pruneHashes()

Replaces *_hash fields starting with $:

import { pruneHashes } from 'pgsql-test/utils';

pruneHashes({ password_hash: '$2b$10$xyz...' });
// { password_hash: '[hash]' }

pruneTokens()

Replaces token and *_token fields:

import { pruneTokens } from 'pgsql-test/utils';

pruneTokens({ access_token: 'eyJhbGciOiJIUzI1NiIs...' });
// { access_token: '[token]' }

pruneIdArrays()

Replaces *_ids array fields with count placeholder:

import { pruneIdArrays } from 'pgsql-test/utils';

pruneIdArrays({ member_ids: ['id1', 'id2', 'id3'] });
// { member_ids: '[UUIDs-3]' }

prunePeoplestamps()

Replaces *_by fields (audit columns):

import { prunePeoplestamps } from 'pgsql-test/utils';

prunePeoplestamps({ created_by: 'user-123', updated_by: 'user-456' });
// { created_by: '[peoplestamp]', updated_by: '[peoplestamp]' }

pruneSchemas()

Replaces schema names starting with zz-:

import { pruneSchemas } from 'pgsql-test/utils';

pruneSchemas({ schema: 'zz-abc123' });
// { schema: '[schemahash]' }

ID Hash Tracking

Track ID relationships across snapshots using IdHash:

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

const idHash: IdHash = {
  '550e8400-e29b-41d4-a716-446655440001': 'alice',
  '550e8400-e29b-41d4-a716-446655440002': 'bob'
};

const result = [
  { id: '550e8400-e29b-41d4-a716-446655440001', name: 'Alice' },
  { id: '550e8400-e29b-41d4-a716-446655440002', name: 'Bob' }
];

expect(snapshot(result, idHash)).toMatchSnapshot();
// [
//   { id: '[ID-alice]', name: 'Alice' },
//   { id: '[ID-bob]', name: 'Bob' }
// ]

Numeric ID tracking:

const idHash: IdHash = {};
let counter = 1;

// Assign IDs as you encounter them
for (const row of result) {
  if (!idHash[row.id]) {
    idHash[row.id] = counter++;
  }
}

expect(snapshot(result, idHash)).toMatchSnapshot();
// [
//   { id: '[ID-1]', name: 'Alice' },
//   { id: '[ID-2]', name: 'Bob' }
// ]

Custom Pruners

composePruners()

Combine multiple pruners into one:

import { composePruners, pruneDates, pruneIds } from 'pgsql-test/utils';

const myPruner = composePruners(pruneDates, pruneIds);
const result = myPruner({ id: 123, created_at: new Date() });
// { id: '[ID]', created_at: '[DATE]' }

createSnapshot()

Create a custom snapshot function with specific pruners:

import { createSnapshot, pruneDates, pruneIds, pruneHashes } from 'pgsql-test/utils';

const mySnapshot = createSnapshot([pruneDates, pruneIds, pruneHashes]);

const result = await db.query('SELECT * FROM users');
expect(mySnapshot(result.rows)).toMatchSnapshot();

Default Pruners

The snapshot() function applies these pruners by default:

1. pruneTokens — token, *_token
2. prunePeoplestamps — *_by
3. pruneDates — *_at, *At, Date objects
4. pruneIdArrays — *_ids arrays
5. pruneUUIDs — uuid, queue_name
6. pruneHashes — *_hash
7. pruneIds — id, *_id

Error Code Extraction

Extract error codes from enhanced error messages:

import { getErrorCode } from 'pgsql-test/utils';

try {
  await db.query('SELECT * FROM nonexistent');
} catch (err) {
  const code = getErrorCode(err.message);
  // Returns first line only, stripping debug context
  expect(code).toBe('UNDEFINED_TABLE');
}

PostgreSQL Error Formatting

Format PostgreSQL errors for readable output:

import { 
  extractPgErrorFields, 
  formatPgError,
  formatPgErrorFields 
} from 'pgsql-test/utils';

try {
  await db.query('invalid sql');
} catch (err) {
  const fields = extractPgErrorFields(err);
  console.log(formatPgError(err));
  // Formatted error with context
}

Complete Test Example

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

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

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

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

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

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

describe('User queries', () => {
  it('returns users with stable snapshot', async () => {
    // Seed test data
    await db.query(`
      INSERT INTO users (email, name) VALUES
      ('alice@example.com', 'Alice'),
      ('bob@example.com', 'Bob')
    `);

    const result = await db.query('SELECT * FROM users ORDER BY email');
    
    // Snapshot with ID tracking
    const idHash: IdHash = {};
    result.rows.forEach((row, i) => {
      idHash[row.id] = i + 1;
    });

    expect(snapshot(result.rows, idHash)).toMatchSnapshot();
  });
});

Best Practices

1. Use snapshot() by default: Covers most common dynamic fields
2. Track IDs with IdHash: When relationships between records matter
3. Custom pruners for special fields: Create domain-specific pruners
4. Order results: Use ORDER BY for deterministic row order
5. Prune before comparing: Apply pruners before any assertions

References

• Related skill: pgsql-test-seeding for seeding test data
• Related skill: pgsql-test-rls for RLS testing
• Related skill: drizzle-orm-test for Drizzle ORM integration