One of the most common mistake which a junior database architect can make is a missed versioning schema. It is so easy to design a schema, release the corresponding application and to realize later how difficult to maintain this schema, support compatibility between the versions and migrate users to the new versions.

However, even when the new schema includes a concept of version, work is required to keep the schema in a health state, have a migration procedure and some tooling to automate the maintenance tasks.

FluentMigrator C# library provides all the needed tools to solve those problems. It provides a syntax to define the versioned schema, it provides a way to migrate databases from version to version and it includes tools to automate the tasks, during the development, deployment and in the field.

Schema

The core concept of FluentMigrator is a migration. Migration is a class which has a version and two methods Up() and Down(). Up() is responsible to migrate the target database from the previous version, to the version defined by the migration. Down() is responsible for the opposite operation – downgrading the database to the previous version.

[Migration(10)]
public class AddNotesTable : Migration
{
      public override Up()
      {
            Create.Table("Notes")
                  .WithIdColumn()
                  .WithColumn("Body").AsString(4000).NotNullable()
                  .WithTimeStamps()
                  .WithColumn("UserId").AsInt32();
      }

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

Instead of using SQL, migrations are defined using fluent C# syntax. This approach makes the migrations almost independent from the concrete databases, hiding the differences in SQL between them.

Migration version is defined using MigrationAttribute. Attribute accepts a number, which will be used by a migration executor to sort all the defined migrations and execute them one by one.

In addition to the schema definition, migrations can also include data seeding.

[Profile("Development")]
public class CreateDevData: Migration
{
      public override Up()
      {
            Insert.IntoTable("User").Row( new
                  {
                        Username = "devuser1",
                        DisplayName = "Dev User1"
                  });
      }

      public override Down()
      {
            // empty, not using
      }
}

This example also demonstrates an idea of profiles – an ability to selectively execute some migrations to have, for example, a seeded database for development or testing.

Execution

All migrations are usually grouped in on assembly and can be executed using the various provided tool. FluentMigrator provides CLI, NAnt, MSBuild and Rake migration runners.

Migrate.exe /connection "Data Source=db\db.sqlite;Version=3;" /db sqlite /target migrations.dll

This code demonstrates usage of CLI tool to execute the migrations from migrations.dll for the database defined via the connection string using sqlite driver. Runner automatically detect the current database version and applies only the required migrations.

FluentMigrator is published under Apache 2.0 license and available at GitHub and NuGet.