Make reversible database migrations

bidirectionalRecently, I added a new feature that significantly changes one of the database tables in my application.  I ran into problems when I created a non-reversible database migration, and realized that other branches I am developing on still require the older database scheme.  In the end I had to end up dropping the DB, deleting the offending migration, rebuilding the DB, and figuring out a smarter way to migrate.  Not fun, and time wasted.

What got me into trouble was forgetting that I have multiple code streams I’m working on.  I’m trying to do a good job and create different branches for different features, merge to master frequently, and re-base WIP branches to keep in sync with master.  This works pretty well for code, but not so well for DB schemas.  Specifically, the challenge I ran into occurred when I renamed a column, changed its type, and remove a couple columns I didn’t need any more in that branch.

Here are some best practices I came up with:

  1. Instead of renaming a column, create a new column with the correct name. Remove the old column once you get all branches migrated to the new column
  2. Before removing a column, make sure no other branches depend on it.
  3. When removing a column, explicitly define your “up” and “down” methods.  It doesn’t seem that the “change” method can magically reinstate a removed column, which makes sense as the type, and other attributes once can define at column creation time do not exist in that file
  4. Evolve your table architecture incrementally.  Like with TDD, make changes in small, coherent steps.  Big changes smell of irreversibility.

 

Leave a Reply

Your email address will not be published. Required fields are marked *