Syncing WordPress Database Changes Between Environments: How We Handle Merging in 2021

#

Database synchronization is a problem that still gets raised almost every month in the local WordPress communities I’m part of – “if I am making changes to my local site, what happens to any updates made on the live site, if I push my local database back to live?”

With WordPress officially becoming an adult, is this not something we should have solved by now? Has anything changed since Brad spoke about this problem at WordCamp Miami in 2014? In this post, I’ll revisit the problem, look at what options you have, share how we deal with it for our sites, and review the off-the-shelf options that exist.

What is the Problem?

It’s an issue that exists for almost any developer working on a local development copy of a WordPress site that also has a live version running. The developer makes changes locally, configuring theme settings, adding new pages, adding Advanced Custom Fields, tweaking plugin options, etc. Meanwhile, the live version is under constant change. Either by the client, perhaps writing new blog posts, or in the case of ecommerce sites, adding new products, or by customers placing orders. When the developer is ready to push their local changes to the live site, there is a chance they will overwrite any changes on the live site. It’s a problem that affects all database-driven content management systems and is one of the trickiest parts of WordPress site development. As it stands, there is no easy way to reconcile these changes with a nice simple merge.

When Workarounds Won’t Work

For straightforward brochure or marketing sites, data merging and conflict resolution aren’t needed. During any development cycles, the data on the live site remains largely unchanged. Copying over any new media files and a partial push of the tables in the MySQL database with changes, using a plugin like WP Migrate DB Pro, would be sufficient. With more complex sites, the data architecture WordPress uses (and encourages developers to use) makes it extremely difficult to perform selective data migrations.

For example, in ecommerce stores where the only data changing on the live site is the products by the store owners and the orders from customers, a partial data migration should work in theory. Ideally, the data tables that store these pieces of data could be ignored when pushing other data changes made during site development. With WordPress, there’s just one problem – custom post types.

Man looking at other woman meme

Why Custom Posts Types Complicate the Problem

WordPress has an extremely flexible data structure that allows data objects of different types to be stored in the wp_posts table. The different data objects are known as custom post types and their type is defined by the post_type column in the wp_posts table. This isn’t the most intuitive name for the table that holds records of data for not only posts, but also pages, menus, attachments, revisions, menu items, and any custom data object registered as a custom post type. Think ‘house’ for a real estate site, ‘animal’ for an animal rescue site, or simply ‘doc’ for the documentation part of a product site.

When custom post types landed in WordPress 3.0, there was much rejoicing at a feature which enabled WordPress installations to become more than just blogs. However, I believe that custom post types, and their data model implementation, have become detrimental to the WordPress ecosystem.

On the one hand, this is a beautifully simple and flexible system, with a simple PHP API to register new data objects with just a few lines of code. When you register a custom post type, you get a lot of functionality for free without having to code it yourself; a new menu option, simple CRUD management of the objects, a table interface for listing and bulk actions, and even REST API endpoints.

On the other hand, the ease of registering new types means that sometimes the wrong kind of data objects are being stored in this way. Like my earlier example of ecommerce products and orders – the most critical of data for a store owner is kept in the same table as all the other post types. The live site will continue to get new records added to wp_posts as new orders are created. If any changes are made to posts or pages on the local site, the wp_posts table can no longer be pushed back to live without losing those orders!

Custom post types can also create other issues for a WordPress site. Using them to store every type of data can cause performance problems. WordPress will continually query the wp_posts table when displaying posts, pages, and other post types on the front end of the site, and as that table grows, the slower those queries become. Of course caching can help, but better care should be given to deciding where to store data that could potentially grow to a large size.

For example, Easy Digital Downloads currently registers a post type of ‘edd_log,’ which records every time an item is downloaded. The log record is really a piece of transactional data that should have its own table, as it could potentially scale to a huge number of rows that are unnecessary in the wp_posts table. I’ve seen this happen first hand:

Thankfully plugin shops are aware of these issues and are changing their approach.

WooCommerce has been working on migrating product and product meta to custom tables but their work to migrate orders to its own table appears to have stalled.

While LiquidWeb and Mindsize have attempted to solve this problem via a separate WooCommerce Custom Orders Table plugin, that plugin is still in development and has had no new updates since March 2020.

The EDD team is working on their big 3.0 update, which will completely migrate all their custom post types to custom tables. John James Jacoby (JJJ) spoke at WordCamp Europe 2019 about a new approach to managing and accessing custom tables. However, EDD 3.0 is still in public beta and is not yet ready for production sites.

Of course, while these improvements help, they will never fully fix the database merging issue. Ignoring certain tables from a database migration will only get you so far. Most of the time, live sites are changing in such a way that any changes to a development site cannot be automatically merged back in, and that’s where the manual approach is probably still the best.

What Alternatives Do You Have?

This was the realization we came to when we built the now-retired Mergebot. For every plugin or theme that writes to the database, you need to know how it stores its data so you can correctly handle IDs and conflicts. Trying to resolve all these conflicts ends up being very complex. Even with a solution that supports all major plugins and themes, developers would still need to manually configure unsupported plugins/themes, which is often more of a pain than the database deployment problem itself. If you only had to do this once, it wouldn’t be so bad, but every time a plugin or theme is updated, it could change the way it stores data. It’s a huge hassle that never ends.

There are a few ways to mitigate these conflicts.

SQL Scripts

One solution is to record any data changes as SQL scripts on your local development environment. Let’s look at a simple example. Installing WooCommerce requires you to create a few pages, for example, the checkout and my account pages. As soon as these pages have been created, you could export the data from the database to create an SQL file that includes the queries used to create these pages:

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(NULL, 1, '2013-02-27 21:03:13', '2013-02-28 01:03:13', '[woocommerce_checkout]', 'Checkout', '', 'publish', 'closed', 'closed', '', 'checkout', '', '', '2013-11-04 09:52:16', '2013-11-04 13:52:16', '', 0, 'http://yourwebsite.com/checkout/', 0, 'page', '', 0),
(NULL, 1, '2013-02-27 21:03:13', '2013-02-28 01:03:13', '[woocommerce_my_account]', 'My Account', '', 'publish', 'closed', 'closed', '', 'my-account', '', '', '2013-11-04 09:52:50', '2013-11-04 13:52:50', '', 0, 'http://yourwebsite.com/my-account/', 0, 'page', '', 0);

Once you’re ready to deploy your changes, import your SQL scripts into the production database, and you’re ready to go.

PHP Scripts

Instead of recording the SQL queries afterward, you could instead write a PHP script that creates the data. Of course, you can include SQL queries in there as well, but you can also do a lot more. Let’s use the scenario above again as an example, but instead, I’ll write a function to deploy the changes.

/*
 * To run the deployment
 * 1. Log in as an administrator
 * 2. Go to http://hellfish.media/?deploy_changes=1
 */
add_action( 'template_redirect', 'deploy_changes' );
function deploy_changes() {

  // If the request is not our deployment
  if ( !isset( $_GET['deploy_changes'] ) ) {
    return;
  }

  // Only allow a logged in admin to run the deployment
  if ( !current_user_can( 'install_themes' ) ) {
    echo 'Not logged in.';
    exit;
  }

  // Run script for 5 mins max
  set_time_limit(60*5);

  $pages = array(
    array(
    'content' => '[woocommerce_checkout]',
    'title'   => 'Checkout'
    ),
    array(
    'content' => '[woocommerce_my_account]',
    'title'   => 'My Account'
    )
  );

  foreach ( $pages as $page ) {
    $new_page = array(
    'post_type'   => 'page',
    'post_title'  => $page['title'],
    'post_content'  => $page['content'],
    );

    wp_insert_post( $new_page );
  }

  echo 'Deployment complete.';
  exit;
}

You could put this code in a custom maintenance.php file and include that from the functions.php file in your theme, or add it as a custom WordPress plugin rather than include it in the theme, but either way works. Once you’re ready to deploy, push this code to your live site and log in as an admin. Then run the code by appending the deploy_changes query string to the end of your domain, for example:

http://hellfish.media/?deploy_changes=1

After you’ve made the changes, remove the function from the file (or disable the plugin) to make sure you don’t rerun the deployment accidentally. Source control (like Git) ensures that you always keep a history of the old deployment code if you ever need it again.

You can take this a step further. In a custom plugin, you could store a version number for the plugin in the database and only run specific changes depending on the version number. That way, you just have to add new code which runs based on the version number and update the version number after each section of code is run. Let’s expand the above example to include a new page using this version check method.


/*
 * To run the deployment
 * 1. Log in as an administrator
 * 2. Go to http://hellfish.media/?deploy_changes=1
 */
add_action( 'template_redirect', 'deploy_changes' );
function deploy_changes() {

    // If the request is not our deployment
    if ( ! isset( $_GET['deploy_changes'] ) ) {
        return;
    }

    // Only allow a logged in admin to run the deployment
    if ( ! current_user_can( 'install_themes' ) ) {
        echo 'Not logged in.';
        exit;
    }

    // Run script for 5 mins max
    set_time_limit( 60 * 5 );

    // get the version number form the wp_options table, or default to 0
    $version = get_option( 'hellfish_custom_plugin_version', '0' );

    // only run if the $version is 0
    if ( version_compare( $version, '0', '=' ) ) {
        $pages = array(
            array(
                'content' => '[woocommerce_checkout]',
                'title'   => 'Checkout'
            ),
            array(
                'content' => '[woocommerce_my_account]',
                'title'   => 'My Account'
            )
        );

        deploy_pages( $pages );

        // update the version stored in the wp_options table
        update_option( 'hellfish_custom_plugin_version', '1' );
        echo 'Deployment complete.';
        exit;
    }

    // only run if the $version is 1
    if ( version_compare( get_bloginfo( 'version' ), '1', '=' ) ) {
        $pages = array(
            array(
                'content' => '[woocommerce_cart]',
                'title'   => 'Cart'
            )
        );

        deploy_pages( $pages );
        update_option( 'hellfish_custom_plugin_version', '2' );
        echo 'Deployment complete.';
    }
}

// deploy any pages set up in the deploy_changes function
function deploy_pages( $pages ) {
    foreach ( $pages as $page ) {
        $new_page = array(
            'post_type' => 'page',
            'post_title'   => $page['title'],
            'post_content' => $page['content'],
        );

        wp_insert_post( $new_page );
    }
}

Building this as part of a custom plugin means you can link any database updates that may be required to the plugin version number that you update with every plugin release.

What We Do

Essentially, we’re using a similar approach here at Delicious Brains, but I’ve added a bit more control and process to it. Inspired by Laravel’s database migrations and seeding system, I’ve created a migrations library that helps me write scripts to make table and data changes during development. There’s a fair bit of overlap here to JJJ’s work as well as the existing wp-table-migrations package, but I wanted something custom that can be used across our sites; deliciousbrains.com and spinupwp.com.

This is the process:

  1. Create a new migration class file that contains the code to add/edit/delete a table, as well as inserting/updating/deleting data from the database
  2. Each migration file, once run, will be recorded in a migrations table to ensure it isn’t run again
  3. This file can be committed to Git as part of a feature branch
  4. The deployment process for the live site has a build step after the files are copied to the server to run the database migrations via a custom wp dbi migrate WP-CLI command

This allows me to write PHP code to change tables and alter data as part of any code changes for a new feature or bug fix. Those changes are stored in Git and form part of the GitHub pull request that is reviewed, merged, and eventually deployed. Using this process does mean I need to be aware of how WordPress code alters the database so that I can make schema changes using code like this:

<?php

namespace DeliciousBrains\WPMigrations\Database\Migrations;

use DeliciousBrains\WPMigrations\Database\AbstractMigration;

class ComposerStatsTable extends AbstractMigration {

    public function run() {
        global $wpdb;

        $sql = "
            CREATE TABLE " . $wpdb->prefix . "woocommerce_software_composer_download_statistics (
                id bigint(20) NOT NULL auto_increment,
                composer_key varchar(50) NOT NULL,
                package varchar(50) NOT NULL,
                version varchar(16) NOT NULL,
                original_package varchar(50) NOT NULL,
                download_time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
                PRIMARY KEY  (id)
            ) {$this->get_collation()};
        ";

        dbDelta( $sql );
    }
}

As well as making changes to the data like this:

<?php

namespace DeliciousBrains\WPMigrations\Database\Migrations;

use DeliciousBrains\WPMigrations\Database\AbstractMigration;

class AddWpSesLandingPage extends AbstractMigration {

    public function run() {
        $page_id = wp_insert_post( array(
            'post_title'  => 'WP SES is Now WP Offload SES Lite',
            'post_status' => 'publish',
            'post_name'   => 'wp-ses-now-wp-offload-ses-lite',
            'post_type'   => 'page',
            'post_parent' => 49760,
        ) );

        update_post_meta( $page_id, '_wp_page_template', 'page-wp-ses-landing.php' );
        update_post_meta( $page_id, '_yoast_wpseo_metadesc', 'Looking for WP-SES.com? WP SES is now WP Offload SES Lite. See what improvements we\'ve made to the free version and what you can get when you upgrade.' );
    }
}

I’m using WordPress PHP code to script any database changes manually, which can be tested using wp dbi migrate on the command line. I can also roll back the last migration if I include any rollback code in the rollback method of the migration class. I’m in control of the changes I make to the database with scripting like this, but sometimes I do wish I could use the WordPress admin UI to make my changes and perform a merge, like we hoped would be possible with Mergebot.

What about other merging solutions on the market?

Honorable Mention: VersionPress

When we started working on Mergebot, a similar plugin existed that used Git to track changes to WordPress files and the database called VersionPress.

Unfortunately, in June 2020, the development team announced that development of VersionPress had come to an end.

WPMerge

Not long after we decided to shut Mergebot down, a new merging solution entered beta. WPMerge is similar to how Mergebot worked in many ways. You make sure your development database is synced with live, start recording, and then make changes to your development site to be applied later to live.

Sometime after its launch, WPMerge partnered with Nexcess to add further value to their WordPress and WooCommerce hosting customers.

I gave WPMerge a quick road test, and it worked well. I did notice some differences from Mergebot in its implementation. It uses database triggers on the development site’s database to record all the queries executed instead of recording them using the WordPress ‘query’ filter.

In my brief testing, I did the following:

  • Adding a new post to dev and making sure when it was deployed to the live site it had the correct post meta relationship with the deployed post ID
  • Adding a new post with a gallery of newly uploaded images to see if the media post IDs in the gallery shortcode added to the post content were correctly replaced on deployment – they were 🎉
  • Editing an existing post title on my development site and deploying to live where the title had also changed – the development edit was used without asking 😬

Unfortunately, WPMerge also doesn’t handle conflict resolution:

For now, just like Git, the development changes take priority. We are looking to develop a conflict resolution module where you can choose which changes to keep.

For example, suppose you change a page title from ‘About’ to ‘About Us’ on your development environment, but before you deploy the changes, your client has also changed the title from ‘About’ to something else like ‘The Team’. In that case, WPMerge will ignore the client’s change and use your change as the new value.

Even as a developer on a former competitor, I was impressed with WPMerge and how it worked out of the box. I do think the UI/UX could do with some polish to make it easier for developers to use and understand the workflow better. I found myself very confused as to what the right action was to apply my current development changes to the live environment. I also noticed just how many queries were recorded and deployed that were unnecessary for the changes I was making. After the simple round of testing I described above, the plugin was recording over 400 queries! This made me nervous about using this on large-scale sites with lots of plugins installed.

While WPMerge is still in active development, it doesn’t seem to be adding any new features besides the core offering. The latest changelog entries reflect only a few bug fixes and an update for PHP 8 support.

Wrapping Up

I still don’t believe there is a magic bullet solution for database merging in WordPress. It is a problem that developers need to be aware of so they don’t get themselves into a situation with out-of-sync local and live databases. Be prepared before you make changes, record what you do and then play those changes back later at deployment time. For me, keeping this process as systematic and automatic as possible is key to improving my development workflow. It also reduces human error, especially in a team environment, as every data change is committed to version control.

What is your preferred method of applying development database changes to a live site? Have you found a tool to perform database merging that you are happy with? Let us know in the comments.

About the Author

Iain Poulson

Iain is a product manager based in the south of England. He also runs multiple WordPress products. He helps people buy and sell WordPress businesses and writes a monthly newsletter about WordPress trends.