Coming from Other Tools#

This guide helps you migrate to pgmi from other database deployment tools. Each section maps familiar concepts to pgmi equivalents and shows a concrete migration path.

How pgmi deploys: The deploy.sql examples below query files from pg_temp.pgmi_plan_view (or pg_temp.pgmi_source_view) and execute them directly with EXECUTE. See Session API for the full reference.

⚠️ Read this first: pgmi re-executes every migration on every deploy. The basic template keeps no ledger — each file runs again every time you pgmi deploy. That is safe only when your SQL is idempotent (CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE FUNCTION, ALTER TABLE ... ADD COLUMN IF NOT EXISTS). If you arrive with Flyway’s, Liquibase’s, or Sqitch’s “each migration runs exactly once” mental model, a non-idempotent statement like a bare ALTER TABLE ... ADD COLUMN succeeds on the first deploy and fails on the second. Two ways out: make every migration idempotent, or turn on apply-once tracking — the basic template’s deploy.sql ships a commented-out opt-in block for exactly this, and Tracking migration state below lays out the options.

Quick concept mapping#

Coming from Flyway#

What changes#

Before (Flyway):

migrations/
├── V1__create_users.sql
├── V2__add_email.sql
└── V3__create_orders.sql

flyway.conf:
flyway.url=jdbc:postgresql://localhost/mydb
flyway.user=postgres

After (pgmi):

myapp/
├── deploy.sql              # You write deployment logic
├── pgmi.yaml               # Connection defaults
└── migrations/
    ├── 001_create_users.sql
    ├── 002_add_email.sql
    └── 003_create_orders.sql

Migration steps#

1. Rename files (optional but cleaner):

   # V1__create_users.sql → 001_create_users.sql
   # The V prefix was Flyway convention; pgmi doesn't require it

2. Create pgmi.yaml:

   connection:
     host: localhost
     database: mydb
     username: postgres

3. Create deploy.sql that mimics Flyway’s behavior:

   -- deploy.sql: Flyway-like linear execution
   BEGIN;
   
   DO $$
   DECLARE
       v_file RECORD;
   BEGIN
       -- Execute all migrations in filename order
       FOR v_file IN (
           SELECT p.path, p.content
           FROM pg_temp.pgmi_plan_view p
           JOIN pg_temp.pgmi_source_view s ON s.path = p.path
           WHERE s.is_sql_file AND p.path LIKE './migrations/%'
           ORDER BY p.execution_order
       )
       LOOP
           RAISE NOTICE 'Executing: %', v_file.path;
           EXECUTE v_file.content;
       END LOOP;
   END $$;
   
   COMMIT;

4. Deploy:

   pgmi deploy . --database mydb

Mapping Flyway features#

What you gain#

• Transaction control: You decide transaction boundaries. Want all-or-nothing? Use BEGIN...COMMIT. Want error context per file? Use exception blocks:

  FOR v_file IN (
      SELECT p.path, p.content
      FROM pg_temp.pgmi_plan_view p
      JOIN pg_temp.pgmi_source_view s ON s.path = p.path
      WHERE s.is_sql_file AND p.path LIKE './migrations/%'
      ORDER BY p.execution_order
  )
  LOOP
      BEGIN
          EXECUTE v_file.content;
      EXCEPTION WHEN OTHERS THEN
          RAISE EXCEPTION 'Failed on %: %', v_file.path, SQLERRM;
      END;
  END LOOP;

  See Production Guide for transaction strategy options.

• Conditional logic: Skip migrations based on environment, feature flags, or database state:

  IF COALESCE(current_setting('pgmi.env', true), 'dev') = 'production' THEN
      FOR v_file IN (
          SELECT p.path, p.content
          FROM pg_temp.pgmi_plan_view p
          JOIN pg_temp.pgmi_source_view s ON s.path = p.path
          WHERE s.is_sql_file AND p.path LIKE './production/%'
          ORDER BY p.execution_order
      ) LOOP
          EXECUTE v_file.content;
      END LOOP;
  END IF;

• No Java dependency: pgmi is a single Go binary.

Concrete differences in Flyway terms#

• Checksum mismatch after formatting: Flyway checksums are raw bytes — reformatting a file triggers a mismatch that requires flyway repair. pgmi provides both a raw checksum and a normalized one (comments stripped, whitespace collapsed); tracking against the normalized checksum makes reformatting free.
• Test gate location: Flyway’s documented testing approach embeds assertions in migration files and uses transaction rollback to iterate. Tests run interactively before committing through Flyway. With pgmi, CALL pgmi_test() runs tests inside the deploy transaction, against the target, post-migration — a failing test means the commit never happened.
• Plan inspectability: Flyway validates checksums against flyway_schema_history. pgmi’s pgmi_plan_view is a SQL view you can query and assert on — “no two files may claim the same sort key” or “the plan must match this signed manifest” are queries, not tool features you wait for.

Coming from Liquibase#

What changes#

Before (Liquibase):

db/
├── changelog.xml
├── changes/
│   ├── 001-create-users.xml
│   └── 002-add-email.xml
└── liquibase.properties

After (pgmi):

myapp/
├── deploy.sql
├── pgmi.yaml
└── migrations/
    ├── 001_create_users.sql
    └── 002_add_email.sql

Migration steps#

1. Convert changesets to SQL files:

   Before (Liquibase XML):

   <changeSet id="1" author="dev">
       <createTable tableName="users">
           <column name="id" type="serial" autoIncrement="true">
               <constraints primaryKey="true"/>
           </column>
           <column name="email" type="varchar(255)"/>
       </createTable>
   </changeSet>

   After (plain SQL):

   -- 001_create_users.sql
   CREATE TABLE users (
       id SERIAL PRIMARY KEY,
       email VARCHAR(255)
   );

2. Create deploy.sql:

   BEGIN;
   
   DO $$
   DECLARE
       v_file RECORD;
   BEGIN
       FOR v_file IN (
           SELECT p.path, p.content
           FROM pg_temp.pgmi_plan_view p
           JOIN pg_temp.pgmi_source_view s ON s.path = p.path
           WHERE s.is_sql_file AND p.path LIKE './migrations/%'
           ORDER BY p.execution_order
       )
       LOOP
           RAISE NOTICE 'Executing: %', v_file.path;
           EXECUTE v_file.content;
       END LOOP;
   END $$;
   
   COMMIT;

3. Map Liquibase contexts to parameters:

   Before (Liquibase):

   <changeSet id="1" context="production">

   After (pgmi):

   IF COALESCE(current_setting('pgmi.env', true), 'dev') = 'production' THEN
       FOR v_file IN (SELECT content FROM pg_temp.pgmi_source_view WHERE path = './migrations/production_only.sql') LOOP
           EXECUTE v_file.content;
       END LOOP;
   END IF;

Mapping Liquibase features#

What you gain#

• No XML/YAML/JSON: Pure SQL files, no framework markup
• Full PostgreSQL power: Use any PostgreSQL feature, not just what Liquibase supports
• Simpler debugging: Errors are PostgreSQL errors, not Liquibase interpretation errors

Concrete differences in Liquibase terms#

• Preconditions vs. test gates: Liquibase preconditions check state before a changeset runs. pgmi’s CALL pgmi_test() tests the database after migrations ran but before committing — a failed test aborts the deploy and rolls back its transactional schema and data changes. PostgreSQL sequence advances and effects outside the transaction are not rolled back.
• Changelog tracking: Liquibase tracks applied changesets in databasechangelog and validates checksums (MD5) against it. pgmi ships both a raw and a normalized checksum; you choose whether to track at all and which checksum to use — reformatting doesn’t break the normalized one.
• Plan visibility: Liquibase’s execution plan is changelog-order, resolved internally. pgmi’s pgmi_plan_view is a SQL view — your deploy.sql can query, filter, and assert on the plan before executing anything.

Coming from raw psql scripts#

If you’re currently running SQL files manually with psql, pgmi adds structure without complexity.

What changes#

Before:

psql -d mydb -f 001_create_users.sql
psql -d mydb -f 002_add_email.sql
psql -d mydb -f 003_create_orders.sql

After:

pgmi deploy . --database mydb

Migration steps#

1. Organize files:

   myapp/
   ├── deploy.sql
   ├── pgmi.yaml
   └── migrations/
       ├── 001_create_users.sql
       ├── 002_add_email.sql
       └── 003_create_orders.sql

2. Create minimal deploy.sql:

   DO $$
   DECLARE
       v_file RECORD;
   BEGIN
       FOR v_file IN (
           SELECT p.path, p.content
           FROM pg_temp.pgmi_plan_view p
           JOIN pg_temp.pgmi_source_view s ON s.path = p.path
           WHERE s.is_sql_file
           ORDER BY p.execution_order
       )
       LOOP
           RAISE NOTICE 'Executing: %', v_file.path;
           EXECUTE v_file.content;
       END LOOP;
   END $$;

What you gain#

• Atomic deployments: Wrap everything in a transaction
• Parameterization: Pass environment-specific values via --param
• Testing: Add __test__/ or __tests__/ directories with savepoint isolation
• Reproducibility: Same deploy.sql, same behavior

Coming from Sqitch#

Sqitch is the closest tool to pgmi in spirit — native SQL scripts, no DSL, no framework opinions about your schema. The difference is where the deployment semantics live.

Be clear-eyed about the trade: Sqitch gives you a mature change-management model — deploy/verify/revert, dependency resolution, and history are first-class tool concepts you configure. pgmi gives you a smaller mechanism — your project files as queryable session data — and delegates the entire orchestration program to your SQL.

Choose Sqitch if you want the tool to own change state and reversion. Choose pgmi when you want test gates, environment branching, and data loading expressed inside one SQL-controlled deployment program — the deployment transaction and its verification gate as a single control flow you write yourself.

Concrete differences in Sqitch terms#

• Verify vs. test gate: sqitch verify runs verify scripts as a separate step after deployment. pgmi’s CALL pgmi_test() runs inside the deployment transaction — a failed test aborts the commit. Sqitch verification confirms what was applied; pgmi verification decides whether to apply at all.
• Plan inspectability: Sqitch’s plan is a text file (sqitch.plan) resolved by the tool. pgmi’s pgmi_plan_view is a SQL view — your deploy.sql can assert on the plan (“nothing may run before the tenancy migration”) using the same language as the deployment itself.
• Same file at multiple positions: Sqitch requires a separate deploy script per change. pgmi’s UNNEST(sort_keys) lets one idempotent file (e.g. a role-grant script) execute at multiple plan positions without duplication.

Migration path#

1. Your deploy/ scripts become ordinary project files; drop the triplet naming.
2. Plan-file dependencies become ORDER BY logic in deploy.sql (path prefixes work; <pgmi-meta> sortKeys when it gets complex).
3. verify/ scripts become __test__/ tests — with a real upgrade: they run inside the deployment transaction, so a failed verification means the deployment never happened.
4. If you relied on the registry, implement a tracking table (see below ) — the advanced template ships one.

Tracking migration state#

Unlike Flyway and Liquibase, pgmi doesn’t mandate a tracking table. You have options:

Option 1: No tracking (idempotent scripts)#

Write scripts that can run multiple times safely:

-- 001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255)
);

Option 2: Use pgmi metadata#

Add UUID-based tracking with the advanced template:

/*
<pgmi-meta
    id="550e8400-e29b-41d4-a716-446655440000"
    idempotent="false">
</pgmi-meta>
*/
ALTER TABLE users ADD COLUMN phone TEXT;

See Metadata Guide for details.

Option 3: Custom tracking table#

Implement your own, like Flyway does:

-- In deploy.sql
CREATE TABLE IF NOT EXISTS migration_history (
    id SERIAL PRIMARY KEY,
    filename TEXT NOT NULL UNIQUE,
    checksum TEXT NOT NULL,
    applied_at TIMESTAMPTZ DEFAULT now()
);

BEGIN;

DO $$
DECLARE
    v_file RECORD;
BEGIN
    FOR v_file IN (
        SELECT p.path, p.content, p.checksum
        FROM pg_temp.pgmi_plan_view p
        JOIN pg_temp.pgmi_source_view s ON s.path = p.path
        WHERE s.is_sql_file AND p.path LIKE './migrations/%'
        ORDER BY p.execution_order
    )
    LOOP
        IF NOT EXISTS (SELECT 1 FROM migration_history WHERE filename = v_file.path) THEN
            RAISE NOTICE 'Executing: %', v_file.path;
            EXECUTE v_file.content;
            INSERT INTO migration_history (filename, checksum) VALUES (v_file.path, v_file.checksum);
        ELSE
            RAISE NOTICE 'Skipping (already applied): %', v_file.path;
        END IF;
    END LOOP;
END $$;

COMMIT;

Next steps#

• Getting Started — Hands-on first deployment
• Session API Reference — All temp tables and helper functions
• Why pgmi? — When pgmi’s approach makes sense