pgmagmig

pgmagmig is a PostgreSQL migration tool with an integrated schema differ, powered by PGlite (PostgreSQL compiled to WASM).

Schema extraction, DDL generation, diffing, and diff validation all run in-process against PGlite. No external PostgreSQL server is required for any of these operations. A real PostgreSQL server is only needed when actually applying migrations to a database.

What pgmagmig does

• Extracts a database schema into a JSON representation by querying PostgreSQL system catalogs.
• Generates DDL from a schema, producing readable CREATE TABLE statements with defaults, constraints, and foreign keys inlined.
• Diffs two schemas and produces the DDL to transform one into the other, with structured hazard annotations on destructive or lock-heavy statements.
• Validates every generated diff by applying it to an in-process PGlite instance and comparing the result to the expected schema.
• Drafts migration files in YAML format, with up and down SQL, automatically validated in both directions.
• Runs migrations against a real PostgreSQL database, with per-migration transactions, branch switching, and rollback support.
• Exposes an ephemeral PGlite via Unix socket or TCP, so any tool that speaks PostgreSQL (Prisma, sqlc, pgtyped, psql) can work against your migration-defined schema without a running database server.

Why PGlite?

PGlite is real PostgreSQL 17 compiled to WASM. It’s not an approximation or a compatibility layer. The system catalogs, type system, DDL parsing, and expression evaluation are identical to a full PostgreSQL server. This means pgmagmig’s schema extraction and diff validation are tested against the same engine your production database runs.

The practical benefit: you can extract, diff, and validate schemas on your laptop, in CI, or anywhere Node.js runs, without provisioning a PostgreSQL instance.

Getting Started

There are two paths to adopting pgmagmig, depending on whether you’re starting with a fresh database or transitioning an existing one.

• Fresh Database — you’re setting up a new project and want to manage the database schema from scratch.
• Existing Database — you have a production database with an established schema, possibly managed by another migration tool, and want to switch to pgmagmig.

Both paths start with the bootstrap command, which creates the initial migration file(s) and provides instructions tailored to your situation.

Fresh Database

If you’re starting a new project with an empty database, getting started with pgmagmig takes three steps.

1. Bootstrap the migrations directory

pgmagmig bootstrap --migrations-dir ./migrations

This creates 0001.yaml containing DDL to set up pgmagmig’s management table. The management table tracks which migrations have been applied.

2. Define your schema and draft a migration

Write your desired schema as SQL:

-- schema.sql
CREATE TABLE public.schema_migrations (
  sequence integer PRIMARY KEY,
  uuid uuid NOT NULL,
  title text NOT NULL,
  down text
);

CREATE TABLE public.users (
  id SERIAL PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text,
  created_at timestamptz DEFAULT now()
);

Note that the management table must be included in your schema definition — it’s part of the managed schema, not hidden.

Then draft a migration:

pgmagmig draft-migration \
  --migrations-dir ./migrations \
  --to-sql schema.sql \
  --title "Create users table" \
  --allow-hazards all

This creates 0002.yaml with the DDL to transform the database from the state after 0001.yaml (just the management table) to your desired schema. The migration is automatically validated by applying both the up and down SQL to PGlite and verifying the result.

Review the generated file. By default it includes invalid: true, which prevents it from being applied until you’ve reviewed the SQL and removed that marker. Use --no-invalid to skip this if you trust the output.

3. Apply migrations

pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url postgres://localhost/mydb \
  --allow-missing-management-table

The --allow-missing-management-table flag is needed for the very first run, since the management table doesn’t exist yet — it’s created by 0001.yaml.

After this, the database has the management table and your schema. Future migrations don’t need the flag.

Next steps

From here, the workflow is:

1. Edit your schema SQL file (or work directly with migration files).
2. Run pgmagmig draft-migration to generate the next migration.
3. Review and apply with pgmagmig migrate.

See Developer Workflow for day-to-day usage patterns.

Existing Database

If you have an existing database — whether managed by another migration tool or maintained manually — pgmagmig can capture its current schema and take over migration management from there.

1. Bootstrap with your existing schema

Point bootstrap at your existing database:

pgmagmig bootstrap \
  --migrations-dir ./migrations \
  --from-database postgres://localhost/mydb

Or if you have your schema as SQL files:

pgmagmig bootstrap \
  --migrations-dir ./migrations \
  --from-sql schema.sql

This creates two files:

• 0001.yaml — creates pgmagmig’s management table.
• 0002.yaml — captures your entire existing schema.

2. Register the migrations in your database

Open 0001.yaml. At the top you’ll find a comment block with exact SQL to run:

CREATE TABLE public.schema_migrations (
  sequence integer PRIMARY KEY,
  uuid uuid NOT NULL,
  title text NOT NULL,
  down text
);

INSERT INTO public.schema_migrations (sequence, uuid, title, down) VALUES
    (1, '<uuid-from-0001>', 'Create pgmagmig management table', NULL),
    (2, '<uuid-from-0002>', 'Existing database schema', NULL);

The UUIDs in the comment match the ones in 0001.yaml and 0002.yaml.

Run this SQL against your database using whatever mechanism you currently use — your existing migration tool, a manual psql session, or a deployment script. This creates the pgmagmig management table and records both bootstrap migrations as already applied, which is correct: the management table now exists (from the SQL you just ran) and the rest of the schema was already there.

3. Verify the state

pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url postgres://localhost/mydb \
  --check

This should report “Database is up to date.” If it doesn’t, the schema in your database differs from what pgmagmig extracted — investigate before proceeding.

4. Clean up the old system (optional)

If your previous migration system had its own management table (e.g., schema_migrations from Rails, _prisma_migrations from Prisma, alembic_version from Alembic), you can now create a pgmagmig migration to drop it:

# Add the old table to your schema SQL as a reminder, then:
pgmagmig draft-migration \
  --migrations-dir ./migrations \
  --to-sql schema.sql \
  --title "Remove old migration table" \
  --allow-hazards all

Or write the migration by hand — it’s just a YAML file with DROP TABLE in the up field.

How it works

The two-migration bootstrap captures a clean baseline:

flowchart TB
  subgraph before ["Before: existing database"]
    direction LR
    old_tables["Your tables,\nindexes, etc."]
    old_mgmt["Old migration\ntable (optional)"]
  end

  subgraph bootstrap ["Bootstrap"]
    direction TB
    gen["pgmagmig bootstrap\n--from-database ..."]
    m1["0001.yaml\nCreate pgmagmig table"]
    m2["0002.yaml\nExisting schema snapshot"]
    gen --> m1
    gen --> m2
  end

  subgraph transition ["Transition SQL (run via old system or manually)"]
    direction LR
    create["CREATE TABLE\nschema_migrations"]
    insert["INSERT rows\nfor 0001 + 0002"]
    create --> insert
  end

  subgraph after ["After: pgmagmig manages the database"]
    direction LR
    tables["Your tables\n(unchanged)"]
    pgm_table["pgmagmig\nmanagement table"]
    future["Future migrations\nvia pgmagmig"]
  end

  before --> bootstrap
  bootstrap --> transition
  transition --> after

  style before fill:#3332
  style after fill:#5a52

• Migration 1 is the management table itself. It exists so that pgmagmig can manage its own infrastructure through the same mechanism it manages everything else.
• Migration 2 is a snapshot of the existing schema at the point of adoption. It has no down migration (down is omitted), because rolling back the entire existing schema is not meaningful.

From this point forward, all schema changes go through pgmagmig. The migration history starts clean, with a clear record of what the database looked like when pgmagmig took over.

Migration Philosophy

pgmagmig has strong opinions about how database migrations should work. These opinions exist because schema migrations are one of the most dangerous operations in a production system — they’re irreversible, they run against live data, and mistakes are expensive. The design choices below all serve the same goal: make it hard to accidentally break your database.

Sequential numbering: git conflicts are a feature

Migration files are named 0001.yaml, 0002.yaml, 0003.yaml, and so on. Numbering must be consecutive starting at 1 — any gap is an error.

This is a deliberate choice. When two developers on separate branches both create 0032.yaml, the second branch to merge gets a git conflict. That conflict forces a conversation: does the new migration still make sense given the one that just landed? Do they interact? Is the ordering correct?

Timestamp-based filenames (like 20240315120000_add_users.sql) avoid this conflict, which sounds convenient but is dangerous. Two migrations developed in parallel may both merge and deploy without conflict, but produce a schema that neither developer intended. The interactions between concurrent migrations are exactly the kind of thing that needs human review.

In spirit, this forced serialisation is not unlike PostgreSQL’s SERIALIZABLE transaction isolation level. SERIALIZABLE makes reasoning about concurrent transactions simple by ensuring they behave as if they ran one at a time. Sequential migration numbering does the same thing for schema changes: by forcing developers to serialise their migrations, it makes reasoning about the cumulative effect simple. Each migration is authored with full knowledge of every migration before it.

The management table is part of the schema

pgmagmig’s management table (schema_migrations by default) is not hidden or special. It appears in schema extractions, diffs, and generated DDL like any other table. If you define your target schema via --to-sql, the management table must be included.

This is intentional. The management table is a real table in your database. Pretending it doesn’t exist leads to surprises — for example, a diff that tries to drop it because it wasn’t in the target schema.

Transaction-safe DDL only

Every migration runs inside a single transaction. If any statement fails, the entire migration is rolled back and the management table remains consistent.

This means pgmagmig deliberately avoids statements that cannot run inside a transaction:

• CREATE INDEX CONCURRENTLY
• ALTER TYPE ... ADD VALUE
• REINDEX CONCURRENTLY

These are genuinely useful in production for large tables, and support for non-transactional migrations is planned as a future extension. But the default is safety: a failed migration leaves no half-applied state.

Hazards are structured, not cosmetic

When pgmagmig generates a diff, destructive or dangerous statements are tagged with a structured hazard type — not a free-text comment. Each hazard has a machine-readable type (DeletesData, AcquiresAccessExclusiveLock, etc.) and a human-readable message.

This serves two purposes:

1. Developer review. When you draft a migration, the hazard comments in the YAML file tell you exactly what’s risky. You can’t miss a DROP COLUMN buried in a long migration.
2. CI gating. The --check-hazards flag lets you fail a CI pipeline if a migration introduces hazards that haven’t been explicitly acknowledged. This catches accidental destructive changes before they reach production.

Hazards are a best-effort signal, not a guarantee of completeness. New hazard types may be added over time as the tool learns about more dangerous patterns.

Verification by construction

pgmagmig doesn’t trust its own output. Every diff is automatically verified by applying it to a real PostgreSQL engine and checking the result:

flowchart LR
  from["Schema A\n(from)"]
  to["Schema B\n(to)"]
  diff["Differ"]
  ddl["Generated\nDDL"]
  pglite["PGlite\nloaded with A"]
  extract["Extract\nschema"]
  actual["Actual\nresult"]
  compare{{"Compare\nfield-by-field"}}
  ok["✅ Valid"]
  fail["❌ Rejected"]

  from --> diff
  to --> diff
  diff --> ddl
  from --> pglite
  ddl --> pglite
  pglite --> extract
  extract --> actual
  actual --> compare
  to --> compare
  compare -->|match| ok
  compare -->|mismatch| fail

  style ok stroke:#5a5,color:#5a5
  style fail stroke:#e55,color:#e55

If the comparison fails, the diff is rejected. This catches bugs in the differ itself, edge cases in DDL generation, and subtle interactions between schema objects.

For draft-migration, both directions are validated: the up migration transforms A to B, and the down migration transforms B back to A.

Readable output

pgmagmig’s differ produces DDL that reads like something a human would write. A new table is a single CREATE TABLE statement with columns, defaults, constraints, and NOT NULL inlined — not a bare table followed by twenty ALTER TABLE statements.

This matters because migration files are reviewed by humans. The easier they are to read, the more likely a reviewer will catch a mistake.

Developer Workflow

pgmagmig is designed for the day-to-day reality of working on a codebase where multiple developers are making schema changes on different branches.

The purpose of down migrations

A common misconception is that down migrations exist to roll back production deployments. They don’t — or at least, they shouldn’t. If you need to revert a schema change in production, write a new forward migration that undoes the change. This gives you a clear audit trail, a reviewed migration, and the ability to test the rollback before deploying it.

So what are down migrations for?

Development. When you’re working on a feature branch that adds a table, and you need to switch to another branch that doesn’t have that table, pgmagmig rolls back your migration and applies the other branch’s migrations. When you switch back, it rolls back those and reapplies yours. This happens automatically when you run pgmagmig migrate — it compares the migration files on disk to what’s recorded in the database and figures out the minimal set of rollbacks and applies.

Thinking through reversibility. Even if you never run a down migration in production, the act of writing one forces you to think about whether your change is reversible. A migration that adds a column has a straightforward reversal (drop the column). A migration that drops a column doesn’t — the data is gone. Writing the down SQL (or explicitly choosing to omit it) is a design decision, not busywork.

Emergency preparedness. If something goes wrong in production and you need to undo a migration, the down SQL already exists. You won’t use it directly (you’ll wrap it in a new forward migration), but having the SQL ready is better than writing it under pressure at 3am.

Branch switching

The typical development loop:

# On feature-branch-a, apply its migrations
pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url postgres://localhost/mydb \
  --allow-rollback

# Switch to feature-branch-b
git checkout feature-branch-b

# Migrate again — pgmagmig rolls back branch-a's migrations
# and applies branch-b's
pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url postgres://localhost/mydb \
  --allow-rollback

The --allow-rollback flag is required because down migrations are inherently destructive (they drop tables, columns, etc.). pgmagmig won’t run them unless you explicitly opt in.

Without --allow-rollback, pgmagmig prints the plan (what it would roll back and apply) and exits with a non-zero status. This is useful in CI where you want to verify the database is in sync without accidentally running destructive operations.

How branch switching works

When you switch branches, the migration files on disk may diverge from what’s applied in the database. pgmagmig walks both lists in parallel, finds the point where UUIDs stop matching, and rolls back everything beyond that point before applying the new branch’s migrations.

block-beta
  columns 5

  space applied["Applied in DB"] space files["Files on disk"] space

  space:5

  a1["0001 · Create tables\n✅ uuid-aaa"] space:2 f1["0001 · Create tables\n✅ uuid-aaa"] space
  space m1["match ✓"] space:3

  a2["0002 · Add users\n✅ uuid-bbb"] space:2 f2["0002 · Add users\n✅ uuid-bbb"] space
  space m2["match ✓"] space:3

  a3["0003 · Add orders\n⬜ uuid-ccc"] space:2 f3["0003 · Add products\n🆕 uuid-ddd"] space
  space m3["mismatch ✗"] space:3

  space:4 f4["0004 · Add reviews\n🆕 uuid-eee"]

  space:5

  space r1["⬅ rollback 0003\n(uuid-ccc)"] space a4["➡ apply 0003\n(uuid-ddd)"] space
  space:2 space a5["➡ apply 0004\n(uuid-eee)"] space

  style a3 stroke:#e55,color:#e55
  style f3 stroke:#5a5,color:#5a5
  style f4 stroke:#5a5,color:#5a5
  style r1 stroke:#e55,color:#e55
  style a4 stroke:#5a5,color:#5a5
  style a5 stroke:#5a5,color:#5a5

The match point is after 0002. Migration 0003 (uuid-ccc) is rolled back using its stored down SQL, then 0003 (uuid-ddd) and 0004 (uuid-eee) from the new branch are applied.

Drafting migrations

The draft-migration command generates a migration from the diff between your current migrations and a target schema:

pgmagmig draft-migration \
  --migrations-dir ./migrations \
  --to-sql schema.sql \
  --title "Add orders table" \
  --allow-hazards all

The generated file includes:

• up SQL — the DDL to apply the change, with -- HAZARD comments on risky statements.
• down SQL — the DDL to reverse the change.
• invalid: true — a marker that prevents the migration from being applied until you’ve reviewed it. Remove this line after review.

Both directions are validated by roundtripping through PGlite.

If you prefer to write migrations by hand, that works too — the file format is simple YAML.

Using the ephemeral database

The run command builds a PGlite database from your migrations and exposes it via Unix socket:

pgmagmig run \
  --from-migrations-dir ./migrations \
  --command "npx prisma db pull"

This is useful for:

• Prisma introspection against your migration-defined schema.
• Code generation tools like sqlc or pgtyped.
• Ad-hoc queries with psql: pgmagmig run --from-migrations-dir ./migrations --command 'psql "$DATABASE_URL"'

The database is ephemeral — it exists only for the duration of the command. No server to start, no port to configure, no cleanup needed.

CI integration

A typical CI pipeline:

# Check that the database is in sync with migrations
pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url $DATABASE_URL \
  --check

# Check for unexpected hazards in the latest migration
pgmagmig diff \
  --from-migrations-dir ./migrations \
  --to-sql schema.sql \
  --check-hazards \
  --allow-hazards IndexBuild

The --check flag on migrate exits non-zero if any migrations are pending. The --check-hazards flag on diff exits non-zero if the diff produces any hazards not in the allow-list.

CLI Reference

pgmagmig provides the following commands:

Common patterns

Most commands accept schema source options (--from-* and/or --to-*) to specify where to read schemas from.

Error output

pgmagmig formats PostgreSQL errors cleanly, showing the error code, message, detail, and hint without stack traces:

ERROR: relation "foo" does not exist
Code: 42P01

extract

Apply SQL to a PGlite instance, extract the schema, and output it as JSON.

Usage

pgmagmig extract --from-sql schema.sql
pgmagmig extract --from-database postgres://localhost/mydb
pgmagmig extract --from-migrations-dir ./migrations

Options

Exactly one --from-* option is required. See Schema Sources.

Output

JSON representation of the database schema, written to stdout. The output includes tables (with columns and constraints), indexes, views, functions, triggers, enums, sequences, schemas, and extensions.

Example

pgmagmig extract --from-sql schema.sql > schema.json

generate

Generate DDL from a schema. This is an alias for diff --from-empty --to-*.

Usage

pgmagmig generate --to-sql schema.sql
pgmagmig generate --to-json schema.json

Options

Exactly one --to-* option is required. See Schema Sources.

Output

DDL statements written to stdout that would create the entire schema from scratch.

Example

pgmagmig generate --to-json schema.json > create.sql

diff

Compare two schemas and output the DDL to transform one into the other.

Usage

pgmagmig diff --from-sql old.sql --to-sql new.sql
pgmagmig diff --from-migrations-dir ./migrations --to-database postgres://localhost/mydb
pgmagmig diff --from-empty --to-sql schema.sql

Options

Schema sources

Exactly one --from-* and one --to-* option is required.

Behaviour options

Output

DDL statements written to stdout. With --annotated, hazard comments appear above their statements.

How the DDL is planned

By default, diff uses the reconciliation loop: it applies changes to an in-memory PGlite instance step by step, re-reading the schema after each step until it matches the target. Because it plans against a live database, it copes with awkward interdependencies — reordering interdependent views and functions, and dropping-and-recreating dependent objects when the thing they depend on changes. Its output is correct by construction, so no separate validation step is needed.

--quick selects the one-shot static differ instead. It computes the whole plan in a single pass, which is faster and produces a minimal diff, but in some situations may mis-order complex inter-object dependencies. In practice it handles most schemas well (it uses the same fine-grained ordering buckets as the reconciliation loop). When --quick is used, the plan is validated by applying it to a fresh PGlite instance and comparing the result to the target; --skip-validation disables that check (faster, but no correctness guarantee).

Hazard gating

When --check-hazards is set, diff exits with status 1 if any statement carries a hazard type not listed in --allow-hazards. This is useful in CI as a tripwire against unexpectedly destructive drift.

# Fail if the diff contains any hazard except IndexBuild
pgmagmig diff \
  --from-migrations-dir ./migrations \
  --to-sql schema.sql \
  --check-hazards \
  --allow-hazards IndexBuild

bootstrap

Create the initial migration file(s) for a new pgmagmig-managed project.

Usage

# Fresh database
pgmagmig bootstrap --migrations-dir ./migrations

# Existing database
pgmagmig bootstrap --migrations-dir ./migrations --from-database postgres://localhost/mydb
pgmagmig bootstrap --migrations-dir ./migrations --from-sql schema.sql

Options

Behaviour

Without --from-* (fresh database)

Creates a single file:

• 0001.yaml — DDL to create the management table, with a down migration (DROP TABLE).

The file includes a comment explaining how to apply it:

pgmagmig migrate --migrations-dir ./migrations \
  --database-url postgres://... \
  --allow-missing-management-table

With --from-* (existing database)

Creates two files:

• 0001.yaml — DDL to create the management table, with no down migration.
• 0002.yaml — DDL to create the existing schema (everything except the management table), with no down migration.

The comment in 0001.yaml provides copy-pasteable SQL to register both migrations in an existing database:

CREATE TABLE public.schema_migrations (
  sequence integer PRIMARY KEY,
  uuid uuid NOT NULL,
  title text NOT NULL,
  down text
);

INSERT INTO public.schema_migrations (sequence, uuid, title, down) VALUES
    (1, '<uuid>', 'Create pgmagmig management table', NULL),
    (2, '<uuid>', 'Existing database schema', NULL);

Run this SQL using your current migration system or manually. After that, the database has the pgmagmig management table with both migrations recorded, and the schema is in sync.

See Getting Started: Existing Database for a complete walkthrough.

draft-migration

Generate a YAML migration file by diffing the current migrations against a target schema.

Usage

pgmagmig draft-migration \
  --migrations-dir ./migrations \
  --to-sql schema.sql \
  --title "Add orders table" \
  --allow-hazards all

Options

Exactly one --to-* option is required.

How it works

1. Builds the “from” schema by applying all existing migrations in --migrations-dir to PGlite.
2. Builds the “to” schema from the --to-* source.
3. Plans in both directions: from → to for the up SQL, to → from for the down SQL.
4. Writes the next sequential YAML file (e.g., 0003.yaml).

By default the plan comes from the reconciliation loop, whose output is correct by construction (see diff for how it works). --quick selects the one-shot static differ instead and validates both directions by roundtripping through PGlite; --skip-validation disables that check.

The invalid marker

By default, generated migrations include invalid: true. This prevents them from being applied (by migrate or read by any command that processes the migrations directory) until you’ve reviewed the SQL and removed the marker. Use --no-invalid to skip this.

Hazard gating

The command fails if the generated diff contains any hazard types not listed in --allow-hazards. Use --allow-hazards all during development, and more restrictive allow-lists in CI.

Output

The generated YAML file includes -- HAZARD (type): message comments in the up SQL, so hazards are visible during code review.

verify-down

Verify that down-migrations correctly reverse their up-migrations.

Migrations drafted by pgmagmig have correct down SQL by construction, but you often edit them by hand — to restore data, or after tweaking the schema and the up SQL. This command checks that a down still returns the schema to exactly the state it was in before the migration ran.

Usage

# Check the most recent migration's down (the default)
pgmagmig verify-down --migrations-dir ./migrations

# Check the last 3 migrations
pgmagmig verify-down --migrations-dir ./migrations --last 3

# Check the entire chain
pgmagmig verify-down --migrations-dir ./migrations --all

Options

How it works

Against a throwaway PGlite instance, each checked migration runs an up → down → up “dance”, comparing schema snapshots at each step:

1. Snapshot the schema before the migration (S₀).
2. Apply up, snapshot (S₁).
3. Apply down, snapshot — assert it matches S₀ (the down restored the prior schema).
4. Apply up again, snapshot — assert it matches S₁ (the up is reproducible).

Only a trailing window is danced. To check the last N, the earlier migrations’ ups are applied plainly to build the base state, then the dance runs on the final N. The default window is the single most recent migration; use --last or --all to widen it.

Each migration is applied as a single transaction, mirroring how migrate runs them — so a down that only works outside a transaction, or one that forgets a needed SET, is caught.

What is compared

Snapshots are compared structurally (tables, columns, constraints, indexes, views, functions, triggers, enums, sequences), the same model the differ uses. Data is not compared — a down that restores dropped data still passes as long as the schema matches, which is the intended granularity.

Skipped migrations

A migration is skipped (not a failure) when it has nothing to reverse:

• No down (down omitted / null) — there is no down migration to check.
• No-op down (bare down:, an empty string) — the down is an intentional no-op, so it is not expected to restore the schema.

Work-in-progress migrations

Freshly drafted migrations carry invalid: true, and verify-down is exactly the tool you use while reviewing them. Unlike other commands, it does not treat invalid: true as a hard stop — it prints a warning and checks the migration anyway.

Exit status

Exits 0 if every checked migration passes (or is skipped), and 1 if any down fails to restore the schema or errors while executing. Suitable as a CI check.

Example output

warning: 0007.yaml is marked invalid: true (checking anyway)
  ok        0005 add orders table
  ok        0006 add orders index
  MISMATCH  0007 add status column
            down migration does not restore the pre-migration schema:
              - unexpected column status

pgmagmig: 1 migration failed verification

migrate

Apply outstanding migrations to a PostgreSQL database.

Usage

pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url postgres://localhost/mydb

# First run (management table doesn't exist yet)
pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url postgres://localhost/mydb \
  --allow-missing-management-table

# Branch switching (requires rollback)
pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url postgres://localhost/mydb \
  --allow-rollback

Options

How it works

1. Reads all migration files from the directory.
2. Reads all applied migrations from the management table.
3. Finds the longest prefix where the file UUIDs match the applied UUIDs.
4. Everything beyond the match point in the database is rolled back (in reverse order).
5. Everything beyond the match point in the files is applied (in order).

Each migration (up or down) runs in its own transaction. If a migration fails, the transaction is rolled back and the management table remains consistent.

Migration plan

Before executing, migrate prints a plan to stderr showing what it will do:

pgmagmig: migration plan

  rollback 0003 Add notifications
  apply    0003 Add orders table
  apply    0004 Add order items

1 migration to roll back, 2 migrations to apply

This happens even with --dry-run, --check, and when rollback is blocked (no --allow-rollback). When the database is already up to date, no plan is printed — just the summary.

Execution output

During execution, every SQL statement is printed before it runs. This includes transaction control (BEGIN, COMMIT) and management table writes, giving full visibility into what happens on the database. After each statement, the execution time is shown:

-- 0003 Add orders table

  [1/5] BEGIN
        ok (1ms)
  [2/5] CREATE TABLE public.orders (
          id integer NOT NULL,
          user_id integer NOT NULL
        )
        ok (4ms)
  [3/5] ALTER TABLE public.orders ADD CONSTRAINT orders_pkey PRIMARY KEY (id)
        ok (2ms)
  [4/5] INSERT INTO public.schema_migrations (sequence, uuid, title, down) VALUES (...)
        ok (1ms)
  [5/5] COMMIT
        ok (0ms)

-- 0003 done (38ms)

The [N/M] counter shows which statement is running. Multi-line SQL is displayed in full, with continuation lines indented to align with the first. The done line shows the wall-clock time for the entire migration.

When all migrations complete, a summary is printed to stdout:

pgmagmig: 2 applied

Or with rollbacks:

pgmagmig: 1 rolled back, 2 applied

Error output

If a statement fails, the error is shown with the PostgreSQL error code and message:

  [2/5] CREATE TABLE public.orders (id integer REFERENCES nonexistent(id))
        FAILED

ERROR in 0003 "Add orders table", statement 2/5
  Code:    42P01
  Message: relation "nonexistent" does not exist

Detail, hint, and context are included when the database provides them. No further migrations are executed after an error.

Rollback safety

Down-migrations are often destructive — they drop tables, columns, or data. By default, migrate refuses to run them:

ERROR: 1 migration(s) need to be rolled back. Down-migrations are often
destructive. Use --allow-rollback to proceed.

Use --allow-rollback to opt in. This is typically used during development when switching between feature branches.

CI usage

# Fail if the database is not in sync with migrations
pgmagmig migrate \
  --migrations-dir ./migrations \
  --database-url $DATABASE_URL \
  --check

The --check flag prints the plan and exits with status 1 if anything is pending, without modifying the database.

run

Build a PGlite database from a schema source, expose it via Unix socket (or TCP), and run a shell command with DATABASE_URL set.

Usage

# Run Prisma introspection against your migrations
pgmagmig run \
  --from-migrations-dir ./migrations \
  --command "npx prisma db pull"

# Run sqlc against a SQL schema
pgmagmig run \
  --from-sql schema.sql \
  --command "sqlc generate"

# Interactive psql session
pgmagmig run \
  --from-migrations-dir ./migrations \
  --command 'psql "$DATABASE_URL"'

# Use TCP instead of Unix socket
pgmagmig run \
  --from-sql schema.sql \
  --host 127.0.0.1 \
  --command "my-tool --db-url \$DATABASE_URL"

Options

Exactly one --from-* option is required.

Connection mode

By default, PGlite is exposed via a Unix domain socket in a temporary directory. This is fast, avoids port conflicts, and works with most PostgreSQL client libraries.

With --host, PGlite is exposed via TCP on the specified address. Use this for tools that don’t support Unix socket connections.

The DATABASE_URL environment variable is set accordingly:

• Unix socket: postgresql://postgres:postgres@/postgres?host=/tmp/pgmagmig-XXXXXX
• TCP: postgresql://postgres:postgres@127.0.0.1:PORT/postgres

SQL preservation

For --from-sql and --from-migrations-dir, the raw SQL is applied directly to PGlite. This preserves INSERT statements, GRANTs, and other non-schema SQL. For other sources (--from-json, --from-database), the schema is extracted and regenerated as DDL.

Exit code

The command’s exit code is propagated:

• Child exits normally → pgmagmig exits with the same code.
• Child killed by a signal → pgmagmig exits with 128 + signal number.

Signal handling

SIGINT and SIGTERM received by pgmagmig are forwarded to the child process. After the child exits, the PGlite instance and socket are cleaned up.

Schema Sources

Most pgmagmig commands accept --from-* and/or --to-* options to specify where to read a schema from. Exactly one source must be given per side.

Available sources

--from-sql <files...> / --to-sql <files...>

Read one or more SQL files and apply them in order to a PGlite instance. The resulting schema is extracted from PGlite’s system catalogs.

Order matters — if 02-tables.sql references a type defined in 01-types.sql, list them in that order.

pgmagmig diff --from-sql 01-types.sql 02-tables.sql --to-sql new-schema.sql

--from-json <file> / --to-json <file>

Read a JSON schema file, as produced by pgmagmig extract.

pgmagmig extract --from-sql schema.sql > schema.json
pgmagmig diff --from-json old.json --to-json new.json

--from-empty / --to-empty

An empty schema containing only the public schema (which PostgreSQL always has).

# Generate DDL for a complete schema (from nothing to everything)
pgmagmig diff --from-empty --to-sql schema.sql

--from-database <url> / --to-database <url>

Connect to a live PostgreSQL database and extract its schema. The connection uses BEGIN READ ONLY followed by ROLLBACK — nothing is written.

pgmagmig diff --from-database postgres://localhost/prod --to-sql schema.sql

--from-migrations-dir <path> / --to-migrations-dir <path>

Read all YAML migration files from the directory, apply their up SQL in order to a PGlite instance, and extract the resulting schema.

All migration files must be valid — any file with invalid: true causes an immediate error.

pgmagmig diff --from-migrations-dir ./migrations --to-sql schema.sql

Migration Files

Migrations are YAML files stored in a directory. They are named sequentially: 0001.yaml, 0002.yaml, 0003.yaml, and so on.

Format

title: Create users table
uuid: 7f3b2a1e-8c4d-4e5f-9a6b-1c2d3e4f5a6b
up: |
  CREATE TABLE public.users (
    id SERIAL NOT NULL,
    email text NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_key UNIQUE (email)
  );
down: |
  DROP TABLE public.users;

Fields

The down field: three states

The down field has three distinct states with different runtime behaviour:

• Present with SQL (down: "DROP TABLE users;") — the runner executes this SQL when rolling back.
• Present but empty (down: "" or down:) — rollback is a no-op. The row is removed from the management table, but no SQL is executed. Use this when a migration can’t be meaningfully reversed but you want to allow the runner to proceed past it.
• Omitted (no down key at all) — the runner errors if rollback is attempted. Use this for migrations that are truly irreversible, like dropping a table with data that can’t be recreated.

In the management table, these map to: a SQL string, an empty string, and NULL, respectively.

The invalid marker

When draft-migration generates a file, it includes invalid: true by default. This is a safety net: the generated SQL should be reviewed by a human before it’s applied. Any command that reads migration files (including migrate, diff --from-migrations-dir, and run --from-migrations-dir) errors immediately if it encounters an invalid file.

After reviewing the SQL, remove the invalid: true line (or the entire invalid field).

Use --no-invalid on draft-migration to skip the marker if you trust the output.

Sequential numbering

Files must be numbered consecutively starting at 1. Any gap is an error: if 0001.yaml and 0003.yaml exist but 0002.yaml doesn’t, all commands refuse to proceed.

This is deliberate — see Migration Philosophy for the rationale.

UUID identity

The UUID field, not the filename, defines a migration’s identity. When the runner compares migration files to the management table, it matches by UUID at each sequence position. If the UUID at position 3 in the files doesn’t match the UUID at position 3 in the database, everything from position 3 onward is rolled back and reapplied.

This enables branch switching: when you check out a different branch with different migrations, the UUIDs diverge and the runner handles the transition automatically.

Hazards

When pgmagmig generates a schema diff, it tags statements that are destructive, lock-heavy, or potentially dangerous with structured hazard annotations. Each hazard has a machine-readable type and a human-readable message.

Hazard types

Where hazards appear

In migration files

The draft-migration command includes -- HAZARD (type): message comments in the up SQL:

up: |
  -- HAZARD (DeletesData): deletes all data in column public.users.old_email
  ALTER TABLE public.users DROP COLUMN old_email;
  -- HAZARD (IndexBuild): builds index public.idx_users_name
  CREATE INDEX idx_users_name ON public.users USING btree (name);

These comments are preserved in the migration file for code review visibility.

In diff output

With pgmagmig diff --annotated, hazard comments appear above their statements in the DDL output.

In the structured API

diffSchemaStatements(from, to) returns Statement[] where each statement carries a hazards: Hazard[] array. Programmatic consumers can filter, block, or report on specific hazard types.

Hazard gating

draft-migration

Fails if the diff produces any hazard types not listed in --allow-hazards:

# Allow only IndexBuild hazards
pgmagmig draft-migration \
  --migrations-dir ./migrations \
  --to-sql schema.sql \
  --title "Add index" \
  --allow-hazards IndexBuild

# Allow all hazards
pgmagmig draft-migration ... --allow-hazards all

diff –check-hazards

Exits non-zero if any hazards are present and not in the allow-list:

# CI: fail if the diff is destructive
pgmagmig diff \
  --from-migrations-dir ./migrations \
  --to-sql schema.sql \
  --check-hazards \
  --allow-hazards IndexBuild

migrate

Does not gate on hazards. Hazard review happens at draft time; the invalid: true marker forces human review before a migration can be applied.

Completeness

The hazard mapping is illustrative, not exhaustive. The canonical set of hazard-tagged patterns lives in the diff implementation and will grow over time. The goal is a reasonable best-effort to surface risk, not a guarantee that every dangerous operation is flagged.