Fluent Migrator for Database MigrationIntroduction
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.