Why Database Migrations First?

What problem is being solved by doing a migration first design? Eilenblogger asked this question in another way, “What do you do if your user acceptance tests fail? Do your migratons roll back, or do you simply restore from a backup?” This question is the crux of why I like to do database migrations first.

I worked on a project where we released new features to production every two weeks. We released every Friday at 5 pm. We were usually done by 5:15 pm, and never went past 5:30 in over a year. In that whole year, we never had to roll back. How could we do that many releases with confidence and success? Because we released the software multiple times every day. In fact, we released the somewhere with every check in made to version control.

By doing migration first the code needed to alter the database in order to keep it synchronized with the application code was part of the build and deploy process from day one. The application was not aware of the database migrations as some have designed their software. Database changes were done as part of releasing new code for any tier.

Here was the process we followed. New features were developed in a feature branch for all tiers. Once complete, that branch is merged into a dev branch. A continuous build and deployment process was executed every time the dev branch had new code checked in. All unit tests were performed, and if the build did not work, it was the first thing we fixed.

Once new features were code complete, the changes to be released were merged into a testing and user acceptance branch. Automated integration tests are performed, and users are able to fully test and approve the new functionality. By this time the database migrations have been executed dozens, maybe hundreds of times. We know they work. We know the code matches.

When release day comes, we merge the changes from acceptance into a production branch. A final build is executed against this new code, and the automated integration tests are again executed in a staging environment.

When 5:00 comes around, the code in staging is applied to the production servers and the database. We never forgot a change to some stored procedure, or to add a record to some lookup table. We did make mistakes. But they were always caught in dev, or on the rare instance, in acceptance. For the production release, all that was necessary was a smoke test verifying that all the code deployed. Since we used the same deployment process every time, it was always solid.

The key to this working, for me, is that if you always write the migrations first, you never forget. Comparing and Synchronizing has holes. It is not foolproof. If you write migrations first, and the migrations always work, you reduce the risk of human error.

What is the one thing developers hate to do most of all; merge code? This is especially true when automated merging does not work, and you have to merge manually. Synchronizing database designers between tools or databases is like doing a merge. How do I make the target look like the source? Writing a migration first eliminates the guesswork.

That’s enough on this topic for now. I hope it has been useful to you.