Database Schema Evolution: Incremental Migrations and Rollback Logic

Previously in this course, we covered Plugin Lifecycle Management: Migrations and Secure Uninstallation, where we established the basic hooks for plugin activation and deactivation. In this lesson, we move beyond simple activation scripts to professional database schema evolution.

When your plugin grows, your data requirements change. Hardcoding dbDelta calls directly into your activation hook is a recipe for disaster in production. Instead, you need a deterministic, versioned migration system that handles schema changes incrementally.

The Problem with "Big Bang" Migrations

Most WordPress developers treat database updates as a single, monolithic dbDelta call executed on every plugin update. This is fragile. If a user skips three versions of your plugin, or if a network-wide activation fails halfway through, you end up with an inconsistent state.

To build a professional, distributable product, you must treat your database schema as code. This means:

1. Versioned Migrations: Every change is a discrete, timestamped file.
2. State Tracking: Your plugin records the current "schema version" in wp_options.
3. Idempotency: Each migration script must be safe to run multiple times, or explicitly check if it needs to run.

Architecting Incremental Migrations

We will store our migrations in a specific directory structure. Each file will be a class implementing a MigrationInterface.

PHP
interface MigrationInterface {
    public function up(): void;
    public function down(): void; #6A9955">// Rollback support
}

Step 1: The Migration Runner

Create a service that checks the current version stored in the database against the list of available migration files.

PHP
class MigrationRunner {
    private $db;
    
    public function __construct($wpdb) {
        $this->db = $wpdb;
    }

    public function run_pending_migrations() {
        $current_version = get_option('my_plugin_db_version', '0.0.0');
        $migrations = $this->get_available_migrations();

        foreach ($migrations as $version => $migration_class) {
            if (version_compare($version, $current_version, '>')) {
                $migration = new $migration_class();
                $migration->up();
                update_option('my_plugin_db_version', $version);
            }
        }
    }
}

Worked Example: Adding a Column

Let's say we need to add a user_id column to our kb_articles table. We define a migration class for version 1.1.0.

PHP
class Migration_1_1_0 implements MigrationInterface {
    public function up(): void {
        global $wpdb;
        $table = $wpdb->prefix . 'kb_articles';
        $wpdb->query("ALTER TABLE $table ADD COLUMN user_id BIGINT(20) UNSIGNED DEFAULT NULL");
    }

    public function down(): void {
        global $wpdb;
        $table = $wpdb->prefix . 'kb_articles';
        $wpdb->query("ALTER TABLE $table DROP COLUMN user_id");
    }
}

Managing Rollback Logic

Rollbacks are rarely automated in production WordPress because they are destructive. However, writing down() methods is critical for development environments and testing.

Pro-tip: Never perform destructive operations (like DROP TABLE or DROP COLUMN) in a production update unless you have verified the backup status. Always favor additive changes. If you must rename or delete, keep the old column for at least one full release cycle before dropping it.

Hands-on Exercise

1. Create a database/migrations directory in your plugin.
2. Implement a MigrationRunner that uses version_compare to track your schema state.
3. Create a migration file that adds an index to an existing table using dbDelta.
4. Test the migration by manually downgrading the version in wp_options and triggering the runner.

Common Pitfalls

• Ignoring dbDelta Limitations: Even with migrations, dbDelta is quirky. It requires specific formatting (e.g., each field on a new line). Always review the Advanced Custom Database Tables: Schema, Migration, and Indexing lesson to ensure your SQL syntax matches dbDelta requirements.
• Assuming Database Permissions: Some managed hosts restrict ALTER TABLE or CREATE TABLE permissions. Always wrap your migration calls in error logging and check $wpdb->last_error.
• Mixing Data and Schema: Never perform heavy data processing (like migrating thousands of rows) inside a schema migration. If you need to move data, run it as a separate, background-processed job to avoid hitting PHP execution time limits.

Recap

Managing database schema evolution requires moving away from simple activation hooks toward a versioned, file-based migration system. By tracking the schema version, you ensure that every site, regardless of its update path, reaches the same state. Always prioritize additive changes and keep your rollbacks tested, even if they aren't exposed to the end user.

Up next: High-Concurrency Data Handling — we will explore how to use database transactions and row-level locking to prevent data corruption when multiple requests hit your custom tables simultaneously.