WordPress Database Schema Migrations for Plugin Developers: Beyond dbDelta
Every WordPress plugin developer hits the same wall eventually. You ship version 1.0 with a custom database table. Users install it. Then you need to change that table for version 1.1. You reach for dbDelta(), the function WordPress itself uses for schema updates, and you discover that it cannot do half of what you need. Columns you want to remove stay put. Type changes behave unpredictably. You start writing raw ALTER TABLE statements wrapped in version checks, and before long your activation hook looks like spaghetti.
This article presents a better path. We will examine exactly where dbDelta() falls short, then build a full migration system from scratch that tracks schema versions, supports rollbacks, handles serialized data safely, integrates with WP-CLI, and runs reliably across development, staging, and production environments. Along the way, we will look at the deliciousbrains/wp-migrations library and discuss strategies for testing migrations in CI/CD pipelines.
The code examples assume PHP 7.4 or later. Everything runs on standard WordPress with no framework dependencies.
What dbDelta Actually Does (and Does Not Do)
The dbDelta() function lives in wp-admin/includes/upgrade.php. WordPress uses it during core upgrades to reconcile the current database schema with the expected schema. You pass it a CREATE TABLE statement, and it compares that statement against the existing table structure. If the table does not exist, it creates it. If it does exist, dbDelta() adds any missing columns and indexes.
That sounds useful until you look at what it cannot handle.
Columns Cannot Be Removed
dbDelta() only adds. It never drops a column, even if that column no longer appears in the CREATE TABLE statement you pass in. The WordPress core team made this decision deliberately. Removing columns automatically would risk destroying data if a developer accidentally omitted a column from their schema string. In practice, this means that once you ship a column to production, dbDelta() will never clean it up for you.
Column Type Changes Are Unreliable
Suppose you shipped a column as VARCHAR(100) and now need it to be TEXT. The dbDelta() function compares column definitions using string matching against the output of DESCRIBE. If the formatting does not match exactly, the comparison fails silently or produces unexpected results. The function does not understand MySQL type equivalences. It does not know that INT and INT(11) are the same thing in modern MySQL. These string comparison quirks mean that type modifications may or may not apply depending on your MySQL version, your existing schema, and the exact whitespace in your CREATE TABLE string.
No Support for Renaming
You cannot rename a column or a table through dbDelta(). There is no mechanism for it. A rename requires an ALTER TABLE … CHANGE COLUMN statement, which dbDelta() will never generate.
No Ordering Guarantees
If you have multiple tables with foreign key relationships, dbDelta() provides no way to control the order in which changes are applied. You get whatever order the function decides internally.
No Rollback
There is no concept of reversing a dbDelta() operation. If an upgrade goes wrong, you are left to fix things manually.
When dbDelta Works Fine
For simple cases, dbDelta() remains the right tool. If your plugin creates tables on activation and never modifies them afterward, or if your schema changes are limited to adding new columns, dbDelta() does the job. The problems surface when your plugin matures and your schema evolves in ways that go beyond additive changes.
Here is a typical dbDelta() call for reference:
function myplugin_create_table() {
global $wpdb;
$table_name = $wpdb->prefix . 'myplugin_records';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
record_type varchar(50) NOT NULL DEFAULT '',
record_data longtext NOT NULL,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id),
KEY user_id (user_id),
KEY record_type (record_type)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
}
register_activation_hook( __FILE__, 'myplugin_create_table' );Note the two spaces before (id) in PRIMARY KEY. That is not a typo. The dbDelta() parser requires exactly two spaces there. This kind of fragile formatting requirement is another reason developers eventually look for alternatives.
Building a Version-Tracked Migration System
The core idea behind a migration system is simple: track the current schema version in the database, and run numbered migration scripts sequentially to bring the schema up to date. Laravel, Rails, Django, and every other major framework uses this pattern. WordPress plugins can use it too.
Storing the Schema Version
The wp_options table is the natural place to store your plugin’s current schema version. Use get_option() and update_option() with autoload set to ‘yes’ so the value is available without an extra database query on every page load.
$current_version = get_option( 'myplugin_db_version', '0.0.0' );
update_option( 'myplugin_db_version', '1.2.0' );The version string follows semantic versioning, but you could also use simple integers. What matters is that versions are comparable and sequential.
The Migration Runner
At its simplest, the runner is a function that checks the stored version, finds all migrations with a higher version number, runs them in order, and updates the stored version after each one succeeds.
function myplugin_run_migrations() {
$current_version = get_option( 'myplugin_db_version', '0.0.0' );
$migrations = array(
'1.0.0' => 'myplugin_migration_1_0_0',
'1.1.0' => 'myplugin_migration_1_1_0',
'1.2.0' => 'myplugin_migration_1_2_0',
);
ksort( $migrations );
foreach ( $migrations as $version => $callback ) {
if ( version_compare( $current_version, $version, '<' ) ) {
call_user_func( $callback );
update_option( 'myplugin_db_version', $version );
}
}
}
add_action( 'plugins_loaded', 'myplugin_run_migrations' );Hooking into plugins_loaded means migrations run on every request where the version is outdated. For small, fast migrations this is fine. For heavy operations, you should gate the runner behind an admin check or use WP-CLI exclusively, which we will cover later.
Why plugins_loaded Instead of Activation Hooks
The activation hook only fires when a user manually activates the plugin through the admin interface. It does not fire during automatic updates via the WordPress updater or when files are replaced via FTP/deployment. Running migrations on plugins_loaded guarantees they execute regardless of how the plugin code was updated. The version check ensures they only run once.
File-Based Migration Classes
Callback functions in an array work for small plugins, but they become unwieldy as your migration count grows. A class-based approach with one file per migration scales much better.
The Migration Interface
Define a simple interface that every migration must implement:
interface MyPlugin_Migration {
/**
* Run the migration.
*/
public function up();
/**
* Reverse the migration.
*/
public function down();
/**
* Return the version string for this migration.
*/
public function get_version();
}The up() method applies the change. The down() method reverses it. Not every migration can be reversed cleanly, but having the method there forces you to think about it.
A Concrete Migration
// migrations/class-migration-1-1-0.php
class MyPlugin_Migration_1_1_0 implements MyPlugin_Migration {
public function get_version() {
return '1.1.0';
}
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query( "ALTER TABLE $table ADD COLUMN priority tinyint(1) NOT NULL DEFAULT 0 AFTER record_type" );
$wpdb->query( "ALTER TABLE $table ADD INDEX idx_priority (priority)" );
}
public function down() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query( "ALTER TABLE $table DROP INDEX idx_priority" );
$wpdb->query( "ALTER TABLE $table DROP COLUMN priority" );
}
}Autoloading Migrations
Place all migration files in a migrations/ directory inside your plugin. The runner scans that directory, instantiates each class, and sorts by version:
function myplugin_load_migrations() {
$migration_dir = plugin_dir_path( __FILE__ ) . 'migrations/';
$files = glob( $migration_dir . 'class-migration-*.php' );
$migrations = array();
foreach ( $files as $file ) {
require_once $file;
$basename = basename( $file, '.php' );
// Convert 'class-migration-1-1-0' to 'MyPlugin_Migration_1_1_0'
$class_name = str_replace(
array( 'class-', '-' ),
array( 'MyPlugin_', '_' ),
$basename
);
// Capitalize first letter of each segment
$class_name = implode( '_', array_map( 'ucfirst', explode( '_', $class_name ) ) );
if ( class_exists( $class_name ) ) {
$instance = new $class_name();
if ( $instance instanceof MyPlugin_Migration ) {
$migrations[ $instance->get_version() ] = $instance;
}
}
}
uksort( $migrations, 'version_compare' );
return $migrations;
}
function myplugin_run_migrations() {
$current_version = get_option( 'myplugin_db_version', '0.0.0' );
$migrations = myplugin_load_migrations();
foreach ( $migrations as $version => $migration ) {
if ( version_compare( $current_version, $version, '<' ) ) {
$migration->up();
update_option( 'myplugin_db_version', $version );
}
}
}
add_action( 'plugins_loaded', 'myplugin_run_migrations' );Each migration is self-contained. You can review them individually, test them in isolation, and track them in version control. When a new developer joins the project, they can read through the migration history to understand how the schema evolved.
Handling Serialized Data During Schema Changes
WordPress stores serialized PHP arrays and objects in many places: wp_options, wp_postmeta, wp_usermeta, and frequently in custom tables too. Schema migrations that touch serialized data require special care because a careless UPDATE query can corrupt serialized strings.
The Serialization Format Problem
PHP's serialize() function encodes string lengths into the serialized output. The value a:1:{s:4:"name";s:5:"Alice";} contains s:4 for the four-character key "name" and s:5 for the five-character value "Alice". If you run a SQL REPLACE on serialized data to change "Alice" to "Bob", the string length marker still says s:5 but "Bob" is only three characters. The result is corrupt data that PHP cannot unserialize.
The Safe Approach: Read, Modify, Write
Always handle serialized data in PHP, never in raw SQL. Read the row, unserialize it, make your modifications in PHP, serialize the result, and write it back.
class MyPlugin_Migration_1_3_0 implements MyPlugin_Migration {
public function get_version() {
return '1.3.0';
}
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
// Fetch all rows with serialized data
$rows = $wpdb->get_results(
"SELECT id, record_data FROM $table WHERE record_data != ''",
ARRAY_A
);
foreach ( $rows as $row ) {
$data = maybe_unserialize( $row['record_data'] );
if ( ! is_array( $data ) ) {
continue;
}
// Rename 'old_field' to 'new_field' in the serialized array
if ( isset( $data['old_field'] ) ) {
$data['new_field'] = $data['old_field'];
unset( $data['old_field'] );
}
// Add a default value for a new field
if ( ! isset( $data['status'] ) ) {
$data['status'] = 'active';
}
$wpdb->update(
$table,
array( 'record_data' => maybe_serialize( $data ) ),
array( 'id' => $row['id'] ),
array( '%s' ),
array( '%d' )
);
}
}
public function down() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$rows = $wpdb->get_results(
"SELECT id, record_data FROM $table WHERE record_data != ''",
ARRAY_A
);
foreach ( $rows as $row ) {
$data = maybe_unserialize( $row['record_data'] );
if ( ! is_array( $data ) ) {
continue;
}
if ( isset( $data['new_field'] ) ) {
$data['old_field'] = $data['new_field'];
unset( $data['new_field'] );
}
unset( $data['status'] );
$wpdb->update(
$table,
array( 'record_data' => maybe_serialize( $data ) ),
array( 'id' => $row['id'] ),
array( '%s' ),
array( '%d' )
);
}
}
}Batching Large Datasets
If your table has millions of rows, loading them all into memory at once will exhaust PHP's memory limit. Process rows in batches:
$batch_size = 500;
$offset = 0;
do {
$rows = $wpdb->get_results(
$wpdb->prepare(
"SELECT id, record_data FROM $table WHERE record_data != '' ORDER BY id ASC LIMIT %d OFFSET %d",
$batch_size,
$offset
),
ARRAY_A
);
foreach ( $rows as $row ) {
$data = maybe_unserialize( $row['record_data'] );
// ... transform data ...
$wpdb->update(
$table,
array( 'record_data' => maybe_serialize( $data ) ),
array( 'id' => $row['id'] ),
array( '%s' ),
array( '%d' )
);
}
$offset += $batch_size;
} while ( count( $rows ) === $batch_size );For truly massive tables, consider using a background processing library like WP Background Processing or running the migration exclusively through WP-CLI where there are no HTTP timeout constraints.
Dealing with Corrupted Serialized Data
In the real world, databases accumulate corrupted serialized strings from buggy plugins, interrupted writes, or character encoding issues. Your migration should handle this gracefully:
$data = @unserialize( $row['record_data'] );
if ( $data === false && $row['record_data'] !== 'b:0;' ) {
// Log the corrupted row for manual review
error_log( sprintf(
'MyPlugin migration 1.3.0: Corrupted serialized data in row %d, skipping.',
$row['id']
) );
continue;
}Using maybe_unserialize() is generally safer than raw unserialize() because it checks whether the data is actually serialized first. But you still need to handle the case where unserialization fails.
Safe Column Removal and Data Migration Strategies
Removing a column from a production database table is one of the riskier schema operations. If any code still references that column, queries will break. The safe way to remove a column follows a multi-step process spread across multiple plugin versions.
The Three-Release Strategy
Release N (Preparation): Add the new column (if you are replacing one). Update all code to write to both the old and new columns. Read from the new column with a fallback to the old one.
// Writing to both columns during the transition
$wpdb->update(
$table,
array(
'old_email' => $email,
'contact_email' => $email, // new column
),
array( 'id' => $record_id ),
array( '%s', '%s' ),
array( '%d' )
);
// Reading with fallback
$email = ! empty( $row->contact_email ) ? $row->contact_email : $row->old_email;Release N+1 (Migration): Run a migration that copies data from the old column to the new column for any rows where the new column is still empty. Update all read operations to use only the new column. Stop writing to the old column.
class MyPlugin_Migration_1_4_0 implements MyPlugin_Migration {
public function get_version() {
return '1.4.0';
}
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
// Copy remaining data from old column to new column
$wpdb->query(
"UPDATE $table SET contact_email = old_email WHERE contact_email = '' AND old_email != ''"
);
}
public function down() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query(
"UPDATE $table SET old_email = contact_email WHERE old_email = '' AND contact_email != ''"
);
}
}Release N+2 (Cleanup): Drop the old column. By this point, all active installations have had at least one update cycle to migrate data.
class MyPlugin_Migration_1_5_0 implements MyPlugin_Migration {
public function get_version() {
return '1.5.0';
}
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
// Verify no data will be lost
$orphaned = $wpdb->get_var(
"SELECT COUNT(*) FROM $table WHERE contact_email = '' AND old_email != ''"
);
if ( $orphaned > 0 ) {
// Safety check failed. Copy remaining data first.
$wpdb->query(
"UPDATE $table SET contact_email = old_email WHERE contact_email = '' AND old_email != ''"
);
}
$wpdb->query( "ALTER TABLE $table DROP COLUMN old_email" );
}
public function down() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query( "ALTER TABLE $table ADD COLUMN old_email varchar(255) NOT NULL DEFAULT '' AFTER contact_email" );
}
}Why Three Releases?
Not every user updates promptly. Some sites skip versions. The three-release window ensures that even users who miss one update will still have their data migrated before the old column disappears. The safety check in the final migration is an extra safeguard for users who somehow jump from a very old version directly to the latest.
Dropping Tables Entirely
When deprecating an entire feature and its associated table, follow the same pattern. Mark the feature as deprecated in Release N. Stop writing to the table in Release N+1. Drop the table in Release N+2. Provide an export mechanism so users can retrieve their data before it is removed.
Testing Migrations in CI/CD Pipelines
Schema migrations that work on your local machine can fail on production due to differences in MySQL versions, character sets, existing data patterns, or table sizes. Automated testing catches these issues before they reach users.
Setting Up a Test Database
The WordPress test suite includes a mechanism for creating a temporary test database. You can use this infrastructure for migration tests. Install the test suite using the WP-CLI scaffold:
wp scaffold plugin-tests mypluginThis creates a tests/ directory with PHPUnit configuration and a bootstrap file. The bootstrap creates a fresh WordPress installation in memory using SQLite or a dedicated MySQL test database.
Writing Migration Tests
Each migration should have at least two tests: one that runs up() on the expected schema state and verifies the result, and one that runs down() and verifies the reversal.
class Test_Migration_1_1_0 extends WP_UnitTestCase {
private $migration;
public function set_up() {
parent::set_up();
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
// Create the pre-migration schema (version 1.0.0)
$wpdb->query( "DROP TABLE IF EXISTS $table" );
$wpdb->query( "CREATE TABLE $table (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
record_type varchar(50) NOT NULL DEFAULT '',
record_data longtext NOT NULL,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (id),
KEY user_id (user_id),
KEY record_type (record_type)
) {$wpdb->get_charset_collate()}" );
$this->migration = new MyPlugin_Migration_1_1_0();
}
public function test_up_adds_priority_column() {
$this->migration->up();
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$columns = $wpdb->get_col( "DESCRIBE $table", 0 );
$this->assertContains( 'priority', $columns );
}
public function test_up_adds_priority_index() {
$this->migration->up();
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$indexes = $wpdb->get_results( "SHOW INDEX FROM $table WHERE Key_name = 'idx_priority'" );
$this->assertNotEmpty( $indexes );
}
public function test_down_removes_priority_column() {
$this->migration->up();
$this->migration->down();
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$columns = $wpdb->get_col( "DESCRIBE $table", 0 );
$this->assertNotContains( 'priority', $columns );
}
public function test_up_preserves_existing_data() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->insert( $table, array(
'user_id' => 1,
'record_type' => 'task',
'record_data' => 'Test data',
'created_at' => '2022-01-15 10:30:00',
) );
$id = $wpdb->insert_id;
$this->migration->up();
$row = $wpdb->get_row(
$wpdb->prepare( "SELECT * FROM $table WHERE id = %d", $id )
);
$this->assertEquals( 'task', $row->record_type );
$this->assertEquals( 'Test data', $row->record_data );
$this->assertEquals( 0, $row->priority ); // default value
}
}Testing the Full Migration Sequence
Beyond testing individual migrations, write an integration test that runs all migrations from version 0.0.0 to the latest version and verifies the final schema matches expectations:
class Test_Full_Migration_Sequence extends WP_UnitTestCase {
public function test_all_migrations_run_successfully() {
// Start with no tables and version 0.0.0
delete_option( 'myplugin_db_version' );
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query( "DROP TABLE IF EXISTS $table" );
// Run the initial setup (version 1.0.0 creates the table)
myplugin_run_migrations();
$current = get_option( 'myplugin_db_version' );
$this->assertEquals( '1.5.0', $current ); // latest version
// Verify final schema
$columns = $wpdb->get_col( "DESCRIBE $table", 0 );
$this->assertContains( 'id', $columns );
$this->assertContains( 'priority', $columns );
$this->assertContains( 'contact_email', $columns );
$this->assertNotContains( 'old_email', $columns );
}
}CI Configuration
A minimal GitHub Actions workflow for running migration tests:
name: Migration Tests
on: [push, pull_request]
jobs:
test:
runs-on: ubuntu-latest
services:
mysql:
image: mysql:8.0
env:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: wordpress_test
ports:
- 3306:3306
options: >-
--health-cmd="mysqladmin ping"
--health-interval=10s
--health-timeout=5s
--health-retries=3
steps:
- uses: actions/checkout@v3
- uses: shivammathur/setup-php@v2
with:
php-version: '8.0'
- name: Install WP Test Suite
run: bash bin/install-wp-tests.sh wordpress_test root root 127.0.0.1
- name: Run Tests
run: vendor/bin/phpunit --group=migrationsRun migration tests against multiple MySQL versions (5.7, 8.0, and MariaDB 10.4) to catch compatibility issues. MySQL 8.0 changed the default character set to utf8mb4 and made several reserved word changes that can break migrations written for older versions.
Multi-Environment Migration Coordination
Real plugin development involves multiple environments: local development, staging, and production. Migrations must run correctly in each environment without causing conflicts.
The Version Lock Problem
Consider this scenario: two developers are working on separate features that each require a schema change. Developer A creates migration 1.6.0. Developer B, working from the same base, also creates migration 1.6.0. When both branches merge, you have two different migrations with the same version number.
Timestamp-Based Versioning
One solution is to use timestamps instead of semantic versions for migration ordering:
class MyPlugin_Migration_20220615_143000 implements MyPlugin_Migration {
public function get_version() {
return '20220615143000';
}
// ...
}Timestamps naturally avoid conflicts because two developers creating migrations at different times will produce different version numbers. The runner sorts by timestamp instead of using version_compare():
uksort( $migrations, function( $a, $b ) {
return strcmp( $a, $b );
});The trade-off is that timestamp versions are less human-readable than semantic versions. A hybrid approach works well: use semantic versioning for your plugin's public version and timestamp-based versioning for individual migration files. Store the list of applied migration timestamps in an option array rather than a single version string.
Tracking Applied Migrations as an Array
function myplugin_run_migrations() {
$applied = get_option( 'myplugin_applied_migrations', array() );
$migrations = myplugin_load_migrations();
foreach ( $migrations as $version => $migration ) {
if ( ! in_array( $version, $applied, true ) ) {
$migration->up();
$applied[] = $version;
update_option( 'myplugin_applied_migrations', $applied );
}
}
}This approach mirrors how frameworks like Laravel track migrations. Each migration is either applied or not, and the system does not care about sequential ordering beyond the natural sort order.
Database Locking for Concurrent Requests
On high-traffic sites, multiple web requests might hit the migration runner simultaneously during an upgrade. Without locking, two processes could try to run the same migration at the same time, which can cause duplicate columns, deadlocks, or corrupted data.
Use a transient-based lock:
function myplugin_run_migrations() {
// Attempt to acquire a lock for 5 minutes
$lock_key = 'myplugin_migration_lock';
if ( get_transient( $lock_key ) ) {
return; // Another process is running migrations
}
set_transient( $lock_key, time(), 5 * MINUTE_IN_SECONDS );
$applied = get_option( 'myplugin_applied_migrations', array() );
$migrations = myplugin_load_migrations();
foreach ( $migrations as $version => $migration ) {
if ( ! in_array( $version, $applied, true ) ) {
$migration->up();
$applied[] = $version;
update_option( 'myplugin_applied_migrations', $applied );
}
}
delete_transient( $lock_key );
}For even stronger locking, use MySQL's GET_LOCK() function:
global $wpdb;
$lock_name = $wpdb->prefix . 'myplugin_migration';
$lock_acquired = $wpdb->get_var(
$wpdb->prepare( "SELECT GET_LOCK(%s, 0)", $lock_name )
);
if ( $lock_acquired != 1 ) {
return; // Could not acquire lock
}
// Run migrations...
$wpdb->query(
$wpdb->prepare( "SELECT RELEASE_LOCK(%s)", $lock_name )
);The MySQL lock is more reliable than transients because it operates at the database level and is immune to object cache inconsistencies.
Staging and Production Sync
When you push a database from production to staging for testing, the staging environment will have production's migration history. This is usually what you want. But if staging has migrations that production does not (because you are testing upcoming changes), pulling the production database will roll back your staging schema without running the down() methods.
The safest workflow: always let migrations run forward. Never sync a database backward. If you need to test against production data, pull the production database to staging and then let the migration runner apply any pending staging migrations on top of it.
WP-CLI Commands for Running and Rolling Back Migrations
WP-CLI provides the ideal interface for running migrations on production systems. There are no HTTP timeouts, no memory limits imposed by the web server, and you get real-time output in the terminal.
Registering a Custom WP-CLI Command
if ( defined( 'WP_CLI' ) && WP_CLI ) {
class MyPlugin_Migration_Command {
/**
* Run all pending migrations.
*
* ## EXAMPLES
*
* wp myplugin migrate
*
* @when after_wp_load
*/
public function migrate( $args, $assoc_args ) {
$applied = get_option( 'myplugin_applied_migrations', array() );
$migrations = myplugin_load_migrations();
$pending = array();
foreach ( $migrations as $version => $migration ) {
if ( ! in_array( $version, $applied, true ) ) {
$pending[ $version ] = $migration;
}
}
if ( empty( $pending ) ) {
WP_CLI::success( 'No pending migrations.' );
return;
}
WP_CLI::log( sprintf( 'Found %d pending migration(s).', count( $pending ) ) );
foreach ( $pending as $version => $migration ) {
WP_CLI::log( sprintf( 'Running migration %s...', $version ) );
try {
$migration->up();
$applied[] = $version;
update_option( 'myplugin_applied_migrations', $applied );
WP_CLI::success( sprintf( 'Migration %s applied.', $version ) );
} catch ( Exception $e ) {
WP_CLI::error( sprintf(
'Migration %s failed: %s',
$version,
$e->getMessage()
) );
}
}
WP_CLI::success( 'All migrations applied.' );
}
/**
* Roll back the last N migrations.
*
* ## OPTIONS
*
* [--steps=]
* : Number of migrations to roll back. Default 1.
*
* ## EXAMPLES
*
* wp myplugin rollback
* wp myplugin rollback --steps=3
*
* @when after_wp_load
*/
public function rollback( $args, $assoc_args ) {
$steps = isset( $assoc_args['steps'] ) ? (int) $assoc_args['steps'] : 1;
$applied = get_option( 'myplugin_applied_migrations', array() );
if ( empty( $applied ) ) {
WP_CLI::warning( 'No migrations to roll back.' );
return;
}
$migrations = myplugin_load_migrations();
// Process in reverse order
$to_rollback = array_slice( $applied, -$steps );
$to_rollback = array_reverse( $to_rollback );
foreach ( $to_rollback as $version ) {
if ( ! isset( $migrations[ $version ] ) ) {
WP_CLI::warning( sprintf(
'Migration file for %s not found. Skipping.',
$version
) );
continue;
}
WP_CLI::log( sprintf( 'Rolling back migration %s...', $version ) );
try {
$migrations[ $version ]->down();
$applied = array_values( array_diff( $applied, array( $version ) ) );
update_option( 'myplugin_applied_migrations', $applied );
WP_CLI::success( sprintf( 'Migration %s rolled back.', $version ) );
} catch ( Exception $e ) {
WP_CLI::error( sprintf(
'Rollback of %s failed: %s',
$version,
$e->getMessage()
) );
}
}
}
/**
* Show migration status.
*
* ## EXAMPLES
*
* wp myplugin migrate-status
*
* @when after_wp_load
*/
public function status( $args, $assoc_args ) {
$applied = get_option( 'myplugin_applied_migrations', array() );
$migrations = myplugin_load_migrations();
$rows = array();
foreach ( $migrations as $version => $migration ) {
$rows[] = array(
'Version' => $version,
'Status' => in_array( $version, $applied, true ) ? 'Applied' : 'Pending',
'Class' => get_class( $migration ),
);
}
WP_CLI\Utils\format_items( 'table', $rows, array( 'Version', 'Status', 'Class' ) );
}
}
WP_CLI::add_command( 'myplugin', 'MyPlugin_Migration_Command' );
} Using the Commands
Check status:
wp myplugin statusOutput:
+-----------------+---------+-------------------------------+
| Version | Status | Class |
+-----------------+---------+-------------------------------+
| 1.0.0 | Applied | MyPlugin_Migration_1_0_0 |
| 1.1.0 | Applied | MyPlugin_Migration_1_1_0 |
| 1.2.0 | Pending | MyPlugin_Migration_1_2_0 |
+-----------------+---------+-------------------------------+Run pending migrations:
wp myplugin migrateRoll back the last two migrations:
wp myplugin rollback --steps=2Dry Run Mode
Adding a --dry-run flag lets you see what would happen without making changes:
/**
* Run all pending migrations.
*
* ## OPTIONS
*
* [--dry-run]
* : Show what would be executed without making changes.
*
* @when after_wp_load
*/
public function migrate( $args, $assoc_args ) {
$dry_run = isset( $assoc_args['dry-run'] );
$applied = get_option( 'myplugin_applied_migrations', array() );
$migrations = myplugin_load_migrations();
foreach ( $migrations as $version => $migration ) {
if ( ! in_array( $version, $applied, true ) ) {
if ( $dry_run ) {
WP_CLI::log( sprintf( '[DRY RUN] Would apply migration %s (%s)', $version, get_class( $migration ) ) );
} else {
$migration->up();
$applied[] = $version;
update_option( 'myplugin_applied_migrations', $applied );
WP_CLI::success( sprintf( 'Applied %s', $version ) );
}
}
}
if ( $dry_run ) {
WP_CLI::log( 'Dry run complete. No changes were made.' );
}
}The deliciousbrains/wp-migrations Library
If you prefer not to build a migration system from scratch, the deliciousbrains/wp-migrations package provides a ready-made solution. It was built by the team behind WP Migrate (formerly WP Migrate DB Pro), so it reflects real-world experience with WordPress database operations.
Installation
composer require deliciousbrains/wp-migrationsCreating a Migration
Migrations extend the DBI\WP_Migration base class:
use DBI\WP_Migration;
class AddPriorityColumn extends WP_Migration {
public function run() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query( "ALTER TABLE $table ADD COLUMN priority tinyint(1) NOT NULL DEFAULT 0" );
}
}Registering Migrations
Migrations are registered in order. The library tracks which ones have been applied using an option in wp_options:
use DBI\WP_Migrations;
WP_Migrations::register(
'myplugin',
array(
AddPriorityColumn::class,
RenameEmailColumn::class,
DropLegacyTable::class,
)
);Running Migrations
The library hooks into admin_init by default to run pending migrations. You can also trigger them manually:
WP_Migrations::run( 'myplugin' );Limitations
The library is intentionally minimal. It does not provide down() methods for rollback, WP-CLI integration, or dry-run support. It handles the version tracking and sequential execution, and leaves everything else to you. For many plugins, this is enough. If you need rollback support or CLI commands, you will need to extend the library or build your own system using the patterns described earlier in this article.
When to Use It vs. Build Your Own
Use deliciousbrains/wp-migrations when:
- Your plugin already uses Composer.
- You need forward-only migrations without rollback.
- You want to avoid maintaining migration infrastructure code.
Build your own when:
- You need rollback support.
- You want WP-CLI integration.
- You need to distribute the plugin through the WordPress.org repository (which does not support Composer dependencies bundled as packages).
- You want full control over locking, batching, and error handling.
Complete Working Example: Building a Migration System from Scratch
Let us put everything together into a complete, working migration system that you can drop into any WordPress plugin. This system supports forward migrations, rollbacks, WP-CLI commands, locking, batch processing, and full testability.
Directory Structure
myplugin/
├── myplugin.php # Main plugin file
├── includes/
│ ├── class-migration-runner.php # Core migration runner
│ ├── interface-migration.php # Migration interface
│ └── class-cli-command.php # WP-CLI integration
├── migrations/
│ ├── class-migration-1-0-0.php # Initial table creation
│ ├── class-migration-1-1-0.php # Add priority column
│ └── class-migration-1-2-0.php # Serialized data transform
└── tests/
└── test-migrations.php # PHPUnit testsThe Migration Interface
// includes/interface-migration.php
interface MyPlugin_Migration_Interface {
/**
* Apply this migration.
*
* @return void
* @throws RuntimeException If the migration fails.
*/
public function up();
/**
* Reverse this migration.
*
* @return void
* @throws RuntimeException If the rollback fails.
*/
public function down();
/**
* Get the unique version identifier for this migration.
*
* @return string
*/
public function get_version();
/**
* Get a human-readable description of what this migration does.
*
* @return string
*/
public function get_description();
}The Migration Runner
// includes/class-migration-runner.php
class MyPlugin_Migration_Runner {
private $option_key;
private $lock_key;
private $migration_dir;
private $class_prefix;
public function __construct( $option_key = 'myplugin_applied_migrations', $migration_dir = null ) {
$this->option_key = $option_key;
$this->lock_key = $option_key . '_lock';
$this->migration_dir = $migration_dir ?: dirname( __DIR__ ) . '/migrations/';
$this->class_prefix = 'MyPlugin_Migration_';
}
/**
* Load all migration classes from the migrations directory.
*
* @return MyPlugin_Migration_Interface[]
*/
public function load_migrations() {
$files = glob( $this->migration_dir . 'class-migration-*.php' );
$migrations = array();
if ( empty( $files ) ) {
return $migrations;
}
foreach ( $files as $file ) {
require_once $file;
$basename = basename( $file, '.php' );
// Extract version part: 'class-migration-1-1-0' -> '1-1-0'
$version_part = str_replace( 'class-migration-', '', $basename );
// Convert to class name: '1-1-0' -> '1_1_0'
$class_suffix = str_replace( '-', '_', $version_part );
$class_name = $this->class_prefix . $class_suffix;
if ( class_exists( $class_name ) ) {
$instance = new $class_name();
if ( $instance instanceof MyPlugin_Migration_Interface ) {
$migrations[ $instance->get_version() ] = $instance;
}
}
}
uksort( $migrations, 'version_compare' );
return $migrations;
}
/**
* Get the list of applied migration versions.
*
* @return string[]
*/
public function get_applied() {
return get_option( $this->option_key, array() );
}
/**
* Get pending migrations that have not been applied yet.
*
* @return MyPlugin_Migration_Interface[]
*/
public function get_pending() {
$applied = $this->get_applied();
$migrations = $this->load_migrations();
$pending = array();
foreach ( $migrations as $version => $migration ) {
if ( ! in_array( $version, $applied, true ) ) {
$pending[ $version ] = $migration;
}
}
return $pending;
}
/**
* Acquire a database-level lock to prevent concurrent migration runs.
*
* @return bool True if the lock was acquired.
*/
private function acquire_lock() {
global $wpdb;
$lock_name = $wpdb->prefix . $this->lock_key;
$result = $wpdb->get_var(
$wpdb->prepare( "SELECT GET_LOCK(%s, 0)", $lock_name )
);
return $result == 1;
}
/**
* Release the database-level lock.
*/
private function release_lock() {
global $wpdb;
$lock_name = $wpdb->prefix . $this->lock_key;
$wpdb->query(
$wpdb->prepare( "SELECT RELEASE_LOCK(%s)", $lock_name )
);
}
/**
* Run all pending migrations.
*
* @param callable|null $logger Optional callback for log messages. Receives (string $message, string $level).
* @param bool $dry_run If true, do not apply changes.
* @return array Array of applied version strings.
* @throws RuntimeException If the lock cannot be acquired.
*/
public function run( $logger = null, $dry_run = false ) {
if ( ! $this->acquire_lock() ) {
throw new RuntimeException( 'Could not acquire migration lock. Another process may be running migrations.' );
}
$applied_versions = array();
try {
$applied = $this->get_applied();
$pending = $this->get_pending();
if ( empty( $pending ) ) {
$this->log( $logger, 'No pending migrations.', 'info' );
return $applied_versions;
}
$this->log( $logger, sprintf( 'Found %d pending migration(s).', count( $pending ) ), 'info' );
foreach ( $pending as $version => $migration ) {
$desc = $migration->get_description();
$this->log( $logger, sprintf( 'Running %s: %s', $version, $desc ), 'info' );
if ( ! $dry_run ) {
$migration->up();
$applied[] = $version;
update_option( $this->option_key, $applied );
$this->log( $logger, sprintf( 'Applied %s.', $version ), 'success' );
} else {
$this->log( $logger, sprintf( '[DRY RUN] Would apply %s.', $version ), 'info' );
}
$applied_versions[] = $version;
}
} finally {
$this->release_lock();
}
return $applied_versions;
}
/**
* Roll back the last N applied migrations.
*
* @param int $steps Number of migrations to roll back.
* @param callable|null $logger Optional logger callback.
* @return array Array of rolled-back version strings.
*/
public function rollback( $steps = 1, $logger = null ) {
if ( ! $this->acquire_lock() ) {
throw new RuntimeException( 'Could not acquire migration lock.' );
}
$rolled_back = array();
try {
$applied = $this->get_applied();
$migrations = $this->load_migrations();
if ( empty( $applied ) ) {
$this->log( $logger, 'No migrations to roll back.', 'warning' );
return $rolled_back;
}
$to_rollback = array_slice( $applied, -$steps );
$to_rollback = array_reverse( $to_rollback );
foreach ( $to_rollback as $version ) {
if ( ! isset( $migrations[ $version ] ) ) {
$this->log( $logger, sprintf( 'Migration file for %s not found, skipping.', $version ), 'warning' );
continue;
}
$this->log( $logger, sprintf( 'Rolling back %s...', $version ), 'info' );
$migrations[ $version ]->down();
$applied = array_values( array_diff( $applied, array( $version ) ) );
update_option( $this->option_key, $applied );
$this->log( $logger, sprintf( 'Rolled back %s.', $version ), 'success' );
$rolled_back[] = $version;
}
} finally {
$this->release_lock();
}
return $rolled_back;
}
/**
* Send a message to the logger callback.
*/
private function log( $logger, $message, $level ) {
if ( is_callable( $logger ) ) {
call_user_func( $logger, $message, $level );
}
}
}Migration Files
// migrations/class-migration-1-0-0.php
class MyPlugin_Migration_1_0_0 implements MyPlugin_Migration_Interface {
public function get_version() {
return '1.0.0';
}
public function get_description() {
return 'Create the myplugin_records table.';
}
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE IF NOT EXISTS $table (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL DEFAULT 0,
record_type varchar(50) NOT NULL DEFAULT '',
record_data longtext NOT NULL,
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY user_id (user_id),
KEY record_type (record_type)
) $charset_collate;";
$result = $wpdb->query( $sql );
if ( $result === false ) {
throw new RuntimeException( 'Failed to create myplugin_records table: ' . $wpdb->last_error );
}
}
public function down() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query( "DROP TABLE IF EXISTS $table" );
}
}// migrations/class-migration-1-1-0.php
class MyPlugin_Migration_1_1_0 implements MyPlugin_Migration_Interface {
public function get_version() {
return '1.1.0';
}
public function get_description() {
return 'Add priority column and index to myplugin_records.';
}
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
// Check if column already exists (safety for partial migrations)
$column_exists = $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'priority'",
DB_NAME,
$table
)
);
if ( ! $column_exists ) {
$result = $wpdb->query( "ALTER TABLE $table ADD COLUMN priority tinyint(1) unsigned NOT NULL DEFAULT 0 AFTER record_type" );
if ( $result === false ) {
throw new RuntimeException( 'Failed to add priority column: ' . $wpdb->last_error );
}
}
// Add index
$index_exists = $wpdb->get_var(
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '$table' AND INDEX_NAME = 'idx_priority'"
);
if ( ! $index_exists ) {
$wpdb->query( "ALTER TABLE $table ADD INDEX idx_priority (priority)" );
}
}
public function down() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query( "ALTER TABLE $table DROP INDEX IF EXISTS idx_priority" );
$wpdb->query( "ALTER TABLE $table DROP COLUMN IF EXISTS priority" );
}
}// migrations/class-migration-1-2-0.php
class MyPlugin_Migration_1_2_0 implements MyPlugin_Migration_Interface {
public function get_version() {
return '1.2.0';
}
public function get_description() {
return 'Restructure serialized record_data: rename settings_v1 key to settings.';
}
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$batch_size = 500;
$offset = 0;
do {
$rows = $wpdb->get_results(
$wpdb->prepare(
"SELECT id, record_data FROM $table WHERE record_data != '' ORDER BY id ASC LIMIT %d OFFSET %d",
$batch_size,
$offset
),
ARRAY_A
);
foreach ( $rows as $row ) {
$data = maybe_unserialize( $row['record_data'] );
if ( ! is_array( $data ) || ! isset( $data['settings_v1'] ) ) {
continue;
}
$data['settings'] = $data['settings_v1'];
unset( $data['settings_v1'] );
$wpdb->update(
$table,
array( 'record_data' => maybe_serialize( $data ) ),
array( 'id' => $row['id'] ),
array( '%s' ),
array( '%d' )
);
}
$offset += $batch_size;
} while ( count( $rows ) === $batch_size );
}
public function down() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$batch_size = 500;
$offset = 0;
do {
$rows = $wpdb->get_results(
$wpdb->prepare(
"SELECT id, record_data FROM $table WHERE record_data != '' ORDER BY id ASC LIMIT %d OFFSET %d",
$batch_size,
$offset
),
ARRAY_A
);
foreach ( $rows as $row ) {
$data = maybe_unserialize( $row['record_data'] );
if ( ! is_array( $data ) || ! isset( $data['settings'] ) ) {
continue;
}
$data['settings_v1'] = $data['settings'];
unset( $data['settings'] );
$wpdb->update(
$table,
array( 'record_data' => maybe_serialize( $data ) ),
array( 'id' => $row['id'] ),
array( '%s' ),
array( '%d' )
);
}
$offset += $batch_size;
} while ( count( $rows ) === $batch_size );
}
}The WP-CLI Command
// includes/class-cli-command.php
if ( ! defined( 'WP_CLI' ) || ! WP_CLI ) {
return;
}
class MyPlugin_CLI_Command {
private $runner;
public function __construct() {
$this->runner = new MyPlugin_Migration_Runner();
}
/**
* Run all pending database migrations.
*
* ## OPTIONS
*
* [--dry-run]
* : Preview migrations without applying them.
*
* ## EXAMPLES
*
* wp myplugin migrate
* wp myplugin migrate --dry-run
*
* @when after_wp_load
*/
public function migrate( $args, $assoc_args ) {
$dry_run = isset( $assoc_args['dry-run'] );
$logger = array( $this, 'cli_logger' );
try {
$applied = $this->runner->run( $logger, $dry_run );
if ( ! empty( $applied ) && ! $dry_run ) {
WP_CLI::success( sprintf( '%d migration(s) applied.', count( $applied ) ) );
}
} catch ( RuntimeException $e ) {
WP_CLI::error( $e->getMessage() );
}
}
/**
* Roll back recent migrations.
*
* ## OPTIONS
*
* [--steps=]
* : Number of migrations to roll back. Default: 1.
*
* [--confirm]
* : Skip the confirmation prompt.
*
* ## EXAMPLES
*
* wp myplugin rollback
* wp myplugin rollback --steps=3 --confirm
*
* @when after_wp_load
*/
public function rollback( $args, $assoc_args ) {
$steps = isset( $assoc_args['steps'] ) ? absint( $assoc_args['steps'] ) : 1;
$logger = array( $this, 'cli_logger' );
if ( ! isset( $assoc_args['confirm'] ) ) {
WP_CLI::confirm( sprintf( 'Roll back %d migration(s)?', $steps ) );
}
try {
$rolled_back = $this->runner->rollback( $steps, $logger );
if ( ! empty( $rolled_back ) ) {
WP_CLI::success( sprintf( '%d migration(s) rolled back.', count( $rolled_back ) ) );
}
} catch ( RuntimeException $e ) {
WP_CLI::error( $e->getMessage() );
}
}
/**
* Display migration status.
*
* ## EXAMPLES
*
* wp myplugin status
*
* @when after_wp_load
*/
public function status( $args, $assoc_args ) {
$applied = $this->runner->get_applied();
$migrations = $this->runner->load_migrations();
if ( empty( $migrations ) ) {
WP_CLI::log( 'No migrations found.' );
return;
}
$rows = array();
foreach ( $migrations as $version => $migration ) {
$rows[] = array(
'Version' => $version,
'Status' => in_array( $version, $applied, true ) ? 'Applied' : 'Pending',
'Description' => $migration->get_description(),
);
}
WP_CLI\Utils\format_items( 'table', $rows, array( 'Version', 'Status', 'Description' ) );
$pending_count = count( $this->runner->get_pending() );
if ( $pending_count > 0 ) {
WP_CLI::log( sprintf( "\n%d pending migration(s). Run 'wp myplugin migrate' to apply.", $pending_count ) );
}
}
/**
* Logger callback for WP-CLI output.
*/
public function cli_logger( $message, $level ) {
switch ( $level ) {
case 'success':
WP_CLI::success( $message );
break;
case 'warning':
WP_CLI::warning( $message );
break;
case 'error':
WP_CLI::error( $message, false );
break;
default:
WP_CLI::log( $message );
}
}
}
WP_CLI::add_command( 'myplugin', 'MyPlugin_CLI_Command' ); The Main Plugin File
// myplugin.php
/**
* Plugin Name: MyPlugin
* Version: 1.2.0
*/
defined( 'ABSPATH' ) || exit;
require_once __DIR__ . '/includes/interface-migration.php';
require_once __DIR__ . '/includes/class-migration-runner.php';
require_once __DIR__ . '/includes/class-cli-command.php';
/**
* Run pending migrations on plugins_loaded.
*
* Only runs in admin context to avoid impacting front-end performance.
* For non-admin migration runs, use: wp myplugin migrate
*/
function myplugin_maybe_run_migrations() {
if ( ! is_admin() ) {
return;
}
// Skip during AJAX requests to avoid timeouts on large migrations
if ( wp_doing_ajax() ) {
return;
}
$runner = new MyPlugin_Migration_Runner();
$pending = $runner->get_pending();
if ( empty( $pending ) ) {
return;
}
// For small migrations (3 or fewer), auto-run.
// For large batches, show an admin notice directing them to WP-CLI.
if ( count( $pending ) <= 3 ) {
try {
$runner->run();
} catch ( RuntimeException $e ) {
add_action( 'admin_notices', function() use ( $e ) {
printf(
'MyPlugin: Database migration failed: %s
',
esc_html( $e->getMessage() )
);
});
}
} else {
add_action( 'admin_notices', function() use ( $pending ) {
printf(
'MyPlugin: %d database migrations are pending. Please run wp myplugin migrate via WP-CLI to apply them.
',
count( $pending )
);
});
}
}
add_action( 'plugins_loaded', 'myplugin_maybe_run_migrations' );Design Decisions in This Implementation
Several choices in this system are worth explaining.
Idempotent migrations: The 1.1.0 migration checks whether the column and index already exist before trying to add them. This makes the migration safe to run multiple times. If a previous run was interrupted after adding the column but before the version was recorded, the next run will not fail with a "duplicate column" error.
Error handling with exceptions: Migrations throw RuntimeException on failure. The runner catches these and stops processing further migrations. This prevents a cascade of failures when one migration leaves the database in an unexpected state.
Admin-only auto-migration: The plugins_loaded handler only auto-runs migrations in the admin context and skips AJAX requests. Front-end visitors never trigger migrations. This avoids performance issues and timeout errors on public-facing pages.
Migration count threshold: If more than three migrations are pending (indicating a large version jump), the system shows an admin notice instead of auto-running. Large migration batches are better handled through WP-CLI where there are no HTTP timeout limits and the developer can monitor progress in real time.
MySQL-level locking: The GET_LOCK() approach prevents race conditions on multi-server setups where two web servers might both detect pending migrations simultaneously. Transient-based locking would not be reliable if the object cache is per-server.
Additional Considerations for Production Deployments
Backup Before Migrating
Every migration guide says this, and every developer ignores it at least once. Automate it. If you are running migrations through WP-CLI, wrap the command in a script that takes a database dump first:
#!/bin/bash
# deploy-migrate.sh
set -e
echo "Creating database backup..."
wp db export backup-pre-migration-$(date +%Y%m%d%H%M%S).sql
echo "Running migrations..."
wp myplugin migrate
echo "Migration complete."Large Table Considerations
ALTER TABLE on a table with millions of rows can lock the table for minutes or longer in MySQL. During that time, all queries against that table will block. For high-traffic sites, consider using tools like pt-online-schema-change from Percona Toolkit or gh-ost from GitHub. These tools create a shadow copy of the table, apply the schema change to the copy, sync the data, and swap the tables atomically.
You can shell out to these tools from within a migration:
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
// For tables under 100k rows, use direct ALTER
$row_count = $wpdb->get_var( "SELECT COUNT(*) FROM $table" );
if ( $row_count < 100000 ) {
$wpdb->query( "ALTER TABLE $table ADD COLUMN priority tinyint(1) NOT NULL DEFAULT 0" );
} else {
// Use pt-online-schema-change for large tables
$command = sprintf(
'pt-online-schema-change --alter "ADD COLUMN priority tinyint(1) NOT NULL DEFAULT 0" D=%s,t=%s --execute',
DB_NAME,
$table
);
exec( $command, $output, $return_code );
if ( $return_code !== 0 ) {
throw new RuntimeException( 'pt-online-schema-change failed: ' . implode( "\n", $output ) );
}
}
}Logging and Audit Trails
For plugins used in regulated environments (healthcare, finance, government), you may need an audit trail of all schema changes. Log migrations to a dedicated table:
global $wpdb;
$log_table = $wpdb->prefix . 'myplugin_migration_log';
$wpdb->insert( $log_table, array(
'migration_version' => $version,
'direction' => 'up',
'executed_by' => wp_get_current_user()->user_login ?: 'cli',
'executed_at' => current_time( 'mysql' ),
'execution_time_ms' => $elapsed_ms,
'status' => 'success',
) );Foreign Key Considerations
WordPress core does not use foreign keys, and many hosting environments run MySQL with FOREIGN_KEY_CHECKS that can cause issues during migrations. If your plugin uses foreign keys, temporarily disable checks during migration:
public function up() {
global $wpdb;
$wpdb->query( 'SET FOREIGN_KEY_CHECKS = 0' );
// Run schema changes...
$wpdb->query( 'SET FOREIGN_KEY_CHECKS = 1' );
}Be cautious with this approach. Disabling foreign key checks means you are responsible for maintaining referential integrity manually during the migration.
Character Set and Collation Migrations
One of the more painful migration scenarios involves changing a table's character set, typically from utf8 to utf8mb4 to support emoji and other four-byte characters. WordPress itself went through this migration in version 4.2.
The migration requires converting every text column in the table:
public function up() {
global $wpdb;
$table = $wpdb->prefix . 'myplugin_records';
$wpdb->query( "ALTER TABLE $table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" );
// Also update any VARCHAR columns that have index length issues
// utf8mb4 uses 4 bytes per character, so a VARCHAR(255) with an index
// needs 1020 bytes, which exceeds the 767-byte index limit on older MySQL.
// Solution: reduce the column length or use innodb_large_prefix.
$wpdb->query( "ALTER TABLE $table MODIFY record_type varchar(191) NOT NULL DEFAULT ''" );
}Test this migration against your actual data. Character set conversions can fail if the data contains invalid byte sequences for the target encoding.
Wrapping Up
The dbDelta() function works for simple, additive schema changes, and there is no reason to avoid it for those cases. But any plugin that evolves over time will eventually need to remove columns, rename fields, transform data, or coordinate schema changes across multiple environments. At that point, a proper migration system pays for itself.
The patterns in this article are not theoretical. They come from real plugins that manage real data for real users. The version tracking with get_option(), the file-based migration classes, the MySQL-level locking, the three-release deprecation strategy, the WP-CLI integration: all of these are battle-tested approaches.
Start simple. Use dbDelta() for your initial table creation. When you ship your first schema change that dbDelta() cannot handle, add a migration runner. Grow the system as your needs grow. The complete example in this article gives you a foundation that can scale from a single table to a complex multi-table schema with serialized data, background processing, and automated testing.
Your users will never see any of this work. They will just notice that your plugin updates smoothly, their data stays intact, and nothing breaks. That is the whole point.
Alex Petrov
Database engineer and WordPress plugin developer. Specializes in custom table design, query optimization, and data migrations. Previously worked in fintech.