Incremental Migrations

The Migrate medium applies incremental schema changes to existing databases. When you can’t drop and rebuild (production, staging with real data), migrations let you evolve the schema while preserving data.

How It Works

Each migration is a pair of SQL files — one to apply the change (up), one to reverse it (down):

# Generate a new migration
confiture migrate generate add_bio_to_users

This creates two files:

db/migrations/001_add_bio_to_users.up.sql

ALTER TABLE tb_user ADD COLUMN bio TEXT;

db/migrations/001_add_bio_to_users.down.sql

ALTER TABLE tb_user DROP COLUMN bio;

Commands

# Apply all pending migrations
confiture migrate up --env production

# Rollback the last migration
confiture migrate down --env production

# Check migration status
confiture migrate status --env production

# Dry run — show what would be applied
confiture migrate up --env production --dry-run

# Apply a specific number of migrations
confiture migrate up --env production --steps 3

Migration Status

confiture migrate status --env production

Migration                        Status      Applied At
001_add_bio_to_users            ✓ applied   2026-01-15 10:30:00
002_add_avatar_url              ✓ applied   2026-01-20 14:22:00
003_create_tb_comment           ✗ pending   —
004_add_comment_views           ✗ pending   —

Add --format json for machine-readable output:

confiture migrate status --env production --format json

{
  "tracking_table": "public.tb_confiture",
  "applied": ["001", "002"],
  "pending": ["003", "004"],
  "migrations": [
    {"version": "001", "name": "add_bio_to_users", "status": "applied", "applied_at": "2026-01-15T10:30:00+00:00"},
    {"version": "003", "name": "create_tb_comment", "status": "pending", "applied_at": null}
  ],
  "summary": {"applied": 2, "pending": 2, "total": 4}
}

migrate status exits with a semantic exit code for use in deployment scripts:

Exit code	Meaning
0	All migrations applied — database is up to date
1	Pending migrations exist
2	Migrations table not initialised (migrate up needed)
3	Connection error — check config

confiture migrate status -c prod.yaml
case $? in
  0) echo "Up to date" ;;
  1) confiture migrate up -c prod.yaml ;;
  2) echo "Run migrate up to initialise tracking table" ;;
  3) echo "Connection error — check your config" ;;
esac

Writing Migrations

Adding Columns

up.sql

ALTER TABLE tb_user ADD COLUMN avatar_url TEXT;

down.sql

ALTER TABLE tb_user DROP COLUMN avatar_url;

Creating Tables

up.sql

CREATE TABLE tb_comment (
    pk_comment INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT UNIQUE NOT NULL,
    fk_user INTEGER NOT NULL REFERENCES tb_user(pk_user),
    fk_post INTEGER NOT NULL REFERENCES tb_post(pk_post),
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

down.sql

DROP TABLE tb_comment;

Adding Indexes

up.sql

CREATE INDEX idx_tb_post_fk_user ON tb_post(fk_user);
CREATE INDEX idx_tb_post_slug ON tb_post(slug);

down.sql

DROP INDEX idx_tb_post_fk_user;
DROP INDEX idx_tb_post_slug;

Updating Views

When you add a column to a table, you typically also update the corresponding view:

up.sql

-- Add column to table
ALTER TABLE tb_user ADD COLUMN avatar_url TEXT;

-- Recreate view to include new field
CREATE OR REPLACE VIEW v_user AS
SELECT
    u.id,
    jsonb_build_object(
        'id', u.id::text,
        'name', u.name,
        'email', u.email,
        'avatar_url', u.avatar_url  -- New field
    ) AS data
FROM tb_user u;

down.sql

-- Recreate view without new field
CREATE OR REPLACE VIEW v_user AS
SELECT
    u.id,
    jsonb_build_object(
        'id', u.id::text,
        'name', u.name,
        'email', u.email
    ) AS data
FROM tb_user u;

-- Drop column
ALTER TABLE tb_user DROP COLUMN avatar_url;

Data Migrations

For migrations that transform existing data:

up.sql

-- Add column
ALTER TABLE tb_user ADD COLUMN display_name TEXT;

-- Populate from existing data
UPDATE tb_user SET display_name = name WHERE display_name IS NULL;

-- Make non-nullable after population
ALTER TABLE tb_user ALTER COLUMN display_name SET NOT NULL;

Migration File Naming

Migrations are auto-numbered with a 3-digit prefix:

db/migrations/
├── 001_initial_schema.up.sql
├── 001_initial_schema.down.sql
├── 002_add_bio_to_users.up.sql
├── 002_add_bio_to_users.down.sql
├── 003_create_tb_comment.up.sql
└── 003_create_tb_comment.down.sql

Confiture auto-increments the version number when you run confiture migrate generate.

Python Library API

Confiture can be used as a Python library — no CLI required:

from confiture import Migrator

with Migrator.from_config("db/environments/prod.yaml") as m:
    result = m.status()
    print(f"{result.summary['pending']} pending migrations")
    if result.has_pending:
        up_result = m.up()
        print(f"Applied {len(up_result.applied)} migrations")

Key classes and methods:

Symbol	Description
Migrator.from_config(config)	Accepts an Environment, Path, or str path to a config file
MigratorSession	Context manager — connection is always closed on exit
.status() → StatusResult	Check migration state
.up(**kwargs) → MigrateUpResult	Apply pending migrations
.down(steps, dry_run) → MigrateDownResult	Roll back migrations
.reinit(through, dry_run) → MigrateReinitResult	Re-initialise tracking

migrate up JSON output (v0.6.2+):

{
  "applied": ["003", "004"],
  "skipped": [],
  "errors": [],
  "total_duration_ms": 412
}

Note: the keys migrations_applied, total_execution_time_ms, and execution_time_ms (per migration) were renamed to applied, total_duration_ms, and duration_ms in v0.6.2. Update any scripts that parse this output.

---

Build + Migrate Together

In practice, you maintain both DDL files and migrations:

• DDL files (db/schema/) — the current state of your schema (used by confiture build)
• Migration files (db/migrations/) — the changes applied to existing databases (used by confiture migrate)

Tip

When you create a migration, also update the corresponding DDL file so that confiture build always produces the same result as running all migrations from scratch.

When to Use Migrate

Use Migrate for

• Production databases with data you can’t lose
• Staging environments with test data you want to preserve
• Any environment where dropping and rebuilding isn’t an option

Don't use Migrate for

• Fresh development databases — use Build instead (faster)
• Major schema refactoring on large tables — use Schema-to-Schema instead (zero downtime)

Next Steps

Confiture Overview

All 4 Mediums and when to use each one.

Build from DDL

For fresh databases — faster than replaying migrations.

Schema-to-Schema

For zero-downtime refactoring of large production tables.

Production Data Sync

Copy production data locally with built-in anonymization.