Backward-compatible migrations: the rules I don't break on an install base

A migration on a live install base is not a schema change, it is a bet that runs on hardware you do not own. On the licensing and affiliate plugins I maintain, a single migration executes on shared hosts with thirty-second timeouts, on databases with millions of rows, and on PHP versions I stopped supporting years ago but users still run. So I hold a short list of rules I do not break: add only, never destroy in a deploy, gate every change on a version number, and always know how I roll back. Break one and you are one ALTER away from a support queue full of stores that stopped working.

Here are the rules, and the code that enforces each one.

Rule 1: additive only, and give every new column a default

New feature needs a column? Add it with a default so every existing row is valid the instant the column exists. Do not add a NOT NULL column with no default onto a populated table, because the database has to decide what the existing millions of rows hold, and on some engines and versions that is an error or a full-table rewrite that blows the timeout.

ALTER TABLE wp_affapp_referrals
  ADD COLUMN commission_tier VARCHAR(20) NOT NULL DEFAULT 'standard';

That default is doing real work. The old code never selects commission_tier, so it keeps working untouched. The new code reads a valid value from every row, old and new, with no separate backfill pass. One additive statement with a sane default beats a two-phase add-then-backfill for anything that can be defaulted, because there is no window where rows are half-migrated, which is exactly what lets me ship a new feature in staged percentages on top of it.

When you truly cannot default the value, the new column is nullable, and the code treats NULL as “not yet computed” rather than assuming presence. You backfill in batches, not in the deploy.

Rule 2: no destructive change in a deploy

Dropping a column, renaming a column, or narrowing a type is a one-way door on a system a user can restore from a backup at any moment. If the old table shape is gone and they roll the plugin back, the old code hits a column that no longer exists and fatals on every request. So destructive changes do not ride in the same release that stops using the column. They wait.

The retirement of a column is a three-release arc, not a step:

1. Stop writing to the column. Both old and new code still read it.
2. Stop reading from it. It sits there, ignored, for at least one full release so anyone who downgrades still has it.
3. Only then, in a much later release, drop it, once no shipped version of the plugin references it.

The column being dead for a release is cheap. A destructive deploy that meets a downgrade is not. This staged retirement is the same patience I bring to evolving a plugin without a rewrite: let the old path die on its own schedule, never in one cut.

Rule 3: gate every change on a stored schema version

I never key migrations off the plugin version. Users downgrade, restore staging over production, and clone sites. The plugin version does not reliably tell me what shape the schema is actually in. So I keep a dedicated schema version in the options table and compare against it on load.

const AFFAPP_DB_VERSION = 15;

add_action( 'plugins_loaded', 'affapp_maybe_upgrade' );

function affapp_maybe_upgrade() {
    $installed = (int) get_option( 'affapp_db_version', 0 );
    if ( $installed >= AFFAPP_DB_VERSION ) {
        return; // Already current. Cheap early return on every request.
    }

    require_once __DIR__ . '/includes/migrations.php';

    // Ordered, cumulative, each step idempotent.
    if ( $installed < 14 ) {
        affapp_migration_14_add_tier_column();
    }
    if ( $installed < 15 ) {
        affapp_migration_15_add_payout_index();
    }

    update_option( 'affapp_db_version', AFFAPP_DB_VERSION );
}

The steps are cumulative and ordered, so a site three versions behind runs 14 then 15 in sequence and lands current. The early return keeps the check nearly free on the vast majority of requests where nothing needs to happen. And critically, each step is written to be safe to run twice, because on a host that kills the request at thirty seconds mid-migration, the next page load will run it again.

Rule 4: every migration step is idempotent

Idempotent means running it twice does no additional harm. This is not optional on WordPress, where you do not control the execution environment and a timeout mid-migration is normal, not exceptional. Guard every structural change with a check.

function affapp_migration_15_add_payout_index() {
    global $wpdb;
    $table = $wpdb->prefix . 'affapp_payouts';

    $has_index = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND INDEX_NAME = %s",
            DB_NAME,
            $table,
            'affiliate_paid_at'
        )
    );

    if ( ! $has_index ) {
        $wpdb->query(
            "ALTER TABLE {$table}
             ADD INDEX affiliate_paid_at (affiliate_id, paid_at)"
        );
    }
}

For table creation, dbDelta() gives you idempotency for free: it creates a table that is missing and leaves a matching one alone. For an ALTER on an existing table, dbDelta() is fussier and will not reliably add an index to a table it did not just create, so I check information_schema myself and only run the change when the object is genuinely absent. The result is a step I can run on a fresh install, a half-migrated install, or a fully current one, and it does the right thing in all three.

Rule 5: know the rollback before you deploy

If I cannot describe the rollback in one sentence before shipping, the migration is not ready. Because every change up to this point was additive and idempotent, the rollback is usually trivial: the old code ignores the new column, so reverting the plugin is a clean downgrade with no schema surgery.

The test I use is whether I would run the rollback myself on a live customer’s site without hesitating. If the honest answer is that I would want a fresh backup in hand first, the migration is not additive enough yet, and I go back and make it so before shipping.

The rollback plan I write down for each migration is short and specific:

• The new column has a default, so the previous release reads every row without it. Downgrade is safe with no DB action.
• The new index only affects query speed, never correctness. Leaving it in place after a downgrade costs nothing.
• If a batched backfill is running, it is resumable and can simply be stopped, because it only fills a nullable column the old code does not read.

Notice what is not on that list: “restore the database from backup.” That is the plan you fall back to when you broke rule 1 or rule 2. If the migration was additive and idempotent, you never need it.

The rules are the product

None of this is clever. It is the opposite of clever, and that is the point. On a plugin thousands of people run in production, the migration that never makes the news is the one that added a defaulted column behind a version gate, ran once, and could be rolled back by clicking downgrade. Additive only, never destructive in a deploy, gated on a stored version, idempotent, with a rollback you wrote down first. I do not break these rules, because the one time you do is the one time a store stops taking orders and it is your name on the changelog.

FAQ