Avoiding WordPress Database Merging

When WP Migrate DB was first created it’s main task was to easily migrate an entire site’s database from one location to another. As time passed more and more people have been experimenting with the plugin attempting to solve other problems.

One such problem looks like this:

You have a live client website that is updated regularly. You clone that website to a local development website and begin tweaking the code and content. When the time comes, you decide to push these changes to the live website.

Here’s where things start getting a little tricky. While you’ve been working on some local changes your client has been adding new blog posts, images, etc. to the live site.

If you push your local changes to the live website you’re going to overwrite your client’s work. But, if you pull the live database back into your local environment you will overwrite your own work.

Currently WP Migrate DB Pro does not have the ability of “merging” two database together. This has been an often requested feature and we are currently brainstorming ideas of how this could be solved with the plugin. So far though, our solutions are looking very complex and still require manual conflict resolution. We haven’t come up with a magic bullet just yet.

But there are ways to avoid getting into this situation in the first place.

SQL Scripts

Let’s pretend you’ve decided to add an e-commerce solution to your client’s website. WooCommerce 2.0, as an example, requires you to create a few pages. Once these pages have been created, you could create an SQL file and record the SQL used to create these pages. e.g.

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 simply import your SQL script and you’re ready to go.

PHP Scripts

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

/*
 * To run the deployment
 * 1. Login as an administrator
 * 2. Go to http://yourdomain.com/?deploy_changes=1
 */
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;
}
add_action( 'template_redirect', 'deploy_changes' );

Once you’re ready to deploy simply push this code to your live site, login as an admin, and visit http://yourdomain.com/?deploy_changes=1 to run the code.

For this site (deliciousbrains.com), we have a maintenance.php file that we put this kind of code into and include it from functions.php. (It would probably be better to create a custom plugin for this, rather than include it in the theme, but either way works.) Then after we’re done with it, we simply remove the function from the file to make sure we don’t run the deployment again accidentally. Source control (git) ensures that we always keep a history of the old deployment code in case we need it again.

Why Database Deployment Scripts Are Awesome

One of the great advantages of scripting your database deployment is that you can test your deployment as you develop.

You can use WP Migrate DB Pro to easily pull down the latest database from the live site. This will overwrite your local database changes, but then you can simply run your database deployment script and continue developing. If there are any major issues, there’s a good chance you’ll notice them as you continue to develop.

Do you use database deployment scripts? How would you like to see WP Migrate DB Pro help out in this area? Let us know in the comments below.

Photo credit: OneFuller

About the Author

Brad Touesnard

As founder of Delicious Brains Inc., Brad wears many hats; from coding and design, to marketing and partnerships. Before starting Delicious Brains, Brad was a busy freelance web developer, specializing in front-end development.

  • Excellent idea! This is one of the biggest issues we’ve struggled with when it comes to developing new features for a live site. We can easily keep our code in sync with git, but the DB is another story.

    Do you know of any easy ways to track/find the actions a plugin would take when installing? Obviously you could crawl through the source, but I’d love it if there was a way to just install a few plugins, make a few tweaks (all on dev) and then get a log showing the DB actions I’d just done so we could at least create the deployment script from that log. Maybe that’s a feature request? 😉

    • I did write a little Query Recorder plugin that hooks into wpdb and records all INSERT and UPDATE queries to an SQL file. I haven’t released it as I didn’t find it very useful because you still have to fiddle with primary and foreign keys and it becomes a pretty big mess. I guess some people might still find it useful?

      • Oh very cool! Yeah I can see how that would be slightly helpful but maybe not always worth the effort. Still if you ever decide to release it, let me know as I’d love to play around with it.

        • thdamon

          that sounds kinda cool. i’ve successfully used the idea of database migrations on other complex projects, but the trick in WP is that typically developers won’t necessarily know what SQL updates they’re making.

          trying to automate it i could see UPDATEs being horrendous (but it could be possible to attempt to match using something other than ID, like title for things like categories…)..

          did you have many issues with INSERTs?

  • Nathan Lenkowski

    Speak of the devil. I was just discussing this WP migration conundrum with my partner today. The database deployment script is definitely an interesting solution, but for complex sites with lots of plugins and database modifications made over time I can’t see it scaling, not without it becoming more work than its worth. The developer who solves this beast of a problem definitely gets an A+ for the year (and likely lots of plugin sales).

    • Yea, we’ve been thinking about it more and more here. No breakthroughs, but have some good ideas so far.

  • Pepe Magaña

    There is a tool that seems very promising : http://dbv.vizuina.com/

    In my case I have 3 environments : dev, staging and production.
    For the files I work with GIT, so not a big problem (I will use the “dev” files and send it to staging for validation and test and finally to production).
    On the other hand for the database I use the production database to update staging and dev. In most of the cases I try to export and re-import the configuration from dev (plugins, preferences, etc) but trying to keep track of everything is almost immpossible).

  • TrevorAGreen

    It seems like the first thing to do would be to build in a way to tell which database version you are running. So if you pull then try and overwrite the live database the plugin will alert you if there have been changes to the live database and allow you to stop. Personally I think that the plugin should remain only as a way to develop your code, not to pull the database locally for content editing. Any content edits done locally should be simply for testing theme and plugin code.

    That is not to say that it wouldn’t be useful but it seems like a core function, to always sync some or all of the database to staging sites. So that you are staging posts and pages, because that is what the draft functionality is for in WordPress. Working locally is supposed to just be for development tasks.

    I’m interested to see what you come up with.

  • John Peterson

    I think it would be very difficult, and possibly dangerous to have an all-in-one merge database capability. There are too many versions of WordPress, too many versions of plugins, and too many variables. If I were writing the plugin, I would be worried about someone suing me if the plugin merged the databases incorrectly. A safer approach might be to merge just pages and posts in the core, and then have add-ons for each type of plugin. Like other posters, we use a 3-tier development model. We’re currently taking the latest development database and migrating the DB in it’s entirety to staging. Then I’ll look at the comment tables on the production database and overwrite just those tables on staging. I’ll then do the same for the tables related to Gravity Forms. I’ll then do the same for other plugins. This should give me a hybrid database on staging that has the best of both development and production. Then, once testing is complete, I’ll do a full migration from staging to production. This is tricky business though. If you’re not a developer with SQL knowledge, how database tables relate, and how that data is stored… you can get into alot of trouble quickly.

    • tex88

      So how much time passes from the hybrid staging database being created, until the full migration from staging to production? Unless it’s very short (and even if it’s very short), new content may have been added to production.. right?

    • Hi @disqus_ddnNqU8m7E:disqus, I would love to read a blog post about your technique for WordPress development and deployment, if you have it in you. Coming from a .NET environment where I have fairly straight down the line control over migrations and data between environments, would be great to read a detailed expose on this from somebody who has a solid process together. Haven’t found much online. If you can recommend some reading that would be great too.

  • jfarsen

    I’m also frequently running into this issue. I agree with @disqus_ddnNqU8m7E:disqus that we could easily be liable for lost data on client sites running in PROD.

    I’ve had to manually copy data from tables like he’s mentioned and it’s PAINFUL. And long. And error-prone, like any manual process.

    It would seem that DB sync/merge is the last piece of the puzzle to truly have environment separation/reconciliation.

    Perhaps the solution would be to see if we can tackle only a few wp_ tables (the ones that matter the most, and hurt us the most) and slowly make our way to full merge/sync?

    Or would a “diff”, at the database (.sql) level be a potential approach at solving this ?

  • LuyenDao

    A CMS like Drupal has (tried) to tackle this issue for years, I think in any deployable environment you’re better off with a file-based system to save configuration files etc…with all the benefits that go with it, rather than trying to mash tables together or running SQL queries, it just leads to problems. Have people in the WP community looked at this possibility?

  • John Peterson

    I would like to be able to make WP Migrate DB Pro scriptable. Once I know a site, I can determine which tables should be deployed from staging to production. I’m already doing this manually. Making this scriptable would be awesome.

  • Derek

    My dev site uses a independent database. I have been thinking about this issue a lot as im attempting to have my deploys as automated as possible. I would like to update a plugin on dev, I don’t want to have to track down the database changes before i deploy nor do i want to manualy update the plugin in stage and prod. My ideal solution is a program that tracks changes to a database and then generates a script that can be run to update the stage and production environment accordingly. I am not a DB expert so I’m going to be slow moving on this but the idea. 1) take snap shot of database 2) install plugins make updates. 3) take new snapshot compare with first , generate script that can recreate the changes.

    Then you can check all files into git or however people manage deploys so that the script can be automatically ran on deploy. If anyone knows any similar tools that does the diffing and script generating please contact me !! 🙂 I have a hard time believing these tools dont exist already. thanx!

  • KJ

    Has anyone tried this tool?

    http://www.red-gate.com/

    • I’ve used the MySQL comparison trial a few times with WordPress and for extreme circumstances it’s a great set of tools, but not something that’s easy or safe to use.

      It’s great for seeing what differences you might have between dev/staging/production and deploying updates to existing records works really smoothly. In WordPress though, it starts getting a lot more complicated if you have new records on both sides with conflicting primary keys.

  • raisononline

    This is a seriously big pain for us developing WooCommerce sites which have orders saved in wp_posts and wp_posts_meta. I think I will build a plugin to backup all WooCommerce orders to a separate table and then insert them back into the live database after a migration. Bit of a sloppy solution, but practical… Welcome other ideas….

  • Dave Navarro

    I think you should get involved with the WP-API project which is adding JSON to WordPress Core for accessing, updating and inserting data. Using JSON it would be fairly simply to compare data between two sites and perform a sync.

  • Paul K

    I am interested in doing this but am not sure where to begin. How do you record the changes in your sql db to create the sql script? Do you back it up beforehand & then compare it afterwards with some tool?

  • WooCommerce really should add its own buttons for copying all transactional activity from a live to a development server. Since WooCommerce is responsible for its own data structures, it’s the logical place for a tool to sync accounting activity in this manner.

    The way this would (should) work, is WooCommerce’s settings page would include a tab for “Data Sync”. In that tab, users would enter an API token generated by the target WooCommerce site. Once entered, WooCommerce would link from the dev or staging site and retrieve any new customers, orders & coupons (and anything else that only WooCommerce knows about), and add that data to the dev or staging site, while allowing MySQL to maintain proper data integrity by updating IDs appropriately.

    WooCommerce should also include an API & documentation so that developers providing WordPress backup and move tools could choose whether to include WooCommerce syncing as a step in the backup & restore process.

    Currently I use the bullet-proof UpdraftPlus plugin. It zips the content into themes.zip, plugins.zip, uploads.zip, database.zip & others.zip. Once I move those 5 files to another site with the UpdraftPlus plugin, the plugin will faithfully unpack & restore those files to completely convert one site to another. It perfectly replaces the URLs & I have rarely ever run into any issues with it.

    Now, if that dandy tool just had settings to specify the WooCommerce site I intend to restore to, it could use the WooCommerce API and add steps of fetching the transactional data from the site to be updated and add it to the staging site data, where it could even be verified before proceeding.

    With the popularity of WooCommerce and the number of sites depending on it, I worry that their market lead could evaporate to whoever provides this seemingly obvious functionality.

    • nathanjohnsoninc

      Hey, did you ever find a solution to this? I’ve looked everywhere for a way to sync between my local environment and production environment without overriding all of the WooCommerce new orders, but can’t find a solution…

  • Rob Jones

    This still remains the big problem for us merging local with live. We tried something that gets close – https://wpstagecoach.com/.

    It lets you create a staging site from the live site then merge both together on the live site. So it is actually merging data rather than just transferring the database from one place to the other. That works very well. The problem is it only allows you to create one staging site which have to be on the stagecoach servers… meaning you can’t work locally.

    It’s close but not quite there but a definite step in the right direction. Maybe someone could check out what they’ve done within the plugin to set you off in the right direction?

  • donor_king

    >>Currently WP Migrate DB Pro does not have the ability of “merging” two database together. This has been an often requested feature and we are currently brainstorming ideas of how this could be solved with the plugin. So far though, our solutions are looking very complex and still require manual conflict resolution. We haven’t come up with a magic bullet just yet.<<

    i don't think you should cloud or confuse your migrate-product with more "other" side features – maybe a separate plugin