pgsql-test

pgsql-test gives you instant, isolated PostgreSQL databases for each test — with automatic transaction rollbacks, context switching, and clean seeding. Forget flaky tests and brittle environments. Write real SQL. Get real coverage. Stay fast.

Install

npm install pgsql-test

Features

• ⚡ Instant test DBs — each one seeded, isolated, and UUID-named
• 🔄 Per-test rollback — every test runs in its own transaction or savepoint
• 🛡️ RLS-friendly — test with role-based auth via .setContext()
• 🌱 Flexible seeding — run .sql files, programmatic seeds, or even load fixtures
• 🧪 Compatible with any async runner — works with Jest, Mocha, etc.
• 🧹 Auto teardown — no residue, no reboots, just clean exits

LaunchQL migrations

Part of the LaunchQL ecosystem, pgsql-test is built to pair seamlessly with our TypeScript-based Sqitch engine rewrite:

• 🚀 Lightning-fast migrations — powered by LaunchQL’s native deployer (10x faster than legacy Sqitch)
• 🔧 Composable test scaffolds — integrate with full LaunchQL stacks or use standalone

Table of Contents

1. Install
2. Features
3. Quick Start
4. getConnections() Overview
5. PgTestClient API Overview
6. Usage Examples
   • Basic Setup
   • Role-Based Context
   • SQL File Seeding
   • Programmatic Seeding
   • CSV Seeding
   • JSON Seeding
   • Sqitch Seeding
   • LaunchQL Seeding
7. Environment Overrides
8. Disclaimer

✨ Quick Start

import { getConnections } from 'pgsql-test';

let db, teardown;

beforeAll(async () => {
  ({ db, teardown } = await getConnections());
  await db.query(`SELECT 1`); // ✅ Ready to run queries
});

afterAll(() => teardown());

getConnections() Overview

The getConnections() helper sets up a fresh PostgreSQL test database and returns a structured object with:

• pg: a PgTestClient connected as the root or superuser — useful for administrative setup or introspection
• db: a PgTestClient connected as the app-level user — used for running tests with RLS and granted permissions
• admin: a DbAdmin utility for managing database state, extensions, roles, and templates
• teardown(): a function that shuts down the test environment and database pool
• manager: a shared connection pool manager (PgTestConnector) behind both clients

Together, these allow fast, isolated, role-aware test environments with per-test rollback and full control over setup and teardown.

The PgTestClient returned by getConnections() is a fully-featured wrapper around pg.Pool. It provides:

• Automatic transaction and savepoint management for test isolation
• Easy switching of role-based contexts for RLS testing
• A clean, high-level API for integration testing PostgreSQL systems

PgTestClient API Overview

The PgTestClient returned by getConnections() wraps a pg.Client and provides convenient helpers for query execution, test isolation, and context switching.

Common Methods

• query(sql, values?) – Run a raw SQL query and get the QueryResult
• beforeEach() – Begins a transaction and sets a savepoint (called at the start of each test)
• afterEach() – Rolls back to the savepoint and commits the outer transaction (cleans up test state)
• setContext({ key: value }) – Sets PostgreSQL config variables (like role) to simulate RLS contexts
• any, one, oneOrNone, many, manyOrNone, none, result – Typed query helpers for specific result expectations

These methods make it easier to build expressive and isolated integration tests with strong typing and error handling.

The PgTestClient returned by getConnections() is a fully-featured wrapper around pg.Pool. It provides:

• Automatic transaction and savepoint management for test isolation
• Easy switching of role-based contexts for RLS testing
• A clean, high-level API for integration testing PostgreSQL systems

Usage Examples

⚡ Basic Setup

import { getConnections } from 'pgsql-test';

let db; // A fully wrapped PgTestClient using pg.Pool with savepoint-based rollback per test
let teardown;

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

  await db.query(`
    CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
    CREATE TABLE posts (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id), content TEXT);

    INSERT INTO users (name) VALUES ('Alice'), ('Bob');
    INSERT INTO posts (user_id, content) VALUES (1, 'Hello world!'), (2, 'Graphile is cool!');
  `);
});

afterAll(() => teardown());

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

test('user count starts at 2', async () => {
  const res = await db.query('SELECT COUNT(*) FROM users');
  expect(res.rows[0].count).toBe('2');
});

🔐 Role-Based Context

describe('authenticated role', () => {
  beforeEach(async () => {
    db.setContext({ role: 'authenticated' });
    await db.beforeEach();
  });

  afterEach(() => db.afterEach());

  it('runs as authenticated', async () => {
    const res = await db.query(`SELECT current_setting('role', true) AS role`);
    expect(res.rows[0].role).toBe('authenticated');
  });
});

🔌 SQL File Seeding

Use .sql files to set up your database state before tests:

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

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

let db;
let teardown;

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

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

🧠 Programmatic Seeding

Use JavaScript functions to insert seed data:

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

let db;
let teardown;

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, seed.fn(async ({ pg }) => {
    await pg.query(`
      INSERT INTO users (name) VALUES ('Seeded User');
    `);
  })));
});

🗃️ CSV Seeding

You can load tables from CSV files using seed.csv({ ... }). CSV headers must match the table column names exactly. This is useful for loading stable fixture data for integration tests or CI environments.

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

const csv = (file: string) => path.resolve(__dirname, '../csv', file);

let db;
let teardown;

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, [
    // Create schema
    seed.fn(async ({ pg }) => {
      await pg.query(`
        CREATE TABLE users (
          id SERIAL PRIMARY KEY,
          name TEXT NOT NULL
        );

        CREATE TABLE posts (
          id SERIAL PRIMARY KEY,
          user_id INT REFERENCES users(id),
          content TEXT NOT NULL
        );
      `);
    }),
    // Load from CSV
    seed.csv({
      users: csv('users.csv'),
      posts: csv('posts.csv')
    }),
    // Adjust SERIAL sequences to avoid conflicts
    seed.fn(async ({ pg }) => {
      await pg.query(`SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT MAX(id) FROM users));`);
      await pg.query(`SELECT setval(pg_get_serial_sequence('posts', 'id'), (SELECT MAX(id) FROM posts));`);
    })
  ]));
});

afterAll(() => teardown());

it('has loaded rows', async () => {
  const res = await db.query('SELECT COUNT(*) FROM users');
  expect(+res.rows[0].count).toBeGreaterThan(0);
});

🗃️ JSON Seeding

You can seed tables using in-memory JSON objects. This is useful when you want fast, inline fixtures without managing external files.

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

let db;
let teardown;

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, [
    // Create schema
    seed.fn(async ({ pg }) => {
      await pg.query(`
        CREATE SCHEMA custom;
        CREATE TABLE custom.users (
          id SERIAL PRIMARY KEY,
          name TEXT NOT NULL
        );

        CREATE TABLE custom.posts (
          id SERIAL PRIMARY KEY,
          user_id INT REFERENCES custom.users(id),
          content TEXT NOT NULL
        );
      `);
    }),
    // Seed with in-memory JSON
    seed.json({
      'custom.users': [
        { id: 1, name: 'Alice' },
        { id: 2, name: 'Bob' }
      ],
      'custom.posts': [
        { id: 1, user_id: 1, content: 'Hello world!' },
        { id: 2, user_id: 2, content: 'Graphile is cool!' }
      ]
    }),
    // Fix SERIAL sequences
    seed.fn(async ({ pg }) => {
      await pg.query(`SELECT setval(pg_get_serial_sequence('custom.users', 'id'), (SELECT MAX(id) FROM custom.users));`);
      await pg.query(`SELECT setval(pg_get_serial_sequence('custom.posts', 'id'), (SELECT MAX(id) FROM custom.posts));`);
    })
  ]));
});

afterAll(() => teardown());

it('has loaded rows', async () => {
  const res = await db.query('SELECT COUNT(*) FROM custom.users');
  expect(+res.rows[0].count).toBeGreaterThan(0);
});

🏗️ Sqitch Seeding

Note: While compatible with Sqitch syntax, LaunchQL uses its own high-performance TypeScript-based deploy engine. that we encourage using for sqitch projects

You can seed your test database using a Sqitch project but with significantly improved performance by leveraging LaunchQL's TypeScript deployment engine:

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

const cwd = path.resolve(__dirname, '../path/to/sqitch');

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, [
    seed.sqitch(cwd)
  ]));
});

it('runs a schema query', async () => {
  const res = await db.query('SELECT COUNT(*) FROM myapp.users');
  expect(+res.rows[0].count).toBeGreaterThanOrEqual(0);
});

This works for any Sqitch-compatible module, now accelerated by LaunchQL's deployment tooling.

🚀 LaunchQL Seeding

For LaunchQL modules with precompiled sqitch.plan, use seed.launchql(cwd) to apply a schema quickly with deployFast(): For maximum performance with precompiled LaunchQL modules, use seed.launchql(cwd) to apply a schema at lightning speed with our TypeScript-powered deployFast():

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

const cwd = path.resolve(__dirname, '../path/to/launchql');

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, [
    seed.launchql(cwd) // uses deployFast() - up to 10x faster than traditional Sqitch!
  ]));
});

it('creates user records', async () => {
  await db.query(`INSERT INTO myapp.users (username, email) VALUES ('testuser', 'test@example.com')`);
  const res = await db.query(`SELECT COUNT(*) FROM myapp.users`);
  expect(+res.rows[0].count).toBeGreaterThan(0);
});

This is the fastest way to bring up a ready-to-query schema from a compiled LaunchQL module - perfect for both development and CI environments.

Why LaunchQL's Approach?

LaunchQL provides the best of both worlds:

1. Sqitch Compatibility: Keep your familiar Sqitch syntax and migration approach
2. TypeScript Performance: Our TS-rewritten deployment engine delivers up to 10x faster schema deployments
3. Developer Experience: Tight feedback loops with near-instant schema setup for tests
4. CI Optimization: Dramatically reduced test suite run times with optimized deployment

By maintaining Sqitch compatibility while supercharging performance, LaunchQL enables you to keep your existing migration patterns while enjoying the speed benefits of our TypeScript engine.

Environment Overrides

pgsql-test respects the following env vars for DB connectivity:

• PGHOST
• PGPORT
• PGUSER
• PGPASSWORD

Override them in your test runner or CI config:

env:
  PGHOST: localhost
  PGPORT: 5432
  PGUSER: postgres
  PGPASSWORD: password

Disclaimer

AS DESCRIBED IN THE LICENSES, THE SOFTWARE IS PROVIDED “AS IS”, AT YOUR OWN RISK, AND WITHOUT WARRANTIES OF ANY KIND.

No developer or entity involved in creating this software will be liable for any claims or damages whatsoever associated with your use, inability to use, or your interaction with other users of the code, including any direct, indirect, incidental, special, exemplary, punitive or consequential damages, or loss of profits, cryptocurrencies, tokens, or anything else of value.