Easy database migrations with C# and FluentMigrator

Database migrations are an increasingly common pattern for managing and automating the creation of your projects database schemas. Typically each migration has 3 elements:

• A Unique Id – each new migration is given a numeric identifier higher than the previous one.
• An UP component – describing the table / row / column / key – you want to create
• A DOWN component – which exactly reverses the change you’re are making in the UP component.

Thus by running each of the migrations in order you can go from an empty database, to the schema required by your project, or if an error occurs you can simply roll back any number of migrations to get to a known stable state.

But why?

The advantages of this approach may not be immediately obvious, but I’d say the main advantages are:

• No seperate SQL scripts, db exports etc.  All database migrations are contained within the project and can be reviewed and managed by the team – No DBA Required.
• Easy deployment onto as many servers as required – most medium to large projects will have a number of environments, minimally dev, test and live.  By creating DB migrations it’s simple to keep each of these environments in synch.
• As the project grows, database migrations can be created as required, meaning you can easily rollout new changes to live projects.
• Easy rollback – if you rollout a patch containing a migration, you can instantly roll it back without discovering you don’t have rollback scripts.
• Roll outs to the live db are usually possible with no downtime

Introducing FluentMigrator

FluentMigrator is a package that allows you to create “fluent” migrations within Visual Studio.  The easiest thing to do is just to demo a migration:

	using FluentMigrator;
	namespace Nant.Builder.Sample.Migrations
	{
	[Migration(3)]
	public class Mig003_CreateUserRoleTable : Migration
	{
	private const string TableName = "UserRole";
	public override void Up()
	{
	Create.Table(TableName)
	.WithColumn("UserId").AsInt32().NotNullable()
	.WithColumn("RoleId").AsInt16().NotNullable();
	var compKey = new[] { "UserId", "RoleId" };
	Create.PrimaryKey("PK_UserRole").OnTable("UserRole").Columns(compKey);
	Create.ForeignKey("FK_UserRole_User").FromTable("UserRole").ForeignColumn("UserId").ToTable("User").PrimaryColumn("UserId");
	Create.ForeignKey("FK_UserRole_Role").FromTable("UserRole").ForeignColumn("RoleId").ToTable("Role").PrimaryColumn("RoleId");
	}
	public override void Down()
	{
	Delete.ForeignKey("FK_UserRole_User").OnTable(TableName);
	Delete.ForeignKey("FK_UserRole_Role").OnTable(TableName);
	Delete.Table(TableName);
	}
	}
	}

view raw

SampleMigration.cs

hosted with ❤ by GitHub

You can see that the migration is decorated with a number in this case 3.  You can see the UP migration creates the UserRoles table and a number of Foreign Keys.  You should also see that the DOWN migration reverses this change, deleting the keys then deleting the table itself.

Thus if a problem occurs during the creation of this table, FluentMigrator can rollback the migration, as described in your DOWN migration.

Running the migrations

Migrations on their own are of little value if you can’t run them against a target database.  FluentMigrator integrates with both MSBuild and Nant, meaning you can run migrations as part of your build process. What’s nice about this is that you can only run in DB changes if all your unit tests etc pass, and if you have a more sophisticated build script you can control which db migrations are rolled out onto different environments.

Readers of my blog will be familiar with my love of Nant and my Nuget package Nant.Builder.  So it won’t come as a great surprise to find out I’ve added a FluentMigrator target into my Nant.Builder scripts.

You can set up the appropriate values in Nant.xml, you can see a working sample in the Nant.Builder.Sample this uses sqlite (note it will only run on 64bit windows).

	<!– Database Migration Settings – expects FluentMigrator–>
	<property name="run.db.migrations" value="true" />
	<!–The name of the project containing your FluentMigrator tests–>
	<property name="migrations.project.name" value="Nant.Builder.Sample.Migrations" />
	<!–Database type, eg sqlserver2008, sqlite –>
	<property name="database.type" value="sqlite" />
	<!–The connection string you for the db you want to migrate–>
	<property name="connection.string" value="Data Source=C:\dev\tools\sqlite\nant-builder-sample.sqlite;Version=3;" />
	<!– Set flag to true if you wish to rollback a db migration, you need to specify a migration number also–>
	<property name="rollback.db.migrations" value="false" />
	<!– The migration number you wish to rollback to – WARNING be careful you don't delete data by setting this value incorrectly–>
	<property name="rollback.to.version" value="0" />

view raw

migration-config.xml

hosted with ❤ by GitHub

Best Practice

As far as best practice goes, as I demonstrate in the sample project I’d advise:

• Keep all the migration classes in a separate project
• Number the migration classes in line with their migration number ie Mig012_CreateTableX – makes it easier to manage them once you have a few migrations.

Here’s a snap of our migrations on one of our large projects, as you can see after iteration 18 it occurs to us to start creating folders per iteration, allowing to keep things a bit more ordered:

Conclusions

Fluentmigrator takes a lot of the pain out of managing your database schema over multiple environments.  If there’s a negative it’s that the project is a little bit flakey on environments other than Sqlserver, I’ve tried it on SqlserverCe and Sqlite and it’s not worked or only worked after poking at the code for a while.

On the positive side the project is being actively maintained and updated, I had a minor update accepted and committed within a few days of posting it.  So download it and get involved.

Advertisement

Automating Visual Studio 2010 builds and deployments with Nant.Builder

Part 2 in my Visual Studio 2010 Turbo series

1. Visual Studio 2010 Workflow
2. Automating Your Builds with Nant.Builder
3. DIY AppHarbor – Deploying Your Builds onto Windows Azure

In this post I look at using Nant and my Nant.Builder nuget package to quickly get your builds automated, from here it should be simple for you to integrate with a CI tool of your choice.

Update (27/07/12) – Anoop Shetty has put together an awesome post on using Nant.Builder here.  Thanks Anoop 🙂

Nant and Nant.Builder

I’ve been using Nant for years now, it’s a great tool for scripting and automating tedious build and deployment tasks.  Some might say it’s getting a bit long in the tooth, and it’s not as hip as rake etc.  However, I find it perfectly usable, with a learning curve that’s not too steep, it’s very well documented and it’s updated usually once or twice a year.

I’ve recently been doing more and more with Nuget, and I’m increasingly finding it a very powerful way of quickly setting up new projects.  One task that always takes a bit of time is setting up a build script for the new project.  Usually I’d cut and paste an existing script and hack out the bits that needed changed.  This was painful, and I wanted to get rid of this boring step, so Nant.Builder was born.

Installing and Integrating Nant

Hopefully you’ve followed part 1 of this enthralling series, so if you haven’t get Nant installed, download the latest stable build and extract it c:\dev\tools\nant-0.91.  If like me you’re trying to do more from the command line, add the bin directory into your Path environment var, ie C:\dev\tools\nant-0.91\bin

Open powershell and type nant you should see something like this, don’t worry about the Failure message for now:

NAnt 0.91 (Build 0.91.4312.0; release; 22/10/2011)
Copyright (C) 2001-2011 Gerry Shaw
http://nant.sourceforge.net

Nant can also be launched from Visual Studio.  Go to the Tools | External Tools menu option, click Add and complete as per screenshot, ensure you tick the Use Output Option.   You can now launch Nant from VS.

Install Nant.Builder

If you followed the first installment of this series you should have your new Solution in your workspace.  Now lets setup Nant.Builder:

• Add a new empty project and name it  <yoursolutionname>.Build, ensure you save it in the src directory.  We’ll use this project to hold our build scripts.

• We don’t want the compiler to build this project so click Build | Configuration Manager.  Untick build on any configurations

• Now we can install Nant.Builder from Nuget run the following command, from the package manager command line:

install-package nant.builder -projectname <yoursolutionname>.Build

• We now have Nant.Builder installed into your .Build project 🙂

Configure Nant.Builder for your solution

I’ve tried to keep configuration to the bare minimum, as the whole point is to keep things fast.

• Open the Nant.Build file.
• Set the solution.name property to the name of your solution, in our example SampleSolution
• If you’ve set up your workspace as described in the Workspace blog, you won’t need to edit solution.src.dir.  If you don’t save your projects in a source dir, and save them in the same directory as the .sln file, edit this property to blank, ie “”
• Set the solution.projects property to a comma separated list (no spaces) of all the projects contained in your solution, in our example SampleSolution.Services,SampleSolution.Tests,SampleSolution.Web
• Set the release.configuration property to the configuration you want the solution to be compiled under, default is Release
• If you’re not using CI, you can manually set the version number.  Nant.Builder will then version all your dlls with the version number you specify.  If you are using CCNet, Nant.Builder will pick up the version number from CCNet
• Set the company.name property to the name of your company, this will also be added to the Assembly.Info, so users can see who created the dll
• So in our sample we have this:

<!--The name of your solution, please overwrite the default -->
<property name="solution.name" value="SampleSolution"/>

<-- If your projects reside in a different directory from the .sln file specify here, or leave empty if not -->
<property name="solution.src.dir" value="src" />

<!-- Comma seperated list of projects contained in your solution -->
<property name="solution.projects" value="SampleSolution.Services,SampleSolution.Tests,SampleSolution.Web" />

<!-- Set the configuration for compilation, typically release, but may be custom -->
<property name="release.configuration" value="Release" />

<!-- Manually set version, if using CCNet this will be overwritten later -->
<property name="version.tag" value="1.0.0.1"/>
<property name="company.name" value="iainhunter.wordpress.com" />

If you’ve followed the first tutorial you shouldn’t need to change anything in GlobalBuildSettings.xml.  However, if you have a different workspace, buildspace, or have msbuild4 located in a non-standard location, set the values appropriately or you’ll get errors.

Running Nant

We can now run Nant from the command line by opening powershell, navigate to your Build directory, eg C:\dev\work\SampleSolution\src\SampleSolution.Build  then type Nant.  Your solution should build, or throw errors if you have warnings etc.

Alternatively in Visual Studio open the Nant.Build file, then in Tools  run your new Nant tool you created above.

Now if you navigate to your builds directory C:\dev\builds\SampleSolution you should see your build, and if you look at one of the Dlls you should see it has been versioned according to your instructions

Next steps

Nant.Builder is available on github here, so feel free to fork or send me a patch if you think it can be improved.  I’m planning to add a few enhancements like a msdeploy task etc, we’ll see how time allows.

Next time

We alter Nant.Builder to automatically deploy your solution onto Windows Azure