How to migrate database schema on plugin updates in PHP in WordPress

Contents

Introduction — why you must handle database schema migrations in plugins

When a WordPress plugin evolves, its database schema often must evolve with it: new tables, added columns, dropped columns, renamed columns, changed indexes, or mass data transformations. If you ship schema changes carelessly you risk data loss, fatal PHP/SQL errors, performance regressions and unhappy users.

This article provides a complete, production-ready approach for migrating database schemas on plugin updates using PHP and WordPress APIs. It covers best practices, versioning strategies, concrete code examples (creation, alteration, renames, data migrations), multisite considerations, transactional safety, error handling, rollback strategies, testing, and cleanup on uninstall.

Principles and high-level workflow

  1. Store a single schema version — save a numeric/string version in the options table (or site options for multisite). Compare the installed version with your plugins schema version on load and run only the needed migrations.
  2. Use incremental, idempotent migrations — each migration should be safe to run multiple times (detect and skip if already applied).
  3. Separate DDL from data migrations — schema changes and data transformations should be independent steps so failures in one don’t corrupt the other.
  4. Use dbDelta for table creation — dbDelta understands CREATE TABLE SQL and will create/alter tables for many cases. For complex ALTER operations, use explicit ALTER TABLE queries.
  5. Validate and back up before changes — instruct administrators to back up production DB before updates and offer programmatic safeguards/logs.
  6. Handle multisite/network activation — update site options across network when appropriate.
  7. Log and fail gracefully — capture SQL errors, store migration status and messages for admin review, avoid fatal errors.
  8. Offer uninstall cleanup — optionally remove tables and options on uninstall via register_uninstall_hook or uninstall.php.

Which hooks and events to run migrations from

  • register_activation_hook — good for first activation and initial setup but does not run on updates.
  • plugin_loaded or init — safe to trigger migrations early check capability and admin area before heavy ops.
  • admin_init — you can run migrations here but avoid doing heavy tasks on every admin page load run only once per upgrade.
  • upgrader_process_complete — runs when update completes useful to run migrations immediately after core/plugin/theme updates (check the action args).
  • Upgrader API filters — detect plugin upgrades programmatically and run migrations only when the plugin was updated.

Schema version storage — options vs site options

Store a schema version string (for example, 1.2.0 or an incremental integer) in the options table using add_option/update_option. For multisite plugins or network-activated plugins, use add_site_option/update_site_option so you can manage the network schema centrally.

Common key names: your_plugin_db_version, your_plugin_schema_version. Keep the version atomic and easy-to-compare (use version_compare for semantic versions).

Basic migration manager — concept and example implementation

Below is a compact, robust MigrationManager class implementing common patterns: register migrations, run migrations in order, store schema version, handle errors, and support multisite. 

lt?php
// Example: Minimal migration manager for plugin updates
class MyPlugin_DB_Migrator {
    protected option_name = myplugin_db_version
    protected migrations = array() // array of version => callable
    protected current_version // plugin-coded version, set by plugin
    protected is_network = false

    public function __construct( current_version, is_network = false ) {
        this->current_version = current_version
        this->is_network = is_network
    }

    public function register_migration( version, callable cb ) {
        this->migrations[ version ] = cb
        // keep migrations sorted by version (semantic)
        uksort( this->migrations, version_compare )
    }

    protected function get_stored_version() {
        if ( is_multisite()  this->is_network ) {
            return get_site_option( this->option_name, 0 )
        }
        return get_option( this->option_name, 0 )
    }

    protected function update_stored_version( version ) {
        if ( is_multisite()  this->is_network ) {
            update_site_option( this->option_name, version )
        } else {
            update_option( this->option_name, version )
        }
    }

    public function maybe_migrate() {
        installed = this->get_stored_version()

        // No-op if already up to date
        if ( version_compare( installed, this->current_version, >= ) ) {
            return
        }

        foreach ( this->migrations as version => callable ) {
            if ( version_compare( installed, version, < )  version_compare( version, this->current_version, <= ) ) {
                try {
                    // Each migration should be idempotent
                    call_user_func( callable )
                    this->update_stored_version( version )
                } catch ( Exception e ) {
                    error_log( MyPlugin migration to  . version .  failed:  . e->getMessage() )
                    // Stop further migrations on failure
                    throw e
                }
            }
        }

        // Final bump to current version if migrations jumped versions
        this->update_stored_version( this->current_version )
    }
}
?gt

How to register and trigger this migrator

In your main plugin file instantiate the migrator with your plugins schema version, register migrations, and call maybe_migrate() on init (or on an update hook). Keep migrations small and fast where possible. 

lt?php
// plugin main file (simplified)
define( MYPLUGIN_VERSION, 1.3.0 )

migrator = new MyPlugin_DB_Migrator( MYPLUGIN_VERSION, true ) // true if network-level

// Register migrations in order:
migrator->register_migration( 1.0.0, function() {
    // create initial table (see create table example below)
} )

migrator->register_migration( 1.1.0, function() {
    // add a column
} )

migrator->register_migration( 1.2.0, function() {
    // transform data or normalize
} )

// Run on admin_init to ensure WordPress is loaded optionally limit to administrators
add_action( admin_init, function() use ( migrator ) {
    if ( ! current_user_can( manage_options ) ) {
        return
    }
    migrator->maybe_migrate()
} )
?gt

Creating tables: use dbDelta

dbDelta is the standard WordPress helper for creating or updating table columns/keys from a CREATE TABLE SQL string. Always include the charset and collate string and use the exact table name (including wpdb-gtprefix). dbDelta is conservative — complex column type changes sometimes require direct ALTER statements. 

lt?php
global wpdb
charset_collate = wpdb-gtget_charset_collate()
table_name = wpdb-gtprefix . myplugin_items

sql = CREATE TABLE {table_name} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id bigint(20) unsigned NOT NULL,
  status varchar(50) NOT NULL DEFAULT new,
  meta longtext NULL,
  created datetime NOT NULL,
  updated datetime NULL,
  PRIMARY KEY  (id),
  KEY user_id (user_id),
  KEY status (status)
) charset_collate

// dbDelta is in wp-admin/includes/upgrade.php
require_once( ABSPATH . wp-admin/includes/upgrade.php )
dbDelta( sql )
?gt

dbDelta tips and gotchas

  • Column definitions must match exactly for dbDelta to recognize changes (types, defaults, lengths).
  • Always end the SQL with a semicolon.
  • dbDelta can add columns and indexes but may not alter complex column type changes (e.g., change TEXT to JSON) — use ALTER TABLE then.
  • Use explicit KEY definitions for indexes dbDelta parses keys to create missing ones.

Altering tables: use prepared ALTER statements

For column renames, type changes, or other DDL operations not reliably handled by dbDelta, send explicit ALTER TABLE queries with wpdb-gtquery() and wpdb-gtprepare() when you include variables. 

lt?php
global wpdb
table = wpdb-gtprefix . myplugin_items

// Add a column if it doesnt exist
column = wpdb-gtget_results( wpdb-gtprepare( SHOW COLUMNS FROM %s LIKE %s, table, new_col ) )
if ( empty( column ) ) {
    sql = ALTER TABLE {table} ADD COLUMN new_col VARCHAR(100) NULL
    wpdb-gtquery( sql )
}

// Rename column (MySQL)
has_old = wpdb-gtget_results( wpdb-gtprepare( SHOW COLUMNS FROM %s LIKE %s, table, old_col ) )
if ( ! empty( has_old ) ) {
    // CHANGE old_col new_col NEWTYPE ... (CHANGE requires full definition)
    wpdb-gtquery( ALTER TABLE {table} CHANGE old_col new_col VARCHAR(255) NULL )
}
?gt

Detecting existing columns and indexes

Use SHOW COLUMNS FROM or information_schema queries:

-- Example query (via wpdb)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = wp_myplugin_items AND COLUMN_NAME = mycol

Data migrations — transformation patterns

When altering schema you often need to migrate existing data: copy values into new columns, normalize serialized arrays into JSON, split rows, etc. Perform data migrations in small chunks to avoid timeouts and to allow resumability.

  • Chunked processing — use LIMIT/OFFSET or primary key ranges to process N rows per HTTP request and resume with an option/transient. Cron or WP-CLI is excellent for long migrations.
  • Transactional guarantees — if using InnoDB, run START TRANSACTION / COMMIT so a failing batch rolls back.
  • Idempotency — mark rows as migrated or check destination column state to skip already-migrated rows.
  • Logging and progress — store counts and error messages in an option for admin control and progress display.
lt?php
global wpdb
table = wpdb-gtprefix . myplugin_items

// Process rows in batches to convert meta (serialized) into JSON in meta_json column
batch_size = 200
offset = 0

do {
    rows = wpdb-gtget_results( wpdb->prepare(
        SELECT id, meta FROM {table} WHERE meta IS NOT NULL LIMIT %d OFFSET %d,
        batch_size, offset
    ) )

    if ( empty( rows ) ) {
        break
    }

    wpdb-gtquery( START TRANSACTION )
    try {
        foreach ( rows as r ) {
            meta = maybe_unserialize( r->meta )
            // normalize to array
            if ( ! is_array( meta ) ) {
                meta = array( value => meta )
            }
            meta_json = wp_json_encode( meta )
            wpdb-gtupdate(
                table,
                array( meta_json =gt meta_json ),
                array( id =gt r-gtid ),
                array( %s ),
                array( %d )
            )
        }
        wpdb-gtquery( COMMIT )
    } catch ( Exception e ) {
        wpdb-gtquery( ROLLBACK )
        throw e
    }

    offset  = batch_size
} while ( count( rows ) === batch_size )
?gt

Transactions and rollback strategies

MySQL transactions can help maintain consistency for data migrations when using InnoDB tables. Use START TRANSACTION / COMMIT / ROLLBACK via wpdb-gtquery(). Keep transactions small (per batch) to avoid long locks.

For DDL (ALTER TABLE) note that many MySQL versions do non-transactional DDL if an ALTER fails, you may need to instruct the admin to restore from backup. Build migrations that can be resumed or that detect partially applied changes and either finish or rollback manually.

Multisite and network-activated plugin considerations

  • If your plugin is network-activated, perform network-wide migrations (iterate sites and upgrade each sites schema or store a network-level schema flag).
  • When iterating many subsites, use WP-CLI or asynchronous background processing (Action Scheduler, wp-cron jobs) to avoid timeouts.
  • Use get_sites() with fields limited to id to iterate efficiently and switch_to_blog / restore_current_blog while running site-level migrations.
lt?php
// Multisite migration example: iterate site IDs and run site-specific setup
if ( is_multisite() ) {
    sites = get_sites( array( fields =gt ids ) )
    foreach ( sites as blog_id ) {
        switch_to_blog( blog_id )
        // Run site-level migration code that uses options or tables with wp_ prefix
        migrator-gtmaybe_migrate()
        restore_current_blog()
    }
}
?gt

Safe checks and idempotency recommendations

  • Check existence of table/column/index before creating/altering.
  • Make migrations skip if a post-condition is already met (e.g., column exists or data flag set).
  • Use version_compare for semantic versions and order migrations accordingly.
  • Test migrations against a copy of production data if possible.

Running migrations during plugin updates (hooking into upgrader)

You can detect plugin updates using the upgrader_process_complete action and run migrations specifically when your plugin was updated. This helps avoid running migrations on every admin page load. 

lt?php
add_action( upgrader_process_complete, function( upgrader_object, options ) {
    // options[action] is update or install options[type] is plugin
    if ( options[action] !== update  options[type] !== plugin ) {
        return
    }
    if ( empty( options[plugins] ) ) {
        return
    }
    // Look for our plugin in the list of updated plugins
    foreach ( options[plugins] as plugin_file ) {
        if ( plugin_file === plugin_basename( __FILE__ ) ) {
            // run migration
            migrator = new MyPlugin_DB_Migrator( MYPLUGIN_VERSION )
            migrator->maybe_migrate()
            break
        }
    }
}, 10, 2 )
?gt

Logging, admin notices and progress reporting

When migrations run automatically, surface minimal progress or errors to administrators via admin notices stored transient/option, or a dedicated log option. Be careful with noisy notices — only show a concise message and link to plugin status page for details. 

lt?php
// Simple error logging to debug.log
try {
    migrator-gtmaybe_migrate()
} catch ( Exception e ) {
    error_log( [MyPlugin] Migration failed:  . e->getMessage() )
    // Set an admin notice to show in the dashboard
    update_option( myplugin_migration_error, e->getMessage() )
}

// On admin_notices, display small message if migration_error exists
add_action( admin_notices, function() {
    if ( msg = get_option( myplugin_migration_error ) ) {
        echo ltdiv class=notice notice-errorgtltpgtMyPlugin migration error:  . esc_html( msg ) . lt/pgtlt/divgt
    }
} )
?gt

Unit and integration testing for migrations

  • Write unit tests that set up a pre-migration schema and data, run the migrator, and assert final schema and data states.
  • Use WP-CLI and a dockerized MySQL instance to run reproducible tests with realistic data.
  • Test incremental upgrades: e.g., 1.0.0 → 1.1.0 → 1.2.0 and 1.0.0 → 1.2.0 directly (to ensure migrations chain correctly).

Backup and recovery guidance

Before releasing migrations that change schemas in production, instruct site owners to back up the database. Provide simple commands and WP-CLI examples: 

# MySQL dump (site admins)
mysqldump -u DBUSER -p DBNAME gt backup-before-update.sql

# WP-CLI export (posts/media, not DB-level)
wp db export backup-before-update.sql

Uninstall and cleanup

If your plugin needs to remove tables/options on uninstall, implement an uninstall.php or register_uninstall_hook that deletes your tables and options. Warn users because uninstall removes user data permanently. 

lt?php
// uninstall.php (this file is loaded by WP when plugin is uninstalled)
if ( ! defined( WP_UNINSTALL_PLUGIN ) ) {
    exit
}

global wpdb
table = wpdb-gtprefix . myplugin_items
wpdb-gtquery( DROP TABLE IF EXISTS {table} )

// remove options
delete_option( myplugin_db_version )
delete_site_option( myplugin_db_version ) // if multisite
?gt

Advanced topics

Schema migrations as classes and automatic discovery

For large projects you may implement migrations as PHP classes/files named by version (e.g., migrations/1.1.0_AddColumn.php). Discover and run them dynamically support up and down methods for reversible migrations. Keep them small and single-responsibility. 

lt?php
// Example migration class pattern
class Migration_1_1_0_AddStatusColumn {
    public static function up() {
        global wpdb
        table = wpdb-gtprefix . myplugin_items
        exists = wpdb-gtget_results( wpdb-gtprepare( SHOW COLUMNS FROM %s LIKE %s, table, status ) )
        if ( empty( exists ) ) {
            wpdb-gtquery( ALTER TABLE {table} ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT new )
        }
    }

    public static function down() {
        global wpdb
        table = wpdb-gtprefix . myplugin_items
        wpdb-gtquery( ALTER TABLE {table} DROP COLUMN IF EXISTS status )
    }
}
?gt

Using WP-CLI for heavy migrations

WP-CLI is ideal for heavy schema migrations. Build an wp myplugin migrate command to run migrations outside web requests, allow dry-run, and provide verbose logging. This avoids web server timeouts and gives site owners control.

Common migration patterns and sample steps

  1. Create table if not exists (dbDelta)
  2. Add column if missing (SHOW COLUMNS ALTER TABLE ADD)
  3. Populate new column from existing data (batch processing with transactions)
  4. Change column type where necessary (ALTER TABLE CHANGE with full definition)
  5. Rename column (ALTER TABLE CHANGE old_name new_name FULLTYPE)
  6. Drop column when safe (ensure data copied/migrated)
  7. Rebuild or partition large tables if performance issues arise

Security and sanitization

  • Always use prepared statements or sanitize table/column names when constructing raw SQL.
  • Never trust user input in migration SQL (migrations run by admin only but still be careful with dynamic values).
  • Sanitize serialized data carefully use maybe_unserialize and wp_json_encode to standardize formats.

Summary checklist before releasing a migration

  • Are migrations idempotent? (safe to run multiple times)
  • Is schema versioning stored and compared correctly?
  • Do migrations have adequate checks (column/table existence, indexes)?
  • Are heavy data migrations chunked or runnable via WP-CLI / background jobs?
  • Are multisite and network-activated cases accounted for?
  • Is there logging, admin feedback, and rollback guidance?
  • Is uninstall cleanup (if any) implemented and documented?
  • Has the migration been tested against production-like data?

Further reading

Concluding notes

Database migrations are one of the riskiest parts of plugin development, but with careful versioning, idempotent migrations, chunked data processing, testing, and operator-friendly tooling (WP-CLI, progress/status reporting) you can upgrade schemas reliably and maintain user trust. Use the patterns and code samples above as a blueprint and adapt for your plugins needs.


Acepto donaciones de BAT's mediante el navegador Brave 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *