Migration Generation

The generation phase takes the detected changes from diffing and produces golang-migrate compatible migration files. pgtofu generates both “up” migrations (apply changes) and “down” migrations (revert changes).

Generation Pipeline

┌─────────────────────────────────────────────────────────┐
│                    Generator                            │
├─────────────────────────────────────────────────────────┤
│  1. Order changes by dependencies                       │
│  2. Group changes into migration files                  │
│  3. Generate DDL for each change                        │
│  4. Add transaction control                             │
│  5. Write migration files                               │
└─────────────────────────────────────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────────────────────┐
│              Migration Files                            │
├─────────────────────────────────────────────────────────┤
│  000001_add_extensions.up.sql                           │
│  000001_add_extensions.down.sql                         │
│  000002_add_tables.up.sql                               │
│  000002_add_tables.down.sql                             │
│  ...                                                    │
└─────────────────────────────────────────────────────────┘

DDL Generation

Registry Pattern

pgtofu uses a registry that maps change types to DDL builder functions:

// Simplified registry pattern
var ddlBuilders = map[ChangeType]DDLBuilder{
    ChangeTypeAddTable:    buildAddTable,
    ChangeTypeDropTable:   buildDropTable,
    ChangeTypeAddColumn:   buildAddColumn,
    ChangeTypeDropColumn:  buildDropColumn,
    // ... 40+ change types
}

func generateDDL(change Change) DDLStatement {
    builder := ddlBuilders[change.Type]
    return builder(change)
}

DDL Statement Structure

type DDLStatement struct {
    SQL         string  // The DDL statement
    Description string  // Human-readable description
    IsUnsafe    bool    // Requires manual review
    RequiresTx  bool    // Must run in transaction
    CannotUseTx bool    // Cannot run in transaction
}

Generated DDL Examples

Tables

Add Table:

CREATE TABLE IF NOT EXISTS public.users (
    id bigint GENERATED ALWAYS AS IDENTITY 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)
);

Drop Table:

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

DROP TABLE IF EXISTS public.users;

Columns

Add Column:

ALTER TABLE public.users
    ADD COLUMN phone character varying(20);

Add Column with NOT NULL:

-- Adding NOT NULL column requires a default value
ALTER TABLE public.users
    ADD COLUMN status character varying(20) NOT NULL DEFAULT 'active';

Drop Column:

-- !! WARNING: BREAKING CHANGE !!
ALTER TABLE public.users
    DROP COLUMN IF EXISTS legacy_field;

Modify Column Type (safe widening):

ALTER TABLE public.users
    ALTER COLUMN name TYPE character varying(200);

Indexes

Add Index:

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

Add Partial Index:

CREATE INDEX IF NOT EXISTS idx_users_active
    ON public.users (email)
    WHERE status = 'active';

Add Covering Index:

CREATE INDEX IF NOT EXISTS idx_orders_user
    ON public.orders (user_id)
    INCLUDE (status, total);

Constraints

Add Foreign Key:

ALTER TABLE public.orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES public.users(id)
    ON DELETE CASCADE;

Add Check Constraint:

ALTER TABLE public.orders
    ADD CONSTRAINT chk_orders_total_positive
    CHECK (total >= 0);

Views

Add View:

CREATE OR REPLACE VIEW public.active_users AS
SELECT id, email, name
FROM public.users
WHERE status = 'active';

Modify Materialized View:

-- Materialized views cannot be altered; must drop and recreate
DROP MATERIALIZED VIEW IF EXISTS public.monthly_stats;

CREATE MATERIALIZED VIEW public.monthly_stats AS
SELECT date_trunc('month', created_at) AS month,
       count(*) AS order_count,
       sum(total) AS revenue
FROM public.orders
GROUP BY 1;

Functions

Add/Modify Function:

CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$function$;

TimescaleDB

Create Hypertable:

SELECT create_hypertable(
    'public.metrics',
    'time',
    chunk_time_interval => INTERVAL '1 day'
);

Add Compression Policy:

ALTER TABLE public.metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('public.metrics', INTERVAL '7 days');

Migration File Format

File Naming

Files follow golang-migrate convention:

{version}_{description}.{direction}.sql

version: 6-digit zero-padded number (000001, 000002, …)
description: Sanitized description (lowercase, underscores)
direction: up or down

Examples:

000001_add_extensions.up.sql
000001_add_extensions.down.sql
000002_create_users_table.up.sql
000002_create_users_table.down.sql

File Structure

-- =====================================================
-- 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;

-- ADD_TABLE: Add table: public.users
CREATE TABLE IF NOT EXISTS public.users (
    id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
    email character varying(255) NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

-- ADD_INDEX: Add index: idx_users_email
CREATE INDEX IF NOT EXISTS idx_users_email ON public.users (email);

COMMIT;

Transaction Control

Wrapped in Transactions

Most DDL operations are wrapped in transactions:

BEGIN;

-- DDL statements here

COMMIT;

This ensures atomicity—if any statement fails, all changes are rolled back.

Cannot Use Transactions

Some operations cannot run inside transactions:

Operation	Reason
`CREATE INDEX CONCURRENTLY`	PostgreSQL limitation
`DROP INDEX CONCURRENTLY`	PostgreSQL limitation
`CREATE DATABASE`	PostgreSQL limitation
Some TimescaleDB operations	Extension limitation

For these, pgtofu generates separate migration files without transaction wrapping.

Idempotent DDL

All generated DDL is idempotent by default:

Statement	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`
ADD COLUMN	Check column existence first
DROP COLUMN	`DROP COLUMN IF EXISTS`

This allows safe re-running of migrations.

Down Migrations

Down migrations reverse up migrations:

Up Migration	Down Migration
CREATE TABLE	DROP TABLE
ADD COLUMN	DROP COLUMN
CREATE INDEX	DROP INDEX
ADD CONSTRAINT	DROP CONSTRAINT
CREATE FUNCTION	DROP FUNCTION

Limitations

Some changes cannot be fully reversed:

Data loss - Dropped columns/tables cannot recover data
Type narrowing - May lose precision
Dropped defaults - Original default may be unknown

pgtofu adds warnings for irreversible down migrations.

Safety Features

Breaking Change Warnings

-- !! WARNING: BREAKING CHANGE !!
-- This operation will DROP TABLE public.legacy_users
-- Ensure this is intentional and data has been migrated.
-- Review carefully before applying to production.

DROP TABLE IF EXISTS public.legacy_users;

Data Migration Indicators

-- !! DATA MIGRATION REQUIRED !!
-- Column type change from integer to text requires manual data migration.
-- The following statement may fail if data cannot be converted.

ALTER TABLE public.users
    ALTER COLUMN status TYPE text;

Safe Identifier Quoting

All identifiers are properly quoted:

-- Handles reserved words and special characters
CREATE TABLE "user" (
    "order" integer,
    "from" text,
    "table-name" text
);

Version Auto-Detection

When --start-version is not specified:

Scan output directory for existing migrations
Parse version numbers from filenames
Start from next available version

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

Getting Started

Core Concepts

Features

Workflows

Development

Migration Generation

Migration Generation

Generation Pipeline

DDL Generation

Registry Pattern

DDL Statement Structure

Generated DDL Examples

Tables

Columns

Indexes

Constraints

Views

Functions

TimescaleDB

Migration File Format

File Naming

File Structure

Transaction Control

Wrapped in Transactions

Cannot Use Transactions

Idempotent DDL

Down Migrations

Limitations

Safety Features

Breaking Change Warnings

Data Migration Indicators

Safe Identifier Quoting

Version Auto-Detection

See Also

Getting Started

Core Concepts

Features

Workflows

Development

​Migration Generation

​Generation Pipeline

​DDL Generation

​Registry Pattern

​DDL Statement Structure

​Generated DDL Examples

​Tables

​Columns

​Indexes

​Constraints

​Views

​Functions

​TimescaleDB

​Migration File Format

​File Naming

​File Structure

​Transaction Control

​Wrapped in Transactions

​Cannot Use Transactions

​Idempotent DDL

​Down Migrations

​Limitations

​Safety Features

​Breaking Change Warnings

​Data Migration Indicators

​Safe Identifier Quoting

​Version Auto-Detection

​See Also

Migration Generation

Generation Pipeline

DDL Generation

Registry Pattern

DDL Statement Structure

Generated DDL Examples

Tables

Columns

Indexes

Constraints

Views

Functions

TimescaleDB

Migration File Format

File Naming

File Structure

Transaction Control

Wrapped in Transactions

Cannot Use Transactions

Idempotent DDL

Down Migrations

Limitations

Safety Features

Breaking Change Warnings

Data Migration Indicators

Safe Identifier Quoting

Version Auto-Detection

See Also