How do you manage database revisions on a medium sized project with branches?

Question

At work we have 4 people working together on a few different projects. For each project we each have a local copy we work on and then there is a development, staging, and live deployment, along with any branches we have (we use subversion). Our database is MySQL.

So my question is, what is a good way to manage which revisions to the database have been made to each deployment (and for the developers their local copies). Right now each change goes into a text file that is timestamped in the name and put into a folder under the project. This isn't working very well to be honest.. I need a solution that will help keep track of what has been applied where.

Answer 1

http://odetocode.com/Blogs/scott/archive/2008/01/30/11702.aspx

The above blog brought us to our current database version control system. Simply put, no DB changes are made without an update script and all update scripts are in our source control repository.

We only manage schema changes but you may also be able/willing to consider keeping dumps of your data available in version control as well; creating such files is a pretty trivial exercise using mysqldump.

Our solution differs from the solution presented in the blog in one key manner: it's not automated. We have to hand apply database updates, etc. Though this can be slightly time consuming, it postponed some of the effort a fully automated system would have required. One thing we did automate however, was the db version tracking in the software: this was pretty simple and it ensures that our software is aware of the database it's running against and will ONLY run if it knows the schema it's working with.

The hardest part of our solution was how to merge updates from our branches into our trunk. We spent some time to develop a workflow to address the possibility of two developers trying to merge branches with DB updates at the same time and how to handle it. We eventually settled on locking a file in version control (the file in question for us is actually a table mapping software version to db version which assists in our manual management strategy), much like you would a thread's critical section, and the developer who gets the lock goes about their update of the trunk. When completed, the other developer would be able to lock and it is their responsibility to make any changes necessary to their scripts to ensure that expected version collisions and other bad juju are avoided.

Answer 2

We keep all of our database scripts (data and schema/ddl) in version control. We also keep a central catalog of the changes. When a developer makes a change to a schema/DDL file or adds a script that changes the data in some way, those files are added to the catalog, along with the SVN commit number.

We have put together a small utility in-house that reads the catalog changes and builds a large update script based on the contents of the catalog by grabbing the contents from each revision in the catalog and applying them. The concept is pretty similar to the DBDeploy tool, which I believe originally came from Thoughtworks, so you may be able to utilize it. It will at least give you a good place to start, from which point you can customize a solution more directly suited to your needs.

Best of luck!

Answer 3

If your database maps nicely to a set of data access objects, consider using 'migrations'. The idea is to store your data model as application code with steps for moving forward and backward through each database version.

I believe Rails did it first.

Java has at least one project.

And here's a .NET migration library.

To change versions, you run a simple script that steps through all of the up or down versions to get you to the version you want. The beauty of it is, you check your migrations into the same source repository as your app code - it's all in one place.

Maybe others can suggest other migration libraries.

Cheers.

Edit: See also http://stackoverflow.com/questions/313/net-migrations-engine and .NET database migration tool roundup (from above post).