pgpm (PostgreSQL Package Manager)

pgpm provides deterministic, plan-driven database migrations with dependency management and modular packaging. It brings npm-style modularity to PostgreSQL database development — every change is deployed exactly once and reverted exactly once.

When to Apply

Use this skill when:

• Creating projects: Setting up workspaces, initializing modules
• Writing changes: Adding tables, functions, triggers, indexes, RLS policies
• Managing dependencies: Within-module and cross-module references, .control files
• Deploying: Running deploy/verify/revert, tagging releases, checking status
• Testing: Writing PostgreSQL integration tests with pgsql-test
• Configuring: Docker setup, environment variables, connection config
• Managing extensions: Adding PostgreSQL extensions or pgpm modules
• Publishing: Bundling and publishing @pgpm/* modules to npm
• Troubleshooting: Connection issues, deployment failures, testing problems

Quick Start

# 1. Install pgpm
npm install -g pgpm

# 2. Start a local PostgreSQL container
pgpm docker start
eval "$(pgpm env)"

# 3. Create a workspace
pgpm init workspace
# Enter workspace name when prompted
cd my-database-project
pnpm install

# 4. Create a module
pgpm init
# Enter module name (e.g., "pets") and select extensions

# 5. Add a change
cd packages/pets
pgpm add schemas/pets
pgpm add schemas/pets/tables/pets --requires schemas/pets

# 6. Write your SQL (see "Three-File Pattern" below)

# 7. Deploy
pgpm deploy --createdb --database mydb

Core Concepts

Three-File Pattern

Every database change consists of three files:

| File | Purpose | Header | |------|---------|--------| | deploy/<change>.sql | Creates the object | -- Deploy <change> to pg | | revert/<change>.sql | Removes the object | -- Revert <change> from pg | | verify/<change>.sql | Confirms deployment | -- Verify <change> on pg |

pgpm.plan

The plan file controls deployment order. Each line:

change_name [dep1 dep2] 2026-01-25T00:00:00Z author <email@example.org>

Dependencies [...] must come immediately after the change name, before the timestamp.

.control File

Each module has a .control file declaring its name and PostgreSQL extension dependencies:

comment = 'My database module'
default_version = '0.0.1'
requires = 'uuid-ossp,plpgsql'

The requires field uses control file names (e.g., pgpm-base32), NOT npm names (e.g., @pgpm/base32). See references/module-naming.md for details.

Workspace vs Module

• Workspace = pnpm monorepo containing one or more modules (pgpm init workspace)
• Module = individual database package with its own .control, pgpm.plan, and deploy/revert/verify directories (pgpm init)

Critical Rules

1. NEVER Use CREATE OR REPLACE

pgpm is deterministic. Each change deploys exactly once. To modify an existing object, create a new change that drops and recreates it.

-- CORRECT
CREATE FUNCTION app.my_function() ...

-- WRONG
CREATE OR REPLACE FUNCTION app.my_function() ...

2. NO Transaction Wrapping

Do NOT add BEGIN/COMMIT to SQL files. pgpm handles transactions automatically.

-- CORRECT — just the raw SQL
CREATE TABLE app.users ( ... );

-- WRONG
BEGIN;
CREATE TABLE app.users ( ... );
COMMIT;

3. NEVER Run CREATE EXTENSION Directly

pgpm handles extension creation during deploy. Declare extensions in your .control file's requires field instead.

Key Commands Quick Reference

| Command | Purpose | |---------|---------| | pgpm init workspace | Create a new pnpm monorepo workspace | | pgpm init | Create a new module in a workspace | | pgpm add <path> --requires <dep> | Add a new change (creates deploy/revert/verify files) | | pgpm deploy | Deploy changes to database | | pgpm deploy --createdb --database <name> | Create database and deploy | | pgpm verify | Run verification scripts | | pgpm revert --to <change> | Revert changes back to a specific point | | pgpm tag <version> | Tag current state for targeted deploys | | pgpm install <module> | Install a pgpm module dependency | | pgpm extension | Interactive dependency selector | | pgpm test-packages | Test all packages | | pgpm test-packages --full-cycle | Test deploy → verify → revert → redeploy | | pgpm docker start | Start PostgreSQL container | | pgpm docker stop | Stop PostgreSQL container | | pgpm env | Print environment variable exports | | pgpm migrate status | Show deployed vs pending changes | | pgpm plan | Generate plan from SQL -- requires: comments | | pgpm package | Bundle module for publishing |

Essential Development Workflow

# Start PostgreSQL and load environment
pgpm docker start
eval "$(pgpm env)"

# Bootstrap admin users (first time)
pgpm admin-users bootstrap --yes
pgpm admin-users add --test --yes

# Add a new change
pgpm add schemas/app/tables/orders --requires schemas/app

# Edit deploy/revert/verify SQL files

# Deploy and verify
pgpm deploy --createdb --database mydb
pgpm verify

# Run tests
pnpm test

# Tag a release
pgpm tag v1.0.0

Common Workflows

Adding a Table

# Add the change
pgpm add schemas/app/tables/users --requires schemas/app

-- deploy/schemas/app/tables/users.sql
-- Deploy schemas/app/tables/users to pg
-- requires: schemas/app

CREATE TABLE app.users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  email text NOT NULL UNIQUE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- revert/schemas/app/tables/users.sql
-- Revert schemas/app/tables/users from pg

DROP TABLE IF EXISTS app.users;

-- verify/schemas/app/tables/users.sql
-- Verify schemas/app/tables/users on pg

DO $$
BEGIN
  ASSERT (SELECT EXISTS (
    SELECT FROM information_schema.tables
    WHERE table_schema = 'app' AND table_name = 'users'
  )), 'Table app.users does not exist';
END $$;

Writing Tests (with pgsql-test)

import { getConnections } from 'pgsql-test';
import * as seed from 'pgsql-test/seed';

let db, teardown;

beforeAll(async () => {
  ({ db, teardown } = await getConnections({}, [
    seed.pgpm({ database: 'mydb' })
  ]));
});
afterAll(() => teardown());
beforeEach(() => db.beforeEach());
afterEach(() => db.afterEach());

it('creates a user', async () => {
  const result = await db.query(`
    INSERT INTO app.users (email, name)
    VALUES ('test@example.com', 'Test User')
    RETURNING *
  `);
  expect(result.rows[0].email).toBe('test@example.com');
});

CI/CD Full-Cycle Validation

pgpm test-packages --full-cycle

This proves: deploy → verify → revert → redeploy works for every module.

Fixing a Broken Deploy

# Check status
pgpm migrate status

# Revert the bad change
pgpm revert --to <last-good-change>

# Fix the SQL, then redeploy
pgpm deploy

Troubleshooting Quick Reference

| Issue | Quick Fix | |-------|-----------| | Can't connect to database | pgpm docker start && eval "$(pgpm env)" | | PGHOST not set | eval "$(pgpm env)" — must use eval, not run in subshell | | Transaction aborted in tests | Use db.beforeEach() / db.afterEach() savepoint pattern | | Tests interfere with each other | Ensure every test file has beforeEach/afterEach hooks | | Module not found during deploy | Verify .control file exists and workspace structure is correct | | Dependency not found | Check .control requires uses control names, not npm names | | Port 5432 already in use | lsof -i :5432 then stop conflicting process | | Invalid line format in pgpm.plan | Dependencies [...] must come right after change name, before timestamp | | CREATE OR REPLACE error | Remove OR REPLACE — pgpm is deterministic | | Container won't start | pgpm docker start --recreate for a fresh container |

See references/troubleshooting.md for detailed solutions.

Reference Guide

Consult these reference files for detailed documentation on specific topics:

| Reference | Topic | Consult When | |-----------|-------|--------------| | references/cli.md | Complete CLI command reference | Looking up command flags, options, or less common commands | | references/workspace.md | Creating and managing workspaces | Setting up a new project, understanding workspace structure | | references/changes.md | Authoring database changes | Writing deploy/revert/verify scripts, using pgpm add | | references/sql-conventions.md | SQL file format and conventions | Writing SQL files, naming conventions, header format | | references/dependencies.md | Managing module dependencies | Within-module or cross-module dependency references | | references/deploy-lifecycle.md | Deploy/verify/revert lifecycle | Understanding deployment process, tagging, status checking | | references/docker.md | Docker container management | Starting/stopping PostgreSQL, custom container options | | references/env.md | Environment variable management | Loading env vars, profiles, Supabase local development | | references/environment-configuration.md | @pgpmjs/env library API | Programmatic configuration, config hierarchy, utility functions | | references/extensions.md | PostgreSQL extensions & pgpm modules | Adding extensions, installing @pgpm/* modules, .control requires | | references/module-naming.md | npm names vs control file names | Confused about which identifier to use where | | references/plan-format.md | pgpm.plan file format | Fixing Invalid line format errors, editing plan files manually | | references/publishing.md | Publishing modules to npm | Bundling, versioning with lerna, publishing @pgpm/* packages | | references/testing.md | PostgreSQL integration tests | Setting up pgsql-test, seed adapters, test patterns | | references/troubleshooting.md | Common issues and solutions | Debugging connection, deployment, testing, or Docker problems |

Cross-References

Related skills (separate from this skill):

• github-workflows-pgpm — CI/CD GitHub Actions workflows for pgpm projects
• constructive-boilerplate-pgpm-init — Detailed pgpm init templates and boilerplate options
• pgsql-test-* skills — Specialized PostgreSQL testing patterns (RLS, seeding, snapshots, JWT context)