Development to Production: A Database Merge Script

Migrating changes from development to production is particularly tricky in Drupal due to a large amount of configuration settings being stored within the database. Attendees of Drupalcon 2008 made it clear that this is a particularly annoying problem. I attended the Development / Test / Production Environments panel to learn about how the big guys (including Drupal.org itself) manage deploying their sites from one working space to the next. We were disappointed to discover that even the big guys are currently writing down all the steps to upgrade and simply performing them on the production server.

This method poses several problems due to the Human Element being involved in the entire process.

• A human is manually writing down what changes need to occur
  • Steps can be forgotten
  • It could be recorded in a way that is not understandable later
  • Adds an extra burden in development to record steps, slowing down development
• The upgrade process needs to be performed once or twice before performing it on the live server to ensure it works properly
  • Very time consuming
  • Performing tedious actions repeatedly will cause memory fatigue where someone can forget
  • Performing all the tasks could take a long time, increasing the time that the production site is either frozen or unaccessible altogether
  • Testing current development against production data would be avoided or rare due to the difficulty to set up

Additionally, if the changes in the database (and thusly configuration settings) are not being kept cleanly within version control, then it reduces the effectiveness of using version control in the first place because the database will be out of sync with the code. There is enormous value to being able to cleanly roll back versions to find where a bug was introduced, or being able to remove commits.

My Solution

I have created a series of scripts which will dump, erase, restore and merge databases. I have released this as the Database Scripts module. The project's documentation should define the specifics of how the scripts work in detail. For now I will list an overview.

As I mentioned above, keeping the database in version control is key due to the extensive amount of configuration settings that are stored in the database. The scripts then make it easy to dump and restore the database in a development environment.

Version controlling the database also allows the possibility to more easily track down the source of bugs and remove single commits from the history. For example, I had an assistant who mistakenly made a very bad configuration change that I didn't notice for weeks. I was able to track down the commit and seamlessly remove it using subversion.

The lovely merge script (which I'm addicted to!) effectively takes the development database, strips it of user data, inserts production's user data and merges the content data of both databases. It is what allows content to be added and manipulated freely on the production site, while developers can freely make changes before deploying the new version to the live version.

I was just able to have almost four months of development on the HarvardScience website while the editor continually added content the entire time and successfully merge in my changes while preserving all data. I made extensive changes, too! I deleted and renamed content types, reworked views, added and deleted fields and made mass changes to nodes all without having to repeat my steps on the live site. And without having to repeat my steps, that also means I didn't have to write the steps down, significantly saving me time.

To sum up, this method allows me to:

• Branch production and development and allow each to have modifications made to the database
• Develop quickly and not be slowed down with documentation
• Keep database schema aligned with code in version control
• Quickly be able to test changes with production data