Creating your own WordPress plugin database migration framework

So, how do you migrate changes to your database structure from one version to another (like you may know it from Ruby on Rails) when you are writing a WordPress plugin? Here’s one way to do it.

Let’s assume that the database migration should take place when the plugin is activated. Assuming your plugin is named myplugin, put this code in the myplugin.php file:

register_activation_hook(__FILE__, 'myplugin_install');

This makes WordPress execute the myplugin_install() method when the user activates your plugin.

Next, add this method:

function myplugin_install() {
  myplugin_migrate();
}

And our migration method:

function myplugin_migrate() {
  global $wpdb;

  $migs = myplugin_migrations();

  $current_migration = get_option('myplugin_current_migration', 0);
  $needed_migration = count($migs);

  for ($i = $current_migration; $i < $needed_migration; $i++) {
    $mig = $migs[$i];
    $wpdb->query($mig);
  }

  if ($current_migration == 0) {
    add_option('myplugin_current_migration', $needed_migration);
  } else {
    update_option('myplugin_current_migration', $needed_migration);
  }
}

What this does is that it runs through database statements (queries) returned from the myplugin_migrations() method. Let’s implement the actual migrations – add this method:

function myplugin_migrations() {
  global $wpdb;

  $migs = array();

  // Create example table
  $migs[] = '
  CREATE TABLE '.$wpdb->prefix.'myplugin_examples (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    PRIMARY KEY (id)
  )';

  // Add examples
  $migs[] = "INSERT INTO ".$wpdb->prefix."myplugin_examples SET name='First example'";
  $migs[] = "INSERT INTO ".$wpdb->prefix."myplugin_examples SET name='Second example'";

  // Return the migrations
  return $migs;
}

That would create an example table and insert some values.

Now imagine that two months later you need a description field in your examples table. What do you do? It’s as easy as adding one line of code to the migrations:

// Add description field to examples
$migs[] = "ALTER TABLE ".$wpdb->prefix."myplugin_examples ADD description VARCHAR( 255 ) NOT NULL";

Deactivate and activate the plugin, and you table now has an extra field. Easy as cake! :)

Share your thoughts, opinions, and suggestions below.

Follow me on Twitter

Related posts

• Using the A/B Test for WordPress plugin to split test between themes
• Three months on the Mac stack
• Translate your Ruby on Rails models and database tables using acts_as_translatable
• How to split test themes using A/B Test for WordPress (video)

Tags: Databases, How-to, Migration, MySQL, PHP, Plugins, Tutorial, WordPress

This entry was posted on Saturday, January 14th, 2012 at 11:58 pm and is filed under WordPress. It has 6,022 views. You can follow any comments to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.