TicketDesk 3 Dev Diary – EF Migrations & TD2 Upgrades – Part 1

Sandhill Crane Migration by Serge MelkiTicketDesk 3 may be starting from a fresh code-base, but it is not a new application. There are organizations using TicketDesk in the real world. New releases of TicketDesk have always supported in-place database upgrades, and later versions automated them. I plan to continue this tradition in TicketDesk 3, but would rather not need to roll my own upgrade code, or rely on hacked up T-SQL scripts.

How Migrations Help:

EF code-first is highly capable of generating a well structured SQL database from a pure-code driven model –most of the time. But, EF’s standard database initializers only handle two operations; drop a database, and generate a new database. When you make a model change, EF can automatically drop the DB, and then create a new one that reflects the current model.

This is all fine when developing locally, and is acceptable when deploying a new installation to production. But once you have a production database that real people are using, it is a bad idea to drop and re-create it to deploy a new version of the code.

Customers often dislike having their production databases dropped.

This is where EF Migrations come in. The migrations framework allows EF to modify an existing database in-place, keeping existing data intact. Customers often prefer this approach.

EF make some assumptions about how you will use migrations. It expects a linear, ordered evolution of your database, starting from some “initial state” and ending with the “current state” of your model. The first migration creates a base-line database, then other migrations modify that base-line to reflect any model changes that come along later.

What TicketDesk needs from EF Migrations:

New TicketDesk 3 installations work fine with the normal linear pattern expected by EF migrations. The initial migration will create the database, and fill it with tables. Since I was careful to start with a model that closely resembles TicketDesk 2, the initial migration will create a complete, and reasonably mature, database schema. The TD3 model shouldn’t experience the kind of rapid churn you’d see when building a brand-new product. Still, some model changes will be necessary as work on TD3 progresses, and additional migrations will be needed. The standard design of EF Migrations fully supports this pattern of model evolution.

In my earlier post, I mentioned that there are some database objects that EF can’t automatically generate from data annotations, or the fluent API –default constraints being the main culprit. TicketDesk 3 could get by without default constraints, and the POCO classes could just handle setting defaults in code. But I want my DB to be as similar to the stock TD2 DB as possible. Fortunately EF Migrations does allow you to add real default constraints as part of the migration routine, though you have to manually edit the code to add them.

When upgrading from a TD2 database things gets more complicated. The linear pattern EF migrations expect is not sufficient for upgrades. EF should not attempt to run the regular kind of initial migration against an existing TD2 database. Such a migration would try to create DB objects that already exist, and would fail.

When upgrading, EF will need to run a different initial migration routine –one designed only to modify the DB where necessary to match the normal TD3 schema. Since I was careful to ensure that the two schemas are nearly identical, all the special migration has to do is clean up a few minor variations (e.g. mark an FK as “cascade on delete”, create a non-clustered index, etc).

In theory, once the special migration runs, the old database should be functionally identical to a regular TD3 database. No matter which initial migration routine runs, the same set of subsequent migrations will be able to upgrade the initial DB to reflect model changes that occur later in development.

So that’s what I want from EF Migrations. In the next post, I’ll try to make it happen.

One Reply to “TicketDesk 3 Dev Diary – EF Migrations & TD2 Upgrades – Part 1”

Leave a Reply

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