Authoring Database Changes with PGPM

Create safe, reversible database changes using pgpm’s three-file pattern. Every change has deploy, revert, and verify scripts.

When to Apply

Use this skill when:

• Adding tables, functions, triggers, or indexes
• Creating database migrations
• Modifying existing schema
• Organizing database changes in a pgpm module

The Three-File Pattern

Every database change consists of three files:

Adding a Change

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

This creates:

deploy/schemas/pets/tables/pets.sql
revert/schemas/pets/tables/pets.sql
verify/schemas/pets/tables/pets.sql

And updates pgpm.plan:

schemas/pets/tables/pets [schemas/pets] 2025-11-14T00:00:00Z Author <author@example.com>

Writing Deploy Scripts

Deploy scripts create database objects. Use CREATE, not CREATE OR REPLACE (pgpm is deterministic).

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

-- Deploy: schemas/pets/tables/pets
-- requires: schemas/pets

CREATE TABLE pets.pets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  breed TEXT,
  owner_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Important: Never use CREATE OR REPLACE unless absolutely necessary. pgpm tracks what’s deployed and ensures idempotency through its migration system.

Writing Revert Scripts

Revert scripts undo the deploy. Must leave database in pre-deploy state.

revert/schemas/pets/tables/pets.sql:

-- Revert: schemas/pets/tables/pets

DROP TABLE IF EXISTS pets.pets;

Writing Verify Scripts

Verify scripts confirm deployment succeeded. Use DO blocks that raise exceptions on failure.

verify/schemas/pets/tables/pets.sql:

-- Verify: schemas/pets/tables/pets

DO $$
BEGIN
  PERFORM 1 FROM pg_tables
  WHERE schemaname = 'pets' AND tablename = 'pets';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Table pets.pets does not exist';
  END IF;
END $$;

Nested Paths

Organize changes hierarchically using nested paths:

schemas/
└── app/
    ├── schema.sql
    ├── tables/
    │   └── users/
    │       ├── table.sql
    │       └── indexes/
    │           └── email.sql
    ├── functions/
    │   └── create_user.sql
    └── triggers/
        └── updated_at.sql

Add changes with full paths:

pgpm add schemas/app/schema
pgpm add schemas/app/tables/users/table --requires schemas/app/schema
pgpm add schemas/app/tables/users/indexes/email --requires schemas/app/tables/users/table
pgpm add schemas/app/functions/create_user --requires schemas/app/tables/users/table

Key insight: Deployment order follows the plan file, not directory structure. Nested paths are for organization only.

Plan File Format

The pgpm.plan file tracks all changes:

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

schemas/pets 2025-11-14T00:00:00Z Author <author@example.com>
schemas/pets/tables/pets [schemas/pets] 2025-11-14T00:00:00Z Author <author@example.com>
schemas/pets/tables/pets/indexes/name [schemas/pets/tables/pets] 2025-11-14T00:00:00Z Author <author@example.com>

Format: change_name [dependencies] timestamp author <email> # optional note

Two Workflows

Incremental (Development)

Add changes one at a time:

pgpm add schemas/pets --requires uuid-ossp
pgpm add schemas/pets/tables/pets --requires schemas/pets

Plan file updates automatically with each pgpm add.

Pre-Production (Batch)

Write all SQL files first, then generate plan:

# Write deploy/revert/verify files manually
# Then generate plan from requires comments:
pgpm plan

pgpm plan reads -- requires: comments from deploy files and generates the plan.

Common Change Types

Schema

pgpm add schemas/app

-- deploy/schemas/app.sql
CREATE SCHEMA app;

-- revert/schemas/app.sql
DROP SCHEMA IF EXISTS app CASCADE;

-- verify/schemas/app.sql
DO $$ BEGIN
  PERFORM 1 FROM information_schema.schemata WHERE schema_name = 'app';
  IF NOT FOUND THEN RAISE EXCEPTION 'Schema app does not exist'; END IF;
END $$;

Table

pgpm add schemas/app/tables/users --requires schemas/app

-- deploy/schemas/app/tables/users.sql
CREATE TABLE app.users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- revert/schemas/app/tables/users.sql
DROP TABLE IF EXISTS app.users;

-- verify/schemas/app/tables/users.sql
DO $$ BEGIN
  PERFORM 1 FROM pg_tables WHERE schemaname = 'app' AND tablename = 'users';
  IF NOT FOUND THEN RAISE EXCEPTION 'Table app.users does not exist'; END IF;
END $$;

Function

pgpm add schemas/app/functions/get_user --requires schemas/app/tables/users

-- deploy/schemas/app/functions/get_user.sql
CREATE FUNCTION app.get_user(user_id UUID)
RETURNS app.users AS $$
  SELECT * FROM app.users WHERE id = user_id;
$$ LANGUAGE sql STABLE;

-- revert/schemas/app/functions/get_user.sql
DROP FUNCTION IF EXISTS app.get_user(UUID);

-- verify/schemas/app/functions/get_user.sql
DO $$ BEGIN
  PERFORM 1 FROM pg_proc WHERE proname = 'get_user';
  IF NOT FOUND THEN RAISE EXCEPTION 'Function get_user does not exist'; END IF;
END $$;

Index

pgpm add schemas/app/tables/users/indexes/email --requires schemas/app/tables/users

-- deploy/schemas/app/tables/users/indexes/email.sql
CREATE INDEX idx_users_email ON app.users(email);

-- revert/schemas/app/tables/users/indexes/email.sql
DROP INDEX IF EXISTS app.idx_users_email;

-- verify/schemas/app/tables/users/indexes/email.sql
DO $$ BEGIN
  PERFORM 1 FROM pg_indexes WHERE indexname = 'idx_users_email';
  IF NOT FOUND THEN RAISE EXCEPTION 'Index idx_users_email does not exist'; END IF;
END $$;

Deploy and Verify

# Deploy to database
pgpm deploy --database myapp_dev --createdb --yes

# Verify deployment
pgpm verify --database myapp_dev

References

• Related skill: pgpm-workspace for workspace setup
• Related skill: pgpm-dependencies for cross-module dependencies
• Related skill: pgpm-testing for testing database changes