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:

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).

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.

Advertisements

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

Testing with Selenium Webdriver, Visual Studio and NUnit

Update 20/06/12 – Updated NareshScaler to work with the new IEDriverServer that ships with the latest version of Selenium Webdriver

We have a multi-tenant solution at work.  As we added tenants we were happy to discover that our app scaled nicely, but we were sad to discover that Naresh, our lone QA superstar, did not.  We discovered (unsurprisingly) it wasn’t possible to stick to a fixed release schedule and do regression/integration testing manually.  So some additional automation was required.

Integration Testing Automation Requirements

Our requirements were

  1. Naresh could create integration tests with minimum oversight from the team
  2. Tests needed to be created quickly without a lot of additional coding
  3. Ideally tools would run on Visual Studio

Ruling out Specflow

I’d been keen to investigate Specflow and the BDD style of integration testing.  However, it quickly became apparent that this would require a significant effort in time to successfully wire up the tests, and would require a reasonable amount of dev to ensure each test passed.  Thus failing requirements 1 and 2.

Ruling in Selenium Webdriver

We already had a bit of experience with Selenium and we found this excellent blog post from Stephen Walther.  Reading this we realised that  Selenium Webdriver met our requirements perfectly.  We could install Selenium Webdriver into Firefox and export the scripts as C# Webdriver classes.  We could then add the classes into a simple test-runner and we’d be able to scale Naresh 🙂

Some Selenium Pitfalls

However, it’s not all good news.  One thing to point out is that there can be a certain amount of flakiness when the various selenium drivers are trying to locate elements on your page.  Ie a test will fail once, then pass again later.  This is obviously far from ideal, but overall I think the benefits out-weigh the drawbacks.   Test that fail consistently can be investigated.

One way to minimise these failures would be to run the tests in only one browser, the Firefox driver seems the most reliable.  If you’re not doing loads of Javascript this is probably safe enough.

Introducing NareshScaler

I wrote NareshScaler to allow Naresh to quickly add each Selenium macro into the test-runner.  I’d also been wanting to try out creating a nuget package for a while, so this seemed like a perfect chance to give it a go.

You can install NareshScaler into your Integration Test project using the nuget package manager.  Once successfully installed, you should add your Selenium Webdriver class file(s).

Then simply mark each class as inheriting from NareshScalerTest.

You will now have to override the RunSeleniumTests method.  You simply need to wire up the driver and list any test methods in your selenium Webdriver class, ie:

[TestFixture]
public class NugetOrgTest : NareshScalerTest
{
private IWebDriver driver;
private string baseURL;

[SetUp]
public void SetupTest()
{
baseURL = "http://nuget.org/";
}

public void Test_That_NareshScaler_Exists_On_NugetOrg()
{
driver.Navigate().GoToUrl(baseURL + "/");
driver.FindElement(By.Id("searchBoxInput")).Clear();
driver.FindElement(By.Id("searchBoxInput")).SendKeys("NareshScaler");
driver.FindElement(By.Id("searchBoxSubmit")).Click();
driver.FindElement(By.LinkText("Naresh Scaler")).Click();
}

public override void RunSeleniumTests(IWebDriver webDriver)
{
driver = webDriver;
Test_That_NareshScaler_Exists_On_NugetOrg();
}
}

We use NUnit here, so once everything is wired up, you should be able to point NUnit at your Integration Tests dll and see all your tests running in IE, Firefox and Chrome  Which I have to admit is pretty cool when you see everything running automatically.

Additionally NareshScaler includes an Nant build file, to allow you to wire up your integration tests into CruiseControl etc.  I’ve added a sample, so you can hopefully see how it works.  Hope you find it useful.