Migration Scenarios
Deployment of an web application sometimes is hard. We test everything and we backup our database before deploying an app, in case of something goes wrong. In this case, we rollback. We bring back previous version of an app and restore database. this can be done, when nobody put new data in out database since our last backup. What if we want to rollback our app after 3 days?
Introducing changes to database can be tricky, changes in nullability, column renames, separating parts of data from one table to another, changing relationships from one to many to many to many. After this kind of changes our app that we rollbacked, will most certainly crash. This kind of problem is known in programming for a long time, how to change a contract and not break clients that using it. Simple answer is you don't change a public contract once you published it. If you are certain that nobody is using it after some time you can remove obsolete parts of that contract. the same is true in case of database, but it can be done more easily, essentially when we know for a fact what apps are using our database.
This concept of making contract obsolete and giving time people to adjust is used for a long time. In .NET we have [Obsolete] attribute, in java we have @Deprecated. Sometimes like for example in MS SQL we have a list of deprecated parts of an api https://msdn.microsoft.com/en-us/library/ms143729.aspx, that will be removed in next release.
We can do the same to our database. We can change schema when our application changes, but we have to be certain that our database is compatible with previous version of an application. When we are sure that we will not rollback and nobody is using obsolete parts of schema, we can deploy our database again without deploying our application and remove obsolete parts of schema. Simple.
Changes can be performs in two cycles, lets name them Tick and Tock (after Intel model). Tick is breaking (it introduces new architecture) Tock is non breaking (old architecture is maintained).
As I mentioned before some kind of changes make our previous version of an application incompatible. These changes are:
- renaming column
- adding not null without default value
- concatenating columns
- splitting column
- moving some data from one table to another
- renaming table
- changing one to many to many to many
- ... (if you think its worth mention specific change I missed here, tell me)
All those changes can be planed in two stages. Making them non breaking.
- when we want to rename column, we can add column and copy data in migration script in Tock cycle, and remove obsolete column in next Tick cycle.
- turn off nullability in Tock cycle we feel null values, and in Tick cycle we turn off nullability.
- concatenation is the same as rename.
- splitting is the same as rename.
- moving data in Tock cycle leaves old columns in place, data is copied in migration script to new table, in Tick cycle we remove columns.
- table rename can be done in two ways, rename table and add table synonym for old one in Tock cycle, remove synonym in Tick cycle.
- one to many to many to many can be done by adding new table, populating it in migration script in Tock cycle, in Tick cycle we can remove foreign key in many table.
When we downgrade our app, we can write some migration that copy missing data to old structures.
We can have as many Tock cycles as we want.
when we use Visual Studio and SSDT project, we have intellisense and project can be compiled and verified. Making Tick cycle also in project is very important, because, when we remove some data structures, some migration will be not applicable to new schema.
To solve this issue, we should differentiate migration scripts, one kind of script is a script that we want to apply to new database instance always when we deploy from scratch. Good example of this is script that seeds dictionary table. Another kind of script what we want to remove is that one that copied our data for example from obsolete parts of schema.
After Tick cycle we have database scripts and data that can be applied from scratch. Scripts from Tock cycle can be applied on its own also, but they can contain come obsolete data. Migrations should be written is such a way that they should be possible to apply them to database deployed from scratch.
Clone and review commits in examples branch