Adding a change log to your database is the best way to make sure you're working on a version of your web application that adequately reflects a given state of your product. However, when working with a branching-and-merging development environment, where two different developers may be working on a migration at the same time, we often encounter a race condition that can cause keeping development-level environments pristine compared to those that only deploy stable code. Here, I outline a methodology that allows both the flexibility of distributed development as well as sane migration management in an application with an evolving database schema.

Object-Oriented Rollbacks

I'm a big believer in using some kind of database change log for any project you're working on. As someone firmly entrenched in the PHP world, I'm used working with serial migrations such as you'd see in the DbDeploy functionality of Phing. So the form our DB change log table takes basically lists the major-level component upon which the logged migration interacted, and the migration number. Furthermore, with each release, we have a Version class that always lists the current version of the product and the most stable migration number for each type of migration.

This basic table allows us a couple of different operations:

  • Update to latest migration available: This is used when we deploy stable code, or when we're updating our local databases to the current iteration of development code
  • Update only to the latest stable migration This is used for both updating to a stable version of the database schema as well as returning the database schema to its last stable state.

For both of the above actions, if the database's change log shows that it is at a migration later than the most stable one, we roll back all migrations, removing the latest row from the change log to reflect where we are in the state of the database's schema. Rollback logic was originally a just a required method of each migration class that implemented a "Reversible" interface. The migration would run a deploy() method when rolling forward, and a rollback() method when rolling back. Here are some issues with utilizing this methodology off the shelf:

  1. Before pulling down new code in your development branch, you'll have to roll back to the stable branch in case you need to, for instance, ameliorate a code conflict. Sometimes, this unnecessarily destroys test data.
  2. If you forget to roll back to stable, you now might try to roll back code that never ran if migrations have been reordered or changed
  3. Your database only knows whether it ran Migration_N, and not whether calling Migration_N::rollback() will actually roll back the logic created by Migration_N::deploy(). It's entirely possible that Migration_N is now Migration_N+1 or even Migration_N-1

So how do we mitigate against these possibilities? Ideally, we would still like to tie rollback logic to the same migration class that is deploying the changes to the database schema. However, we don't want to make the mistaken assumption that calling Migration_N::rollback() will universally roll back what posted the original increment to the database change log.

Pairing Deployments to Rollbacks

My solution to this problem is to insert rollback logic directly into your database change log. I added a TEXT field called "rollback" to each database change log. I then changed the Migration's rollback() method to a getRollbackSQL() method that returns the fully valuated query that we store in the change log. We now take the following steps when rolling back a migration:

  1. Check if the change log entry contains rollback SQL. If so, run the query, delete the row, and continue iterating
  2. If there's no rollback SQL in the change log entry, we try to call Migration::getRollbackSQL() for the migration number we're on.
  3. Worst case scenario: there's nothing available for us to do. For instance, the change may have been irreversible, data cleanup, or just asymmetrical. Just remove the entry from the DB change log, and hope that we

This behavior is encapsulated in the parent Migration class's rollback() method. So how about the old migrations that may have utilized rollback() in a way that (here's hoping rarely) flouts the paradigm of just executing a query against the database? And how about future cases where a simple DB query may not be all we have to do during the migration? Well, for these cases, we can just extend the rollback() method itself instead of the getRollbackSQL() method. This allows us freedom when we need it, and simplicity when we don't.

Application

Let's walk through a scenario where this methodology provides a significant value against a process that requires we always update our database to a stable state before updating your development branch.

Joe Developer creates a migration in a branch to address a ticket during a maintenance sprint. He finishes development on the ticket, and merges his completed work, including the migration, into the development branch. As he's finishing this up, Joe gets a call from his boss. Someone needs a hot fix, pronto. Joe immediately switches over to work on said hot fix, branching off of the most stable tag of the software.

As it turns out, the fix also needs a migration, but Joe never rolled his changes back. Switching back to the development branch just to roll back to stable would break his stride, but it's something he'd have to do now that he's working on the hot fix, which doesn't have an instance of a development-level migration. However, regardless of what branch he's got checked out, if he's working under the new paradigm, he can easily roll back any unstable migrations (provided they all use the getRollbackSQL() methodology), and can be sure that he's rolling back the old migrations, and not any migration he's currently coding under the same migration number. Furthermore, when he merges his completed hot fix into the development branch, if he's using continuous integration, he can be assured that the migration deployment process won't get confused by any migration reordering. Otherwise, there's a chance that on some instances that use development code, Migration_N_hotfix (the real Migration_N) would never run, or that the rollback method would be called against Migration_N_hotfix when we'd actually want it to run against Migration_N+1_Dev now.

Conclusion

Obviously, this is just one way to handle the issue -- particularly, if you're using migration classes rather than simple SQL scripts. Reconciling server-side application code with database-level logic will always introduce areas where you can improve your coordination. Some people just opt to avoid this trouble all together and write their migrations as .SQL scripts rather than as classes, but there is a value in treating your approach to migration at the application layer, through objects. For instance, in our application, one type of migration is actually iterated over a variety of databases, so we can just act over the same table on a number of databases by just creating one class that manages providing the appropriate values for each dynamic script.

Whenever there's an opportunity for iterating your software, there's often an opportunity for iterating your process. The original approach was developed for lack of having any database rollback whatsoever -- changes were in effect permanent, leading the shared development instance to diverge significantly from the stable instance in unexpected and irreversible ways. I'm sure there will be opportunity in the future to improve things even further. Altering your database schema invariably introduces some level of risk. So what are some of the tricks you use to safely iterate your database?