Handling Database Migrations in Mature Applications with Fluent Migrator

A while ago I had to implement a solution to automatically update multiple databases in a mature production environment as part of a CI/CD pipeline.  The environment and setup had some nuances.

Getting licenses for Redgate wasn’t an option, neither was rewriting code to use Entity Framework migrations.  Batches of dynamic SQL scripts were supplied by other processes also had to be run.

I needed a way to bundle this into a cohesive and repeatable process.  I needed mechanisms for rolling back the process in the event of a script failing.  I’ve written this short guide as an end-to-end for future reference.

Full code for this walkthrough can be found on GitHub here.

Fluent Migrator

A library called Fluent Migrator had most of the required features. You can find out more about here.

Fluent Migrator is a NuGet package that helps you create migration classes or scripts that can be tracked and automated in code.

How it Works

Migrations can be written programmatically, or you can execute SQL scripts directly from code. You can target multiple databases and have control down to the transaction level which can also be helpful.

Some of the main points include:

• Fluent Migrator tracks migrations using a table called VersionInfo
• Each record in this table contains a unique VersionId, Date Applied and Description for a single migration
• Databases upgrades or rollbacks are handled in unique migrations classes within your solution.

Creating a Unique Version Id

A unique version is assigned to each class along with a description using the Migration attribute.  The upgrade and downgrade methods are implemented:

[Migration(12345,"a description")]
public class M001_DB_DescriptionOfTask : Migration
{
  public override void Up()
  {
    Execute.Script(Directory.GetCurrentDirectory() + @"YourUpgradeScript.SQL");
  }
  public override void Down()
  {
    throw new NotImplementedException();
  }
}

You don’t need to run SQL scripts and just like Entity Framework, you can write code-first migrations.

Having to manually create unique version numbers adds extra friction to the development loop.

Luckily, you can create your own custom attribute to enforce a naming convention for each migration you create.

Custom Attribute

Here you can see a custom attribute.  It accepts several parameters which are used to generate a unique migration version and description:

public class CustomMigrationAttribute : FluentMigrator.MigrationAttribute
{
  public CustomMigrationAttribute(int branchId, int year, int month, int day, int hour, int minute, string author, FluentMigrator.TransactionBehavior transactionBehavior = 
                                  FluentMigrator.TransactionBehavior.Default) : base(CalculateValue(branchId, year, month, day, hour, minute), transactionBehavior)
  {
    this.Author = author;
  }
     
  public string Author { get; private set; }
      
  private static long CalculateHash(int branchId, int year, int month, int day, int hour, int minute)
  {
            return branchNumber * 1000000000000L + year * 100000000L + month * 1000000L + day * 10000L + hour * 100L + minute;
  }
}

By default, each migration will run in a database transaction. I’ve extended the above example (taken from Fluent Migrator docs) to include additional parameter FluentMigrator.TransactionBehavior transactionBehavior .

This lets you specify if you want to wrap the C# migration in a transaction (Default). Setting it to None will prevent the transaction from being added.

Learn more about custom attributes here:

SQL Code with Transactions

As mentioned, Fluent Migrator will wrap each call within a database transaction.  This means your migration will fail if you have any SQL scripts with logic such as :

IF (@@TRANCOUNT>0)
BEGIN
  RAISERROR('Transaction Count mismatch',16,1)
END
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT>0)
  BEGIN
    ROLLBACK
    SELECT 'Error inserting data schema, Operation rolled Back' AS [MSG]
END

Set FluentMigrator.TransactionBehavior transactionBehavior = FluentMigrator.TransactionBehavior.None to prevent this from happening.

Example

You can see an example migration class here.  This class runs an upgrade that adds a column Age to the User table.

[CustomMigration(0007, 2023, 02, 18, 08, 00, "Jamie Maguire")]
public class M0007_Sandbox_AlterUserAddAge : Migration
{
    public override void Up()
    {
        Execute.Script(Directory.GetCurrentDirectory() + @"\..\..\..\..\SandboxMigrations\Scripts\2023\02\0007\AlterUserAddAge.SQL");
    }

    public override void Down()
    {
        throw new NotImplementedException();
    }
}

The SQL script is nothing fancy:

ALTER TABLE [User]

ADD Age int

After a migration is complete, the Version table is updated.  This signals this migration has run and can be ignored on subsequent runs:

Main Code

All of this is wrapped in a method that fetches the connection string from a JSON config file and any projects with migrations are loaded in using reflection:

private static void Main(string[] args)
{
    var builder = new ConfigurationBuilder()
                       .SetBasePath(Directory.GetCurrentDirectory())
                       .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);


        string connectionString = string.Empty;

        connectionString = builder.Build().GetConnectionString("Sandbox");

        var sc = new ServiceCollection()
        // Add common FluentMigrator services
        .AddFluentMigratorCore()
        .ConfigureRunner(rb => rb
        // Add SQL support to FluentMigrator
        .AddSqlServer()
        // Set the connection string
        .WithGlobalConnectionString(connectionString)
        // Define the assembly containing the migrations
        .ScanIn(typeof(SandboxMigrations.Sandbox).Assembly)
               .For.Migrations())
        // Enable logging to console in the FluentMigrator way
        .AddLogging(lb => lb.AddFluentMigratorConsole())
        // Build the service provider
        .BuildServiceProvider(false);

        using (var sandpitScope = sc.CreateScope())
        {
            UpdateDatabase(sandpitScope.ServiceProvider);
        }
    }
static void UpdateDatabase(IServiceProvider serviceProvider)
{
      // Instantiate the runner
       var runner = serviceProvider.GetRequiredService<IMigrationRunner>();
      // Execute the migrations
      if (runner.HasMigrationsToApplyUp())
      {
          runner.ListMigrations();
          runner.MigrateUp();
      }
}

Demo

You can watch the video of it in action here on YouTube or check it out here:

Tips

You can tell Fluent Migrator to emit migrations to the console but setting the following command ListMigrations() :

You can override the the default behaviour of wrapping all migrations in a transaction by changing the configuration options of the Fluent Migrator “runner”.

Visual Studio Solution Structure:

A solution structure like the following helps you create a logical grouping for each migration in the project/db:

Solution Name->

• Common (Shared Project, contains the Custom Migration Attribute)
• Migrations (Contains migrations for each database)
  • Year
    • Month
    • M{TaskId}_Database_Description.cs
  • Scripts (Contains migration class and any SQL classes)
    • Year
      • Month
      • TaskId

How this looks in Visual Studio:

That’s it!