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

Visual Studio Turbo – DIY AppHarbor with Nant.Builder

In the final part of this series I look at automating uploading your app into the Windows Azure Cloud, or as I like to think of it a Do It Yourself AppHarbor, hopefully with no leftover screws ;-).  The series so for:

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

Update 08/08/12 – Updated Nant.Builder and links to reflect changes for Azure 1.7 and Azure Powershell Commandlets

Prerequisites

1.  You’ll hopefully not be surprised to learn you’re going to need a Windows Azure account (there’s a rather stingy 90 day free trial, if you haven’t signed up already).  Within your account you’re going to need to set up one Hosted Service where we’ll deploy the app to, and one Storage Account where the package gets uploaded to prior to deployment.  If you’re stuggling just Google for help on configuration and setting up Windows Azure, there’s plenty of good guides out there.

2. You’ll also need to install the .net Windows Azure SDK v1.7.  Again I’ll assume you know how to add and configure an Azure project to your solution.

3.  Finally, you need to download the Windows Azure Powershell Cmdlets.  This will be installed automatically using Web Platform Installer.  Follow the Getting Started instructions here to ensure it was successfully installed.  You can get a list of available commands, here.

Getting Started – Importing your Azure Credentials

• You’re going to need to download your Azure credentials, so Nant.Builder can contact Azure on your behalf.  We can do this by clicking here:
  • https://windows.azure.com/download/publishprofile.aspx?wa=wsignin1.0
• You should now have file called <your-sub>-<date>-credentials.publishsettings
  • Unhelpfully you can’t seem to rename the file on the portal to make it more meaningful
• If you open the file you’ll see it’s an XML file containing your subscription details.
  • IMPORTANT– if you have multiple azure subscriptions you’ll need to edit the file so that it only includes the one subscription that you want to deploy your app into.
• With the file downloaded open powershell and run the following commands, note you’ll need to change the path and filename to your .publishsettings file:

Import-AzurePublishSettingsFile –PublishSettingsFile 'c:\users\<username>\downloads\your-credentials.publishsettings' –SubscriptionDataFile 'c:\dev\tools\windowsazure\subscriptions\your-sub.xml'

view raw

ImportAzureSettings.ps1

hosted with ❤ by GitHub

• If the above command run successfully you should have an xml containing your subscriptionId and thumbprint in c:\dev\tools\windowsazure\subscriptions
• ** REALLY IMPORTANT** – The subscription xml file is basically the keys to your Azure account, so you DO NOT want to be casually emailing it around, take it to the pub etc.  Ensure you save it behind a firewall etc etc.
• OK that’s us got our Azure credentials organised, next we can configure Nant.Builder

Configure Nant.Builder for Azure Deployment

Packaging your solution for Azure

• Install and configure Nant.Builder as described in Part 2 of this series.
• Open the Nant.build file and navigate to the Azure Settings section.
• Set the create.azure.package parameter to true, this will call CSPack to package your solution in a format suitable for deployment to Windows Azure.  If you’re interested in what’s happening here I’ve talked about CSPack in depth here and here
• Set the azure.project.name parameter to the name of the Azure project in your solution.
• Set the azure.role.project.name parameter to the name of the project which contains the entrypoint to your app.  This will most likely be the Web project containing your MVC views etc.
• Finally set the azure.service.config.file parameter to the name of the *.cscfg file containing the Azure config you want to deploy.  The default is *.cloud.cscfg but may be different if you have a test config, live config etc.
• You can run Nant.Builder now and your solution should be packaged and output in C:\dev\releases\<your-solution-name>

Deploying your solution to Azure

• If packaging has succeeded, you can now finally automate deployment to Azure.  Navigate to the Azure deployment section within Nant.Build
• Set the deploy.azure.package parameter to true
• Set the azure.subscription.credentials.file parameter to the name of the the file you created in the Import your Azure Credentials section above, ie C:\dev\tools\WindowsAzure\Subscriptions\yourSubscription.xml
• Set the azure.hosted.service.name parameter to the name of the hosted service you want to deploy your app into.  IMPORTANT – be aware that this is the name listed as the DNS Prefix not the actual service name

• Set the azure.deployment.environment parameter to the environment type you wish to deploy your app into.  Valid values are either staging or production
• Finally set the azure.storage.account.name parameter to the name of the storage account you set up earlier, this is where the app will be uploaded to temporarily when it’s being deployed.
• That’s it we should now be ready to test our DIY App Harbor.  Your Azure Config section should look similar to this, obviously with your app details replaced:

 <!--Azure Settings-->

<!-- Packaging -->
 
 <!--The name of the project containing the Azure csdef, cscfg files-->
 
 <!-- This is the name of the project containing your app entry point, probably the Web project, but may be a library if using a worker role-->
 
 <!-- The name of the file containing the azure config for your app, default is .Cloud but may be custom if you have multiple configs, eg test, live etc -->


<!-- Deployment -->
 
 <!-- The name of the file containing your exported subcription details - IMPORTANT keep this file safe as it contains very sensitive credentials about your Azure sub -->
 
 <!-- The name of a azure hosted service where you want to deploy your app-->
 
 <!-- The environment type either Staging or Production-->
 
 <!-- The name of a storage account that exists on your subscription, this will be used to temporarily load your app into while it's being deploed-->
 

One Click Deployment

So we have hopefully achieved the dream of all modern developers being able to deploy our app into the cloud with one click.  If it’s successful you should see something similar to

DeployAzurePackage:

     [exec] 27/05/2012 22:54 - Azure Cloud App deploy script started.
     [exec] 27/05/2012 22:54 - Preparing deployment of ContinuousDeploy to your service
     [exec] or inception with Subscription ID your subid
     [exec] 27/05/2012 22:54 - Creating New Deployment: In progress
     [exec] 27/05/2012 22:56 - Creating New Deployment: Succeeded, Deployment ID
     [exec] 27/05/2012 22:56 - Starting Instances: In progress
     [exec] 27/05/2012 22:56 - Starting Instance 'Nant.Builder.Sample.Web_IN_0': Stopped
     [exec] 27/05/2012 22:57 - Starting Instance 'Nant.Builder.Sample.Web_IN_0': Initializing
     [exec] 27/05/2012 23:00 - Starting Instance 'Nant.Builder.Sample.Web_IN_0': Busy
     [exec] 27/05/2012 23:01 - Starting Instance 'Nant.Builder.Sample.Web_IN_0': Ready
     [exec] 27/05/2012 23:01 - Starting Instances: Succeeded
     [exec] 27/05/2012 23:01 - Created Cloud App with URL http://xxx
     [exec] 27/05/2012 23:01 - Azure Cloud App deploy script finished.

BUILD SUCCEEDED

Note – You are better to run Nant from the command line to see the above output, as the powershell script that deploys your build echos progress to the command line, but not to Visual Studio, if you are running Nant as an external tool

Nant.Builder.Sample

I’ve created a sample project on GitHub that shows Nunit.Builder integrated into it, so it should be more obvious how it all wires up.  Download Nant.Builder.Sample here

Conclusions

I hope you’ve found the series useful, and that you benefit from turbo-charging your workflow.  Over the next month I’m going to refactor Nant.Builder to be a bit more modular, so it will be easy for other to extend the platform with different targets.  Stay tuned for further exiting announcements 🙂

Run Nunit 2.6 from within Visual Studio 2010

If you don’t have Resharper it’s often handy to be able to run NUnit from within VisualStudio.  This is easily achieved from the Tools | External Tools menu.  Just point the Command at where you have installed the Nunit executable, and complete the rest of the parameters as follows:

After completing this you should find that you can launch Nunit from the Tools menu using your newly created NUnit 2.6 option 🙂

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

Visual Studio 2010 Turbo – Workflow

Recently I’ve been attempting to streamline my workflow, to help me Get Things Done 🙂  So I thought I’d share some of that work, in a mini-series of blog posts:

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

Tool Up

I won’t spend much time expounding on the tools that I use, as excellent guides are but a Google search away, but I’d recommend installing and using the following:

• Powershell – Yeah it’s a bit clunkier than bash, but it’s extremely powerful and lets you easily automate your day to day life
• Console2 – A nice way of working on the CommandLine and Powershell- read Hanselman’s excellent guide
• Git – As a looooong time SVN user and aficionado I was reluctant to move to Git, but now I have I have to confess I’m enjoying the experience
• PoshGit – Work with Git in Powershell, read Haack’s excelent guide to getting it set up here and here.
• Nant – My build tool of choice, I’ll talk about it a bit more in follow up posts.
• Nuget – Jump start your projects with this excellent package manager, and then upload some packages of your own 🙂

Configure your workspace

I’d encourage all dev teams to configure their workspace in the same way.  That way you can jump onto a colleague’s machine and you’ll know where to go to find the code if pairing etc.  Also it makes it easy to share buildscripts around the team.  I set up my workspace as follows:

C:\dev 
    \builds 
    \releases 
    \tools 
    \work

So all work is done in the C:\dev dir.  We then create 4 subdirs:

• builds – used solely by your build tool to copy files into for compilation, running unit tests etc
• releases – used by your build tool to copy your solution in a format that can be easily released, ideally any subfolders here would be dated or versioned
• tools –  contains any tools you use to help with developing code, ie Nant, NUnit etc
• work – the main event, contains all your various solutions

So for example we might have:

C:\dev 
	\builds 
		\DemoApp1 
	\releases 
		\DemoApp1-Build-120508-0915 
		\DemoApp1-Build-120508-1115 
		\DemoApp2-Build-120507-1506 
	\tools 
		\Nant-0.91 
		\Nunit-2.6 
	\work 
		\DemoApp1 
		\DemoApp2

Creating a new empty solution

A number of years ago I read the excellent Code Leader, the author advised using a tool called TreeSurgeon to set up a new solution.  This tool is slightly long in the tooth now and could do with being updated, but you can follow the guide below to quickly define a new solution in the same style:

• Create a new blank solution in c:\dev\work and name it after your project  – eg SampleSolution

• Your solution will open in Visual Studio and will contain only the solution file.  Now you can add projects to your solution you might want to add a Web project a Services project and a Tests project.
• So right-click on the solution file and select Add | New Project
• As per .Net convention you should call the various projects <solutionname>.<projecttype>.  So we’ll add SampleSolution.Web, SampleSolution.Tests, SampleSolution.Services.
• When you add the project ensure you set the location of the project to the /src folder within your solution

• Right-click on the solution file again and select Enable Nuget Package Restore.   Click Yes when you get the pop-up about wanting Nuget to manage packages restores for you.  A couple of nuget files will be added to the root of your solution.

We should now have a nice neat solution, with all projects saved in the src dir, eg:

Next Steps

Now to test if Nuget is working lets add a package to see if all is working as expected, lets add the awesome Twitter Bootstrap.  So if we run the command in the Package Manager console

install-package TwitterBootstrap

Twitter Bootstrap will be installed successfully, and if we now look at our Solution dir, you’ll see we now have a new packages dir, containing Bootstrap and its dependencies

Now that we’re happy you can add your solution to the source code management tool of your choice, I’d recommend checking out Git.  BTW with nuget installed, you do NOT add the Packages directory to Source Control.

Next time

I’ll look at using Nant, to quickly build our solution.