Tuesday, 28 June 2016

Getting started with fluent migrator

Introduction

Migrations are a structured way to alter a database schema and are an alternative to creating lots of sql scripts that have to be run manually by every developer involved. It acts like the version control system for an application database allowing a team to easily share the schema. Also, it aides in the evolution of a schema used several physical databases

Fluentmigrator is the .net migration framework. Database migrations was first started in ruby on rails applications and soon afterwards every languages had their own migration framework.

Typically any modification onthe schema of a database typically involve dding/removing new table or columns to/from the database. So the basic concept of most migration frameworks is to just move the schema of the database forward or backward in time. Fluentmigrator is no different as it has both an “Up” method (ie: forward through time) and a “Down” method (ie. backwards through time).

The basic unit of fluent migrator is an abstract class called “Migration” that has two virtual methods “Up” and “Down”. So your migration classes will have to derive from these class and implement those methods. Also you will decorate your migration class with a migration attribute that takes a long value as identifier. Commonly, most programmers use time stamps such as YYYYMMDDHHMM as their identifier. This identifier is  used in sorting the execution order of the migration classes. Lower numbers are executed first. Migrations can also be run out of sequence depending on how they were checked in. For example if migration 12, 14 and 15 is checked in before migration 13 these migrations will be executed, but it does not prevent migration 13 from being executed.

Using FluentMigrator

To use fluentmigrator, install the nugget packages a class library of your project (It is highly recommended to install into a new class library specifically meant for migration).



Nuget packgaes


Install-Package FluentMigrator

Install-Package FluentMigrator.Tools


Then create a concrete class that derives from the abstract "Migration" class and implement the virtual methods as shown below.


[Migration(2016060280140)]
    public class CreateTableMigration: Migration
    {
        public override void Down()
        {
            //Creates a table with three columns
            Create.Table("Profiles")
                .WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity()
                .WithColumn("FirstName").AsString().NotNullable()
                .WithColumn("LastName").AsString().NotNullable();

             //creates an index on the FirstName column of the table.
            Create.Index("Fst_Index")
                .OnTable("Profiles")
                .OnColumn("FirstName")
                .Ascending()
                .WithOptions().NonClustered();

            //creates an index call "Lst_Index" on the LastName column of the table.
            Create.Index("Lst_Index")
               .OnTable("Profiles")
               .OnColumn("LastName")
               .Ascending()
               .WithOptions().NonClustered();

            // Inserts data into the table using anonymous classes
            Insert.IntoTable("Profiles")
                .Row(new {FirstName = "John", LastName = "Doe"})
                .Row(new {FirstName = "Jane", LastName = "Doe"});

             //Inserts a column called id if it does not already exist.
            if (!Schema.Table("Profiles").Column("Id").Exists())
            {
                Create.Column("Id")
                    .OnTable("Profiles")
                    .AsInt32()
                    .NotNullable()
                    .PrimaryKey()
                    .Identity();
            }
            
        }

        public override void Up()
        {
            Delete.Table("Profiles");
        }
    }

The Fluent Interface

The fluent migrator fluent api allows one to create tables, columns, indexes, foreign keys, insert data into tables and most other constructs you will need to manipulate a database.

It works by populating a semantic model that is used to analyze and apply migrations in batch. The fluent api is available in the migration class with five (5) root expressions which are:
  1.  Create: This expression allows you to create a table, column, index, foreign key and     schema.
  2.  Delete: This expression allows one to delete a table, column, foreign key and a     schema. You can also delete multiple column using the “Column” extension method.
  3.              Alter: This expression allows you to alter existing tables and columns.
  4.              Execute: This expression allows you to execute a block of sql, or a script by name (ie. myscript.sql) or an embedded sql script.
    Note: To embed a sql script, add the file to your migration project and change the build action property to Embedded Resource.
  5. Rename: This expression allows you to rename a column or a table.

Data Expressions: This expressions allows you to insert a row into a table using an anonymous data type for the row contents.

Insert.IntoTable("Profiles")
                .Row(new {FirstName = "John", LastName = "Doe"})
                .Row(new {FirstName = "Jane", LastName = "Doe"});

AllRows Attribute

You use this attribute when you want to add a non-nullable column without specifying a default value. Note you can also you SetExistingRows() methods to add a predefined value to a newly created column.

//Update table
            Update.Table("Profiles").Set(new {DateJoined = DateTime.Today}).AllRows();

IfDatabase Expression

This expression allows you to execute conditional expression depending on the type of database in use. Currently fluentmigrator supports these databases:
  1.        SqlServer (this includes Sql Server 2005 and Sql Server 2008)
  2.        SqlServer2000
  3.             SqlServerCe
  4.         Postgres
  5.         MySql
  6.        Oracle
  7.       Jet
  8.      Sqlite.

Multiple databases type (as specified above) can be passed into the IfDatabase Expression.

IfDatabase("SqlServer", "Postgres")
                .Create.Table("Users")
                .WithIdColumn()
                .WithColumn("Name").AsString().NotNullable();

Schema.Exists Expressions

This expression allows you to write conditional migrations that gets executed based on some preexisting schema. For example you can write a migration checking if some columns already exists before adding it.

if (!Schema.Table("Profiles").Column("Id").Exists())
            {
                Create.Column("Id")
                    .OnTable("Profiles")
                    .AsInt32()
                    .NotNullable()
                    .PrimaryKey()
                    .Identity();
            }

Conclusion

Fluent migrator is a simple but powerful framework used in managing and migrating different versions of your database schema in c#. One  major advantage is that the code can be checked into a version control system as it provides a neat interface for growing or regressing your schema without resorting to multiple sql scripts.

In this post, I have tried to give you a brief but detailed introduction to fluent migrator. In the next we will go inroad into the various usage scenario and how one can use nant, commandline, fluentmigrator.runner to execute migration class. Thanks.

References





No comments:

Post a Comment