pg-migration-engine

PostgreSQL schema introspection, diff, DDL generation, and safe migration execution.

Why This Exists • Supported Objects • Architecture • Installation • Quick Start • Schema Format • Key Features • API Reference • Documentation

---

Why This Exists

No existing open-source tool covers PostgreSQL object types comprehensively:

Tool	Coverage	Limitations
DataGrip / DBeaver / pgAdmin	GUI only	No programmatic API or CLI, manual UI workflow
Flyway / Liquibase	Manual SQL	No state-based diff engine — developers write all DDL by hand
Atlas	~15 types	Missing PG behavioral objects (views, functions, triggers, policies)
Prisma / Drizzle	~10 types	ORM-bound; ignores PG-native types, procedures, and behavioral objects

This engine covers EVERYTHING — 50+ object types with extensive property coverage.

---

Supported Object Types (50+)

Covers 50+ PostgreSQL object types and 190+ properties across PostgreSQL 10–18:

• Structural Objects: Tables, Columns, Indexes, Constraints, Sequences, Enums, Domains, Ranges, Partitions, Foreign Tables, ACLs, and more.
• Behavioral Objects: Views, Materialized Views, Functions, Procedures, Triggers, RLS Policies, Text Search (FTS), Rules, FDWs.
• Cross-Database Objects: Databases, Roles, Tablespaces, Publications, Subscriptions.

👉 See full breakdown and property matrix in Supported Objects Wiki.

---

Architecture

SQL ➔ Introspector ➔ Translator ➔ Differ ➔ Risk Tagger ➔ DDL Generator ➔ Planner ➔ Executor ➔ Storage

👉 Explore the 8-module deep dive in Architecture Wiki.

---

Installation

npm install pg-migration-engine

---

Quick Start

import pg from 'pg';
import { SwMigrationEngine } from 'pg-migration-engine';

const pool = new pg.Pool({ 
  host: 'localhost', 
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'pass'
});

const engine = new SwMigrationEngine();

// Introspect current database
const snapshot = await engine.introspect(pool);

// Diff against desired schema
const desired = /* your target schema */;
const diff = engine.diff(desired, snapshot);

// Generate DDL
const ddl = engine.generateDDL(diff);

// Plan migration respecting dependencies
const plan = engine.plan(diff);

// Execute with safety checks
if (!plan.blocked) {
  const result = await engine.execute(pool, plan);
  console.log('Migrated:', result.migrationId);
}

// Or one-shot migrate
const result = await engine.migrate(pool, desired);

---

Schema Format

The engine accepts desired schemas in either Object Format (keyed by name) or Array Format (automatically normalized):

import { normalizeSchema, validateSchemaFormat } from 'pg-migration-engine';

// Object Format (Recommended) or Array Format
const desired = {
  tables: {
    'public.users': {
      name: 'users',
      schema: 'public',
      columns: [
        { name: 'id', dataType: 'serial', isNullable: false }, // accepts 'dataType' or 'type'
        { name: 'email', dataType: 'varchar(255)', isNullable: false },
        { name: 'created_at', dataType: 'timestamp', isNullable: false, defaultValue: 'now()' },
      ],
      constraints: [
        { name: 'users_pkey', constraintType: 'PRIMARY_KEY', columns: ['id'] },
      ],
    },
  },
  types: {
    'public.status': { name: 'status', schema: 'public', kind: 'ENUM', enumValues: ['draft', 'active'] },
  },
  indexes: {
    'public.users_email_idx': { name: 'users_email_idx', table: 'users', columns: ['email'] }, // accepts strings or objects
  },
};

// Normalize array format & validate
const normalized = normalizeSchema(desired);
const { valid, errors } = validateSchemaFormat(desired);

---

Key Features

• 50+ Object Types (PG 10–18): Full introspection & state-based diffing across all PostgreSQL structural, behavioral, and cross-database objects.
• Safe Migration Patterns: Automated non-blocking DDL workflows for zero-downtime changes (CONCURRENTLY indexes, NOT VALID constraints, lock-free NOT NULL). See Safe Patterns Wiki.
• 5-Level Risk Assessment: Categorizes change safety (none to critical); destructive operations (DROP TABLE, DROP COLUMN) blocked by default. See Risk Assessment Wiki.
• RENAME Detection: Smart entity matching detects renames instead of destructive DROP + CREATE, preserving data.
• Drift Detection: Fingerprint-based schema checksums alert when database state is altered out-of-band outside migrations.
• Non-Transactional Aware: Automatically isolates statements requiring outside-transaction execution (ALTER ENUM ADD VALUE, CREATE INDEX CONCURRENTLY).
• Rollback Generation: Generates automatic reverse DDL statements for safe migration rollbacks.

---

API Reference

Method	Description
introspect(pool)	Capture database schema as SchemaSnapshot
diff(desired, current)	Detect changes between schemas
generateDDL(diff)	Generate PostgreSQL DDL statements
plan(diff)	Create dependency-ordered migration plan
execute(pool, plan)	Apply migration transactionally
migrate(pool, desired)	One-shot: introspect → diff → plan → execute
rollback(pool, migrationId)	Revert migration (best-effort)
createMigrationPlan(changes)	Create plan from change array (alias)
diffSchemas(desired, current)	Alias for diff()

Full API: API Reference

---

Ecosystem

Package	Purpose
sw-agent	PostgreSQL connection pool, security, audit
pg-ddl-parser	DDL parsing (CREATE/ALTER/DROP → schema)
pg-migration-engine	Schema introspection, diff, migration

import { parsePostgresSQL } from 'pg-ddl-parser';
import { SwMigrationEngine } from 'pg-migration-engine';

// Parse DDL → desired schema
const desired = parsePostgresSQL(fs.readFileSync('schema.sql', 'utf8'));

// Migrate
const engine = new SwMigrationEngine();
await engine.migrate(pool, convertParsedToSnapshot(desired));

---

Documentation

• Architecture — 8-module deep dive
• Supported Objects — Full 50+ type coverage
• Safe Patterns — 6+ safe workflows
• Risk Assessment — 5 levels explained
• PG Version Matrix — Feature support PG10-18

---

PostgreSQL Version Support

Version	Status	Key Features
PG 10	✅	Generated columns
PG 11	✅	Procedures, INCLUDE indexes
PG 12	✅	GENERATED ALWAYS
PG 13	✅	Incremental sort
PG 14	✅	Multirange, security invoker
PG 15	✅	NULLS NOT DISTINCT
PG 16	✅	ANY_VALUE
PG 17	✅	MERGE improvements
PG 18	✅	Temporal tables, PERIOD, NOT ENFORCED constraints

---

License

Business Source License 1.1 (BSL) — see LICENSE

• ✅ Free for development, testing, evaluation
• ✅ Free for non-production environments
• ❌ Production use requires commercial license

Contact: vivek@vivekmind.com

---

Built by Schema Weaver.