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:

File Purpose

deploy/<change>.sql

Creates the object

revert/<change>.sql

Removes the object

verify/<change>.sql

Confirms deployment

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