Safe WordPress Database Migrations with dbDelta and Online DDL

Once a custom WordPress application or commercial plugin reaches real volume, the default data model (posts, postmeta, terms, options) stops scaling. High-volume transactional work such as ledger logging, multi-vendor commission accounting, webhook event streams or IoT sensor telemetry calls for dedicated custom MySQL tables.

Creating the table on first activation is only the beginning. Requirements change: you will need new columns, different data types, composite indexes, retired structures and backfills over millions of rows. A migration that fails can corrupt customer data, lock tables while customers are checking out, or throw fatal PHP errors, and a slow or locked store also hurts the speed and crawlability your SEO depends on.

WordPress ships dbDelta() to create tables and diff schemas, but it is known for strict parsing quirks, almost no error reporting and no support for dropping or renaming columns. This guide dissects how dbDelta() parses SQL, builds a PSR-4 Schema Migration Framework, handles zero-downtime MySQL 8.0 DDL, runs cursor-based Action Scheduler backfills, performs shadow table atomic swaps, covers foreign key integrity and collation conflicts, and finishes with PHPUnit integration tests and WP-CLI tooling.

How Strict `dbDelta()` Is About Syntax

dbDelta(), defined in wp-admin/includes/upgrade.php, has no real SQL grammar parser. It uses regular expressions to compare the existing table schema with the SQL string you hand it, so a stray space or a wrongly capitalized keyword can make it fail silently or issue the same ALTER TABLE on every page load.

`dbDelta()` Formatting Checklist

A Reliable `dbDelta()` DDL Template

This is the exact layout a dependable table definition needs:

<?php
declare(strict_types=1);

function wpstack_get_invoices_schema(): string {
    global $wpdb;
    $table_name = $wpdb->prefix . 'wpstack_invoices';
    $charset_collate = $wpdb->get_charset_collate();

    return "CREATE TABLE {$table_name} (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        order_id bigint(20) unsigned NOT NULL,
        customer_id bigint(20) unsigned NOT NULL,
        invoice_number varchar(64) NOT NULL DEFAULT '',
        total_amount decimal(12,4) NOT NULL DEFAULT '0.0000',
        currency varchar(3) NOT NULL DEFAULT 'USD',
        status varchar(32) NOT NULL DEFAULT 'pending',
        created_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
        updated_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
        PRIMARY KEY  (id),
        KEY idx_order_id (order_id),
        KEY idx_customer_status (customer_id, status),
        KEY idx_created_at (created_at)
    ) {$charset_collate};";
}

What `dbDelta()` Will Not Do

dbDelta() is good at creating tables and adding missing columns, but it has real limits you must plan around:

• No column drops: Delete a field from the schema string and dbDelta() just ignores the omission. It never runs DROP COLUMN, which protects data from accidental loss.
• No column renames: If you change user_id to customer_id, dbDelta() sees a brand-new column, adds it with default values and leaves the old user_id column and its data behind.
• No primary key changes: Turning a single-column primary key into a composite key is not supported.
• No foreign keys: dbDelta() neither parses nor maintains FOREIGN KEY ... REFERENCES constraints.
• Not for heavy migrations on huge tables: Running dbDelta() in an admin request against a very large table risks PHP timeouts and HTTP 504 errors.

Collation and Character Set Traps in MySQL 8.0

A particularly sneaky failure shows up when a custom table is joined to a WordPress core table (for example INNER JOIN wp_users ON wp_wpstack_invoices.customer_id = wp_users.ID). If the two tables use different character sets or collations, MySQL stops with an error:

-- Fatal Database Error
Error: 1267 SQLSTATE: HY000 (ER_CANT_AGGREGATE_2COLLATIONS)
Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='

It happens when a collation is hardcoded or left to the server default. On WordPress 6.x with MySQL 8.0, core tables normally use utf8mb4_unicode_520_ci or utf8mb4_0900_ai_ci. Appending $wpdb->get_charset_collate() to your DDL makes the new table match them exactly.

Designing a Versioned PSR-4 Migration Engine

To work around those gaps, mature teams wrap schema changes in a versioned migration framework. It records every applied migration in its own store and runs schema diffs and data transformations in order.

1. The Interface (`MigrationInterface.php`)

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseMigrations;

interface MigrationInterface {
    /**
     * Unique semantic version or timestamp (e.g. "2026_10_01_001").
     */
    public function getVersion(): string;

    /**
     * Human-readable description of migration purpose.
     */
    public function getDescription(): string;

    /**
     * Execute forward migration logic.
     */
    public function up(): void;

    /**
     * Optional rollback logic for staging test environments.
     */
    public function down(): void;
}

2. A First Migration That Creates the Table (`Migration_2026_10_01_CreateInvoicesTable.php`)

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseMigrations;

use wpdb;

class Migration_2026_10_01_CreateInvoicesTable implements MigrationInterface {
    public function __construct(private readonly wpdb $db) {}

    public function getVersion(): string {
        return '2026_10_01_001';
    }

    public function getDescription(): string {
        return 'Create initial custom invoices table with composite indexes.';
    }

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

        $tableName = $this->db->prefix . 'wpstack_invoices';
        $charsetCollate = $this->db->get_charset_collate();

        $sql = "CREATE TABLE {$tableName} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            customer_id bigint(20) unsigned NOT NULL,
            invoice_number varchar(64) NOT NULL DEFAULT '',
            total_amount decimal(12,4) NOT NULL DEFAULT '0.0000',
            status varchar(32) NOT NULL DEFAULT 'pending',
            created_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
            PRIMARY KEY  (id),
            KEY idx_order_id (order_id),
            KEY idx_customer_status (customer_id, status)
        ) {$charsetCollate};";

        dbDelta($sql);
    }

    public function down(): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';
        $this->db->query("DROP TABLE IF EXISTS {$tableName}");
    }
}

3. Renaming Columns and Backfilling Data

When an update has to rename a column or reshape data, combine dbDelta() with explicit transactional DDL statements:

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseMigrations;

use wpdb;

class Migration_2026_10_15_AddTaxColumnsAndRenameStatus implements MigrationInterface {
    public function __construct(private readonly wpdb $db) {}

    public function getVersion(): string {
        return '2026_10_15_002';
    }

    public function getDescription(): string {
        return 'Add tax_rate column and safely migrate legacy status codes.';
    }

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

        $tableName = $this->db->prefix . 'wpstack_invoices';
        $charsetCollate = $this->db->get_charset_collate();

        // 1. Use dbDelta to add new columns safely
        $sql = "CREATE TABLE {$tableName} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            order_id bigint(20) unsigned NOT NULL,
            customer_id bigint(20) unsigned NOT NULL,
            invoice_number varchar(64) NOT NULL DEFAULT '',
            total_amount decimal(12,4) NOT NULL DEFAULT '0.0000',
            tax_rate decimal(5,2) NOT NULL DEFAULT '0.00',
            tax_amount decimal(12,4) NOT NULL DEFAULT '0.0000',
            status varchar(32) NOT NULL DEFAULT 'pending',
            created_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
            PRIMARY KEY  (id),
            KEY idx_order_id (order_id),
            KEY idx_customer_status (customer_id, status)
        ) {$charsetCollate};";

        dbDelta($sql);

        // 2. Execute transactional data transform
        $this->db->query("START TRANSACTION");
        try {
            // Transform legacy status values
            $this->db->query(
                "UPDATE {$tableName} 
                 SET status = 'paid_in_full' 
                 WHERE status = 'complete'"
            );

            // Compute default tax amounts for existing rows
            $this->db->query(
                "UPDATE {$tableName} 
                 SET tax_amount = total_amount * 0.20, tax_rate = 20.00 
                 WHERE tax_amount = 0.0000 AND status = 'paid_in_full'"
            );

            $this->db->query("COMMIT");
        } catch (Throwable $e) {
            $this->db->query("ROLLBACK");
            throw $e;
        }
    }

    public function down(): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';
        $this->db->query("ALTER TABLE {$tableName} DROP COLUMN tax_rate, DROP COLUMN tax_amount");
    }
}

4. The Coordinator (`SchemaMigrationManager.php`)

The coordinator decides the run order, reads which migration versions are already applied from the database, and prevents anything running twice:

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabase;

use wpdb;
use WPStackEnterprisePluginDatabaseMigrationsMigrationInterface;

class SchemaMigrationManager {
    private const OPTION_KEY = 'wpstack_applied_schema_migrations';

    /** @var list */
    private array $migrations = [];

    public function __construct(private readonly wpdb $db) {}

    public function registerMigration(MigrationInterface $migration): void {
        $this->migrations[] = $migration;
    }

    /**
     * Execute all pending migrations in chronological order.
     *
     * @return array{executed: list, skipped: list}
     */
    public function migrate(): array {
        $applied = $this->getAppliedMigrations();
        $executed = [];
        $skipped = [];

        // Sort migrations by version string
        usort($this->migrations, static fn(MigrationInterface $a, MigrationInterface $b) => strcmp($a->getVersion(), $b->getVersion()));

        foreach ($this->migrations as $migration) {
            $version = $migration->getVersion();

            if (in_array($version, $applied, true)) {
                $skipped[] = $version;
                continue;
            }

            // Execute migration step
            $migration->up();

            // Record version as applied
            $applied[] = $version;
            update_option(self::OPTION_KEY, $applied, false);
            $executed[] = $version;
        }

        return [
            'executed' => $executed,
            'skipped'  => $skipped,
        ];
    }

    /**
     * Retrieve list of previously applied migration version tags.
     *
     * @return list
     */
    public function getAppliedMigrations(): array {
        $applied = get_option(self::OPTION_KEY, []);
        return is_array($applied) ? $applied : [];
    }
}

Backfilling Data in the Background with Action Scheduler

Suppose a migration adds a computed column to a table with 2,000,000+ rows. A single UPDATE wp_custom SET column = calc() locks the whole table for 45+ seconds, halts writes and causes replication lag.

Instead, keep the schema change separate from the heavy data transformation and process the data through an Action Scheduler queue that advances a cursor:

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseQueue;

use wpdb;

class SchemaBackfillWorker {
    public const HOOK_NAME = 'wpstack_async_schema_backfill';
    public const GROUP_NAME = 'wpstack_migrations';
    private const BATCH_SIZE = 500;

    public function __construct(private readonly wpdb $db) {}

    public function register(): void {
        add_action(self::HOOK_NAME, [$this, 'processBatch'], 10, 1);
    }

    public function dispatchInitialJob(): void {
        if (!as_has_scheduled_action(self::HOOK_NAME, ['last_id' => 0], self::GROUP_NAME)) {
            as_schedule_single_action(time(), self::HOOK_NAME, ['last_id' => 0], self::GROUP_NAME);
        }
    }

    public function processBatch(int $lastId): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';

        // Fetch primary keys of next batch using cursor pagination (avoids OFFSET performance cliff)
        $ids = $this->db->get_col(
            $this->db->prepare(
                "SELECT id FROM {$tableName} 
                 WHERE id > %d AND tax_amount = 0.0000 
                 ORDER BY id ASC 
                 LIMIT %d",
                $lastId,
                self::BATCH_SIZE
            )
        );

        if (empty($ids)) {
            // Backfill finished! Mark migration flag as complete
            update_option('wpstack_tax_backfill_completed', true, false);
            return;
        }

        $idPlaceholders = implode(',', array_map('intval', $ids));
        $maxProcessedId = max(array_map('intval', $ids));

        // Execute batch update on small subset of locked rows
        $this->db->query(
            "UPDATE {$tableName} 
             SET tax_amount = total_amount * 0.20, tax_rate = 20.00 
             WHERE id IN ({$idPlaceholders})"
        );

        // Schedule next batch immediately
        as_schedule_single_action(time(), self::HOOK_NAME, ['last_id' => $maxProcessedId], self::GROUP_NAME);
    }
}

Shadow Table Swaps Without Downtime

Changing primary keys, switching storage engines or shedding gigabytes of bloat is too risky to do in place on a busy store. The shadow table atomic swap pattern avoids that:

1. Create the shadow table: Provision wp_wpstack_invoices_v2 with the final schema.
2. Write to both tables: In the plugin’s repository layer, save each new record to v1 and v2 at the same time.
3. Backfill history: Copy existing rows from v1 to v2 in chunks with Action Scheduler.
4. Swap the tables: Rename both in one atomic MySQL statement, which completes instantly:
5. Retire the old table: After seven days of clean telemetry, drop wp_wpstack_invoices_legacy_backup.

With this pattern, a structural change carries no downtime and no risk of losing data, which suits high-availability setups and custom plugin work alike. MoxSEO’s web development team can help plan and review such migrations; see our web development services.

Foreign Keys and Cascading Deletes in Application Code

WordPress core stays away from database-level foreign keys (such as FOREIGN KEY (user_id) REFERENCES wp_users(ID) ON DELETE CASCADE) so it keeps working with MyISAM and older storage engines. In plugins, leaning only on MySQL foreign keys can also deadlock against core’s deletion hooks.

A hybrid approach works better: solid database indexes, plus application-level listeners on WordPress deletion actions that cascade the cleanup:

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabase;

use wpdb;

class DatabaseCascadeIntegrityManager {
    public function __construct(private readonly wpdb $db) {}

    public function register(): void {
        // Listen for WordPress user deletions
        add_action('deleted_user', [$this, 'handleUserDeleted'], 10, 2);
        // Listen for WooCommerce order deletions
        add_action('woocommerce_delete_order', [$this, 'handleOrderDeleted'], 10, 1);
    }

    public function handleUserDeleted(int $userId, ?int $reassignId): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';

        if ($reassignId !== null && $reassignId > 0) {
            // Reassign customer invoices to target user
            $this->db->update($tableName, ['customer_id' => $reassignId], ['customer_id' => $userId]);
        } else {
            // Soft-delete or mark invoices as orphaned for accounting compliance
            $this->db->update($tableName, ['status' => 'anonymized_gdpr'], ['customer_id' => $userId]);
        }
    }

    public function handleOrderDeleted(int $orderId): void {
        $tableName = $this->db->prefix . 'wpstack_invoices';
        // Delete child invoices when parent order is permanently removed
        $this->db->delete($tableName, ['order_id' => $orderId]);
    }
}

Running Migrations Across a Multisite Network

On a Multisite network with thousands of sub-sites, running migrations in a single request at network activation will eventually exhaust PHP workers. Process sub-sites in cursor-based batches instead:

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginDatabaseMultisite;

use WP_Site;
use WPStackEnterprisePluginDatabaseSchemaMigrationManager;

class MultisiteMigrationOrchestrator {
    public const ASYNC_HOOK = 'wpstack_multisite_batch_migrate';
    private const SITE_CHUNK_SIZE = 50;

    public function register(): void {
        add_action('wp_initialize_site', [$this, 'migrateNewSite'], 10, 1);
        add_action(self::ASYNC_HOOK, [$this, 'processSiteBatch'], 10, 1);
    }

    public function migrateNewSite(WP_Site $site): void {
        switch_to_blog((int) $site->blog_id);
        try {
            global $wpdb;
            $manager = new SchemaMigrationManager($wpdb);
            $manager->migrate();
        } finally {
            restore_current_blog();
        }
    }

    public function processSiteBatch(int $lastSiteId): void {
        $sites = get_sites([
            'number'   => self::SITE_CHUNK_SIZE,
            'site__gt' => $lastSiteId,
            'public'   => 1,
            'spam'     => 0,
            'deleted'  => 0,
        ]);

        if (empty($sites)) {
            update_site_option('wpstack_network_migration_complete', true);
            return;
        }

        $highestId = $lastSiteId;

        foreach ($sites as $site) {
            $siteId = (int) $site->blog_id;
            $highestId = max($highestId, $siteId);

            switch_to_blog($siteId);
            try {
                global $wpdb;
                $manager = new SchemaMigrationManager($wpdb);
                $manager->migrate();
            } finally {
                restore_current_blog();
            }
        }

        // Enqueue next subsite chunk
        as_schedule_single_action(time() + 2, self::ASYNC_HOOK, ['last_site_id' => $highestId], 'wpstack_migrations');
    }
}

Online Migrations for High-Traffic Tables

Once a custom table passes 500,000 rows, an ordinary ALTER TABLE run through dbDelta() can lock it for minutes, block WooCommerce checkouts and bring 504 Gateway Timeouts.

Incident Walkthrough: A Table Lockout on Black Friday

Take an online luxury goods store running custom order-fulfillment plugins whose checkout failed completely on Black Friday. At 00:05 UTC an automated plugin update ran dbDelta() inside a plugins_loaded hook to add an index to an 8-million-row table.

1. What Happened and Why

• Metadata lock pile-up: The ALTER TABLE asked for an exclusive metadata lock, but checkout transactions still held read locks. MySQL queued the DDL and every new order write queued behind it.
• Worker exhaustion: Within 40 seconds PHP-FPM hit its 256-worker limit and the whole store returned 504 Gateway Timeouts.

2. How It Was Fixed

1. Killed the blocked DDL: The on-call team ran SHOW PROCESSLIST and killed the waiting ALTER TABLE thread, which restored checkout immediately.
2. Moved to online DDL: They added the composite index with ALTER TABLE wp_orders ADD KEY idx_status (status), ALGORITHM=INPLACE, LOCK=NONE; in a planned off-peak window.
3. Changed the policy: Synchronous dbDelta() calls in general page-load hooks were banned, and the versioned SchemaMigrationManager now runs only from WP-CLI deploy scripts.

Managing Schema Changes with WP-CLI

These WP-CLI commands expose migrations to DevOps automation pipelines:

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginCli;

use WP_CLI;
use WPStackEnterprisePluginDatabaseSchemaMigrationManager;
use WPStackEnterprisePluginDatabaseMigrationsMigration_2026_10_01_CreateInvoicesTable;
use WPStackEnterprisePluginDatabaseMigrationsMigration_2026_10_15_AddTaxColumnsAndRenameStatus;

class SchemaCliCommand {
    /**
     * Run all pending custom database schema migrations.
     *
     * ## EXAMPLES
     *
     *     wp wpstack-schema migrate
     */
    public function migrate(array $args, array $assocArgs): void {
        global $wpdb;
        $manager = new SchemaMigrationManager($wpdb);
        $manager->registerMigration(new Migration_2026_10_01_CreateInvoicesTable($wpdb));
        $manager->registerMigration(new Migration_2026_10_15_AddTaxColumnsAndRenameStatus($wpdb));

        WP_CLI::log('Starting database schema migration check...');
        $result = $manager->migrate();

        if (empty($result['executed'])) {
            WP_CLI::success('Database schema is already up to date. No pending migrations.');
            return;
        }

        foreach ($result['executed'] as $version) {
            WP_CLI::log("Successfully applied migration: [{$version}]");
        }

        WP_CLI::success('All pending schema migrations completed successfully.');
    }

    /**
     * Display status of applied and pending migrations.
     *
     * ## EXAMPLES
     *
     *     wp wpstack-schema status
     */
    public function status(array $args, array $assocArgs): void {
        global $wpdb;
        $manager = new SchemaMigrationManager($wpdb);
        $applied = $manager->getAppliedMigrations();

        WP_CLI::line('Applied Schema Migrations:');
        if (empty($applied)) {
            WP_CLI::line('  (None)');
            return;
        }

        foreach ($applied as $version) {
            WP_CLI::line("  - [X] {$version}");
        }
    }
}

if (defined('WP_CLI') && WP_CLI) {
    WP_CLI::add_command('wpstack-schema', SchemaCliCommand::class);
}

Testing Migrations with PHPUnit

PHPUnit exercises the full migration lifecycle and checks that tables are created, column changes apply cleanly and indexes exist:

<?php
declare(strict_types=1);

namespace WPStackEnterprisePluginTestsIntegration;

use WP_UnitTestCase;
use WPStackEnterprisePluginDatabaseSchemaMigrationManager;
use WPStackEnterprisePluginDatabaseMigrationsMigration_2026_10_01_CreateInvoicesTable;
use WPStackEnterprisePluginDatabaseMigrationsMigration_2026_10_15_AddTaxColumnsAndRenameStatus;

class Test_SchemaMigrationsIntegration extends WP_UnitTestCase {
    private SchemaMigrationManager $manager;
    private string $tableName;

    public function setUp(): void {
        parent::setUp();
        global $wpdb;

        $this->tableName = $wpdb->prefix . 'wpstack_invoices';
        $wpdb->query("DROP TABLE IF EXISTS {$this->tableName}");
        delete_option('wpstack_applied_schema_migrations');

        $this->manager = new SchemaMigrationManager($wpdb);
        $this->manager->registerMigration(new Migration_2026_10_01_CreateInvoicesTable($wpdb));
        $this->manager->registerMigration(new Migration_2026_10_15_AddTaxColumnsAndRenameStatus($wpdb));
    }

    public function tearDown(): void {
        global $wpdb;
        $wpdb->query("DROP TABLE IF EXISTS {$this->tableName}");
        parent::tearDown();
    }

    public function test_migrations_create_table_and_apply_incremental_columns(): void {
        global $wpdb;

        // 1. Execute migrations
        $result = $this->manager->migrate();
        $this->assertCount(2, $result['executed']);

        // 2. Verify table exists
        $tableExists = $wpdb->get_var("SHOW TABLES LIKE '{$this->tableName}'");
        $this->assertSame($this->tableName, $tableExists);

        // 3. Verify columns exist
        $columns = $wpdb->get_col("DESCRIBE {$this->tableName}", 0);
        $this->assertContains('id', $columns);
        $this->assertContains('order_id', $columns);
        $this->assertContains('tax_rate', $columns);
        $this->assertContains('tax_amount', $columns);

        // 4. Verify re-running migration performs zero duplicate executions
        $secondRun = $this->manager->migrate();
        $this->assertEmpty($secondRun['executed']);
        $this->assertCount(2, $secondRun['skipped']);
    }
}

Troubleshooting Migrations in Production

For complex custom-table migrations on multisite and larger installs, this table lists the usual symptoms, causes and fixes:

Common Questions

Aditya Bhimrajka

Aditya Bhimrajka is the Chief Search Systems Architect at MoxSEO, leading research in enterprise technical SEO, knowledge graphs, large-scale indexing physics, and generative engine optimization.