Follow

FluentMigrator for Database Migration

Introduction

In this blog, we’ll be looking at FluentMigrator, a database migration framework provided by .NET. FluentMigrator is a C# based framework which can be used to migrate databases, be it, creating or updating or dropping database tables OR creating stored procedures, triggers, functions OR inserting data into tables.

It can be set up in a project by installing the framework in IDE. Migration files have to be created which holds the schema for database migration.

Why should we use it?

If a team comprises of two or more people, version control tools will help in merging files and stay in sync. Unfortunately, database structure can’t be synced through these tools, and that’s the reason schema migration tools came into the picture. It allows user to represent the current state of the schema via simple scripts, instead of a bunch of SQL queries.

FluentMigrator is much like Ruby on Rails Migrations. Migrations are a structured way to alter the database schema and an alternative for SQL scripts that have to be run manually by every developer involved. Migrations solve the problem of evolving a database schema for multiple databases (for example, the developer’s local database, the test database and the production database). Database schema changes are described in classes written in C# that can be checked into a version control system.

Prerequisite

  •  .NET > 3.5
  •  Knowledge on .NET and MySQL or SQL.

Installation and Initial Setup

Step 1: Create a Class Library project for an existing solution. (lets say Database Migration)

Step 2: Install NuGet package of FluentMigrator under the project you have created in step 1. To install, run the following command in Package Manager Console.

Install-Package FluentMigrator

Database Migration

Step 3: FluentMigrator will provide a default abstract class called migrate. Create a class for migration which should be inherited from the migrate class. The class you have created must have unique version number in that migration. Every class should have a version number specified.

Version number can be given in date format or numbers. The maximum character limit for version is 20.

Example,

Step 4: In the class you have created in step 3, write whatever queries you want to execute in Up and Down methods.

Down method is used to roll back the transactions done using Up method.

For example, if you are writing queries to create a table in Up method, then write a query to delete the same table in Down method. If you write a query to delete a table in Up method, then write a query to create the same table in Down method.

Your class should look like this:

Step 5: Build the solution for the project and go to the directory where migrate.exe is located.

You can find migrate.exe under packages\FluentMigrator.1.6.0\tools

Step 6: Open command prompt and execute the commands to execute Up or Down method or write the commands in a batch file and run it.

Command will have three parameters

  1. Database connection
  2. Type of Database server
  3. Target where migration project DLL is present.

Extensions like tags, roll back etc can be added at the end of the command. Commands should be written in the following format:

For Up()

To execute queries in Up method, write the command in the below format.

After executing you will see something like this in the command prompt.

For Down()

To execute queries in Down method, write the command in the below format.

After executing you will see something like this in the command prompt.

migrate database

Ensure Down method is added in every migration class as they are needed for roll back.

 

After executing the commands, a version table (version_info) is created for every Database schema and it stores the versions and class names of the migrations. Every time migration is ran, new records are inserted in this table.

Execute.Script() method 

This method can be used if you have all the queries written in a SQL file and want to execute them through migration without writing queries in the migration class. This method has to be added inside Up() or Down() methods.

SQL file path should be passed as parameter for Execute.Script() method.

Execute.Sql() method

This method can be used to write normal SQL queries instead of using migration syntax. SQL query should be passed as parameter for this method. This method has to be added inside Up() or Down() methods.

Example migration class

Below is the example of how a migration class will look:

Extensions

context command

context command is used to run different migration queries in different contexts. Example, to run different queries on two different databases.

To achieve this, in the migration class, add ApplicationContext  and specify the context. ApplicationContext cannot be added in class attributes.

While executing the queries, add context command at the end and specify the ApplicationContext  value for which you want to run queries.

tag command

tag serves the same purpose as ApplicationContext except that tag can be specified as class attribute.

While executing the queries, add tag command at the end and specify the attribute/value.

Points to remember

  1. Duplicate version numbers – If same version number is mentioned in the attribute of multiple classes, then an error will occur.
  2. Syntax errors in the inline queries should be taken care of, as the errors are highlighted only while running the migration commands.
  3. If you are using batch file for executing the migrations, then make sure to specify correct migration commands in the batch file.
  4. Do not change the version in version_info table directly. Doing so will execute the Down method in the specific migration class automatically.

Advantages

  • No need of writing SQL queries.
  • Easy to deploy on different environments. The schema design is bundled with the code.
  • Easy to revert and redo the changes
  • Supports all the databases like SQL, MySQL, Oracle, etc. and all the versions of a database.

Limitations

  • Once the migration class is run and the version is inserted in the version_info table, that specific migration class cannot be ran again. If you want same set of queries to run again, create one more migration class with different version. For every new modification or event, a new migration class file has to be created.
  • Roll back to a specific version cannot be done. You can roll back either the last migration or last x migrations. Down method in all the x migrations will be executed.

Reference

https://www.codeproject.com/Articles/1012802/Fluent-Migrator-for-Database-Migration

Posted By: Jyothsna Nadella, Osmosee

 

Are you interested? follow us and get notified of new posts

Leave A Reply

twelve + 6 =