Strategies for re-deploying an older version of your application's database

Question

We have a web application written using ASP.NET MVC, C# and MySQL and using Entity Framework 6 as the ORM.

We're using Entity Framework's CodeFirst Migrations to control changes to the database over time and from release to release.

We have a Continuous Integration/Deployment process set up using Jenkins to create a build artifact (an archive file) which is uploaded to AWS S3 and from there a combination of Lambda functions and AWS's CodeDeploy are responsible for deploying the application to various EC2 instances.

Each EF Migration has both the Up() and Down() methods allowing that specific migration file to both upgrade and downgrade the database for that specific version of the application's code.

One thing that has recently come up and for which we have no clear answer is:

How do you manage a "downgrade" to the database if you are re-deploying a previous version of your application?

Since the application version (and thus the code) is older, any new EF migration files will not be included within it, therefore, there are no Down() methods that can revert your database to it's previous state as the Down() methods are all included within the later EF Migrations that are now no longer part of the (older version's) application code.

We don't often revert to older versions of our application, but it's something we may do occasionally if we experience unforeseen issues, and I'm sure that others have had to deal with the effective rollback/downgrade of a database that has been upgraded by a newer version of the application.

Does anyone have any good strategies for dealing with database rollbacks/downgrades, ideally in the most automated way as possible and by, ideally, using the exact same previous build artifact for the older version (i.e without having to completely "rebuild" the old version)?

Answer 1

This is an interesting topic, and I was hoping to see some more answers here. Since there aren't many contributions yet, I just wanted to relay my thoughts on this.

In my experience, there is not always a clear answer when performing database downgrades. Downgrading the schema is fairly straightforward, and a series of steps can be performed that can take the schema down to a previous version. As was mentioned, EF migrations already has this capability. The trouble comes in with the data itself. While some data can be versioned (lookup tables, configuration, etc.), the vast majority of data is typically outside the safety imposed by version control and the downgrade process. So, when applying a downgrade script, you may inadvertently delete some data from the database. UNLESS, of course, your downgrade process is written and tested in such a way that it takes the data into account when downgrading. For example, if you are migrating data from one table to another with a different structure during an upgrade, your downgrade process would need to migrate the data back (if possible). There are some cases where it may be difficult or impossible to downgrade, for example, if you drop a column from a table. In that case you would almost need to restore from a backup (at least that bit of data) since dropping the column would destroy the data in that column.

In the past, I've found it helpful to review the changes that will be made by the downgrade, and customize them as necessary. Actually, much of the time you will probably find that a database downgrade is not even necessary, and that your application will run just fine with the database changes intact.