Fluent Migrator for Database Migration

• Download demo (ZIP) - 133.1 KB
• Download demo (RAR) - 107.3 KB

Introduction

While developing an application, we manage database manually, i.e., we make SQL scripts (for creating and updating tables, SPs, functions, etc.) and then execute them and we also need to manage them in a certain order so that it can execute on upper environment seamlessly. So managing these database changes with regular development and deployment is a tough task.

Now the good news is that Fluent Migrator is here to solve all the above problems.

What is Fluent Migrator

Fluent Migrator is a database migration framework for .NET. It uses fluent interface to manipulate database. To use Fluent Migrator, we write schema change in classes which has two methods, Up() and Down(). As the name suggests, Up() method is used to upgrade and Down() method to downgrade the DB. These classes can be committed to a version control system.

How Fluent Migrator Works

Migration classes are simple C# classes that are inherited from “Migration” base class. We need to put a unique identifier in Migration attribute in each class which acts as version number of migration. This identifier can be incremental integer or we can use a number in format of YYYYMMDDHHMM so that when multiple developers are creating migration, then it will not clash.

Then, we implement the Up() and Down() methods. For example, in Up() method, we can create a new table and in Down(), we remove that table. All the migration classes are kept in a single assembly.

Fluent Migrator provides a Migration Runner tool (Migrate.exe) which executes the Up() or Down() methods of migration classes in the correct order. We can integrate this tool in any CI (Continuous Integration) tools like Jenkins, Team-City or TFS to automate the migration process.

Fluent Migrator also maintains a “Version” table in database to keep track of which migration version has executed.

Implement Fluent Migrator Step by Step

Setup the Project

Implement Fluent Migrator is an easy task. Firstly, open your existing application in Visual Studio and add a new “Class Library” type project in your Solution. You can name it “DatabaseMigration”.

Install NuGet package of Fluent Migrator in “DatabaseMigration” project using the following command in Package Manager Console:

Install-Package FluentMigrator

This will install the latest package and add reference of Fluent Migrator in your project.

For more information of using NuGet package, you can visit the following link: http://www.codeproject.com/Tips/990440/How-to-Use-NuGet-Packages

Upgrade Database using Migration Classes

Now create a new folder in “DatabaseMigration” project and name it like “Migrations” to keep all Migrations classes.

Next, create a new class inside this folder and name it “M0001_CreateMemberTable.cs” and paste the following code:

using FluentMigrator;
namespace DatabaseMigration.Migrations
{
    [Migration(1)]
    public class M0001_CreateMemberTable:Migration
    {
        public override void Up()
        {
            Create.Table("Member")
                .WithColumn("MemberId").AsInt32().PrimaryKey().Identity()
                .WithColumn("Name").AsString(50)
                .WithColumn("Address").AsString()
                .WithColumn("MobileNo").AsString(10);
        }

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

Here, we created a class derived from “Migration” class with version number 1 and implement Up() and Down() methods. In Up() and Down() methods, we can run any SQL command but Fluent Migrator provides another way of defining schema by using Fluent API commands like Create, Delete, Rename, Insert, Update, Execute, etc.

In Up() method, we are creating a “Member” table with some columns and in Down() method, we are deleting “Member” table.  

Now compile your project and then we are ready to execute our migration. To execute migration, we have “Migrate.exe” which can be found at the path of package folder “packages\FluentMigrator.1.6.0\tools”.

Run the following command from the command prompt to execute migration:

Migrate.exe /connection "data source=localhost;initial catalog=MyTemp;
User ID=sa;Password=******;" 
/db SQLserver2008 /timeout 600 /target ..\DatabaseMigration\bin\Debug\DatabaseMigration.dll

Here, we are passing connection string of our database, kind of database server, i.e., SQLserver2008, connection timeout and the path of assembly where all migration classes are kept.

For the sake of simplicity, I created a batch file named “MigrateDatabase.bat” under “Utils” folder and put the above command in this file. When you execute this batch file, it will show the following output:

The above output is showing that Version table is created as we are executing migration first time and then our “M0001_CreateMemberTable” migration executed successfully. You can check the database and you will find that both tables are created like below:

Check the Version table. You will find that there is one record with migration number as Version, date-time as AppliedOn and migration name in Description column.

Upgrade Database using SQL Scripts

Now let’s take another very important scenario, where we want to execute SQL Script using Migration class. So for this, create a separate folder in your project, name it like “Scripts” and put your SQL Script there.

For example: I have put two SQL Scripts, one for creating a Stored Procedure and another to Drop that Stored Procedure.

NOTE: Don’t forget to set the Build Action property of both files as Embedded Resource.

Then, create another migration class, name it “M0002_CreateSP_GetAllMember.cs” and paste the following code in that class file:

using FluentMigrator;
namespace DatabaseMigration.Migrations
{
    [Migration(2)]
    public class M0002_CreateSP_GetAllMember : Migration
    {
        public override void Up()
        {
            Execute.EmbeddedScript("CreateSP_GetAllMember.sql");
        }

        public override void Down()
        {
            Execute.EmbeddedScript("DropSP_GetAllMember.sql");
        }
    }
}

In the above code, we are simply executing our SQL Scripts using Execute.EmbeddedScript function.

Now run our batch file “MigrateDatabase.bat” again and you will find that the Stored Procedure has created and the Version table has two records now.

Downgrade the Database 

To rollback your database at a particular version is extremely easy. You need to execute the following command with the version number.

Migrate.exe /connection "data source=localhost;initial catalog=MyTemp;User ID=sa;Password=******;" 
/db SQLserver2008 /timeout 600 /task rollback --steps=1 /target ..\
DatabaseMigration\bin\Debug\DatabaseMigration.dll

Here, we use a switch /task rollback with option --steps and provide the version number =1. So, it will rollback our database to version 1 by executing the Down() method of all the migration script which version is greater than 1. In our case, it will execute the Down() method of “M0002_CreateSP_GetAllMember.cs”.

Again, I have created a batch file named “MigrateDatabase-RollbackToVersion-1.bat” under Utils folder for executing the above command. This will show the below output:  

Output is showing “M0002_CreateSP_GetAllMember” migration reverted successfully.

Now check the Version table, you will find that the second record with version number 2 has been removed.

Summary

So in this way, you can easily Upgrade and Downgrade the database using Fluent Migrator. You can also automate the database migration process easily by integrating it with any CI (Continuous integration) tools. I am also providing the source code of this application for easy reference but to run it, you first need to install NuGet Package of Fluent Migrator. Click here to download the source code.

This article does not include all the features and commands provided by Fluent Migrator. For more information, please visit https://github.com/schambers/fluentmigrator.