WordPress dbDelta database migration is one of the most critical yet misunderstood aspects of plugin development. The dbDelta function allows you to safely evolve your database schema, but its quirks and limitations can lead to failed migrations, data loss, and corrupted tables. This comprehensive guide explores dbDelta deeply, covering schema evolution, safe migrations, version tracking, rollback strategies, and the common pitfalls that catch developers.
Table of Contents
- Understanding dbDelta Fundamentals
- Schema Evolution with dbDelta
- Safe Migration Patterns
- Version Tracking and Migration History
- Rollback Strategies and Recovery
- Common dbDelta Pitfalls
- WordPress Database Migration Tools
- Testing Migrations Safely
Understanding dbDelta Fundamentals
The dbDelta function compares your desired database schema against the actual database state and makes necessary changes. It's designed to be idempotent—running it multiple times produces the same result, making it safe to execute during plugin activation. This idempotency property is crucial because it means you can call dbDelta every time WordPress loads without worrying about it creating duplicate tables or throwing errors on subsequent runs.
Understanding how dbDelta differs from traditional database migration systems is essential. Frameworks like Laravel migrations or Django South maintain a sequential history of applied migrations. Each migration is numbered, applied once in order, and tracked in a migrations table. If migration 005 fails, you know exactly which migrations succeeded and can plan your recovery accordingly.
dbDelta works differently. Instead of tracking migration history, it treats your PHP code as the source of truth. Every time dbDelta runs, it examines the current database schema and compares it against the schema definition you provide. If the schema matches, dbDelta does nothing. If it doesn't match, dbDelta makes the necessary changes. This stateless approach has advantages and disadvantages you need to understand to use dbDelta effectively.
// Import dbDelta function
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
// Define desired schema
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);";
// Create table if it doesn't exist
dbDelta( $sql );
The WordPress dbDelta database migration approach differs fundamentally from traditional database migration tools. It doesn't maintain a sequential migration history like Laravel migrations or Django south. Instead, it works by comparing desired and actual schemas, making it stateless and inherently safe.
However, this design has implications. WordPress dbDelta database migration treats the SQL definition as the source of truth. If you modify your schema outside of plugin code, dbDelta won't know about it. Similarly, dbDelta doesn't track which migrations have been applied—it simply ensures the current state matches the desired state.
Schema Evolution with dbDelta
Evolving your schema over time requires careful planning and understanding of dbDelta's limitations. As your plugin matures and new requirements emerge, your database schema must evolve. Perhaps version 1.0 had a simple users table. Version 2.0 needs to track user preferences. Version 3.0 needs audit logs. Rather than recreating the table from scratch each version (which would delete all data), you evolve the schema incrementally, adding columns, creating indexes, and adjusting constraints as needed.
dbDelta makes schema evolution easier than raw SQL because it handles the comparison logic. You don't need to manually check whether a column exists before adding it. You don't need conditional SQL statements. You just specify the desired schema, and dbDelta figures out what changed and applies the necessary SQL.
However, this approach has limitations. dbDelta can create tables, add columns, add indexes, and add constraints. It cannot reliably modify columns, delete columns, delete tables, or handle complex constraint changes. Understanding what dbDelta can and cannot do prevents hours of debugging when your schema evolution doesn't work as expected.
Initial Table Creation:
function my_plugin_create_tables() {
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL,
email varchar(100) NOT NULL,
created_at datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY email (email)
) $charset_collate;";
dbDelta( $sql );
}
// Execute during plugin activation
register_activation_hook( __FILE__, 'my_plugin_create_tables' );
Adding Columns in Subsequent Versions:
function my_plugin_add_status_column() {
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
// Include the new column in the definition
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL,
email varchar(100) NOT NULL,
status varchar(50) NOT NULL DEFAULT 'active', -- NEW COLUMN
created_at datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY email (email)
) $charset_collate;";
dbDelta( $sql );
}
// Execute during plugin update (in version check hook)
add_action( 'plugins_loaded', function() {
if ( get_option( 'my_plugin_version' ) < 2 ) {
my_plugin_add_status_column();
update_option( 'my_plugin_version', 2 );
}
} );
Creating Indexes for Performance:
function my_plugin_add_indexes() {
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
// dbDelta includes indexes in the definition
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL,
status varchar(50) NOT NULL DEFAULT 'active',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY status (status), -- Index on status
KEY created_at (created_at) -- Index on created_at
) $charset_collate;";
dbDelta( $sql );
}
Modifying Column Definitions:
function my_plugin_expand_name_field() {
global $wpdb;
// dbDelta won't modify existing columns automatically
// For modifications, use direct SQL ALTER TABLE
$wpdb->query( "ALTER TABLE {$wpdb->prefix}my_table
MODIFY COLUMN name varchar(500) NOT NULL" );
// Then run dbDelta to ensure overall schema matches
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(500) NOT NULL, -- Updated length
email varchar(100) NOT NULL,
status varchar(50) NOT NULL DEFAULT 'active',
created_at datetime DEFAULT CURRENT_TIMESTAMP,
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY email (email),
KEY status (status)
) $charset_collate;";
dbDelta( $sql );
}
Safe Migration Patterns
Safe WordPress dbDelta database migration involves careful planning and comprehensive error handling. Migration failures in production are nightmares. The plugin fails to activate, users can't access the site, and you're scrambling to restore from backups. Prevention is far better than recovery. Building safety into your migration patterns prevents most failures before they happen.
Safe migrations require several practices. First, always test migrations before deploying. Test initial table creation, test adding columns to existing tables, test modifications. Test that running the migration twice produces the same result (idempotency). Test on multiple WordPress versions and database systems if possible. Second, wrap migrations in error handling. If a migration fails partway through, you want to know immediately and be able to recover. Third, maintain version tracking so you know which migrations have been applied and can skip them on subsequent runs. Fourth, plan for rollback from day one. If a migration causes problems, you need a way to revert it safely without losing data.
Migration Manager Class:
class Migration_Manager {
private $table_prefix;
private $migrations = [];
public function __construct() {
global $wpdb;
$this->table_prefix = $wpdb->prefix;
}
public function register_migration( $version, callable $migration ) {
$this->migrations[ $version ] = $migration;
ksort( $this->migrations );
}
public function apply_pending_migrations() {
$current_version = get_option( 'my_plugin_db_version', 0 );
foreach ( $this->migrations as $version => $migration ) {
if ( $version > $current_version ) {
try {
call_user_func( $migration );
update_option( 'my_plugin_db_version', $version );
error_log( "Migration to v{$version} succeeded" );
} catch ( Exception $e ) {
error_log( "Migration to v{$version} failed: " . $e->getMessage() );
throw $e;
}
}
}
}
}
// Usage
$manager = new Migration_Manager();
$manager->register_migration( 1, function() {
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
global $wpdb;
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
PRIMARY KEY (id)
);";
dbDelta( $sql );
});
$manager->register_migration( 2, function() {
global $wpdb;
$wpdb->query( "ALTER TABLE {$wpdb->prefix}my_table ADD COLUMN name varchar(255)" );
});
$manager->apply_pending_migrations();
Transaction-Safe Migrations:
function safe_migration_with_transaction() {
global $wpdb;
// Start transaction
$wpdb->query( 'START TRANSACTION' );
try {
// Make schema changes
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( "CREATE TABLE {$wpdb->prefix}new_table (...)" );
// Verify changes
$result = $wpdb->get_results( "SHOW TABLES LIKE '{$wpdb->prefix}new_table'" );
if ( empty( $result ) ) {
throw new Exception( 'Table creation failed' );
}
// Commit if everything worked
$wpdb->query( 'COMMIT' );
} catch ( Exception $e ) {
// Rollback on error
$wpdb->query( 'ROLLBACK' );
error_log( 'Migration failed: ' . $e->getMessage() );
throw $e;
}
}
Validate Your Migration Safety
WP HealthKit audits your plugin's dbDelta migrations, identifying schema risks, rollback gaps, and version tracking issues. Ensure your database changes are safe and reversible across all WordPress environments.
Check Migration SafetyVersion Tracking and Migration History
Proper version tracking ensures migrations apply in the correct order and don't repeat. Without version tracking, you can't answer basic questions: Has migration version 2.0.0 already run? Should we apply migrations 2.0.0 and 2.5.0 or just 2.5.0? If migration 2.1.0 failed and we rolled back, can we skip it on the next attempt?
Version tracking is particularly important in WordPress because plugins are updated automatically via the WordPress.com update mechanism, via WP-CLI, or manually. Each update path might be different. A user might upgrade from version 1.0 directly to version 3.0, skipping version 2.0. If your migrations assume version 2.0 ran before version 3.0, they'll fail. Version tracking lets you build migrations that handle any upgrade path safely.
Beyond simple version numbers, detailed migration history provides visibility into what happened. You log when each migration ran, whether it succeeded or failed, and what errors occurred. This history becomes invaluable when debugging migration failures in production.
Simple Version Tracking:
function my_plugin_handle_updates() {
$current_version = get_option( 'my_plugin_version', '0.0.0' );
$new_version = '2.5.0';
if ( version_compare( $current_version, $new_version, '<' ) ) {
if ( version_compare( $current_version, '1.0.0', '<' ) ) {
migrate_to_v1();
}
if ( version_compare( $current_version, '2.0.0', '<' ) ) {
migrate_to_v2();
}
if ( version_compare( $current_version, '2.5.0', '<' ) ) {
migrate_to_v2_5();
}
update_option( 'my_plugin_version', $new_version );
}
}
add_action( 'plugins_loaded', 'my_plugin_handle_updates' );
Detailed Migration History:
class Migration_History {
const HISTORY_KEY = 'my_plugin_migration_history';
public static function record_migration( $version, $status, $details = '' ) {
$history = get_option( self::HISTORY_KEY, [] );
$history[] = [
'version' => $version,
'status' => $status, // success, failed, rolled_back
'timestamp' => current_time( 'mysql' ),
'details' => $details,
];
update_option( self::HISTORY_KEY, $history );
}
public static function get_history() {
return get_option( self::HISTORY_KEY, [] );
}
public static function last_successful_version() {
$history = self::get_history();
$successful = array_filter( $history, function( $record ) {
return $record['status'] === 'success';
} );
if ( empty( $successful ) ) {
return null;
}
$last = end( $successful );
return $last['version'];
}
}
Rollback Strategies and Recovery
Database changes can go wrong. Have rollback strategies ready. A migration that works perfectly in your test environment might fail in production due to data volumes, existing constraints, or incompatibilities with other plugins. A migration that causes performance problems needs to be reverted immediately. A migration that reveals a logic error needs to be undone so you can deploy a corrected version.
Rollback strategies separate successful deployments from disaster recovery. With a good rollback strategy, a migration problem is merely an inconvenience. You revert the migration, investigate the problem, deploy a fix, and retry. Without a rollback strategy, a migration failure is a crisis. Data might be corrupted, users can't access the site, and you're left trying to manually repair the database.
The most straightforward rollback strategy is backups. Before each migration, back up the affected tables. If something goes wrong, restore from the backup. Backups take storage and time to create, but they're simple and reliable. For critical migrations, backups are essential.
A more sophisticated approach is reversible migrations. Each migration has both an "up" path (apply the change) and a "down" path (revert the change). This lets you reverse specific migrations without restoring the entire database. Reversible migrations are more complex to implement but provide finer-grained control over rollbacks.
Backup Before Migration:
function backup_before_migration() {
global $wpdb;
$table = $wpdb->prefix . 'my_table';
$backup_table = $table . '_backup_' . current_time( 'timestamp' );
// Create backup
$wpdb->query( "CREATE TABLE {$backup_table} LIKE {$table}" );
$wpdb->query( "INSERT INTO {$backup_table} SELECT * FROM {$table}" );
// Store backup reference
add_option( 'my_plugin_last_backup_table', $backup_table );
return $backup_table;
}
function restore_from_backup() {
global $wpdb;
$backup_table = get_option( 'my_plugin_last_backup_table' );
if ( ! $backup_table ) {
return false;
}
$original_table = $wpdb->prefix . 'my_table';
// Restore from backup
$wpdb->query( "TRUNCATE TABLE {$original_table}" );
$wpdb->query( "INSERT INTO {$original_table} SELECT * FROM {$backup_table}" );
// Clean up backup
$wpdb->query( "DROP TABLE {$backup_table}" );
delete_option( 'my_plugin_last_backup_table' );
return true;
}
Forward and Backward Migrations:
class Reversible_Migration {
private $version;
private $upgrade;
private $downgrade;
public function __construct( $version, callable $upgrade, callable $downgrade ) {
$this->version = $version;
$this->upgrade = $upgrade;
$this->downgrade = $downgrade;
}
public function apply() {
try {
call_user_func( $this->upgrade );
Migration_History::record_migration( $this->version, 'success' );
} catch ( Exception $e ) {
Migration_History::record_migration( $this->version, 'failed', $e->getMessage() );
throw $e;
}
}
public function rollback() {
try {
call_user_func( $this->downgrade );
Migration_History::record_migration( $this->version, 'rolled_back' );
} catch ( Exception $e ) {
Migration_History::record_migration( $this->version, 'rollback_failed', $e->getMessage() );
throw $e;
}
}
}
// Usage
$migration = new Reversible_Migration(
'2.0.0',
function() {
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( "ALTER TABLE {$wpdb->prefix}my_table ADD COLUMN new_field varchar(255)" );
},
function() {
global $wpdb;
$wpdb->query( "ALTER TABLE {$wpdb->prefix}my_table DROP COLUMN new_field" );
}
);
// Apply
$migration->apply();
// Or rollback if needed
// $migration->rollback();
Common dbDelta Pitfalls
Understanding what dbDelta can't do prevents hours of debugging. dbDelta's quirks catch many developers. You write code that looks correct, call dbDelta, and nothing happens. Or you get cryptic error messages. Or the table gets created in an unexpected way. These frustrations come from dbDelta's specific limitations and sensitivities.
The most common pitfall is trying to use dbDelta for operations it doesn't support. Developers expect dbDelta to be a general-purpose schema migration tool like other frameworks provide. But dbDelta is deliberately limited. It can create and add things, but it can't reliably modify or delete things. This limitation forces you to use a combination of approaches: dbDelta for safe operations, direct SQL for operations dbDelta doesn't handle.
Another common pitfall is ignoring dbDelta's sensitivities to SQL formatting. dbDelta parses your SQL definition to understand what tables and columns you want. If your SQL formatting is inconsistent, dbDelta might misparse it. Similarly, dbDelta is sensitive to character set and collation. If you don't specify the correct charset, the created table might not match your database's default charset.
Understanding these pitfalls prevents you from trying to debug things that aren't really broken. If dbDelta silently does nothing when you expect it to modify a column, it's not a bug—it's the expected behavior. dbDelta can't modify columns. You need to use ALTER TABLE directly instead.
Pitfall 1: dbDelta Doesn't Modify Columns
// WRONG: This won't work
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id INT,
name VARCHAR(100) -- Changed from 255
);";
dbDelta( $sql );
// CORRECT: Use ALTER TABLE for modifications
global $wpdb;
$wpdb->query( "ALTER TABLE {$wpdb->prefix}my_table
MODIFY COLUMN name VARCHAR(100)" );
Pitfall 2: Spacing and Formatting Matter
// dbDelta is sensitive to SQL formatting
// WRONG - inconsistent spacing
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id int,
name varchar(255)
);";
// CORRECT - proper formatting
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);";
dbDelta( $sql );
Pitfall 3: Missing Charset Collate
// WRONG - Missing charset specification
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id int PRIMARY KEY
);";
// CORRECT - Always include charset
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
PRIMARY KEY (id)
) $charset_collate;";
dbDelta( $sql );
Pitfall 4: Forgetting PRIMARY KEY
// WRONG - No primary key
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id INT,
name VARCHAR(255)
);";
// CORRECT - Always define primary key
$sql = "CREATE TABLE {$wpdb->prefix}my_table (
id bigint(20) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);";
WordPress Database Migration Tools
Beyond dbDelta, understand other migration approaches. dbDelta is the standard WordPress approach, but it's not the only tool available. Some plugins use alternative approaches for greater control or specific requirements. Understanding your options helps you choose the right tool for each situation.
WP-CLI provides migration commands that work similarly to dbDelta but with more structured migration files. Third-party migration libraries add features like numbered migrations, rollback tracking, and more sophisticated change detection. Some plugins build custom migration systems tailored to their specific needs.
The trade-off is complexity. dbDelta is simple—just define your schema and call it. Custom migration systems are more flexible but require more code and maintenance. For most plugins, dbDelta is the right choice because its simplicity outweighs its limitations. Only if dbDelta doesn't provide the functionality you need should you consider alternatives.
Using WP-CLI for Migrations:
# Create a migration
wp cli wp_cli_migration create add_status_column
# This generates a migration file with:
# - up() method for applying the migration
# - down() method for reverting
# Run migrations
wp cli wp_cli_migration run
Third-Party Migration Libraries:
Some plugins use migration libraries for more control:
// Using a custom migration runner
class Plugin_Migrations {
private $migrations_dir;
public function __construct( $dir ) {
$this->migrations_dir = $dir;
}
public function run_pending() {
$applied = get_option( 'plugin_applied_migrations', [] );
foreach ( scandir( $this->migrations_dir ) as $file ) {
if ( substr( $file, -4 ) !== '.php' ) {
continue;
}
if ( in_array( $file, $applied ) ) {
continue;
}
require_once( $this->migrations_dir . '/' . $file );
$applied[] = $file;
}
update_option( 'plugin_applied_migrations', $applied );
}
}
Testing Migrations Safely
Always test WordPress dbDelta database migration thoroughly. Testing migrations is different from testing code logic. Migration tests need to verify not just that the migration runs without error, but that the resulting database schema is correct, that existing data is preserved, and that the migration is truly idempotent.
A comprehensive migration test suite verifies initial table creation works, adding columns to existing tables works, creating indexes works, and running the same migration multiple times produces identical results. It also tests edge cases: running migrations on tables with existing data, running migrations on tables with constraints, running migrations when columns already exist but have slightly different definitions.
Beyond unit tests, always test migrations in a staging environment that mirrors production. Run the migration, verify it completes successfully, verify the schema is correct, verify your application still works with the new schema, and verify performance is acceptable. Only after successful staging tests should you deploy the migration to production.
Migration Test Suite:
class Migration_Tests extends WP_UnitTestCase {
public function test_create_table() {
// Load fixture
$this->assertTrue( function_exists( 'dbDelta' ) );
// Run migration
my_plugin_create_tables();
// Verify table exists
global $wpdb;
$table = $wpdb->get_var(
"SHOW TABLES LIKE '{$wpdb->prefix}my_table'"
);
$this->assertNotNull( $table );
}
public function test_add_column() {
// Create initial table
my_plugin_create_tables();
// Run migration
my_plugin_add_status_column();
// Verify column exists
global $wpdb;
$columns = $wpdb->get_col(
"DESCRIBE {$wpdb->prefix}my_table"
);
$this->assertContains( 'status', $columns );
}
public function test_migration_idempotency() {
// Running migration twice should produce same result
my_plugin_create_tables();
$first_run = $wpdb->get_results(
"DESCRIBE {$wpdb->prefix}my_table"
);
my_plugin_create_tables();
$second_run = $wpdb->get_results(
"DESCRIBE {$wpdb->prefix}my_table"
);
$this->assertEquals( $first_run, $second_run );
}
}
Additional Resources
For a comprehensive view of how WP HealthKit approaches plugin analysis, explore our 17 verification layers or browse the plugin directory to see real audit scores. Ready to check your own plugin? Run a free audit now.
Frequently Asked Questions
Can dbDelta delete columns or tables?
No. dbDelta can only create and add, never delete. Use DROP COLUMN or DROP TABLE directly if necessary, but be very careful.
Why is my dbDelta not working?
Common causes: incorrect SQL formatting, missing charset_collate, table prefix not specified, or calling dbDelta before WordPress is fully loaded. Always require wp-admin/includes/upgrade.php first.
Should I use dbDelta or direct SQL?
Use dbDelta when possible (table creation, adding columns) for safety. Use direct SQL for operations dbDelta can't handle (modifying, deleting). Combine both for maximum safety and functionality.
How do I handle multisite database changes?
For multisite, apply migrations to each blog separately:
function apply_multisite_migration() {
global $wpdb;
$blogs = $wpdb->get_col( "SELECT blog_id FROM {$wpdb->blogs}" );
foreach ( $blogs as $blog_id ) {
switch_to_blog( $blog_id );
my_plugin_create_tables();
restore_current_blog();
}
}
What if a migration fails partway through?
This is why transaction-safe patterns matter. Use backups, error logging, and rollback procedures. WP HealthKit audits your migration safety.
How long do migrations take on large tables?
ALTER TABLE operations can be slow on large tables. Consider adding columns with DEFAULT values (faster) and avoiding index changes during peak hours.
Conclusion
WordPress dbDelta database migration is powerful when understood properly. By following safe patterns, maintaining version tracking, implementing rollback strategies, and testing thoroughly, you ensure your plugin's schema evolves safely and reliably.
The WordPress dbDelta database migration approach requires discipline. Document your migrations, test in staging, maintain backups, and track versions. Tools like WP HealthKit audit your migration practices, ensuring safety and consistency across all deployments.
Audit your plugin's database migrations with WP HealthKit today and identify risks before they become production issues.