Reverting a dropped column

In my talks and article I always mention that when it comes to database migrations it is generally a bad idea to rely on undo-patches for rollbacks. As an example I always use the same story: “Imagine you write a database patch that removes a column from a table, and then you write an undo-patch that adds the column back again. Sure, your database schema is now the same as it was before – but the content is gone!” And then I would move on by saying that you probably shouldn’t rely on undo-patches anyway for rollbacks, because it is better to thoroughly test everything before updating so you are absolutely certain that you won’t have to rollback. Also, I recommended to make backups first, and use that to revert database migrations when needed. Easier said than done!

The fact that I am in the luxurious position where this approach usually is an option, doesn’t mean that this is a solution for everyone. For other projects where, for example, larger databases are involved, rolling back by restoring a backup would be considered highly inefficient (and if the database is in use it would actually result in data loss). Being able to rollback patches can be convenient, but reverting dropped columns can’t be done off course.

When I did my talk at DPC10 in Amsterdam however, someone in the audience (sorry, I don’t remember who it was) suggested a very simple but very effective solution. In fact, it was such a simple solution that I couldn’t stand I hadn’t thought of it before: If you rename your column or table first instead of dropping it you can still just use an undo-patch to revert the change. In a later stage, when you are absolutely certain you don’t need to do a rollback anymore, you can simply drop the renamed column or table by writing another patch. For example in a next release.

Reverting a renamed column

As you can see, sometimes the simplest solution is the best. Beware though, as in some situations this approach might still cause problems: Records added to this table between the moment the patch was applied and the moment the undo file was applied won’t have a value in the age column, to name just one. As always when we are talking database version control, it all depends on the specific situation you are in.

I myself still refuse to write undo-patches as most patches create new tables or columns anyway, instead of removing them – and if patches actually do drop tables or columns they probably haven’t been very significant for quite some time anyway. Maybe the main reason is that I have simply never found myself in the situation where an undo-patch would have helped me. This might be different for you though, and if you are looking for a way to rollback dropped columns or tables, renaming them first might be a pretty good idea.