pgsql-test

pgsql-test provides an isolated PostgreSQL testing environment with per-test transaction rollback, ideal for integration tests involving SQL, roles, simulations, and complex migrations. With automatic rollbacks and isolated contexts, it eliminates test interference while delivering tight feedback loops for happier developers. We made database testing simple so you can focus on writing good tests instead of fighting your environment.

Install

npm install pgsql-test

Features

• ⚡ Quick-start setup with getConnections()
• 🧹 Easy teardown and cleanup
• 🔄 Per-test isolation using transactions and savepoints
• 🛡️ Role-based context for RLS testing
• 🌱 Flexible seed support via .sql files and programmatic functions
• 🧪 Auto-generated test databases with UUID suffix
• 📦 Built for tools like sqitch, supporting full schema initialization workflows
• 🧰 Designed for Jest, Mocha, or any async test runner

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
   • Composed 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');
    `);
  })));
});

🧬 Composed Seeding

Combine multiple seeders with seed.compose():

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.compose([
    seed.sqlfile([
      sql('schema.sql'),
      sql('roles.sql')
    ]),
    seed.fn(async ({ pg }) => {
      await pg.query(`INSERT INTO users (name) VALUES ('Composed');`);
    })
  ])));
});

These examples show how flexible pgsql-test is for composing repeatable and transactional test database environments.

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.