Migrations are very powerful. When they work it?s awesome, but when things go wrong trying to determine what happened can be extremely frustrating. I have spent quite a bit of time mastering a process that works well for me. Here are a few pointers I have learned along the way:
1 – Do not use Automatic Migrations
Automatic Migrations are fun for demos and quick proof of concepts, but have no place in a production application (use Code-based). Here are 3 main reasons not to use Automatic Migrations:
- An Automatic Migration cannot be uniquely identified, thus a ?diff? script cannot be generated between two migrations (to apply to other environments).
- Automatic Migrations don?t show what changes are being applied to the database, nor provide a way to be individually backed out.
- Automatic Migrations are applied using the same ?Update-Database? command that also runs the Code-Based migrations (side effect free!). When mixing Automatic and Code-based migrations a simple ?Update-Database? to run Code-based migrations may also generate Automatic ?ghost? scripts against the database.
2 – Know the _MigrationHistory Table
The _MigrationHistory Table provides metadata about the database. Most importantly it shows what migrations have been applied. At some point it may be necessary to manually remove records from here. By default it is a system table, but can be configured otherwise.
3 – Migrations are both ?Up? and ?Down?
Each migration has the concept of ?Up? and ?Down?, where ?Up? applies the changes to the target database and ?Down? reverts them. Understanding what it takes for the schema and data to go ?Down? is just as important as ?Up?. Think of it as a contingency plan, or insurance.
4 – If you ?re-scaffold?, make sure to undo first (Down)
?Re-scaffolding? regenerates an existing migration with additional changes.
If the migration has already been applied to the database it must be backed out before re-scaffolding (by targeting the previous migration with Update-Database). If not backed out the re-scaffold will replace the migration with only the new changes and the database will be stuck (it wont be able to go ?down?).
5 ? Never delete a Migration before backing it out (Down)
A migration has ?Down? code in it (see 3). If deleted before the changes are undone the database will be stuck in an invalid state. This can get very problematic if subsequent migrations are added on top.
6 – Understand the Relationships (and the Fluent API)
Does this sound Greek?
It did to me. However, taking a database beyond ?Blogs and Posts? requires intimate knowledge of the Fluent API in order to configure the correct relationship. Learn it.
7 – Look at the Migration code
What!? It?s not perfect? Nope, it is not. I make changes to the generated code all the time. Sometimes it is just flat out wrong or misses things.
8 -Tear all the way down, and then back up
Frequently check to make sure the database changes are in sync and healthy. One way to do this is by tearing all the way down to Migration ?0?, and the doing an ?Update-Database? to bring it current. A unit test works great to automate this.
9 – Relax
Learning Migrations can be a frustrating time suck. Don?t punch your monitor. Instead take a breather and relax, then come back to it. It is an investment, but when mastered it will pay dividends to your team and process.
Thanks Jarod, good stuff. I’ve been toying with migrations since the first CTP but there are def some gotches I’m glad I know of now.
last tip is probably the best one 🙂
Can you post an example of #8? I’m not clear on how to give it a connectionstring and how you delete the database afterwards
var settings = new Settings
{
TargetDatabase = new DbConnectionInfo(“Foo”),
AutomaticMigrationDataLossAllowed = true
};
var migrator = new DbMigrator(settings);
migrator.Update(targetMigration: “0”);
migrator.Update();
———————————
+ You can derive your own Settings (DbMigrationsConfiguration) specifically for this use case and environment)
+ I don’t think there is anyway to delete the DB from the DbMigrator, but you can just create a helper to do that if needed. IMO it makes sense to not delete it so you can ensure the migrations work against the last known point.
My plan was to run it against a totally new database so it’s not screwing with data that someone else may be using. I guess I could just use the same name everytime, but there is a slight possibility that I run 2 CI builds at the same which both try run the migrations on the same database. Which is why I was going to delete it afterwards. I’ll have a play and post my results.
What I would do in that case is build up to the current, then back down, then up 🙂
migrator.Update(); //Up
migrator.Update(targetMigration: “0”); //Down
migrator.Update(); //Up
That way you ensure your down code is being tested.