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)