With SQL Server we tend to build databases, when necessary, from one or more build scripts. If making changes to existing versions of the database, we then script the required changes. Usually, a synchronization tool will create a script that can be tweaked to work; although occasionally it will require something more complicated, as when we change a group of tables that model a complex relationship. The alternative, a technique that seems especially popular with MySQL users, is to do builds and updates using a series of migration scripts, executed in the correct order. Occasionally, this can seem like another cultural divide, where 'migrations' developers argue loudly with the 'state' establishment, but in fact is merely a difference in emphasis.

I've always been on the 'essential' wing, when ascribing a value to the importance to source control. A working database developer sees the source of a database as an asset of the organisation that, hopefully, has value and so must enjoy the same custodianship as any intellectual assets. A developer who works primarily with migration scripts must still use source control: it isn't enough to store migration scripts without the source scripts. It is important to be able to track individual database objects so that one can see who in the team did what and when. You still need the object creation scripts in a migration-first development even if you just back-fill them into source control after a successful build.

A developer using a more traditional approach, working from the object scripts, can and usually will, create a migration script to make changes, and will test it out in staging until it works. Often, the auto-generated script will do the job unmodified. If you're wise, you will then enshrine that successful migration script in source control.

Whichever way you've done it, you end up with both the source, preferably at object-level, and the migration scripts to go from each published version to the next. Whatever way we do it, we end up using a migration script to update the production database. What, I've often puzzled, is the difference?

After asking around, it occurred to me that 'migration-first' and 'state-first' are just techniques that we all use at different development stages in the lifecycle of a database. We already have plenty of ways of working. After all, when we generate a first-cut database from an E-R diagram we are hardly going to claim a new development technique. When we test a data model against cruel reality, we will do a lot of fine-tuning of tables that have test data in them, using migration scripts to deal with the complexity of shuffling the data around. As a database matures, and more people work on it simultaneously, the unpicking of source control conflicts becomes intolerable and we settle for a state-based object-level way of working. Where is the dichotomy? Where is the conflict? 'Migrations-first' seems to be just another tool in our toolbox.

Phil Factor