Database Migrations using FluentMigrato

Managing database changes together with application development and regular deployments has always been a hard problem. For applications that run on a Platform-As-A-Service, such as Windows Azure, this problem becomes even harder, because the database is used as a service, and some techniques that we are used to are not even available to us. We were used to generating a SQL change script that would run as part of the solution deployment, but with Azure deployments you have no precise control over the timing of deployment of cloud services, which makes it hard to synchronize database upgrade with the code deployment.

We decided to do this in another way: by integrating and automating database migrations right into the website solution. We used Fluent Migrator as the mechanism to help us do that, and it has been working great for us. The SqlFuMicroORM we use recently added data migrations in its featureset, but it seems to migrate one-way only, not as flexible and powerful as FluentMigrator.

Fluent Migrator works by searching the assembly for Migration classes, and running them. The Migration class implements an interface with two functions: Up() and Down(), that do exactly what their name says. The class is decorated with a attribute that tells FluentMigrator the version number of this migration, FluentMigrator automatically runs them in the correct order. The Up() and Down() mthods can run any SQL commands, but FluentMigrator also offers a more elegant way of defining the database structure: using a fluent .NET API to generate SQL Data Definition Language. It works like this:

[Migration(1)] public class InitialDatabase : Migration { public override void Up() { Create.Table("Auction") .WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity() .WithColumn("Title").AsString(80).Nullable() .WithColumn("Start").AsDateTime().NotNullable().Indexed("IX_auction_start") .WithColumn("End").AsDateTime().Nullable() .WithColumn("Type").AsInt32().NotNullable().WithDefaultValue(0) .WithColumn("State").AsInt32().NotNullable().WithDefaultValue(0).Indexed("IX_auction_state"); } public override void Down() { Delete.Table("Auction"); } }

It is also possible to run SQL scripts, which comes handy when you get a database creation script with a component, like Quartz.NET or Elmah. Just put the script file inside a .NET resource, and run it like this:

[Migration(2)] public class Quartz : Migration { public override void Down() { Execute.Sql(Resources.Quartz.MigrateDown); } public override void Up() { Execute.Sql(Resources.Quartz.MigrateUp); } }

You pay for SQL Azure as a service, so you might as well use it. It is possible to do “crazy” things, like create stored procedures and triggers, and fully utilize the power of a SQL database. Like this one, which prevents the deletion of a “special” record in a table with ID=0. Try to get around that with sloppy .NET code!

Execute.Sql(@"CREATE TRIGGER [dbo].[PreventSystemAuctionDelete] ON [dbo].[Auction] INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; IF ((Select COUNT(*) from [Deleted] Where [Deleted].[Id] = 0) = 1) BEGIN RAISERROR('You can not delete this specific record!', 0, 0) ROLLBACK TRANSACTION RETURN END ELSE BEGIN DELETE [dbo].[Auction] FROM [dbo].[Auction] INNER JOIN [Deleted] ON [dbo].[Auction].[Id] = [Deleted].[Id] END END");

Installing FluentMigrator into the MVC project is easy. In Package Manager console, enter:

PM> Install-Package FluentMigrator

PM> Install-Package FluentMigrator.Tools

Normally, Fluent Migrator runs database migration as part of a build task (as we used to do), but we are running the upgrade during application startup. During development, we delete and re-create the database from scratch everytime we start the application. In production, we need to be a little more careful: just upgrade the database schema and keep all of the data.

To get Fluent Migrator to run the database migration as part of the application, we’ll need to add a reference to the FluentMigrator.Tools.dll assembly manually, and add this function to run the migration:

public class Migrator { string connectionString; public Migrator(string connectionString) { this.connectionString = connectionString; } private class MigrationOptions : IMigrationProcessorOptions { public bool PreviewOnly { get; set; } public int Timeout { get; set; } } public void Migrate(Action<IMigrationRunner> runnerAction) { var options = new MigrationOptions { PreviewOnly = false, Timeout = 0 }; var factory = new FluentMigrator.Runner.Processors.SqlServer.SqlServer2008ProcessorFactory(); var assembly = Assembly.GetExecutingAssembly(); //using (var announcer = new NullAnnouncer()) var announcer = new TextWriterAnnouncer(s => System.Diagnostics.Debug.WriteLine(s)); var migrationContext = new RunnerContext(announcer) { #if DEBUG // will create testdata Profile = "development" #endif }; var processor = factory.Create(this.connectionString, announcer, options); var runner = new MigrationRunner(assembly, migrationContext, processor); runnerAction(runner); } }
Using this class, we can run the migration by calling this code from Application_Start in global.asax. I have created a class in App_Start, following MVC conventions. Just watch out and never ever deploy a debug build to production using this code (it will delete all data)!

public class DatabaseConfig { public static void MigrateDatabase(string connectionString) { var migrator = new Migrator(connectionString); #if DEBUG migrator.Migrate(runner => runner.MigrateDown(0)); #endif migrator.Migrate(runner => runner.MigrateUp()); } }

And that’s all there is to it. FluentMigrator has been working very well for us. Whenever some new feature requires a database change, we add a new Migration class and just implement it. When the feature is done we build a new Azure deployment package and deploy it over the running production instance. Windows Azure brings down the servers one by one, copies the package and then restarts the server instance. The first server that starts with the new version will cause the database to upgrade to the new version. Theoretically, the servers running the old version could run into problems because they don’t know how to handle the new database schema. It would be possible to query the azure environment and delay the upgrade until the last server comes up, also making sure that the new code still works against the previous database scheme. Of course this would cause additional development and testing effort. In practice we never had problems using it the way we do.

This post is part of the series Development of a mobile website with apps and social features