Configuring Migrator.NET as an External Tool in Visual Studio using MSBuild

Introduction

Migrations allow programmers to incrementally create a database schema using their preferred programming language. Each migration is a logical evolution of the database schema and is ideally executed in a transaction, so the migration either succeeds as a whole, or fails all together, leaving the database in the previous state. Migrations have a versioning system and can be executed upwards, and downwards. A flexible migration framework allows not just DDL statements, but regular SQL statements as well (useful for inserting data into lookup tables for instance). Migrator.NET is one such framework for .NET.

Once you start using Migrator.NET you will wonder how you ever lived without it (I know I did) and you will quickly want to incorporate it into all your green-field projects. But it’s a relatively young project and there’s not a whole lot of documentation for it. Well, I’m here to help you with that. In this post I will explain how to set up your Visual Studio environment so that running your migrations just takes a couple of clicks and involves no context switching, making your workflow a bit more streamlined.

Downloading Migrator.NET, what’s in the package?

When you download the current 0.8 release you get a bunch of files that you may not know what to do with. You probably don’t need all of them, in fact, you might only need a couple depending on your workflow and environment. For the sake of this post we will use only the necessary files and disregard the rest. These files are:

Migrator.dll
Contains the logic for running a migration, migration runners reference this assembly.
Migrator.Console.exe
A migration runner, this one allows you to run migrations from the command line. After we are finished setting up our Visual Studio integration we will not need this anymore.
Migrator.Framework.dll
Referenced in the project that contains your migrations, has the base classes that you inherit from and the attributes that you decorate your migrations with.
Migrator.MSBuild.dll
Another migration runner, this one contains MSBuild tasks that allow you to run migrations using MSBuild.
Migrator.Providers.dll
Contains database specific providers which turn migrations into SQL statements, taking into account the different SQL dialects.
Migrator.Targets
Configuration file to make MSBuild aware of the tasks contained in the Migrator.MSBuild.dll assembly.
System.Data.SQLite.dll
Official ADO.NET implementation for SQLite, a self-contained, serverless database engine.

Setting up your solution

Everyone has their own preferred way of structuring a solution, and so do I. I find my structure to be flexible and it has always worked well for me. As always, ymmv and your feedback is welcome. Having said that, here it is:

My own code and documentation goes into source and docs respectively. I separate third party libraries and executables into tools and references, where tools contains executables and supporting files, and references contains .NET assemblies that your projects have an actual reference to. Migrator.NET is both a library (used to write migrations) and an executable (used to run migrations). For this example it will nicely illustrate what the different components of Migrator.NET are for.

Our database schema

Of course, we set out to create a database schema using migrations. We could create a completely unoriginal database schema to store information about products and categories… But we will do no such schema, instead we will create a schema to store information about planets and planetary systems. I will not have unimaginative examples on this blog (Northwind… just say no). So let’s create our first migration.

A migration always inherits from the abstract class Migration, which is in the Migrator.Framework.dll assembly, so we’ve added that assembly to the reference folder in our solution and then we referenced it from our project. Now we must override the abstract Up() and Down() methods. Below is the first migration. I have omitted the second migration , you can find it in the download below.

[Migration(1)]
public class _001_Planets : Migration
{
    public override void Up()
    {
        Database.AddTable("Planets",
            new Column("Id", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity),
            new Column("Name", DbType.String, 100, ColumnProperty.NotNull),
            new Column("Diameter", DbType.Double),
            new Column("Mass", DbType.Double),
            new Column("SupportsLife", DbType.Boolean, false)
        );
    }

    public override void Down()
    {
        Database.RemoveTable("Planets");
    }
}

As you may have noticed, each migration is decorated with a [Migration] attribute. The attribute defines the version of the migration defined by the class (which needs to be public by the way, otherwise it can’t be found by the migration runner). It is therefore not necessary to use a number in the file or class names, but it is still a good practice to do that. That way you will never have a problem coming up with unique names for your migrations and they are listed in the right order in the solution explorer.

Let’s migrate!

Our class library project builds into an assembly that now contains our migrations each marked and versioned by the Migration attribute. Let’s go the simple route of running from the command line first. Since we are going to use the console runner we will put it and all of its dependencies in the tools folder of our solution:

I use the Open Command Prompt Shell Extension by Kai Liu to open command prompts directly from an Explorer window. Even though we will soon integrate Migrator into Visual Studio, it is still a great tool, so go download it!

That’s it, that’s all you need to run your migrations. Here is the command to run the migrations:

Migrator.Console.exe SQLite "Data Source=..\..\source\test.db;Version=3" "..\..\source\bin\Debug\Demo.dll"

The output should look something like this:

CREATE TABLE SchemaInfo (Version INTEGER PRIMARY KEY)
Latest version applied : No migrations applied yet!. Target version : 2
Applying 1: 001_ planets
CREATE TABLE Planets (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Diameter NUMERIC, Mass NUMERIC, SupportsLife INTEGER DEFAULT False)
INSERT INTO SchemaInfo (version) VALUES ('1')
Applying 2: 002_ systems
CREATE TABLE Systems (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name INTEGER NOT NULL, Distance INTEGER)
INSERT INTO SchemaInfo (version) VALUES ('2')
Migrated to version 2

We are using the SQLite ADO.NET provider, it will create a new database file in the location specified by the connection string. If you want to see the schema and contents of the database you can install the SQLite ADO.NET 2.0 Provider which has an installer for SQLite Design-Time Support for Visual Studio 2008.

Visual Studio 2008 Server Explorer

So now we are getting somewhere, we have our migrations and we can run them on our database. But this is not exactly a friction-free workflow. You have to context switch out of your IDE to the command line. Also notice that our project is configured to output an assembly named Demo.dll. We have to take into account the path from the tools folder to the output folder of the project. It could be Debug or Release, or anything else depending on the solution configuration. So this is a problem with running the migrations from the command line, as well as having to type that long command. Sure it’s no big deal, but we are programmers, we can do better right?

Migrator.NET and MSBuild

Luckily for us, the Migrator.NET developers have created an MSBuild task for running migrations. And since Visual Studio project files are MSBuild files, we can integrate migrations as a target into our project file and run migrations using MSBuild. For this, we need to edit the project file. Right click the project in the Solution Explorer window and choose Unload Project. Now right-click the project again and choose Edit Migrator and MSBuild Demo.csproj.

I will assume that you are somewhat familiar with MSBuild files. If not, I recommend reading this MSBuild overview. First of all, we need to make MSBuild aware of the Migrate task. This is a class located in the Migrator.MSBuild.dll assembly. The way to do this is to put that assembly and all its dependencies in a standard location for MSBuild. This location is %ProgramFiles%\MSBuild\MigratorTasks. Here’s the list of files:

Now we have to add another file to our tool belt (we are starting to look like Batman here). It’s the Migrator.Targets file, so let’s copy this to the tools folder in our solution. This file, when referenced from an MSBuild file, sets up a few properties in the build environment that will make MSBuild aware of the task in the assembly we just copied to %ProgramFiles%\MSBuild\MigratorTasks. The Migrator.Targets file contains the following XML:

<?xml version="1.0" encoding="utf-8" ?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <PropertyGroup>
        <MigratorTasksPath Condition="'$(MigratorTasksPath)' == ''">$(MSBuildExtensionsPath)\MigratorTasks</MigratorTasksPath>
        <MigratorTasksLib>$(MigratorTasksPath)\Migrator.MSBuild.dll</MigratorTasksLib>
    </PropertyGroup>
    <UsingTask AssemblyFile="$(MigratorTasksLib)" TaskName="Migrator.MSBuild.Migrate" />
</Project>

The Migrator.Targets file first sets up the MigratorTasksLib property with a value that points to the assembly with the Migrate task. Then it uses that property in a UsingTask element to make the task available to MSBuild. Phew, that’s pretty complicated, but MSBuild configuration usually is.

Now, all that is left is to reference the Migrator.Targets file in our tools folder from our project file. The following line in the project file takes care of this:

<Import Project="$(MSBuildProjectDirectory)\..\tools\Migrator.NET\Migrator.Targets"/>

Wait, what?

Let’s recap what we just did… We copied the MSBuild migration runner assembly and all of its dependencies to our computer. We have a .Targets file that points to that assembly inside a UsingTask element. We import this .Targets file in our project file. Do you know where this is going? We can skip this part if we just put the MSBuild runner in our tools folder and directly reference it in a UsingTask element. No need for the .Targets file and no need to copy files into %ProgramFiles%\MSBuild\MigratorTasks. Just put the following line into the project file:

<UsingTask
    AssemblyFile="$(MSBuildProjectDirectory)\..\tools\Migrator.NET\Migrator.MSBuild.dll"
    TaskName="Migrator.MSBuild.Migrate"/>

And make sure that the Migrator.MSBuild.dll assembly and it’s dependencies are located in the tools folder. This also makes our solution completely portable, since all the required files for building the project and running the migrations are included.

Now we can define a new target in our MSBuild file, one that uses the Migrate task to run the migrations. That target looks like this:

<Target Name="Migrate" DependsOnTargets="Build">
    <Migrate
        Provider="SQLite"
        Connectionstring="Data Source=$(MSBuildProjectDirectory)\test.db"
        Migrations="$(OutputPath)$(AssemblyName).dll"
        To="$(To)" />
</Target>

The MSBuild migration runner needs the same parameters as the command line runner and they are provided by attributes on the Migrate element. In our example we will run the migrations on a SQLite database. But how do we execute this target? The answer is…

Visual Studio External Tools

In Visual Studio we can add commands to the Tools menu using a feature called External Tools. Go to Tools > External Tools… and add a new command with the following settings:

Title
Migrator
Command
C:\WINDOWS\Microsoft.NET\Framework\v3.5\MSBuild.exe
Arguments
$(ProjectFileName) /target:Migrate /p:To=-1
Initial directory
$(ProjectDir)

Check Use Output window and Prompt for arguments. What this command does is run MSBuild using the project file as its configuration. We call the Migrate target and supply the To argument to control which migrations will be executed (-1 means all migrations). Because we prompt for arguments we can easily change this argument. Once you get the hang of MSBuild you can configure different argument. I like to use an argument to switch the connection string so I can run the migrations on a different database using the same command (a database on a staging server for instance).

Conclusion

In this post we looked at how to configure MSBuild and Visual Studio to make it easy to run migrations written using Migrator.NET from within Visual Studio. We started by using the command line migration runner and then switched to the MSBuild runner. We ended up not using the Migrator.Targets file, instead we put all the required files in our solution and used a relative path to use the task in MSBuild.

If you came this far, thanks for reading, I hope this helps and I look forward to your feedback!

Downloads

Comments