Bojan Veljanovski's Tech Blog

Database Development Guide for .NET teams with FluentMigrator (2014 edition)

.NET Data Access

I have been thinking a lot lately about how properly and simply to implement database versioning strategy. These years I've experienced working with different types of database setup and furthermore researched and analyzed some more approaches and tools regarding this topic. In this posting I write about my findings and why I like Fluent Migrator as a help tool in order to get the job done. But first, let's talk about the goals we try to achieve.

Goals

Links to Fluent Migrator and this guide's project.

In the end - all you just need to do is run MSBuildMigrator.Migrate.bat file and watch your database being deployed, upgraded, downgraded...it will figure out ;) .

Step by step guide

1. Open Visual Studio and create New Class Library Project

Open Visual Studio and create New Class Library Project

2. Install-Package FluentMigrator

Install-Package FluentMigrator

3. Create new folder "Migrations" to project - here we are going to store migration files

Create new folder "Migrations" to project - here we are going to store migration files

4. Now, let's create database tables with migration files 

[FluentMigrator.Migration(0)]
public class Baseline : FluentMigrator.Migration
{
    public override void Up()
    {
        Create.Table("Category")
            .WithColumn("Id").AsGuid().NotNullable().PrimaryKey()
            .WithColumn("Name").AsString(255);

        Create.Table("Product")
            .WithColumn("Id").AsGuid().NotNullable().PrimaryKey()
            .WithColumn("CategoryId").AsGuid().ForeignKey("Category", "Id")
            .WithColumn("Name").AsString(255)
            .WithColumn("Price").AsDecimal();
    }

    public override void Down()
    {
        Delete.Table("Product");
        Delete.Table("Category");
    }
}

That is all what is needed. In essence a migration is a class which drives from Migration abstract class and implements 'Up' and 'Down' methods. Additionally you will also need to define Migration Attribute with unique identifier in order the migration runner to know the order of migration files. I like it how FM API is designed, it really follows the SQL language and how I would write this script in plain SQL. Read further here.

Just for providing more examples I have added one more migration file for adding one more column to Product table for storing image URL.

[Migration(201411131100)]
public class M201411131100_Product_added_column_for_storing_image_url : Migration
{
    public override void Up()
    {
        Alter.Table("Product")
            .AddColumn("ImageUrl").AsString(255);
    }

    public override void Down()
    {
        Delete.Column("ImageUrl").FromTable("Product");
    }
}

Now this is how everything looks in my solution.

Next, let's initialize the database with our script.

5. Creating Migration Runner (MSBuild), Migrator (.BAT) and ConnectionStrings (.CONFIG)

1. MSBuildMigrationRunner.proj 

<?xml version="1.0"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Migrate">
	<PropertyGroup>
		<DatabaseProvider></DatabaseProvider>
		<ConnectionStringConfigPath></ConnectionStringConfigPath>
		<ConnectionStringName></ConnectionStringName>
		<DataMigrationProjectName>DatabaseMigrationsExample</DataMigrationProjectName>
		<DataMigrationProjectRootPath>$(MSBuildProjectDirectory)</DataMigrationProjectRootPath>
		<MigratorTasksDirectory></MigratorTasksDirectory>
				
		<DataMigrationProjectBuildDLL>$(DataMigrationProjectRootPath)\bin\Debug\$(DataMigrationProjectName).dll</DataMigrationProjectBuildDLL>
		<DataMigrationProjectCsproj>$(DataMigrationProjectRootPath)\$(DataMigrationProjectName).csproj</DataMigrationProjectCsproj>
	</PropertyGroup>
		
	<UsingTask TaskName="FluentMigrator.MSBuild.Migrate" AssemblyFile="$(MigratorTasksDirectory)FluentMigrator.MSBuild.dll"/>
			
	<Target Name="Build">
		<MSBuild Projects="$(DataMigrationProjectCsproj)" Properties="Configuration=Debug"/>
	</Target>
			
	<Target Name="Migrate" DependsOnTargets="Build">
		<Message Text="Starting FluentMigrator Migration"/>
		<Migrate Database="$(DatabaseProvider)"
					Connection="$(ConnectionStringName)"
					ConnectionStringConfigPath="$(ConnectionStringConfigPath)"
					Target="$(DataMigrationProjectBuildDLL)"
					Output="True"
					Verbose="True">
		</Migrate>
	</Target>
		
	<Target Name="MigratePreview" DependsOnTargets="Build">
		<Message Text="Previewing FluentMigrator Migration"/>
		<Migrate Database="$(DatabaseProvider)"
					Connection="$(ConnectionStringName)"
					ConnectionStringConfigPath="$(ConnectionStringConfigPath)"
					Target="$(DataMigrationProjectBuildDLL)"
					Output="True"
					Verbose="True"
					PreviewOnly="True">
		</Migrate>
	</Target>
</Project>

2. ConnectionStrings.config 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<clear />
		<add name="Default" connectionString="Server=###;User ID=###;Password=###;Database=###;"/>
	</connectionStrings>
</configuration>

3. MSBuildMigrator.Migrate.bat 

C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe MSBuildMigrationRunner.proj /t:Migrate /p:DatabaseProvider=SqlServer2012 /p:ConnectionStringConfigPath=ConnectionStrings.config /p:ConnectionStringName=Default /p:DataMigrationProjectName=DatabaseMigrationsExample /p:DataMigrationProjectRootPath=. /p:MigratorTasksDirectory=..\packages\FluentMigrator.1.3.1.0\tools\
pause

Viola, this is all you need to do. For your project you will need to put the connection string to your database and make changes where needed in the .BAT file, such as database provider and project name as an essential changes. Other config stuff should be pretty common, but if you have different structure than mine, you have full power and control with the flexibility provided here.

5. Run your MSBuildMigrator.Migrate.bat file

Table VersionInfo is used for storing migration metadata.

All of our tables are created.

In VersionInfo table you can see the "commits".

Rules of Thumb

Happy coding folks! Having questions or concerns? Shoot me a tweet -> @bojanv91