Skip to main content

​
Core Concepts

pgtofu transforms database schema management from an imperative process (writing migrations) to a declarative one (defining desired state). This page explains the key concepts that make this possible.

​
The Four Phases

pgtofu operates in four distinct phases: 
┌───────────┐    ┌────────────┐    ┌──────────┐    ┌───────────┐
│  Extract  │ -> │   Parse    │ -> │   Diff   │ -> │ Generate  │
│           │    │            │    │          │    │           │
│ Database  │    │ SQL Files  │    │ Compare  │    │ Migration │
│ -> JSON   │    │ -> JSON    │    │ Schemas  │    │ Files     │
└───────────┘    └────────────┘    └──────────┘    └───────────┘

Extract

Connect to PostgreSQL and export the current schema as JSON

Parse

Parse SQL files defining your desired schema into JSON

Diff

Compare current and desired schemas to detect changes

Generate

Create ordered migration files from detected changes

​
Declarative vs Imperative

​
Imperative Approach (Traditional)

With traditional migration tools, you manually write each change: 
-- 20240115_add_users.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255)
);

-- 20240116_add_name.up.sql
ALTER TABLE users ADD COLUMN name VARCHAR(100);

-- 20240117_add_created_at.up.sql
ALTER TABLE users ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW();
Problems:
  • Hard to see current schema state
  • Easy to miss dependencies
  • Manual ordering required
  • Merge conflicts are common

​
Declarative Approach (pgtofu)

With pgtofu, you define the desired end state: 
-- schema/tables/users.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW()
);
pgtofu automatically:
  • Compares with current database
  • Detects what changed
  • Orders changes correctly
  • Generates safe migrations

​
Schema Representation

Both extracted and parsed schemas are normalized to the same JSON format: 
{
  "tables": [{
    "schema": "public",
    "name": "users",
    "columns": [{
      "name": "id",
      "data_type": "integer",
      "is_nullable": false,
      "is_identity": true
    }, {
      "name": "email",
      "data_type": "character varying",
      "max_length": 255,
      "is_nullable": true
    }],
    "constraints": [{
      "name": "users_pkey",
      "type": "PRIMARY KEY",
      "columns": ["id"]
    }]
  }]
}
This normalized representation enables accurate comparison regardless of:
  • SQL formatting differences
  • Type alias variations (VARCHAR vs character varying)
  • Whitespace and comments

​
Change Detection

The differ compares schemas field-by-field and detects:
CategoryExamples
AdditionsNew tables, columns, indexes, constraints
DeletionsRemoved objects
ModificationsChanged types, defaults, definitions
Each change is classified by severity:
  • SAFE - Non-breaking additions
  • POTENTIALLY_BREAKING - May affect applications
  • BREAKING - May cause data loss
  • DATA_MIGRATION_REQUIRED - Cannot be automated

​
Dependency Resolution

pgtofu uses topological sorting to order changes correctly: 
Extension: uuid-ossp
    └── Type: uuid (from extension)
        └── Table: users
            ├── Column: id (uuid type)
            └── Index: idx_users_id
                └── Foreign Key: orders.user_id
Changes are ordered so dependencies are created before dependents, and dropped in reverse order.

​
Migration Generation

The generator creates golang-migrate compatible files with:
  • Idempotent DDL - IF EXISTS / IF NOT EXISTS clauses
  • Transaction control - Wrapped in BEGIN/COMMIT when safe
  • Reversibility - Both up and down migrations generated
  • Safety warnings - Breaking changes are flagged

​
Key Principles

Your SQL files in version control are the authoritative schema definition. The database state is derived, not authoritative.
pgtofu prioritizes generating safe, correct migrations over minimizing migration count or simplifying output.
All schema objects must be explicitly defined. pgtofu doesn’t infer or assume schema elements.
Given the same current schema and desired schema, pgtofu always generates the same migrations.

​
Next Steps

Declarative Migrations

Learn how to structure your schema files

Schema Extraction

Understand how schemas are extracted from databases

Schema Diffing

Deep dive into change detection

Dependency Resolution

Learn how changes are ordered