Skip to main content

​
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

FlagDescriptionDefault
--currentPath to current schema JSON file (from extract)Required
--desiredPath to desired schema SQL file or directoryRequired
--output-dirOutput directory for migration files./migrations
--previewPreview migrations without writing filesfalse
--start-versionStarting version numberAuto-detect
--help, -hHelp 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 TypeIdempotent Form
CREATE TABLECREATE TABLE IF NOT EXISTS
DROP TABLEDROP TABLE IF EXISTS
CREATE INDEXCREATE INDEX IF NOT EXISTS
DROP INDEXDROP INDEX IF EXISTS
CREATE EXTENSIONCREATE EXTENSION IF NOT EXISTS
DROP EXTENSIONDROP EXTENSION IF EXISTS

​
Transaction Control

pgtofu wraps migrations in transactions when safe:
OperationTransaction
DDL statementsYes
CREATE INDEX CONCURRENTLYNo (cannot run in transaction)
TimescaleDB operationsDepends 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:
  1. Extensions - Must be created first
  2. Custom Types - Enums, composites, domains
  3. Sequences - Before tables that use them
  4. Tables - In dependency order (referenced tables first)
  5. Constraints - After tables exist
  6. Indexes - After tables exist
  7. Views - After tables they reference
  8. Functions - After types and tables they use
  9. Triggers - After functions and tables
  10. 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:
  1. Includes a warning comment in the migration
  2. Generates the DDL but marks it as requiring review
  3. 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

If no changes are detected:
  • Run pgtofu diff first to verify changes exist
  • Check that schema names match (default: public)
This usually indicates a design issue in your schema:
  • Check for circular foreign key references
  • Consider using deferrable constraints
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

​
See Also