Skip to main content
This guide walks you through the basic pgtofu workflow: extracting your current schema, defining your desired schema, and generating migrations.

​
Prerequisites

  • A running PostgreSQL database (local or remote)
  • Docker installed (or Go 1.25+ for building from source)
  • golang-migrate for applying migrations

​
Step 1: Install pgtofu

The quickest way to get started is with Docker: 
docker pull accented/pgtofu:latest

go install github.com/accented-ai/pgtofu/cmd/pgtofu@latest

​
Step 2: Set Up Your Database Connection

export DATABASE_URL="postgres://user:password@localhost:5432/db"
Never commit database credentials to version control. Use environment variables or a secrets manager.

​
Step 3: Extract Current Schema

Extract your database’s current schema to a JSON file: 
docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  -e DATABASE_URL="$DATABASE_URL" \
  accented/pgtofu:latest extract \
  --output current-schema.json
This creates a current-schema.json file containing a complete representation of your database schema.

​
Step 4: Define Your Desired Schema

Create a schema directory and define your desired schema using standard SQL files: 
mkdir -p schema/tables schema/functions
Create schema/tables/users.sql: 
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
Create schema/tables/orders.sql: 
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total NUMERIC(12, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
Organize your SQL files by type (tables, views, functions, etc.) for maintainability. pgtofu will automatically resolve dependencies regardless of file organization.

​
Step 5: Preview Changes

Compare your current schema with the desired schema to see what changes will be made: 
docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  accented/pgtofu:latest diff \
  --current current-schema.json \
  --desired ./schema
Example output: 
Schema Comparison Summary
========================

Changes detected: 4

SAFE Changes:
  - ADD_TABLE: Add table: public.users
  - ADD_TABLE: Add table: public.orders
  - ADD_INDEX: Add index: public.idx_users_email
  - ADD_INDEX: Add index: public.idx_orders_user_id

No breaking changes detected.

​
Step 6: Generate Migrations

Generate migration files that can be applied with golang-migrate: 
docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  accented/pgtofu:latest generate \
  --current current-schema.json \
  --desired ./schema \
  --output-dir ./migrations
This creates migration files in the migrations directory:
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
000001_add_users_table.up.sql:
-- Migration generated by pgtofu
-- Changes: ADD_TABLE (public.users)

BEGIN;

CREATE TABLE IF NOT EXISTS public.users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

COMMIT;

​
Step 7: Apply Migrations

Use golang-migrate to apply the generated migrations: 
migrate -path ./migrations -database "$DATABASE_URL" up

​
Step 8: Iterate

As your application evolves, repeat the workflow:
  1. Update your SQL files in schema/
  2. Re-extract the current schema: pgtofu extract --output current-schema.json
  3. Preview changes: pgtofu diff --current current-schema.json --desired ./schema
  4. Generate new migrations: pgtofu generate --current current-schema.json --desired ./schema
  5. Apply: migrate -path ./migrations -database "$DATABASE_URL" up
Use --preview with the generate command to see what migrations would be created without writing files.

​
Next Steps

Installation

Learn about all installation options including Docker and building from source

Core Concepts

Understand how pgtofu’s declarative approach works under the hood

CLI Reference

Complete reference for all CLI commands and options

TimescaleDB

Learn how to use pgtofu with TimescaleDB hypertables and policies