pgmold

PostgreSQL schema-as-code management tool. Define schemas in native PostgreSQL DDL, diff against live databases, plan migrations, and apply them safely.

Features

Schema-as-Code: Define PostgreSQL schemas in native SQL DDL files
Introspection: Read schema from live PostgreSQL databases
Diffing: Compare schemas and generate migration plans
Safety: Lint rules prevent destructive operations without explicit flags
Drift Detection: Detect schema drift in CI/CD
Transactional Apply: All migrations run in a single transaction
Partitioned Tables: Full support for PARTITION BY and PARTITION OF syntax
JSON Output: Machine-readable output for all commands via --json / -j
Describe: Inspect available commands, object types, and providers with pgmold describe
Grant Management: GRANT/REVOKE sync enabled by default; --manage-ownership for ALTER OWNER
Environment Variable: Set connection string via PGMOLD_DATABASE_URL
PostgreSQL 13-17: Tested against every major version (compatibility matrix)

How pgmold Works

┌─────────────────────┐     ┌─────────────────────┐
│   Schema Files      │     │   Live Database     │
│   (Desired State)   │     │   (Current State)   │
└──────────┬──────────┘     └──────────┬──────────┘
           │                           │
           └───────────┬───────────────┘
                       ▼
              ┌─────────────────┐
              │   pgmold diff   │
              │   (compare)     │
              └────────┬────────┘
                       ▼
              ┌─────────────────┐
              │  Generated SQL  │
              │  (only changes) │
              └─────────────────┘

Example:

Your schema file says:

CREATE TABLE users (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,  -- NEW
  created_at TIMESTAMP
);

Database currently has:

CREATE TABLE users (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMP
);

pgmold generates only the delta:

ALTER TABLE users ADD COLUMN email TEXT NOT NULL;

Installation

cargo install pgmold

For the latest version from source:

cargo install --git https://github.com/fmguerreiro/pgmold

Quick Start

# 1. Create a schema file
cat > schema.sql << 'EOF'
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);
EOF

# 2. See what would change
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb

# 3. Apply the migration
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb

Usage

# Diff two SQL schema files (outputs migration SQL)
pgmold diff --from sql:old.sql --to sql:new.sql

# Diff with JSON output for CI
pgmold diff --from sql:old.sql --to sql:new.sql --json

# Generate migration plan
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb

# Generate rollback plan (reverse direction)
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb --reverse

# Apply migrations (with safety checks)
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb

# Apply with destructive operations allowed
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb --allow-destructive

# Dry run (preview SQL without executing)
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb --dry-run

# Lint schema (requires a database connection to resolve types)
pgmold lint -s sql:schema.sql -d postgres://localhost/mydb

# Detect drift (returns JSON report with exit code 1 if drift detected)
pgmold drift -s sql:schema.sql -d postgres://localhost/mydb -j

Guides

Multi-File Schemas

Organize your schema across multiple files using directories or glob patterns:

# Load all SQL files from a directory (recursive)
pgmold apply -s sql:./schema/ -d postgres://localhost/mydb

# Use glob patterns
pgmold apply -s "sql:schema/**/*.sql" -d postgres://localhost/mydb

# Multiple sources
pgmold apply -s sql:types.sql -s "sql:tables/*.sql" -d postgres://localhost/mydb

Example directory structure:

schema/
├── enums.sql           # CREATE TYPE statements
├── tables/
│   ├── users.sql       # users table + indexes
│   └── posts.sql       # posts table + foreign keys
└── functions/
    └── triggers.sql    # stored procedures

Duplicate definitions across files produce an error with file locations.

Filtering Objects

Filter by name patterns or object types.

Filter by name pattern:

# Include only objects matching patterns
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --include 'api_*' --include 'users'

# Exclude objects matching patterns
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --exclude '_*' --exclude 'pg_*'

Filter by object type:

# Only compare tables and functions (ignore extensions, views, triggers, etc.)
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --include-types tables,functions

# Exclude extensions from comparison
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --exclude-types extensions

Combine type and name filters:

# Compare only functions matching 'api_*', excluding internal ones
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --include-types functions \
  --include 'api_*' \
  --exclude '_*'

Filter nested types within tables:

# Compare tables without RLS policies
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --exclude-types policies

# Compare only table structure (no indexes, constraints, or policies)
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --exclude-types policies,indexes,foreignkeys,checkconstraints

Available object types:

Top-level: extensions, tables, enums, domains, functions, views, triggers, sequences, partitions
Nested (within tables): policies, indexes, foreignkeys, checkconstraints

Extension Objects

By default, pgmold excludes objects owned by extensions (e.g., PostGIS functions, pg_trgm operators) from diffs.

# Include extension objects if needed (e.g., for full database dumps)
pgmold dump -d postgres://localhost/mydb --include-extension-objects -o full_schema.sql

Adopting pgmold in an Existing Project

Use pgmold dump to create a baseline from a live database:

# Export current database schema to SQL files
pgmold dump -d postgres://localhost/mydb -o schema/baseline.sql

# For specific schemas only
pgmold dump -d postgres://localhost/mydb --target-schemas public,auth -o schema/baseline.sql

# Split into multiple files by object type
pgmold dump -d postgres://localhost/mydb --split -o schema/

The --split option creates separate files for extensions, types, sequences, tables, functions, views, triggers, and policies.

After this, your schema files match the database exactly and pgmold plan shows zero operations.

Workflow After Baseline

Make changes by editing the SQL schema files
Preview with pgmold plan -s sql:schema/ -d postgres://localhost/mydb
Apply with pgmold apply -s sql:schema/ -d postgres://localhost/mydb

Integrating with Existing Migration Systems

pgmold is declarative -- it computes diffs and applies directly. To maintain compatibility with an existing migration system:

# Generate a numbered migration file automatically
pgmold migrate \
  -s sql:schema/ \
  -d postgres://localhost/mydb \
  --migrations ./migrations \
  --name "add_email_column"
# Creates: migrations/0044_add_email_column.sql

# Or manually capture output
pgmold diff --from sql:current.sql --to sql:schema/ > migrations/0044_my_change.sql

The migrate command auto-detects the next migration number. Use pgmold for diffing while keeping your existing migration runner.

CI Integration

pgmold includes a GitHub Action for schema CI: migration plan comments, drift detection, PR auto-labeling, and warning annotations.

GitHub Action Usage

- uses: fmguerreiro/pgmold/.github/actions/drift-check@main
  with:
    schema: sql:schema/
    database: db:${{ secrets.DATABASE_URL }}
    target-schemas: public,auth

The action runs in two modes:

Live database mode: Requires database. Generates a migration plan, posts it as a PR comment, and optionally checks for drift.
SQL-to-SQL baseline mode: Requires baseline. Diffs schema against a baseline SQL file — no live database needed.

Key inputs:

schema (required): Schema source(s), space-separated.
database: PostgreSQL connection string. Required unless baseline is set.
baseline: sql:path/to/baseline.sql for SQL-to-SQL diff mode.
target-schemas: Comma-separated PostgreSQL schemas. Default: public.
fail-on-drift: Fail if drift detected. Default: true.
plan-comment: Post migration plan as a PR comment. Default: true.
drift-check: Run drift detection. Default: true.
auto-label: Add database-schema label to the PR when schema changes are detected. Default: true.

Outputs:

has-drift: Whether drift was detected (true/false).
expected-fingerprint: Expected schema fingerprint from SQL files.
actual-fingerprint: Actual schema fingerprint from database.
report: Full JSON drift report.
plan-json: Full plan JSON output.
statement-count: Number of SQL statements in the migration plan.
has-destructive: Whether the plan contains destructive operations.
comment-id: ID of the PR comment posted or updated.

See .github/actions/drift-check/README.md for full documentation and .github/workflows/examples/schema-check.yml for a complete example.

CLI Drift Detection

For local or custom CI environments, use the drift command directly:

# Get JSON report with exit code 1 if drift detected
pgmold drift -s sql:schema/ -d postgres://localhost/mydb --json

Output:

{
  "has_drift": true,
  "expected_fingerprint": "abc123...",
  "actual_fingerprint": "def456...",
  "differences": ["AddColumn { schema: \"public\", table: \"users\", ... }"]
}

Drift detection compares SHA256 fingerprints of normalized schemas. Any difference triggers drift.

Terraform Provider

pgmold is available as a Terraform provider for infrastructure-as-code workflows.

Installation

terraform {
  required_providers {
    pgmold = {
      source  = "fmguerreiro/pgmold"
      version = "~> 0.3"
    }
  }
}

provider "pgmold" {}

Usage

resource "pgmold_schema" "app" {
  schema_file       = "${path.module}/schema.sql"
  database_url      = var.database_url
  allow_destructive = false  # Set true to allow DROP operations
}

Terraform diffs against the live database and applies only necessary migrations on changes.

Attributes

Name	Type	Required	Description
`schema_file`	string	yes	Path to SQL schema file
`database_url`	string	yes	PostgreSQL connection URL
`target_schemas`	list(string)	no	PostgreSQL schemas to manage (default: `["public"]`)
`allow_destructive`	bool	no	Allow DROP operations (default: `false`)

Computed attributes:

id - Resource identifier
schema_hash - SHA256 hash of schema file
applied_at - Timestamp of last migration
migration_count - Number of operations applied

Migration Resource

Generate numbered migration files instead of applying directly:

resource "pgmold_migration" "app" {
  schema_file  = "${path.module}/schema.sql"
  database_url = var.database_url
  output_dir   = "${path.module}/migrations"
  prefix       = "V"  # Flyway-style prefix
}

Safety Rules

By default, pgmold blocks destructive operations:

DROP TABLE, DROP COLUMN, DROP ENUM require --allow-destructive
Type narrowing and SET NOT NULL produce warnings

Set PGMOLD_PROD=1 for production mode, which blocks table drops entirely.

Comparison with Other Tools

vs Declarative Schema-as-Code Tools

These tools share pgmold's approach: define desired state, compute diffs automatically.

Feature	pgmold	Atlas	pg-schema-diff	pgschema
Language	Rust	Go	Go	Go
Schema Format	Native SQL	HCL, SQL, ORM	Native SQL	SQL
Multi-DB Support	PostgreSQL	✅ Many	PostgreSQL	PostgreSQL
Drift Detection	✅	✅	❌	❌
Lock Hazard Warnings	✅	✅	✅	❌
Safety Linting	✅	✅	❌	❌
RLS Policies	✅	✅	❌	❌
Partitioned Tables	✅	✅	✅	?
Cloud Service	❌	Atlas Cloud	❌	❌
Library Mode	❌	❌	✅	❌

vs Migration-Based Tools

Traditional tools where you write numbered migration files manually.

Feature	pgmold	Flyway	Liquibase	Sqitch
Approach	Declarative	Versioned	Versioned	Plan-based
Auto-generates Migrations	✅	❌	❌	❌
Multi-DB Support	PostgreSQL	✅ Many	✅ Many	✅ Many
Drift Detection	✅	✅ (preview)	✅	❌
Rollback Scripts	Auto (reverse diff)	Manual	Manual	Required
Enterprise Features	❌	Teams edition	Pro edition	❌

When to Choose pgmold

Pure SQL schemas -- no HCL or DSLs to learn
PostgreSQL-only projects needing deep PG integration
Single binary -- no JVM/Go runtime required
CI/CD drift detection
Safety-first workflows with destructive operation guardrails
RLS policies as first-class citizens

When to Choose Alternatives

Multi-database support → Atlas, Flyway, Liquibase
HCL/Terraform-style syntax → Atlas
Embeddable Go library → pg-schema-diff
Zero-downtime migrations → pgroll, Reshape
Enterprise compliance/audit → Liquibase, Bytebase
Managed cloud service → Atlas Cloud

Development

# Build
cargo build

# Test
cargo test

# Run integration tests (requires Docker)
cargo test --test integration

License

MIT

Name		Name	Last commit message	Last commit date
Latest commit History 517 Commits
.cargo		.cargo
.github		.github
benches		benches
docs		docs
site		site
src		src
tests		tests
.gitignore		.gitignore
AGENTS.md		AGENTS.md
ARCHITECTURE.md		ARCHITECTURE.md
Cargo.lock		Cargo.lock
Cargo.toml		Cargo.toml
LICENSE		LICENSE
README.md		README.md
SHARED_TASK_NOTES.md		SHARED_TASK_NOTES.md
logo.png		logo.png

Folders and files

Latest commit

History

Repository files navigation

pgmold

Features

How pgmold Works

Installation

Quick Start

Usage

Guides

Multi-File Schemas

Filtering Objects

Extension Objects

Adopting pgmold in an Existing Project

Workflow After Baseline

Integrating with Existing Migration Systems

CI Integration

GitHub Action Usage

CLI Drift Detection

Terraform Provider

Installation

Usage

Attributes

Migration Resource

Safety Rules

Comparison with Other Tools

vs Declarative Schema-as-Code Tools

vs Migration-Based Tools

When to Choose pgmold

When to Choose Alternatives

Development

License

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases 129

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Packages