Need of Database Migration
Fluent Migrator is a framework for doing database migration. Migrations are written to create or alter the database schema.These Migrations are the classes that are derived from the Migration class of Fluent Migrator. Upon deriving from Migration you will need to write the code necessary to "Up" or "Down" that migration.
When you migrate to that version of the database, the Up() method will be called to execute the database modifications that are needed to run in order to bring your database to that version. When you migrate in reverse to a previous version, the Down() method will be called to execute the database modifications in order to undo the set of database changes.
Every Migration should be a very small incremental change in the database. Migrations create a table and add one or more columns to a table, alter the data of a table, run a custom query with ExecuteQuery on tables.
To start with, we need to perform the steps mentioned below:
1. Install FluentMigrator and FluentMigrator.Runner from Nuget Package Manager.
Every Migration should be a very small incremental change in the database. Migrations create a table and add one or more columns to a table, alter the data of a table, run a custom query with ExecuteQuery on tables.
To start with, we need to perform the steps mentioned below:
1. Install FluentMigrator and FluentMigrator.Runner from Nuget Package Manager.
2. Create the Migration Classes. The three things are important while creating it:
a) [Migration (long Integer value)]: This will determine the order in which the scripts are executed. The one with the lower value in the Migration attribute will be executed prior to the one with the greater value.
b) Up Method: Override this method and put the Code to create or alter the database table here.
c) Down Method: This method will be overridden in order to revert the changes done by the Up Method
Below is the template
of the Migration class:
using FluentMigrator;
[Migration(longIntegerNumber)]
public class MigrationScript : Migration
{
public override void Up()
{
//logic to create or alter the table
}
public override void Down()
{
//logic to revert the changes done using the Up Method logic
}
}
To create the table put the following logic in the Up Method
Create.Table(TableName)
.WithColumn("ProjectId").AsInt32().PrimaryKey().Identity()
.WithColumn("UserId").AsInt32().NotNullable().ForeignKey("ProjectUser", "UserId")
.WithColumn("ProjectDescription").AsString().Nullable()
.WithColumn("ProjectStartDate").AsDate().Nullable()
.WithColumn("ProjectEndDate").AsDate().Nullable();
To revert the following logic, put the following logic in the down method
Delete.ForeignKey("FK_Project_UserId_ProjectUser_UserId").OnTable(TableName);
Delete.Table(TableName);
To Alter the table schema the following code is used
//Alter Column Datatype to Decimal
Alter.Column(ColumnName).OnTable(TableName).AsDecimal(10,1).WithDefaultValue(DefaultValue);
//Rename the Column from OldColumnName to NewColumnName
Rename.Column(OldColumnName).OnTable(TableName).To(NewColumnName);
//To Add Column
Alter.Table(TableName).AddColumn(ColumnName).AsString().WithDefaultValue(DefaultValue);
//To DeleteColumn from the table
Delete.Column(ColumnName).FromTable(TableName);
To execute Migration file that we have created above,we need to create RunnerContext class object to set the value of property “Namespace” and give IAnnouncer object as parameter. We use :migrationType.Assembly” created from “typeof (MigrationScriptClass)”. You can assign your namespace directly that has your migration classes.
Next MigrationRunner object is created with the following parameters:
SqlServer2008ProcessorFactory that tells the version of SQLServer that is to be used and to replace it with your SQLServer version like SqlServer2012ProcessorFactory.
ProcessorOptions with two parameter.
MigrationProcessor object by using “factory.Create (connString, announcer, options)” that take three parameters and returns its instance.
Next runner.MigrateUp(true) or runner.MigrateDown(1) will execute up or down migration script respectively. Comment out runner.MigrateUp(true) or runner.MigrateDown(1) which you don’t want to run.
var migrationType = typeof(MigrationScriptClass);
var announcer = new TextWriterAnnouncer(s => System.Diagnostics.Debug.WriteLine(s));
var assembly = migrationType.Assembly;
var migrationContext = new RunnerContext(announcer)
{
Namespace = migrationType.Namespace
};
MigrationProcessorFactory factory = new FluentMigrator.Runner.Processors.SqlServer.SqlServer2008ProcessorFactory();
var options = new ProcessorOptions { PreviewOnly = false, Timeout = 3000 };
//Retrieving the connection string from config file
string connString = ConfigurationSettings.AppSettings["connString"];
var processor = factory.Create(connString, announcer, options);
try
{
var runner = new MigrationRunner(assembly, migrationContext, processor);
runner.MigrateUp(true);
// runner.MigrateDown(number);
}
catch (Exception ex)
{
throw;
}
Running the migrations will result in the creation of the database schema. In that database VersionInfo Table is created that will keep the track of all the migrations that you run. If you will create the new migration you have to keep the migration number in the [Migration(migration number)] attribute to be greater than the previous migration to execute it.Running the new migration will result in the creation of the new entry in the VersionInfo table.
We can also run the sql scripts or commands using the FluentMigrator using the code below
Execute.EmbeddedScript("InsertScript.sql");
Execute.Sql(@"Write the Query you want to run eg ‘UPDATE table
SET attribute = value ‘");
We can use Build Events (Pre or Post) of the project where we can place this application to create the schema of the database required for the project.
We can either place that in the Application_Start() method of the global.ascx.cs file of the project.
Advantages
- All database migrations are contained within the project and can be reviewed and managed by the team – No seperate script and DBA required.
- Easy deployment onto as many as server required.By creating migrations it is easier to sync different environments(dev,test,production).
- As the project grows, database migrations can be created as required, meaning you can easily rollout new changes to live projects.
- It makes the seeding of database easier.
- Versioning of database
