Quickstart

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

Alternative: Install from source

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
Binary

docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  -e DATABASE_URL="$DATABASE_URL" \
  accented/pgtofu:latest extract \
  --output current-schema.json

pgtofu 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
Binary

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

pgtofu 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
Binary

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

pgtofu 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

View generated migration content

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:

Update your SQL files in schema/
Re-extract the current schema: pgtofu extract --output current-schema.json
Preview changes: pgtofu diff --current current-schema.json --desired ./schema
Generate new migrations: pgtofu generate --current current-schema.json --desired ./schema
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

Getting Started

Core Concepts

Features

Workflows

Development

Prerequisites

Step 1: Install pgtofu

Step 2: Set Up Your Database Connection

Step 3: Extract Current Schema

Step 4: Define Your Desired Schema

Step 5: Preview Changes

Step 6: Generate Migrations

Step 7: Apply Migrations

Step 8: Iterate

Next Steps

Installation

Core Concepts

CLI Reference

TimescaleDB

​Prerequisites

​Step 1: Install pgtofu

​Step 2: Set Up Your Database Connection

​Step 3: Extract Current Schema

​Step 4: Define Your Desired Schema

​Step 5: Preview Changes

​Step 6: Generate Migrations

​Step 7: Apply Migrations

​Step 8: Iterate

​Next Steps

Installation

Core Concepts

CLI Reference

TimescaleDB

Prerequisites

Step 1: Install pgtofu

Step 2: Set Up Your Database Connection

Step 3: Extract Current Schema

Step 4: Define Your Desired Schema

Step 5: Preview Changes

Step 6: Generate Migrations

Step 7: Apply Migrations

Step 8: Iterate

Next Steps