DEV Community

Thesius Code
Thesius Code

Posted on • Originally published at datanest-stores.pages.dev

Database Migration Toolkit

#postgres #performance #database #sql

Database Migration Toolkit

Stop dreading database migrations. This toolkit provides production-tested frameworks for schema changes, data migrations, zero-downtime deployments, and rollback procedures across PostgreSQL, MySQL, and MongoDB. Every script follows the expand-contract pattern so you can migrate safely even under heavy traffic — no maintenance windows required.

Key Features

  • Schema migration framework with numbered, idempotent migration files and a version tracking table
  • Zero-downtime migration patterns using expand-contract (add column, backfill, swap, drop) for live systems
  • Data migration scripts for bulk transformations with checkpointing, so interrupted jobs resume where they left off
  • Rollback procedures for every migration type — tested DOWN scripts, not afterthoughts
  • Cross-engine support covering PostgreSQL (psql), MySQL (mysql), and MongoDB (mongosh) with engine-specific idioms
  • Pre-flight validation checks that verify disk space, lock contention, replication lag, and FK constraints before executing
  • Large table migration helpers using pg_repack, pt-online-schema-change, and batched UPDATE strategies
  • CI/CD integration templates for GitHub Actions and GitLab CI migration pipelines

Quick Start 

unzip database-migration-toolkit.zip
cd database-migration-toolkit/

# Initialize the migration tracking table
psql -h localhost -U admin -d myapp -f src/init_migration_table.sql

# Run a migration
psql -h localhost -U admin -d myapp -f migrations/001_add_orders_table.sql

# Verify migration status
psql -h localhost -U admin -d myapp \
  -c "SELECT * FROM schema_migrations ORDER BY applied_at DESC LIMIT 5;"
Enter fullscreen mode Exit fullscreen mode

The migration tracking table:

CREATE TABLE IF NOT EXISTS schema_migrations (
    id              SERIAL PRIMARY KEY,
    version         VARCHAR(14) NOT NULL UNIQUE,  -- e.g., '20260315_0001'
    name            VARCHAR(255) NOT NULL,
    applied_at      TIMESTAMPTZ DEFAULT now(),
    execution_ms    INTEGER,
    checksum        VARCHAR(64),                  -- SHA-256 of migration file
    rolled_back     BOOLEAN DEFAULT FALSE
);
Enter fullscreen mode Exit fullscreen mode

Architecture / How It Works 

database-migration-toolkit/
├── src/
│   ├── init_migration_table.sql    # Bootstrap the tracking table
│   ├── migrate.sh                  # Shell runner: apply pending migrations
│   ├── rollback.sh                 # Shell runner: revert last N migrations
│   ├── preflight_check.sh          # Pre-migration safety checks
│   └── helpers/
│       ├── backfill_batched.sql    # Batched UPDATE for large tables
│       ├── pg_repack_wrapper.sh    # Zero-downtime table rewrite
│       └── checksum.sh             # Verify migration file integrity
├── migrations/                     # Example numbered migrations
│   ├── 001_add_orders_table.sql
│   ├── 001_add_orders_table.DOWN.sql
│   ├── 002_add_customer_email_index.sql
│   └── 002_add_customer_email_index.DOWN.sql
├── examples/
│   ├── expand_contract_example.sql
│   ├── mysql_pt_osc_example.sh
│   └── mongodb_migration.js
└── config.example.yaml
Enter fullscreen mode Exit fullscreen mode

Each migration has a paired .DOWN.sql rollback file. The shell runner applies them in order and records checksums to detect tampering.

Usage Examples

Expand-contract pattern for renaming a column with zero downtime:

-- Step 1: EXPAND — add new column alongside old one
ALTER TABLE customers ADD COLUMN full_name VARCHAR(200);

-- Step 2: BACKFILL — copy data in batches
UPDATE customers SET full_name = name
WHERE full_name IS NULL
  AND id BETWEEN 1 AND 100000;  -- repeat in batches

-- Step 3: SWAP — point application code to new column, then:
ALTER TABLE customers ALTER COLUMN full_name SET NOT NULL;

-- Step 4: CONTRACT — drop old column after all readers migrated
ALTER TABLE customers DROP COLUMN name;
Enter fullscreen mode Exit fullscreen mode

Batched backfill with checkpoint (handles interruptions):

-- Create checkpoint table
CREATE TABLE IF NOT EXISTS migration_checkpoint (
    migration_id       VARCHAR(50) PRIMARY KEY,
    last_processed_id  BIGINT DEFAULT 0,
    updated_at         TIMESTAMPTZ DEFAULT now()
);

-- Batched update with throttling
DO $$
DECLARE
    batch_size INT := 10000;
    start_id   BIGINT;
    rows_updated INT;
BEGIN
    SELECT COALESCE(last_processed_id, 0) INTO start_id
    FROM migration_checkpoint WHERE migration_id = 'backfill_fullname';

    LOOP
        UPDATE customers
        SET full_name = name
        WHERE id > start_id AND id <= start_id + batch_size
          AND full_name IS NULL;

        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;

        start_id := start_id + batch_size;

        INSERT INTO migration_checkpoint
        VALUES ('backfill_fullname', start_id, now())
        ON CONFLICT (migration_id)
        DO UPDATE SET last_processed_id = start_id, updated_at = now();

        COMMIT;
        PERFORM pg_sleep(0.1);  -- throttle to reduce replication lag
    END LOOP;
END $$;
Enter fullscreen mode Exit fullscreen mode

MySQL large-table migration with pt-online-schema-change:

pt-online-schema-change \
  --alter "ADD COLUMN full_name VARCHAR(200) AFTER name" \
  --host 127.0.0.1 --user admin --ask-pass \
  --chunk-size 5000 \
  --max-lag 2s \
  --critical-load "Threads_running=50" \
  D=myapp,t=customers \
  --execute
Enter fullscreen mode Exit fullscreen mode

Configuration 

# config.example.yaml
migration:
  engine: postgresql          # postgresql | mysql | mongodb
  migrations_dir: ./migrations
  tracking_table: schema_migrations
  checksum_verify: true       # fail if migration file changed after apply

safety:
  require_down_file: true     # every UP must have a matching DOWN
  max_lock_wait: "5s"         # abort if table lock exceeds this
  preflight_checks:
    - disk_space_gb: 10       # minimum free disk
    - replication_lag_ms: 500 # max replica lag before proceeding
    - active_locks: 5         # max concurrent locks allowed

batching:
  default_batch_size: 10000
  throttle_ms: 100            # pause between batches
  checkpoint: true            # enable resume-from-checkpoint
Enter fullscreen mode Exit fullscreen mode

Best Practices

  1. Never mix schema and data changes in the same migration file. Schema DDL auto-commits in MySQL, making rollback impossible if the data step fails.
  2. Test rollbacks in staging before every production deploy. An untested DOWN script is worse than no rollback at all.
  3. Use SET lock_timeout in PostgreSQL migrations to fail fast instead of blocking all queries behind a long-held ACCESS EXCLUSIVE lock.
  4. Monitor replication lag during backfills. Throttle batch size or add pg_sleep() between batches to keep replicas healthy.
  5. Checksum your migration files. If someone edits a migration after it was applied, your environments will silently diverge.

Troubleshooting

Problem Cause Fix
Migration hangs indefinitely ALTER TABLE waiting for ACCESS EXCLUSIVE lock Add SET lock_timeout = '5s'; at top of migration
Rollback fails with FK violation DOWN script drops table still referenced Drop the FK constraint first, then the table
Checksum mismatch error Migration file edited after being applied If intentional, update checksum in schema_migrations; if not, investigate
Replication lag spikes during backfill Batch size too large Reduce batch_size to 5000 and increase throttle_ms to 200

This is 1 of 9 resources in the Database Admin Pro toolkit. Get the complete [Database Migration Toolkit] with all files, templates, and documentation for $39.

Get the Full Kit →

Or grab the entire Database Admin Pro bundle (9 products) for $109 — save 30%.

Get the Complete Bundle →

Related Articles

Top comments (0)