Adding Your First Database Change

You've created a database module, but now you need to evolve your schema. How do you add a new table? How do you track what's been deployed? How do you ensure changes can be rolled back safely?

This is where pgpm's migration system shines. In this lesson, you'll learn the fundamental workflow for adding database changes using the pgpm add command. You'll understand the three-file pattern (deploy, revert, verify) that makes migrations safe and reversible.

The Three-File Pattern

pgpm uses a three-file pattern for every database change:

1. deploy - The forward migration (creates tables, adds columns, etc.)
2. revert - The rollback script (undoes the deploy)
3. verify - The verification script (confirms the deploy worked)

This pattern ensures every change is reversible and verifiable. You never deploy a change without knowing how to undo it.

Adding Your First Change

Navigate to your database module:

cd packages/pets

Add a new change:

pgpm add

pgpm prompts for the change name:

? Change name: create_pets_table

That's it. pgpm creates three SQL files and updates your plan file automatically.

What Just Happened?

pgpm created:

1. deploy/create_pets_table.sql - Your forward migration
2. revert/create_pets_table.sql - Your rollback script
3. verify/create_pets_table.sql - Your verification script

And updated pgpm.plan:

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

create_pets_table 2025-11-14T00:00:00Z Author <author@example.com> # Adds create_pets_table

The plan file tracks every change in order. When you deploy, pgpm reads this file to know what to apply.

Writing the Deploy Script

Edit deploy/create_pets_table.sql:

-- Deploy create_pets_table

CREATE TABLE pets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  species TEXT NOT NULL,
  age INTEGER,
  adopted BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

Notice the comment at the top. pgpm uses this to track which change this script belongs to. The format is -- Deploy change_name.

Writing the Revert Script

Edit revert/create_pets_table.sql:

-- Revert create_pets_table

DROP TABLE pets;

The revert script undoes everything the deploy script does. If deploy creates a table, revert drops it. If deploy adds a column, revert removes it.

Writing the Verify Script

Edit verify/create_pets_table.sql:

-- Verify create_pets_table

SELECT table_name
FROM information_schema.tables
WHERE table_name = 'pets';

The verify script checks that the deployment succeeded. It should query the database to confirm the expected state. If the query returns no rows, verification fails.

Deploying Your Change

Deploy to your database:

pgpm deploy --database petapp_dev

pgpm shows what it's about to deploy:

Deploy pets:create_pets_table to petapp_dev? (y/N)

Type y and press Enter. pgpm:

1. Runs the deploy script in a transaction
2. Records the change in pgpm_migrate.changes
3. Runs the verify script to confirm success

Verifying the Deployment

Check that everything worked:

pgpm verify

Or query the table directly:

psql -d petapp_dev -c "\d pets"

You should see your table structure.

Rolling Back (If Needed)

If something goes wrong, roll back:

pgpm revert

pgpm runs the revert script, removing the table and updating the tracking schema.

Adding More Changes

Add another change:

pgpm add create_adoptions_table

Edit the deploy script:

-- Deploy create_adoptions_table

CREATE TABLE adoptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pet_id UUID NOT NULL REFERENCES pets(id),
  adopter_name TEXT NOT NULL,
  adopter_email TEXT NOT NULL,
  adoption_date DATE DEFAULT CURRENT_DATE,
  created_at TIMESTAMPTZ DEFAULT now()
);

Deploy again:

pgpm deploy --database petapp_dev

pgpm only deploys the new change. It tracks what's already been applied and skips it.

Key Takeaways

• pgpm add creates three files - deploy, revert, verify
• Deploy scripts are forward migrations - CREATE TABLE, ADD COLUMN, etc.
• Revert scripts undo deploys - DROP TABLE, DROP COLUMN, etc.
• Verify scripts confirm success - Query to check expected state
• pgpm tracks what's deployed - Only applies new changes
• Everything happens in transactions - Safe, atomic changes

What's Next

You've learned the basic workflow for adding changes. But what happens when your schema grows? How do you organize dozens of changes? How do you group related changes together?

In the next lesson, we'll explore nested paths and organizational strategies for managing complex schemas.