Why this exists

This chapter covers the core features and capabilities of Database Migration Toolkit.

Why this exists

The SQL in a schema migration is rarely the hard part. The hard parts are:

• Two code versions hit the database at once during a rolling deploy — the schema has

to be valid for *both*.

• A single ALTER or UPDATE can lock or rewrite a huge table and stall production.
• Rolling back is often impossible (data's gone) or unsafe (DDL auto-commits on MySQL).
• Teams collide on version numbers and silently edit already-applied migrations.

This toolkit gives you a concrete, working model for all four, with the trade-offs written

down.

Features

• runner/migrate.py — a ~480-line, stdlib-only migration runner: ordered

up/down, a schema_migrations tracking table, SHA-256 checksums that detect a

changed-after-apply migration, status, validate, and a new scaffolder. Each

migration runs in one transaction.

• Drives 3 engines through one thin seam: SQLite with zero setup (default), plus

PostgreSQL and MySQL by passing a --url (install that driver).

• A proven expand/contract example — four bundled migrations that split a full_name

column into first_name/last_name with zero downtime, fully reversible end to end.

• Production PostgreSQL DDL — the same change with CREATE INDEX CONCURRENTLY-class

concerns, a transition trigger, a batched backfill procedure, and the lock-light

NOT NULL technique.

• A reusable batched-backfill template (PostgreSQL + MySQL) using keyset pagination.
• Six field guides covering versioning, zero-downtime expand/contract, online schema

change per engine, the rollback playbook, large-table backfills, and a cross-engine

cheat sheet — plus a reviewer's checklist for migration PRs.

What's inside

database-migration-toolkit/
├── README.md
├── LICENSE
├── runner/
│   └── migrate.py                       # the stdlib migration runner (CLI)
├── migrations/                          # runnable on SQLite with zero setup
│   ├── V001__create_core_schema.up.sql / .down.sql
│   ├── V002__add_indexes.up.sql / .down.sql
│   ├── V003__expand_split_customer_name.up.sql / .down.sql   # EXPAND
│   └── V004__contract_drop_full_name.up.sql / .down.sql      # CONTRACT
├── examples/
│   ├── backfill_batched.sql             # reusable keyset-batched backfill (PG + MySQL)
│   └── postgres_expand_contract/        # the production-grade PostgreSQL version
│       ├── README.md
│       ├── 00_setup.sql
│       ├── 01_expand.sql
│       ├── 02_backfill.sql
│       └── 03_contract.sql
├── docs/
│   ├── versioned-migrations.md
│   ├── zero-downtime-expand-contract.md
│   ├── online-schema-change.md
│   ├── rollback-playbook.md
│   ├── data-backfill.md
│   └── cross-engine-checklist.md
└── checklists/
    └── migration-review-checklist.md

Requirements

Follow this guide to get Database Migration Toolkit up and running in your environment.

Requirements

• Python 3.10+ (uses modern type-hint syntax). The runner imports only the standard

library — sqlite3 is included with Python.

• A PostgreSQL or MySQL driver only if you point the runner at those engines

(psycopg2-binary or mysql-connector-python). They are *not* required for the bundled

SQLite demo.

Quick start

From the product directory:

# 1. See what's pending (nothing applied yet)
python3 runner/migrate.py status

# 2. Apply all four migrations to a throwaway SQLite database
python3 runner/migrate.py up

# 3. Confirm and check for drift
python3 runner/migrate.py status
python3 runner/migrate.py validate

up creates a local migrations_demo.db (SQLite) and applies V001→V004. No setup, no

network, nothing to clean up but one file.

Prove it

The whole point is that these migrations are safe and reversible. Watch it:

# Apply only the EXPAND step and see old + new columns coexist (backward compatible):
python3 runner/migrate.py up --to V003
sqlite3 migrations_demo.db "SELECT id, full_name, first_name, last_name FROM customers;"

# Apply the CONTRACT step, then revert just it — full_name is rebuilt losslessly:
python3 runner/migrate.py up
python3 runner/migrate.py down --steps 1
sqlite3 migrations_demo.db "SELECT id, full_name, first_name, last_name FROM customers;"

# Revert everything back to empty — downs are real:
python3 runner/migrate.py down --steps 4
python3 runner/migrate.py status

(sqlite3 is only for peeking at the data; the runner itself doesn't need it.)

Scaffold your own

python3 runner/migrate.py new add_invoices_table
# -> creates migrations/V005__add_invoices_table.up.sql and .down.sql with guidance

Pointing at PostgreSQL or MySQL

The runner uses the DB-API the same way for every engine; only the URL changes.

# PostgreSQL (needs: pip install psycopg2-binary)
python3 runner/migrate.py --url "postgresql://user:pass@db.example.com:5432/appdb" status

# MySQL (needs: pip install mysql-connector-python)
python3 runner/migrate.py --url "mysql://user:pass@db.example.com:3306/appdb" status

# Or set it once:
export DATABASE_URL="postgresql://user:pass@db.example.com:5432/appdb"
python3 runner/migrate.py up

The bundled migrations are written in SQLite-portable SQL so the demo runs anywhere.

For engine-specific, lock-sensitive production DDL (concurrent index builds, the

NOT VALID→VALIDATE constraint dance, batched backfills with mid-loop commits), see

examples/postgres_expand_contract/ and the docs

below. Credentials shown are placeholders — never commit a real connection string.