Database Migrations

Migrations

Migrations are a convenient way for you to alter your database in a structured and organized manner. You could edit fragments of SQL by hand but you would then be responsible for telling other developers that they need to go and run them. You’d also have to keep track of which changes need to be run against the production machines next time you deploy.

The underlying migration framework tracks which migrations have already been run so all you have to do is update your source and run appropriate migration commands the application should out which migrations should be run. Migrations also allow you to describe these transformations using code. The great thing about this is that it is database independent: you don’t need to worry about the precise syntax of CREATE TABLE any more than you worry about variations on SELECT * (you can drop down to raw SQL for database specific features). The other advantage of migrations is the database schema resides together with the application code making is very easy for new developers to clone the repository and start development in the shortest possible time without worrying about attaching or creating database files.

Migrations also allow the versioning of database schema by way of storing both schema definition and initial data scripts inside a C# class making it very easy for source control systems to track changes.

FluentMigrator is the migration framework used for CATS. 

FluentMigrator is a migration framework for .NET much like Ruby Migrations. Migrations are a structured way to alter your database schema and are an alternative to creating lots of 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 version control.

Getting Started

FluentMigration is available as nuget package and can be easily installed through Visual Studio Package Manager. The two packages you need to install to get started with migrations are:

Install-Package FluentMigrator

Install-Package FluentMigrator.Tools

Project Setup

The suggested project structure for implementing migrations is to include a class library project to contain all migrations for the project inside the current solution. For example we have a DRMFSS.Migrations. This will allow us to have a separate project to evolve as the project progresses and will contain the full history of database schema and seed data for all changes starting from the start.

All migration projects should go into 'Migrations' solution folder. Ideally we will have only one migration project per solution assuming that each solution represents the major modules in CATS such as HUB, EARLY WARNING etc.

Anatomy Migrations

A single migration is represented by a class which inherits from FluentMigrator.Migration. The following example represents a single migration which is taken from DRMFSS HUB project.

using FluentMigrator;
namespace DRMFSS.Migrations
{
  [Migration(201305221729)]
  public class CreateAccountTable : Migration
  {
     public override void Up()
     {
        Create.Table("Account").WithColumn("AccountId").AsInt32().PrimaryKey()
                               .WithColumn("EntityType").AsString().NotNullable()
                               .WithColumn("EntityID").AsString(size: 500);
     }


     public override void Down()
     {
        Delete.Table("Account");
     }
  }
}

1. All migration classes must inherit from FluentMigrator.Migration
2. You should provide the Migration attribute for the class. This attribute accepts a version parameter of type long to uniquely identify the migration and also determine the execution order. The way migrations work is that they are executed in sequential order starting from the first version to the last one. In order to make the migration version unique, the recommended approach is to use the current time-stamp as the version number. In the above example the version number 201305221729 can be interpreted as May 22 2013 17:29 or 5/22/2013 17:29. One gotcha in this approach is if we create more than one migration in a given minute we will run into having duplicated version numbers since we are not considering seconds. As a solution we can manually update the version number after the class is generated by the new template dialog.
3. We need to override two methods from the base Migration class. The 'Up' method is the one which is executed whenever we are upgrading the database into a newer version and usually contains Create or Alter methods for schema definitions and Insert ones for seed data while the 'Down' method contains migrations whenever we want to down grade the database to that specific version. The 'Down' method usually contains Drop and Delete instructions to reset the database to a version just right before the version we are actually rolling back to.
4. We can use Profiles to selectivly apply migrations depending on command line options passed for the migration runner. For instance you may want to run the "Development" profile only in a Dev environment and run the "Testing" profile on a Testing environment. Profile will always be run if you specify them.

Creating Migrations

The simplest way to create a migration is to add a new class to your project and:

1. Import the FluentMigrator namespace
2. Inherit from FluentMigrator.Migration base class
3. Override Up and Down methods
4. Add migration logic to your class. For more information on the different tasks you can perform using FluentMigration please see the project wiki at https://github.com/schambers/fluentmigrator/wiki

Installing and Using Resharper File Templates

Starter Template

Running Migrations

There are a number of options for running migrations in FluentMigrator. There is a tool called Migrate.exe that comes with FluentMigrator.Tools package which is able to run migrations against a provided database connection string. Migrations can be exectured against any database as long as there is an ADO.NET provider for it. Out of the box FluentMigrator supports SQL Server, MySQL, PostgreSQL, Oracle, Sqlite and SQL Server CE. The other option is to run the migration tasks an MSBuild task during the project build process. 

The preferred method of running migrations for all of CATS applications is through the Migrate.exe command line tool. This option provides us with a flexible and controlled process for executing migrations. We absolutely would not want our migrations to execute on each build! We will need to provide connection string, migration assembly and the database provider as in the following example.

FluentMigrator\Migrate.exe --conn "Server=.;Initial Catalog=CTS;Integrated Security=SSPI" --provider sqlserver2008 --assembly "..\DRMFSS.Migrations\bin\Debug\DRMFSS.Migrations.dll"

The above windows shell command is found inside the tools directory of DRMFSS HUB solution and run it as an administrator in order to migrate your database to the latest version.

WARNING: Running migrations will overwrite your database with whatever difinition you might have inside your migration classes so you should take all the necessary caution before issuing any migrations on your database.

Resharper template Migration.DotSettings