pgpm

name: pgpm description: PostgreSQL Package Manager — deterministic, plan-driven database migrations with dependency management. Use when asked to "deploy database", "run migrations", "manage pgpm modules", "add a table", "create a function", "add a migration", "write database changes", "create a workspace", "set up pgpm", "manage dependencies", "revert a migration", "verify deployments", "tag a release", "start postgres", "run database locally", "set up database environment", "load env vars", "add an extension", "install a module", "publish pgpm module", "test database", "write integration tests", "troubleshoot pgpm", or when working with PostgreSQL package management, .control files, pgpm.plan, or SQL migration scripts. compatibility: pgpm CLI, PostgreSQL 14+, Node.js 22+, Docker metadata: author: constructive-io version: "2.0.0"

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:

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

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

Non-Interactive / CI Usage

pgpm uses inquirerer for interactive prompts. When stdin is a TTY but no human is present (agents, PTY-allocated CI, Docker with -t), prompts hang indefinitely.

How noTty detection works

The inquirerer library enters non-interactive mode (noTty) when any of these are true:

• --no-tty flag is passed
• process.env.CI === 'true'
• The caller explicitly sets noTty: true

In noTty mode, inquirerer uses defaults where available and throws with the full list of required arguments if any are missing. This is the correct mode for all automated usage.

Rules for automated environments

1. Pass --yes for confirmation prompts — this provides the yes argument so the confirm prompt is skipped:

   pgpm deploy --yes --database mydb
   pgpm admin-users bootstrap --yes
   pgpm admin-users add --test --yes
   pgpm revert --yes --database mydb
   

2. Pass --no-tty for fully non-interactive mode — this tells inquirerer to never wait for input and use defaults or throw:

   pgpm deploy --no-tty --yes --database mydb --createdb
   

3. In CI (CI=true) — noTty is auto-detected. You still need --yes for confirm questions since they have no implicit default:

   # GitHub Actions — CI=true is set automatically
   pgpm deploy --yes --database $DB_NAME
   

4. Provide ALL required args on the command line — in noTty mode, any missing required argument throws. Pre-supply everything:

   pgpm deploy --database mydb --package my-module --yes --recursive
   

5. pgpm init and pgpm extension are fully interactive — they require multi-step input (names, selections). Do not use them in scripts. Instead, create .control, pgpm.plan, and package.json files directly.

6. pgpm test-packages does NOT prompt — safe to run without any flags.

7. If a command hangs with no output, it's waiting for a prompt. Kill it and re-run with --yes and --no-tty.

Quick reference

Troubleshooting Quick Reference

See references/troubleshooting.md for detailed solutions.

Reference Guide

Consult these reference files for detailed documentation on specific topics:

Project Scaffolding

Database Operations (from constructive-db)

Cross-References

Related skills:

• constructive-testing — PostgreSQL testing patterns (RLS, seeding, snapshots, JWT context)
• constructive-setup — Monorepo setup and local development environment
• constructive-cli — Generated CLI commands and scaffolding