Database Migration Tool

A checkpointed, validated MySQL → PostgreSQL migration framework — built for a scenario with ~1.2M rows across 34 tables and a hard 4-hour maintenance window, with a scripted rollback if anything goes wrong.

About this project. This is a portfolio project. It reproduces a real-world migration pattern -- MySQL 5.7 to PostgreSQL 14, with the type quirks, foreign-key inconsistencies, and time pressure that pattern actually involves -- but no real company, schema, or data is used anywhere here. Everything under db/mysql_init/ is synthetic, generated by scripts/generate_seed_data.py with a fixed seed, so anyone can clone the repo and reproduce every result below, including the Performance numbers.

A team is migrating its primary application database from MySQL 5.7 to PostgreSQL 14 -- driven by PostgreSQL's stronger JSON support, better full-text search, and a cloud provider switch where Postgres is the preferred managed option. The database has ~1.2M rows across 34 tables. Three things make it harder than a straight dump-and-restore:

• MySQL-specific types that don't exist in PostgreSQL: TINYINT(1) used as a boolean, ENUM columns, a YEAR column.
• Inconsistently enforced foreign keys. Some relationships were never backed by a real MySQL constraint, so orphaned rows exist that PostgreSQL, which will enforce them, would reject outright.
• A hard deadline. The whole cutover has to fit in a single maintenance window, with a rollback path ready if validation fails partway through.

flowchart LR
    A[("MySQL 5.7\n(docker-compose, seeded)")] -->|introspect| B["migrator.schema\n(DDL translation)"]
    B --> C[("PostgreSQL 14\n(docker-compose)")]
    B --> D["migrator.data\n(batched copy,\ndependency order)"]
    D --> C
    D <-->|checkpoint.json| E["migrator.checkpoint"]
    D --> F["migrator.validate\n(row counts +\nsample checksums)"]
    F -->|pass| G["migrator.cutover\n(final sync, FKs,\nsequences)"]
    F -->|fail| H["stop + report"]
    I["migrator.rollback"] -.->|truncate + reopen source| A
    J["migrator.cli"] --> B & D & F & G & I

Four phases, each its own module and CLI command:

1. Audit (migrator/audit.py) -- finds rows that violate a logical foreign-key relationship MySQL never enforced, and lists every ENUM column so application code can be checked for reliance on ordinal position before the column becomes plain text.
2. Schema translation (migrator/schema.py) -- introspects MySQL via SQLAlchemy and generates PostgreSQL DDL. Every type mapping (TINYINT(1) -> BOOLEAN, ENUM -> TEXT + CHECK, YEAR -> SMALLINT, ...) comes from config/type_mapping.json, not code -- supporting a new MySQL type is a config edit. Foreign keys are deliberately not created yet; see step 4.
3. Data migration (migrator/data.py + migrator/checkpoint.py) -- copies each table in dependency order (parents before children, computed from a topological sort over both real MySQL FK constraints and the logical relationships in config/logical_relationships.json), in configurable batches. Progress is written to checkpoint.json after each completed table, so an interrupted run resumes instead of restarting.
4. Validation (migrator/validate.py) -- after every table: row-count comparison plus a sample checksum (a deterministic hash of a batch of rows) between source and destination. Any mismatch halts the migration before the next table starts.
5. Cutover (migrator/cutover.py) -- syncs any rows added to the source after the bulk copy, adds every foreign key (real and logical) to PostgreSQL now that the data is clean and validated, and resets each SERIAL sequence from MAX(id) so the first post-cutover insert doesn't collide.
6. Rollback (migrator/rollback.py) -- scripted, tested: truncates the PostgreSQL destination and confirms the MySQL source is still intact and writable.

git clone <this-repo-url>
cd pythonProject-database-migration-tool
docker-compose up -d              # MySQL 5.7 + PostgreSQL 14, source auto-seeded
pip install -r requirements.txt
cp .env.example .env               # defaults already point at the docker-compose services

python -m migrator.cli audit                # see the orphaned rows and ENUM columns
python -m migrator.cli audit --clean         # dispose of the orphaned rows
python -m migrator.cli translate-schema      # create the translated schema in Postgres
python -m migrator.cli run                   # migrate data, table by table, checkpointed
python -m migrator.cli validate              # row counts + sample checksums
python -m migrator.cli cutover               # delta sync, foreign keys, sequences
python -m migrator.cli status                # see the checkpoint at any point

python -m migrator.cli run
# ...Ctrl+C partway through...

python -m migrator.cli status
# categories: completed | customers: completed | orders: in_progress | order_items: pending ...

python -m migrator.cli run
#   [skip] categories already completed (checkpoint)
#   [skip] customers already completed (checkpoint)
#   orders: 100%|##########| 1478/1478 [00:00<00:00, 8404.78rows/s]
#   ...continues from where it left off, not from zero

• Config-driven type mapping (config/type_mapping.json) -- new MySQL types are a config edit, not a code change.
• Checkpointed, resumable migration -- checkpoint.json tracks per-table status; an interrupted run resumes at the first incomplete table instead of starting over.
• Row-count + sample-checksum validation after every table, before the next one starts.
• Dependency-ordered migration that respects both real MySQL foreign keys and the logical relationships MySQL never enforced (config/logical_relationships.json).
• Pre-migration audit that finds orphaned rows and lists ENUM columns before a single row is copied.
• Scripted, tested rollback -- truncates the destination and confirms the source is still writable.
• Deferred foreign-key enablement -- constraints are added at cutover, once data is validated, so bulk loading isn't blocked by insertion order.

docker-compose up -d
pip install -r requirements.txt
pytest -v

tests/unit/ needs no database: type-mapping resolution, checksum determinism, checkpoint resume logic. tests/integration/ runs the full pipeline against live containers, including two scenarios worth calling out: a table deliberately corrupted after migration is caught by validate (tests/integration/test_validation.py), and a table interrupted mid-copy reaches the same end state on resume as an uninterrupted run (tests/integration/test_resume.py). GitHub Actions (.github/workflows/ci.yml) runs the same suite against mysql:5.7 and postgres:14 service containers on every push.

db/mysql_init/01_schema.sql is hand-written (34 tables trimmed to 8 that keep every interesting case: a TINYINT(1) boolean, two ENUM columns, a YEAR column, a real FK chain, and two relationships left logical-only on purpose). db/mysql_init/02_seed_data.sql is generated by scripts/generate_seed_data.py (Faker, seed 42) -- 6,556 rows total, auto-loaded by docker-compose up -d, with 28 rows deliberately dangling (22 orders referencing a deleted customer, 6 reviews referencing a deleted product) for migrate audit to find.

scripts/generate_seed_data.py --large scales the same generator to ~1.2M rows and loads them directly into a running MySQL container (too big to ship as a committed SQL file) -- that run is what produced the Performance numbers below.

migrator/                 schema, data, checkpoint, validate, cutover, rollback, audit, cli
config/                   type_mapping.json, logical_relationships.json
db/mysql_init/            MySQL schema + seed data (docker-compose auto-load)
scripts/                  seed data generator, benchmark harness, demo script
tests/unit/               type mapping, checksum, checkpoint (no DB)
tests/integration/        full pipeline against live containers
docs/                     screenshots + the demo GIF

Inconsistent FK enforcement. MySQL allowed orphaned rows on two relationships that were never backed by a real constraint -- orders.customer_id and reviews.product_id (see config/logical_relationships.json). PostgreSQL, which will enforce them, would reject those rows outright. migrate audit finds them before a single row is copied; migrate audit --clean disposes of them, cascading to dependent rows in child tables first so the delete itself doesn't trip a different FK constraint.

ENUM to text migration. MySQL ENUM columns carry an implicit ordering, and application code can end up relying on the ordinal value rather than the string. migrate audit lists every ENUM column and its declared values up front specifically so that check can happen before migrating to TEXT + CHECK -- which has no implicit order at all.

Sequence starting values. PostgreSQL's SERIAL sequences don't know about rows that arrived via bulk copy rather than INSERT. migrate cutover sets each sequence from MAX(id) after the data lands, so the first real insert after cutover doesn't collide with a migrated row.

The pre-migration audit was the most valuable step, by far. Running every check against the source data before writing migration code surfaces the real scope of the work -- in this scenario, orphaned rows on two relationships and two ENUM columns to check against application code. Most of the actual effort in a migration like this is data-quality remediation, not the migration itself.

Checkpointing pays for itself the first time something goes wrong. tests/integration/test_resume.py proves the mechanism directly: a table interrupted mid-copy reaches the exact same end state on resume as an uninterrupted run, and every already-completed table is untouched. On a 1.2M-row run, that's the difference between losing seconds and losing whatever the slowest table cost.

Numbers from an actual run of scripts/benchmark.py against the --large dataset (~1.2M rows, generated by scripts/generate_seed_data.py --large) on this machine's local Docker containers -- not a claim, a reproducible result:

That's on a single machine with both databases in local containers, so it isn't directly comparable to a real production migration over a network with larger rows and live indexes -- but it's the actual bottleneck-free ceiling of this approach, and it's why a scenario like this can target a multi-hour maintenance window with room to spare: the batched, checkpointed copy itself is not what eats the time budget in practice -- data-quality remediation (the audit step above) is.

Reproduce it: python scripts/generate_seed_data.py --large then python scripts/benchmark.py (writes benchmark_results.json).

MIT -- see LICENSE.