Spinning Up Temporary Testing Databases for Postgres

Testing database logic requires real Postgres with actual constraints, triggers, and RLS policies—not mocks or simulators. But spinning up test databases shouldn't slow you down.

In this lesson, you'll learn how to create ephemeral (temporary) Postgres databases for testing with pgsql-test. These databases are fast, isolated, and automatically cleaned up after your tests run.

Prerequisites

Ensure Postgres is running and database users are initialized as shown in prerequisites.

Why Ephemeral Databases?

Ephemeral databases give each test suite a fresh, isolated database that's automatically created and destroyed. No shared state, no manual cleanup, no conflicts. Fast and automatic.

Setting Up Your Workspace

Create a pnpm workspace optimized for Modular Postgres with pgpm:

pgpm init workspace
cd my-database-project
pnpm install

Create a module:

pgpm init

Enter module details (name: pets, select uuid-ossp extension). Then navigate to the module:

cd packages/pets

Your First Ephemeral Database Test

Inside of __tests__/basic.test.ts:

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

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

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

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

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

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

it('database is ready', async () => {
  const result = await pg.query('SELECT 1 as num');
  expect(result.rows[0].num).toBe(1);
});

Run the test:

pnpm test

Note: If you experience connection issues, see Tests fail to connect to database.

You should see:

 PASS  __tests__/basic.test.ts
  ✓ database is ready (15ms)

Test Suites: 1 passed, 1 total
Tests:       1 passed, 1 total

What just happened? When you called getConnections(), pgsql-test:

1. Generated a unique database name (UUID-based)
2. Created the database in Postgres
3. Connected a client to it
4. Returned a db client and teardown function

When teardown() runs, the database is dropped and connections are closed. Everything is automatic.

Testing with Real Schema

Now let's add a schema and table to test. Add a schema change:

pgpm add schemas/pets

Edit deploy/schemas/pets.sql:

-- Deploy schemas/pets

CREATE SCHEMA pets;

Add a table change:

pgpm add schemas/pets/tables/pets --requires schemas/pets

Edit deploy/schemas/pets/tables/pets.sql:

-- Deploy schemas/pets/tables/pets
-- requires: schemas/pets

CREATE TABLE pets.pets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  species TEXT NOT NULL,
  age INTEGER,
  adopted BOOLEAN DEFAULT false
);

Now update __tests__/basic.test.ts to test your table:

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

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

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

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

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

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

it('can insert and query pets', async () => {
  await pg.query(`
    INSERT INTO pets.pets (name, species, age)
    VALUES ('Buddy', 'dog', 3)
  `);

  const result = await pg.query('SELECT * FROM pets.pets WHERE name = $1', ['Buddy']);
  expect(result.rows[0].species).toBe('dog');
  expect(result.rows[0].age).toBe(3);
  expect(result.rows[0].adopted).toBe(false);
});

it('starts with clean state', async () => {
  const result = await pg.query('SELECT * FROM pets.pets');
  expect(result.rows).toHaveLength(0);
});

Run the tests:

pnpm test

pgsql-test automatically deploys your module before running tests. Each test starts with a clean database state thanks to beforeEach() and afterEach().

Understanding Transaction Isolation

The beforeEach() and afterEach() hooks provide transaction-based isolation:

beforeEach(): Starts a transaction and creates a savepoint afterEach(): Rolls back to the savepoint and commits the transaction

This means:

• Each test runs in its own transaction
• Changes are automatically rolled back after each test
• Tests are completely isolated from each other
• No manual cleanup needed

This is much faster than recreating the database for each test. The database schema persists, but data changes are rolled back.

Key Takeaways

• Ephemeral databases provide complete isolation between test runs
• Use pgpm add to create schemas and tables for testing
• getConnections() automatically creates and deploys your module to test databases
• Transaction isolation with beforeEach()/afterEach() keeps tests clean
• Real Postgres means testing actual constraints, triggers, and features
• Fast performance enables tight feedback loops

What's Next

You've learned how to spin up ephemeral Postgres databases for testing. In the next lesson, we'll explore how to test Row-Level Security (RLS) policies by simulating different users and roles.

Testing RLS is where pgsql-test really shines—you'll see how to verify that your security policies actually work as intended.