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

#

Database merging is a problem that still exists in WordPress. It’s a question that gets raised almost every month at the local WordPress meetup I run – “if I am making changes to my local site, what happens to any new changes to the live site if I push my local database back to live?”

It’s still a very valid question.

Brad talked about this problem at WordCamp Miami in 2014 and wrote a post about avoiding database merging. We even tried to tackle the problem with our since-retired merging solution Mergebot. But the problem still exists, and what’s a WordPress developer to do about it?

In this post, I’ll revisit the problem, survey the current landscape, and share how we deal with it for our sites.

What is the Problem?

The problem exists for almost any developer working on a local development copy of a site that also has a live version running. The developer makes changes locally; configuring theme settings, adding new pages, Advanced Custom Fields fields, and tweaking plugin options. Meanwhile the live version is being changed by the client, writing new blog posts, or in the case of ecommerce sites, receiving numerous orders and getting new products added.

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 certain sites where full-blown data merging and conflict resolution isn’t needed, a partial push of the database with something like WP Migrate DB Pro would work in theory, but the data architecture WordPress uses and encourages other plugins to use makes it extremely difficult to perform selective data migrations.

As an example, for ecommerce stores where the only data changing on the live site is the products by the store owners, and the orders from customers, a simple migration could work. Ideally these pieces of data could be ignored when pushing data changes made during site development back to live. There’s one problem with this – WordPress custom post types.

Man looking at other woman meme

Why Custom Posts Types Complicate the Problem

WordPress has an extremely flexible data structure which allows data objects of different types to be stored in the wp_posts table. Which isn’t an intuitive name for the table that holds records of data for posts, 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. The type of data object is defined by the post_type column in the table.

When custom post types landed in WordPress 3.0, there was much rejoicing at a feature which enabled WordPress to become more than just a blogging platform. 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 stuff for free without having to code pretty much anything else; 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, wp_posts 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 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.

The EDD team is working on their big 3.0 update which will completely migrate all their custom post types to custom tables. In fact, John James Jacoby spoke at WordCamp Europe 2019 about a new approach to managing and accessing custom tables.

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 We Do

Brad previously suggested using SQL or PHP scripts to perform the data changes during your development, which can then be used to apply the same changes to the production database when it’s time to go live with your changes. Essentially, that’s still the same approach we are taking 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 wp dbi migrate

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, and 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 manages 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.' );
    }
}

This is a change to the development process that we encouraged with Mergebot, where you recorded the data updates you made via the WordPress admin UI. Instead, I’m using WordPress PHP code to script those changes manually, which can be tested using wp dbi migrate, as well as rolling back the last migration if rollback code has been included in the migration class as the rollback method.

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?

Current Merging Solutions

VersionPress

When we started working on Mergebot, a similar plugin already existed that used the power of Git to track changes to WordPress files and the database. VersionPress is still going but, as far as I know, the plugin is in developer preview and not recommended for use on production sites. VersionPress, like Mergebot, needs to know all about the data relationships in WordPress core, and all plugins used on the site – which as we found out is quite a challenge:

For actions that perform database merging (pull, undo, …), all plugins on your site must be compatible with VersionPress, otherwise the site integrity cannot be guaranteed. At this point, you will need to ensure this yourself and possibly write plugin definitions.

I spoke to Borek at WordCamp Europe 2019, and although VersionPress is still in development, they’re exploring other solutions as well.

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.

It seems to be gaining traction and recently partnered with Liquid Web to add further value to their WooCommerce hosting customers.

I recently 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 prod 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 dev, and deploying to prod where the title had also changed – the dev edit was used without asking 😬

Unlike Mergebot, WPMerge doesn’t yet 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, if the developer updates a page title from ‘About’ to ‘About Us’, but before you deploy the changes your client has also changed the title from ‘About’ but to something else like ‘The Team’, WPMerge will ignore the client’s change and use the development 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 could do with a polish to make it easier for developers to use and understand the workflow better. I also noticed just how many queries were recorded and deployed that were unnecessary for the changes I was making, and made me nervous about using this on large scale sites with lots of plugins running.

I’m interested to see how these products progress and mature, and if any new solutions might enter the market.

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 in 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 systemized and automatic as possible is key to improving my development workflow as well as reducing 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 WordPress and PHP developer from England. He builds free and premium plugins, as well as occasionally blogging about WordPress. Moonlights as a PhpStorm evangelist.