scheme

README

scheme

Declarative PostgreSQL schema migration tool. Define your desired schema in SQL, and scheme computes the diff against a live database — then applies it.

Inspired by Atlas and Terraform's declarative approach: you describe what the schema should look like, not how to get there.

How it works

1. You write a schema.sql file describing your desired database schema
2. scheme loads your SQL into a clean PostgreSQL database to normalize it — either an ephemeral Docker container (default) or an existing database you provide via --dev-url
3. It introspects the target database to get the current state
4. Computes the diff and generates DDL statements
5. Applies changes with per-step transactions (or saves a plan file for review)

Features

• Multi-schema — schemas are first-class objects, not just namespaces
• Extension tracking — CREATE EXTENSION statements are managed
• Per-step transactions — each DDL change runs in its own transaction
• Plan files — save migration plans to disk, review/edit, apply later
• Partial recovery — re-running a failed plan resumes from the last successful step
• Advisory locks — prevents concurrent migrations on the same database
• Docker-based normalization — accurate diffs regardless of SQL formatting
• Custom Docker images — use Citus, TimescaleDB, or any PostgreSQL-compatible image
• External dev database — skip Docker entirely by pointing to an existing clean database

Install

go install github.com/nvcnvn/scheme/cmd/scheme@latest

Or build from source:

make build

Prerequisites

• Docker (for ephemeral PostgreSQL containers) — not required if using --dev-url
• A target PostgreSQL database

Usage

Show diff

Compare schema.sql against a target database:

scheme diff --url postgres://user:pass@localhost:5432/mydb

Without --url, diffs against an empty database (shows all CREATE statements):

scheme diff

Apply changes

Compute and apply the diff directly:

scheme apply --url postgres://user:pass@localhost:5432/mydb

Flags:

• --auto-approve — skip the confirmation prompt
• --dry-run — print the plan without applying
• --allow-destructive — allow DROP statements

Plan files

Generate a plan file for review:

scheme plan --url postgres://user:pass@localhost:5432/mydb

This creates a timestamped .plan.sql file in the migrations/ directory. You can edit the file (adjust SQL, reorder steps, toggle transactions) before applying:

scheme plan apply --file migrations/20250101_120000.plan.sql --url postgres://user:pass@localhost:5432/mydb

List saved plans:

scheme plan list

Inspect

Print the current schema of a database as SQL:

scheme inspect --url postgres://user:pass@localhost:5432/mydb

Validate

Check that schema.sql is valid by loading it into a temporary PostgreSQL instance:

scheme validate

Custom Docker images

Use --image to pick any PostgreSQL-compatible Docker image for schema normalization:

# Use a specific PostgreSQL version
scheme diff --image postgres:16 --url postgres://user:pass@localhost:5432/mydb

# Use Citus
scheme diff --image citusdata/citus:12.1 --url postgres://user:pass@localhost:5432/mydb

# Use TimescaleDB
scheme diff --image timescale/timescaledb:latest-pg16 --url postgres://user:pass@localhost:5432/mydb

If the image exists locally it is used directly. Otherwise scheme pulls it from the registry.

External dev database

Use --dev-url to skip Docker and connect to an existing clean database for schema normalization. This is useful when Docker is unavailable or you need a database with custom extensions/configuration:

scheme diff --dev-url postgres://user:pass@localhost:5433/scratch_db --url postgres://user:pass@localhost:5432/mydb

The database provided via --dev-url must be clean (empty) — scheme loads your schema SQL into it for normalization.

Global flags

Flag	Default	Description
--url		Target database connection URL
--schema	schema.sql	Path to desired schema file
--image	postgres:18	Docker image for the ephemeral container
--dev-url		Use an existing clean database instead of Docker
--dir	migrations	Directory for plan files

License

See LICENSE.