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,
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
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
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 “
Install NuGet package of Fluent Migrator in “
DatabaseMigration” project using the following command in Package Manager Console:
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:
public class M0001_CreateMemberTable:Migration
public override void Up()
public override void Down()
Here, we created a class derived from “
Migration” class with version number 1 and implement
Down() methods. In
Down() methods, we can run any SQL command but Fluent Migrator provides another way of defining schema by using Fluent API commands like
Up() method, we are creating a “
Member” table with some columns and in
Down() method, we are deleting “
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;
/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:
Version table. You will find that there is one record with migration number as
AppliedOn and migration name in
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:
public class M0002_CreateSP_GetAllMember : Migration
public override void Up()
public override void Down()
In the above code, we are simply executing our SQL Scripts using
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 ..\
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.
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.