User Id :    Password :      New Member   Forgot Password  
 
Fluent Migrator for Database Migration
Description Fluent Migrator makes the database migration very easy. It is a database migration framework for .NET and uses fluent interface to manipulate database.   No. of Views     2160
  Rating     4
Author Sumit Gupta   Posted On     30 Jul 2015
Tags C#,SQL SERVER,.NET Frameworks,C# : How To,SQL SERVER : How To,Visual Studio : How To,General,Visual Studio 2012,Visual Studio 2013,SQL SERVER 2008,SQL SERVER 2010,SQL SERVER 2012    

Sample Code   Download Code

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 certain order so that it can execute on upper environment seamlessly. So managing these database changes with regular development and deployment is 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 suggest Up() method is used to upgrade and Down() method 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 the 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 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 execute the Up() or Down() methods of migration classes in the correct order. We can integrate this tool in our CI tools like Jenkins, Team-City or TFS to automate the migration process.

 

Fluent Migrator also maintains a “Version” table in database to keep track 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 like “DatabaseMigration”.

 

Install NuGet package of Fluent Migrator in “DatabaseMigration” project using 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 following link:

 

http://www.dotnetlogix.com/Article/dotnet/285/How-to-use-NuGet-Packages.html

 

 

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 provide 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. For 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, the 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 that file. When you execute this batch file it will show following output:

 

 

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 you provided 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 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 Stored Procedure has created and Version table has two records now.

 

 

Downgrade the database

 

To rollback your database at particular version is extremely easy. All you need to execute 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 above command. This will show below output:

 

 

Output is showing “M0002_CreateSP_GetAllMember” migration reverted successfully.

 

Now check the Version table, you will find that version 2 record has 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.

 

About Author

About Author I am Sumit Gupta working in 3 Pillar Global Pvt. Ltd as Module Lead. I have 7+ year of experience in .Net technologies. I love to explore new technologies and write technical article. Sumit Gupta
No Photo
 
Country India
Company 3 Pillar Global Pvt. Ltd.
Home Page http://www.facebook.com/sumitgupta1225

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

 
 
Posted By Gaurav on 02 Sep 2015 at 12:05 PM
 
Really helpful great information.
 
 
   
Write your comment here.
Comment
Verification Code