Get started with modular databases in minutes. Install prerequisites and deploy your first module.

Quickstart: Getting Up and Running

Set up Node.js, PostgreSQL, and pgpm. Everything you need to get started with modular database development.

Prerequisites

Experience the power of modular databases by installing a pre-built module and deploying it to PostgreSQL—no SQL writing required.

Spinning Up a Modular Database in 2 Minutes

Learn to organize PostgreSQL projects with pgpm workspaces and reusable database modules.

Modular PostgreSQL Development with Database Packages

Understand how pgpm workspaces bring npm-style modularity to PostgreSQL development.

pgpm Workspaces: A New Way to Organize PostgreSQL Projects

Set up your first pgpm workspace with proper configuration and structure.

Initializing a Workspace for Modular PostgreSQL Development

Build your first database module with deploy, revert, and verify scripts.

Creating Your First PostgreSQL Database Module

Write and run tests for your database module with watch mode for rapid feedback.

Testing Your Database Module

Master module dependencies, cross-module references, and automatic dependency resolution.

Managing Database Package Dependencies in a Workspace

Master the workflow for adding, organizing, and managing database changes with pgpm.

Authoring Database Changes

Learn the fundamental workflow for adding database changes using the pgpm add command and the three-file pattern for safe, reversible migrations.

Adding Your First Database Change

Structure migrations using nested paths for maintainable schemas.

Change Naming Conventions

Understand the plan file format and how pgpm generates deployment plans from SQL dependencies.

Plan Files and Deployment Plans

Master end-to-end PostgreSQL testing with ephemeral databases, RLS testing, and CI/CD automation.

End-to-End PostgreSQL Testing with TypeScript

Create ephemeral PostgreSQL databases for testing with pgsql-test. Fast, isolated, and automatically cleaned up.

Spinning Up Temporary Testing Databases for Postgres

Test RLS policies by simulating different users and roles. Verify that your security policies actually work as intended.

How to Test Row-Level Security (RLS) in PostgreSQL

Test complex multi-user scenarios by switching between users within tests. Master the savepoint pattern to handle exceptions and aborted transactions when testing operations that should fail.

Advanced RLS Testing Scenarios - Simulating Many Users and Roles

Learn how to seed test databases with realistic data using loadJson, loadSql, and loadCsv methods for comprehensive testing scenarios.

Seeding PostgreSQL Test Databases

TypeScript-native testing for Supabase with modern workflows.

Supabase Testing

Introducing Supabase Test Suite: TypeScript-native testing for Supabase with isolated databases, RLS testing, and instant feedback loops.

Supabase Testing for the Modern Developer

Get started with supabase-test: install dependencies, configure local Supabase, and write your first isolated database test with automatic rollbacks.

Setting Up Local Supabase Database Testing

Create your first Supabase test with supabase-test's getConnections(), understand test isolation, and verify your setup with simple database queries.

Writing Your First Supabase Test in TypeScript

Test Supabase RLS policies by simulating authenticated users and anonymous access. Verify your security policies work as intended with supabase-test.

Testing Row-Level Security Policies in Supabase

Learn how to seed Supabase test databases with auth.users, realistic data using loadJson, loadSql, and loadCsv methods for comprehensive testing.

Seeding Supabase Test Databases

Set up GitHub Actions CI/CD pipeline to automatically test your Supabase database with every commit. Learn how to configure workflows, start Supabase, and run tests.

Running Supabase Tests in GitHub Actions

Automate Supabase testing in GitHub Actions: provision local Supabase stack, run tests in CI, and optimize for fast feedback loops.

Supabase Troubleshooting

Common issues and solutions for pgpm, PostgreSQL, and testing.

Troubleshooting

Quick fixes for common pgpm, PostgreSQL, and testing issues.

Common Issues and Solutions


Testing database logic requires real PostgreSQL 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) PostgreSQL databases for testing with [pgsql-test](https://www.npmjs.com/package/pgsql-test). These databases are fast, isolated, and automatically cleaned up after your tests run.

## Prerequisites

Ensure PostgreSQL is running and database users are initialized as shown in [prerequisites](/learn/quickstart/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`:

```bash
pgpm init --workspace
cd my-database-project
pnpm install
```

Create a module:

```bash
pgpm init
```

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

```bash
cd packages/pets
```

## Your First Ephemeral Database Test

Inside of `__tests__/basic.test.ts`:

```typescript
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:

```bash
pnpm test
```

> **Note:** If you experience connection issues, see [Tests fail to connect to database](/learn/troubleshooting/common-issues-and-solutions#tests-fail-to-connect-to-database).

You should see:

```bash
 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 PostgreSQL
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:

```bash
pgpm add schemas/pets
```

Edit `deploy/schemas/pets.sql`:

```sql
-- Deploy schemas/pets

CREATE SCHEMA pets;
```

Add a table change:

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

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

```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:

```typescript
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:

```bash
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 PostgreSQL** means testing actual constraints, triggers, and features
- **Fast performance** enables tight feedback loops

## What's Next

You've learned how to spin up ephemeral PostgreSQL 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.
