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 Postgres 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.

End-to-End Postgres Testing with Supabase

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.

Troubleshooting Supabase Test

Learn to test PostgreSQL databases with Drizzle ORM, including RLS policies and context management.

End-to-End Postgres Testing with Drizzle ORM

Set up Drizzle ORM with drizzle-orm-test for PostgreSQL testing. Create type-safe database queries with automatic test isolation.

How to Setup Drizzle ORM for End-to-End Testing

Test RLS policies with Drizzle ORM using automatic context management. Verify security policies with type-safe queries.

Testing Row-Level Security with Drizzle ORM

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

Troubleshooting

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

Common Issues and Solutions


You've created a database module, but now you need to evolve your schema. How do you add a new table? How do you track what's been deployed? How do you ensure changes can be rolled back safely?

This is where pgpm's migration system shines. In this lesson, you'll learn the fundamental workflow for adding database changes using the `pgpm add` command. You'll understand the three-file pattern (deploy, revert, verify) that makes migrations safe and reversible.

## The Three-File Pattern

pgpm uses a three-file pattern for every database change:

1. **deploy** - The forward migration (creates tables, adds columns, etc.)
2. **revert** - The rollback script (undoes the deploy)
3. **verify** - The verification script (confirms the deploy worked)

This pattern ensures every change is reversible and verifiable. You never deploy a change without knowing how to undo it.

## Adding Your First Change

Navigate to your database module:

```bash
cd packages/pets
```

Add a new change:

```bash
pgpm add
```

pgpm prompts for the change name:

```sh
? Change name: create_pets_table
```

That's it. pgpm creates three SQL files and updates your plan file automatically.

## What Just Happened?

pgpm created:

1. `deploy/create_pets_table.sql` - Your forward migration
2. `revert/create_pets_table.sql` - Your rollback script
3. `verify/create_pets_table.sql` - Your verification script

And updated `pgpm.plan`:

```sh
%syntax-version=1.0.0
%project=pets
%uri=pets

create_pets_table 2025-11-14T00:00:00Z Author <author@example.com> # Adds create_pets_table
```

The plan file tracks every change in order. When you deploy, pgpm reads this file to know what to apply.

## Writing the Deploy Script

Edit `deploy/create_pets_table.sql`:

```sql
-- Deploy create_pets_table

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

Notice the comment at the top. pgpm uses this to track which change this script belongs to. The format is `-- Deploy change_name`.

## Writing the Revert Script

Edit `revert/create_pets_table.sql`:

```sql
-- Revert create_pets_table

DROP TABLE pets;
```

The revert script undoes everything the deploy script does. If deploy creates a table, revert drops it. If deploy adds a column, revert removes it.

## Writing the Verify Script

Edit `verify/create_pets_table.sql`:

```sql
-- Verify create_pets_table

SELECT table_name
FROM information_schema.tables
WHERE table_name = 'pets';
```

The verify script checks that the deployment succeeded. It should query the database to confirm the expected state. If the query returns no rows, verification fails.

## Deploying Your Change

Deploy to your database:

```bash
pgpm deploy --database petapp_dev
```

pgpm shows what it's about to deploy:

```sh
Deploy pets:create_pets_table to petapp_dev? (y/N)
```

Type `y` and press Enter. pgpm:

1. Runs the deploy script in a transaction
2. Records the change in `pgpm_migrate.changes`
3. Runs the verify script to confirm success

## Verifying the Deployment

Check that everything worked:

```bash
pgpm verify
```

Or query the table directly:

```bash
psql -d petapp_dev -c "\d pets"
```

You should see your table structure.

## Rolling Back (If Needed)

If something goes wrong, roll back:

```bash
pgpm revert
```

pgpm runs the revert script, removing the table and updating the tracking schema.

## Adding More Changes

Add another change:

```bash
pgpm add create_adoptions_table
```

Edit the deploy script:

```sql
-- Deploy create_adoptions_table

CREATE TABLE adoptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pet_id UUID NOT NULL REFERENCES pets(id),
  adopter_name TEXT NOT NULL,
  adopter_email TEXT NOT NULL,
  adoption_date DATE DEFAULT CURRENT_DATE,
  created_at TIMESTAMPTZ DEFAULT now()
);
```

Deploy again:

```bash
pgpm deploy --database petapp_dev
```

pgpm only deploys the new change. It tracks what's already been applied and skips it.

## Key Takeaways

- **`pgpm add` creates three files** - deploy, revert, verify
- **Deploy scripts are forward migrations** - CREATE TABLE, ADD COLUMN, etc.
- **Revert scripts undo deploys** - DROP TABLE, DROP COLUMN, etc.
- **Verify scripts confirm success** - Query to check expected state
- **pgpm tracks what's deployed** - Only applies new changes
- **Everything happens in transactions** - Safe, atomic changes

## What's Next

You've learned the basic workflow for adding changes. But what happens when your schema grows? How do you organize dozens of changes? How do you group related changes together?

In the next lesson, we'll explore nested paths and organizational strategies for managing complex schemas.
