generate

The generate command compares your current and desired schemas and produces golang-migrate compatible migration files. It automatically orders operations based on dependencies and generates both up and down migrations.

Usage

pgtofu generate [flags]

Flags

Flag	Description	Default
`--current`	Path to current schema JSON file (from `extract`)	Required
`--desired`	Path to desired schema SQL file or directory	Required
`--output-dir`	Output directory for migration files	`./migrations`
`--preview`	Preview migrations without writing files	`false`
`--start-version`	Starting version number	Auto-detect
`--help`, `-h`	Help for generate

Examples

Basic Generation

# Generate migrations
pgtofu generate \
  --current current-schema.json \
  --desired ./schema \
  --output-dir ./migrations

Preview Mode

Preview what migrations would be created without writing files:

pgtofu generate \
  --current current-schema.json \
  --desired ./schema \
  --preview

Custom Starting Version

pgtofu generate \
  --current current-schema.json \
  --desired ./schema \
  --start-version 10

Docker

docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  accented/pgtofu:latest generate \
  --current current-schema.json \
  --desired ./schema \
  --output-dir ./migrations

Output Files

Generated migration files follow the golang-migrate naming convention:

migrations

000001_add_users_table.up.sql

000001_add_users_table.down.sql

000002_add_orders_table.up.sql

000002_add_orders_table.down.sql

000003_add_indexes.up.sql

000003_add_indexes.down.sql

File Format

Each migration file includes:

Header with metadata (timestamp, description)
List of changes included
Transaction control (BEGIN/COMMIT)
Idempotent DDL statements

Example up migration:

-- =====================================================
-- Migration: 000001_add_users_table.up.sql
-- Generated: 2024-01-15T10:30:00Z
-- Generated by pgtofu
-- =====================================================
--
-- Changes:
--   ADD_TABLE: Add table: public.users
--   ADD_INDEX: Add index: idx_users_email
--
-- =====================================================

BEGIN;

CREATE TABLE IF NOT EXISTS public.users (
    id bigserial NOT NULL,
    email character varying(255) NOT NULL,
    name character varying(100),
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_key UNIQUE (email)
);

CREATE INDEX IF NOT EXISTS idx_users_email ON public.users (email);

COMMIT;

Example down migration:

-- =====================================================
-- Migration: 000001_add_users_table.down.sql
-- Generated: 2024-01-15T10:30:00Z
-- Generated by pgtofu
-- =====================================================
--
-- Reverses:
--   ADD_TABLE: Add table: public.users
--   ADD_INDEX: Add index: idx_users_email
--
-- =====================================================

BEGIN;

DROP INDEX IF EXISTS public.idx_users_email;

DROP TABLE IF EXISTS public.users;

COMMIT;

Idempotent DDL

All generated DDL statements are idempotent by default:

Statement Type	Idempotent Form
CREATE TABLE	`CREATE TABLE IF NOT EXISTS`
DROP TABLE	`DROP TABLE IF EXISTS`
CREATE INDEX	`CREATE INDEX IF NOT EXISTS`
DROP INDEX	`DROP INDEX IF EXISTS`
CREATE EXTENSION	`CREATE EXTENSION IF NOT EXISTS`
DROP EXTENSION	`DROP EXTENSION IF EXISTS`

Transaction Control

pgtofu wraps migrations in transactions when safe:

Operation	Transaction
DDL statements	Yes
CREATE INDEX CONCURRENTLY	No (cannot run in transaction)
TimescaleDB operations	Depends on operation

Some operations cannot run inside a transaction. pgtofu automatically handles this by splitting migrations or adding appropriate comments.

Change Ordering

pgtofu automatically orders operations based on dependencies:

Extensions - Must be created first
Custom Types - Enums, composites, domains
Sequences - Before tables that use them
Tables - In dependency order (referenced tables first)
Constraints - After tables exist
Indexes - After tables exist
Views - After tables they reference
Functions - After types and tables they use
Triggers - After functions and tables
TimescaleDB - Hypertables, policies, continuous aggregates

For DROP operations, the order is reversed.

Version Auto-Detection

When --start-version is not specified, pgtofu scans the output directory for existing migration files and continues from the next version:

# If migrations/ contains 000001_*.sql through 000005_*.sql
pgtofu generate ... --output-dir ./migrations
# Creates 000006_*.sql

Preview Mode

Use --preview to see what would be generated without writing files:

pgtofu generate \
  --current current.json \
  --desired ./schema \
  --preview

Output:

Migration Preview
=================

Would generate 3 migration pairs:

000001_add_extensions.up.sql:
  - ADD_EXTENSION: Add extension: uuid-ossp

000002_add_tables.up.sql:
  - ADD_TABLE: Add table: public.users
  - ADD_TABLE: Add table: public.orders

000003_add_indexes.up.sql:
  - ADD_INDEX: Add index: idx_users_email
  - ADD_INDEX: Add index: idx_orders_user_id

Total: 5 changes in 3 migration pairs

Handling Breaking Changes

When breaking changes are detected, pgtofu:

Includes a warning comment in the migration
Generates the DDL but marks it as requiring review
Displays a summary of breaking changes

-- !! WARNING: BREAKING CHANGE !!
-- This operation will DROP TABLE public.legacy_users
-- Ensure this is intentional and data has been migrated.

DROP TABLE IF EXISTS public.legacy_users;

Troubleshooting

No migrations generated

If no changes are detected:

Run pgtofu diff first to verify changes exist
Check that schema names match (default: public)

Circular dependency error

This usually indicates a design issue in your schema:

Check for circular foreign key references
Consider using deferrable constraints

Permission denied writing files

Ensure the output directory is writable:

mkdir -p ./migrations
chmod 755 ./migrations

Applying Migrations

Use golang-migrate to apply generated migrations:

# Apply all pending migrations
migrate -path ./migrations -database "$DATABASE_URL" up

# Apply specific number of migrations
migrate -path ./migrations -database "$DATABASE_URL" up 1

# Rollback last migration
migrate -path ./migrations -database "$DATABASE_URL" down 1

# Check current version
migrate -path ./migrations -database "$DATABASE_URL" version

CLI Reference

Usage

Flags

Examples

Basic Generation

Preview Mode

Custom Starting Version

Docker

Output Files

File Format

Idempotent DDL

Transaction Control

Change Ordering

Version Auto-Detection

Preview Mode

Handling Breaking Changes

Troubleshooting

Applying Migrations

See Also

​Usage

​Flags

​Examples

​Basic Generation

​Preview Mode

​Custom Starting Version

​Docker

​Output Files

​File Format

​Idempotent DDL

​Transaction Control

​Change Ordering

​Version Auto-Detection

​Preview Mode

​Handling Breaking Changes

​Troubleshooting

​Applying Migrations

​See Also

Usage

Flags

Examples

Basic Generation

Preview Mode

Custom Starting Version

Docker

Output Files

File Format

Idempotent DDL

Transaction Control

Change Ordering

Version Auto-Detection

Preview Mode

Handling Breaking Changes

Troubleshooting

Applying Migrations

See Also