Data migrations and plugin updates

The custom tables lesson created the bookings table with dbDelta() and stored myplugin_db_version. That handles the first install. Real plugins change their data over years: new columns, new indexes, values moved from post meta into tables, formats that change. This lesson builds a migration system that runs each change exactly once per site, in order, at the right time, without timing out on large tables and without breaking sites halfway through an update.

Why activation hooks are not enough

register_activation_hook() runs when a user clicks Activate. It does not run when the plugin is updated, whether through the Plugins screen, automatic updates, WP-CLI or a deployment that replaces files. After an update, the new code is running against old data until something notices.

The upgrader_process_complete action is not a reliable substitute either. It fires only for updates done through WordPress's own upgrader, and it runs in the request that performed the update, while the old version of your plugin is still loaded in memory.

The reliable approach compares versions on every request:

1. The code defines the data version it needs: MYPLUGIN_DB_VERSION.
2. The database records the version it has: the myplugin_db_version option.
3. Early in each request, if the code's version is newer, run the missing migrations and update the option.

The check is one autoloaded option and a version_compare(), so it costs almost nothing when there is nothing to do.

The plan for this lesson

Two releases change the bookings table:

Versions 1.0 and 1.1 already exist on live sites, so the migrator must reproduce them exactly. The main file now says define( 'MYPLUGIN_DB_VERSION', '1.3' );.

The migrator

All upgrade logic lives in one class. It replaces the dbDelta() code in the installer and the myplugin_maybe_upgrade_db() check from the custom tables lesson, which you can now delete:

<?php
// myplugin/src/Database/Migrator.php

namespace MyPlugin\Database;

use MyPlugin\Contracts\Hookable;

defined( 'ABSPATH' ) || exit;

final class Migrator implements Hookable {

    public const VERSION_OPTION = 'myplugin_db_version';
    public const BACKFILL_HOOK  = 'myplugin_backfill_event_dates';
    private const LOCK_OPTION   = 'myplugin_migration_lock';
    private const BATCH_SIZE    = 1000;

    public function __construct( private \wpdb $wpdb ) {}

    public function register(): void {
        add_action( self::BACKFILL_HOOK, array( $this, 'backfill_event_dates' ) );

        $this->maybe_migrate();
    }

    /**
     * Ordered list: data version => method that brings the data to it.
     */
    private function migrations(): array {
        return array(
            '1.0' => 'update_schema',
            '1.1' => 'upgrade_1_1',
            '1.2' => 'update_schema',
            '1.3' => 'queue_event_date_backfill',
        );
    }

    public function maybe_migrate(): void {
        $installed = (string) get_option( self::VERSION_OPTION, '0' );

        // Up to date, or the data is newer than this code (a downgrade): do nothing.
        if ( version_compare( $installed, MYPLUGIN_DB_VERSION, '>=' ) ) {
            return;
        }

        if ( ! $this->acquire_lock() ) {
            return;
        }

        try {
            foreach ( $this->migrations() as $version => $method ) {
                if ( version_compare( $installed, $version, '>=' ) ) {
                    continue;
                }

                $this->{$method}();

                // Record each step, so a failure resumes from the right place.
                update_option( self::VERSION_OPTION, $version );
                $installed = $version;
            }
        } finally {
            delete_option( self::LOCK_OPTION );
        }
    }

    private function acquire_lock(): bool {
        if ( add_option( self::LOCK_OPTION, time(), '', false ) ) {
            return true;
        }

        // A crashed request can leave the lock behind. Treat old locks as stale.
        $locked_at = (int) get_option( self::LOCK_OPTION, 0 );

        if ( $locked_at < time() - 10 * MINUTE_IN_SECONDS ) {
            delete_option( self::LOCK_OPTION );
            return add_option( self::LOCK_OPTION, time(), '', false );
        }

        return false;
    }
}

Key decisions:

• Ordered, keyed migrations. Each entry runs once, in order, only if the stored version is older. A site that skipped several releases runs all the missing steps in one go.
• The version is recorded after each step. If step 1.3 fails, the next request starts at 1.3, not at the beginning.
• A lock. add_option() fails if the option already exists, so only one request runs migrations at a time. Other requests carry on with the current data, which is why the code must cope with both old and new data for a while (see below).
• Downgrades are left alone. If someone rolls back to an older plugin version, the stored version is newer than the code's, and the migrator does nothing. Rolling data back is rarely safe automatically.

Schema steps

dbDelta() compares a CREATE TABLE statement with the live table and adds what is missing: columns, indexes, changed column types. So the schema step always describes the table as it should be now:

<?php
// myplugin/src/Database/Migrator.php (continued, inside the class)

    private function update_schema(): void {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $table   = $this->wpdb->prefix . 'myplugin_bookings';
        $charset = $this->wpdb->get_charset_collate();

        dbDelta(
            "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            event_id bigint(20) unsigned NOT NULL,
            user_id bigint(20) unsigned NOT NULL,
            guests tinyint(3) unsigned NOT NULL DEFAULT 0,
            status varchar(20) NOT NULL DEFAULT 'confirmed',
            event_date date DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY event_status (event_id,status),
            KEY user_created (user_id,created_at),
            KEY created_at (created_at),
            KEY event_date (event_date)
            ) {$charset};"
        );
    }

    /**
     * The custom tables lesson's 1.1 upgrade: schema, then drop indexes dbDelta() cannot remove.
     */
    private function upgrade_1_1(): void {
        $this->update_schema();

        $table = $this->wpdb->prefix . 'myplugin_bookings';

        foreach ( array( 'event_id', 'user_id' ) as $index ) {
            $exists = $this->wpdb->get_var(
                $this->wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table, $index )
            );

            if ( $exists ) {
                $this->wpdb->query( $this->wpdb->prepare( 'ALTER TABLE %i DROP INDEX %i', $table, $index ) );
            }
        }
    }

On a new site, step 1.0 creates the full, current table, and the later schema steps find nothing to change. On a site at 1.1, step 1.2 adds the column and its index. The 1.1 step is the custom tables lesson's upgrade moved into the class; it checks before dropping, so it is harmless on a table that never had those indexes. The new column allows NULL on purpose: existing rows have no value until the backfill reaches them.

dbDelta() only adds and alters. It never drops columns or indexes. Removing something is a deliberate, separate step with $wpdb->query(), and usually a later release, as described below.

Data steps, in batches

Copying a date into a few hundred rows takes milliseconds. On a site with two million bookings, one big UPDATE can run for minutes, lock the table, hit PHP's time limit and fail halfway. Large data migrations therefore run in the background, one small batch per job, with each job queuing the next.

Action Scheduler, from lesson 11, is the best tool when it is available. WP-Cron is the fallback:

<?php
// myplugin/src/Database/Migrator.php (continued, inside the class)

    private function queue_event_date_backfill(): void {
        $this->schedule_backfill( 0 );
    }

    private function schedule_backfill( int $after_id ): void {
        if ( function_exists( 'as_enqueue_async_action' ) ) {
            as_enqueue_async_action( self::BACKFILL_HOOK, array( $after_id ), 'myplugin' );
            return;
        }

        wp_schedule_single_event( time(), self::BACKFILL_HOOK, array( $after_id ) );
    }

    public function backfill_event_dates( int $after_id ): void {
        $table = $this->wpdb->prefix . 'myplugin_bookings';

        $ids = $this->wpdb->get_col(
            $this->wpdb->prepare(
                'SELECT id FROM %i WHERE id > %d AND event_date IS NULL ORDER BY id ASC LIMIT %d',
                $table,
                $after_id,
                self::BATCH_SIZE
            )
        );

        if ( ! $ids ) {
            return; // Finished.
        }

        $last_id = (int) end( $ids );

        $this->wpdb->query(
            $this->wpdb->prepare(
                'UPDATE %i AS b
                INNER JOIN %i AS pm ON pm.post_id = b.event_id AND pm.meta_key = %s
                SET b.event_date = pm.meta_value
                WHERE b.id > %d AND b.id <= %d AND b.event_date IS NULL',
                $table,
                $this->wpdb->postmeta,
                'myplugin_event_date',
                $after_id,
                $last_id
            )
        );

        $this->schedule_backfill( $last_id );
    }

How the batching works:

• Each job handles at most 1,000 rows, chosen by primary key range. Range scans on the primary key stay fast however large the table grows, unlike LIMIT ... OFFSET, which slows down as the offset rises.
• The job passes its last ID to the next job, so progress is stored in the queue itself. If a job fails, Action Scheduler records the failure and you can rerun it from the admin screen or WP-CLI.
• Job arguments are a plain list. Both Action Scheduler and WP-Cron pass them to the callback in order, so $after_id arrives as the first parameter.
• The query only touches rows where event_date IS NULL, so running a batch twice does no harm. Migrations should always be safe to repeat.

Bookings whose event has no date stay NULL, and the next batch moves past them, so the job always finishes.

Wiring it in

The migrator runs first, before any service that might read the table. Plugin::register() runs on plugins_loaded, so the check happens on every kind of request: front end, admin, REST, cron and WP-CLI. The plugin class now keeps $wpdb:

 final class Plugin {

-    public function __construct( \wpdb $wpdb ) {
-        $this->bookings = new Repository( $wpdb );
+    public function __construct( private \wpdb $wpdb ) {
+        $this->bookings = new Repository( $this->wpdb );
     }

     public function register(): void {
         ...
+        ( new Database\Migrator( $this->wpdb ) )->register();
+
         $this->services[] = new Event();

Some plugins run the check on admin_init instead, so visitors never trigger a migration. The cost is that a site updated automatically can serve front-end requests against old data until an administrator logs in. If you choose admin_init, every front-end code path must work with the old schema.

Activation reuses the same code, so a fresh install and an upgrade follow one path:

<?php
// myplugin/src/Installer.php

namespace MyPlugin;

use MyPlugin\Database\Migrator;

defined( 'ABSPATH' ) || exit;

final class Installer {

    public static function activate(): void {
        self::install();
    }

    public static function install(): void {
        global $wpdb;

        ( new Migrator( $wpdb ) )->maybe_migrate();
    }
}

Lesson 18's test bootstrap calls Installer::install(), so the test database always has the latest schema.

Staying backwards compatible

Between the schema step and the end of the backfill, some rows have event_date and some do not. Old and new code may also overlap: on a server with several web nodes, a request can reach a node that still has the previous release. Use the expand, then contract pattern from the deployment lesson of the WordPress course:

1. Expand (release 1.5): add the column. Write it for new rows and keep writing the old source too. Readers fall back to post meta when the column is NULL.
2. Migrate: the backfill fills old rows in the background.
3. Switch (release 1.6): once the backfill is complete everywhere, read only from the column.
4. Contract (a later release): remove fallbacks, and drop anything no longer used.

For step 1, the repository writes the new column on insert:

             'guests'     => $guests,
             'status'     => $status,
+            'event_date' => (string) get_post_meta( $event_id, 'myplugin_event_date', true ) ?: null,
             'created_at' => current_time( 'mysql', true ),
         ),
-        array( '%d', '%d', '%d', '%s', '%s' )
+        array( '%d', '%d', '%d', '%s', '%s', '%s' )

Never rename or drop a column in the same release that stops using it. A site that rolls back one version would find its data gone.

Testing migrations

A migration runs once on each live site, on data you cannot see, so test it before release. Using the setup from lesson 18:

• Set myplugin_db_version to '1.0', call maybe_migrate(), and assert the column exists and the option is '1.3'.
• Insert bookings without event_date, call backfill_event_dates( 0 ) directly, and assert the dates are filled.
• Call each step twice and assert nothing changes the second time.

On Multisite, every site has its own tables and its own myplugin_db_version. The next lesson shows how the same migrator copes with that.

Summary

• Activation hooks do not run on updates, and upgrader_process_complete runs with the old code loaded; compare MYPLUGIN_DB_VERSION with the stored myplugin_db_version on every request instead.
• Keep an ordered list of migrations, run only the missing ones, record the version after each step, and guard the run with an add_option() lock.
• Schema steps describe the full current table for dbDelta(), which adds but never drops.
• Move large data in background batches with Action Scheduler, or WP-Cron as a fallback, using primary key ranges and repeatable queries.
• Expand, migrate, switch, then contract, so old and new code and old and new data work side by side.