> ## Documentation Index
> Fetch the complete documentation index at: https://pgtofu.com/llms.txt
> Use this file to discover all available pages before exploring further.

# generate

> Generate migration files from schema differences

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

```bash theme={null}
pgtofu generate [flags]
```

## Flags

| Flag              | Description                                       | Default        |
| ----------------- | ------------------------------------------------- | -------------- |
| `--current`       | Path to current schema JSON file (from `extract`) | Required       |
| `--desired`       | Path to desired schema SQL file or directory      | Required       |
| `--output-dir`    | Output directory for migration files              | `./migrations` |
| `--preview`       | Preview migrations without writing files          | `false`        |
| `--start-version` | Starting version number                           | Auto-detect    |
| `--help`, `-h`    | Help for generate                                 |                |

## Examples

### Basic Generation

```bash theme={null}
# Generate migrations
pgtofu generate \
  --current current-schema.json \
  --desired ./schema \
  --output-dir ./migrations
```

### Preview Mode

Preview what migrations would be created without writing files:

```bash theme={null}
pgtofu generate \
  --current current-schema.json \
  --desired ./schema \
  --preview
```

### Custom Starting Version

```bash theme={null}
pgtofu generate \
  --current current-schema.json \
  --desired ./schema \
  --start-version 10
```

### Docker

```bash theme={null}
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:

<Tree>
  <Tree.Folder name="migrations" defaultOpen>
    <Tree.File name="000001_add_users_table.up.sql" />

    <Tree.File name="000001_add_users_table.down.sql" />

    <Tree.File name="000002_add_orders_table.up.sql" />

    <Tree.File name="000002_add_orders_table.down.sql" />

    <Tree.File name="000003_add_indexes.up.sql" />

    <Tree.File name="000003_add_indexes.down.sql" />
  </Tree.Folder>
</Tree>

### 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:**

```sql theme={null}
-- =====================================================
-- 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:**

```sql theme={null}
-- =====================================================
-- 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 Type   | 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`           |
| CREATE EXTENSION | `CREATE EXTENSION IF NOT EXISTS` |
| DROP EXTENSION   | `DROP EXTENSION IF EXISTS`       |

## Transaction Control

pgtofu wraps migrations in transactions when safe:

| Operation                 | Transaction                    |
| ------------------------- | ------------------------------ |
| DDL statements            | Yes                            |
| CREATE INDEX CONCURRENTLY | No (cannot run in transaction) |
| TimescaleDB operations    | Depends on operation           |

<Warning>
  Some operations cannot run inside a transaction. pgtofu automatically handles this by splitting migrations or adding appropriate comments.
</Warning>

## 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:

```bash theme={null}
# 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:

```bash theme={null}
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

```sql theme={null}
-- !! 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

<AccordionGroup>
  <Accordion title="No migrations generated">
    If no changes are detected:

    * Run `pgtofu diff` first to verify changes exist
    * Check that schema names match (default: `public`)
  </Accordion>

  <Accordion title="Circular dependency error">
    This usually indicates a design issue in your schema:

    * Check for circular foreign key references
    * Consider using deferrable constraints
  </Accordion>

  <Accordion title="Permission denied writing files">
    Ensure the output directory is writable:

    ```bash theme={null}
    mkdir -p ./migrations
    chmod 755 ./migrations
    ```
  </Accordion>
</AccordionGroup>

## Applying Migrations

Use golang-migrate to apply generated migrations:

```bash theme={null}
# 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

* [`extract`](/cli/extract) - Extract current database schema
* [`diff`](/cli/diff) - Preview changes before generating
* [Dependency Resolution](/concepts/dependency-resolution) - How operations are ordered
* [CI/CD Integration](/workflows/ci-cd-integration) - Automate migration generation