In a current database driven project that has multiple developers the single biggest issue we have is version control for the database.
For us at ibrow, the number one practise that we engage in is Version Control/Revision Control. I personally use version control for almost everything and not using it when having multiple developers working on a project sends shivers down my spine.We currently use Subversion, however I’m thinking of moving over to Git, but there is also Bazaar and Mercurial plus many, many others.
Whilst there is this vast array of version control system for code, there doesn’t seem to be the focus on version control for database schemes. Jeff Atwood has a couple of posts espousing the virtues of version control for databases. There is Rail’s Migrations which seems to be the most advanced version control for databases to date and there is a project to make a PHP version of Migrations. However, Migrations still has it’s flaws, which are discussed in Adam Wiggins’s blog post about this very subject. Adam offers some good insights into the problem and has a brain storm about possible solutions. But I think I’m coming to the problem from a slightly different angle to him.
As most of our projects are in constant development we do not, as such, have versions, but instead revisions. This is a key difference here. If we check in a database change to the version control, we don’t yet know it’s revision, especially if we have multiple developers working on the same project and possibly changing the scheme of the database at the same time.
The ideal solution would use the following steps:
- Developer finishes their chunk of code with any database changes, and are ready to commit. The database changes include UP and DOWN changes.
- Developer commits their code changes, a revision number is assigned
- Based on this revision number, the database changes are committed.
Obviously the developer should update their working copy before committing, just to see if there are any conflicts and to make sure they have the latest copy of the code.
On the updating side, the following steps should occur when the developer updates their code:
- The update process makes a note of their current revision number
- Downloads all code changes via the SVN update command
- Downloads all the database changes since their last revision
- Update process applies all database changes to developer’s local database
This process can be reversed for reverting or merging backwards.
Currently I can’t find anything out there that does this – which probably means one of 2 things:
- My Google-fu isn’t up to scratch
- It is actually a lot harder than I think!
I really need to get this right, as this is possibly the most important thing to get right for us at the moment. It would be great to hear any possible solutions or existing implementations out there.
Maybe this will be an interesting sub project for me over the next few weeks?
(And obviously I haven’t even considered trying to keep the data up to date – but I guess that will be for another day.)