PGPM Testing

Run PostgreSQL integration tests with isolated databases using the pgsql-test package.

Testing Framework Standard

IMPORTANT: Constructive projects use Jest as the standard testing framework. Do NOT use vitest, mocha, or other test runners unless explicitly approved. Jest provides:

Consistent testing experience across all packages
Built-in mocking and assertion libraries
Snapshot testing support
Parallel test execution

When to Apply

Use this skill when:

Writing integration tests that need a database
Testing PGPM modules or migrations
Setting up isolated test databases
Seeding test data from SQL files or PGPM modules
Running PostGraphile/GraphQL integration tests

Quick Start

Installation

pnpm add -D pgsql-test

Basic Test Setup

import { getConnections } from 'pgsql-test';

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

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

afterAll(() => teardown());
beforeEach(() => db.beforeEach());
afterEach(() => db.afterEach());

test('database query works', async () => {
  const result = await db.query('SELECT 1 as num');
  expect(result.rows[0].num).toBe(1);
});

Core API

getConnections()

Creates an isolated test database and returns clients plus cleanup function.

import { getConnections } from 'pgsql-test';

const { db, teardown } = await getConnections(
  connectionOptions?,  // Optional: custom connection settings
  seedAdapters?        // Optional: array of seed adapters
);

Returns:

db - PgTestClient with query methods and transaction helpers
teardown - Cleanup function to drop the test database

PgTestClient Methods

Method	Description
`db.query(sql, params?)`	Execute SQL query
`db.beforeEach()`	Start savepoint (call in beforeEach)
`db.afterEach()`	Rollback to savepoint (call in afterEach)
`db.setContext(key, value)`	Set session context variable
`db.getPool()`	Get underlying pg Pool

Seeding Data

SQL File Seeding

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

const { db, teardown } = await getConnections({}, [
  seed.sqlfile(['./fixtures/schema.sql', './fixtures/data.sql'])
]);

Function Seeding

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

const { db, teardown } = await getConnections({}, [
  seed.fn(async (client) => {
    await client.query(`
      CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL
      )
    `);
    await client.query(`
      INSERT INTO users (name) VALUES ('Alice'), ('Bob')
    `);
  })
]);

PGPM Module Seeding

Deploy a PGPM module into the test database:

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

const { db, teardown } = await getConnections({}, [
  seed.pgpm(process.cwd())  // Deploy module from current directory
]);

CSV Seeding

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

const { db, teardown } = await getConnections({}, [
  seed.sqlfile(['./fixtures/schema.sql']),
  seed.csv('users', './fixtures/users.csv')
]);

Test Patterns

Transaction Isolation

Each test runs in a savepoint that gets rolled back:

beforeEach(() => db.beforeEach());  // Creates savepoint
afterEach(() => db.afterEach());    // Rolls back to savepoint

test('insert is isolated', async () => {
  await db.query("INSERT INTO users (name) VALUES ('Test')");
  // This insert is rolled back after the test
});

test('previous insert not visible', async () => {
  const result = await db.query("SELECT * FROM users WHERE name = 'Test'");
  expect(result.rows).toHaveLength(0);  // Rolled back!
});

Setting User Context

For RLS (Row Level Security) testing:

test('user can only see own data', async () => {
  await db.setContext('user_id', 'user-123');
  
  const result = await db.query('SELECT * FROM user_data');
  // Only returns rows where user_id = 'user-123'
});

Multiple Connections

const { db: adminDb, teardown: teardownAdmin } = await getConnections({
  user: 'postgres'
});

const { db: appDb, teardown: teardownApp } = await getConnections({
  user: 'app_user'
});

Running Tests

Prerequisites

Start PostgreSQL:

pgpm docker start

Load environment:

eval "$(pgpm env)"

Run tests:

pnpm test

One-liner

pgpm env pnpm test

Watch Mode

pgpm env pnpm test --watch

Common Workflows

Testing PGPM Module

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

describe('my-module', () => {
  let db: any, teardown: () => Promise<void>;

  beforeAll(async () => {
    ({ db, teardown } = await getConnections({}, [
      seed.pgpm(__dirname + '/..')  // Deploy parent module
    ]));
  });

  afterAll(() => teardown());
  beforeEach(() => db.beforeEach());
  afterEach(() => db.afterEach());

  test('function works correctly', async () => {
    const result = await db.query('SELECT my_function($1)', ['input']);
    expect(result.rows[0].my_function).toBe('expected');
  });
});

Testing with Fixtures

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

const fixtures = path.join(__dirname, '__fixtures__');

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

Troubleshooting

Issue	Solution
"Connection refused"	Run `pgpm docker start` first
"Database does not exist"	Check PGDATABASE env var or use `pgpm env`
Tests hang	Ensure `teardown()` is called in afterAll
Data leaking between tests	Add `beforeEach/afterEach` savepoint calls
Permission denied	Check database user has CREATE DATABASE permission
Slow tests	Use savepoints instead of recreating database per test

File Structure

Recommended test file organization:

my-module/
  __tests__/
    __fixtures__/
      schema.sql
      seed-data.sql
    my-feature.test.ts
  deploy/
  revert/
  verify/
  pgpm.plan

References

For related skills:

Docker container management: See pgpm-docker skill
Environment variables: See pgpm-env skill
GraphQL codegen: See constructive-graphql-codegen skill

pgpm-testing