A FluentMigrator Controller and Service for .NET Core

• Download FluentMigrator example - 6.9 KB

Table of Contents

Introduction

I find FluentMigrator to be a great tool for making revisions to a database. It's an easy to learn "fluent" syntax and accommodates 90% of what I need to do, and if there's something unique that needs to be done, it provides an Execute method for custom SQL operations. As I find it useful to provide web API endpoints for performing migrations and checking on the migration version, this article is about how to do that in .NET 6. The same controller and service works with .NET Core 3.1 as well, the only difference would be in the program / startup configuration.

An Example Migration

The syntax of Fluent Migrator is described on their intro page so I will provide here only an simple example of an "up" and a "down" migration.

using FluentMigrator;

namespace Clifton
{
  [Migration(202201011201)]
  public class _202201011201_CreateTables : Migration
  {
    public override void Up()
    {
      Create.Table("Test")
        .WithColumn("Id").AsInt32().PrimaryKey().Identity().NotNullable()
        .WithColumn("IntField").AsInt32().Nullable()
        .WithColumn("StringField").AsString().Nullable()
        .WithColumn("DateField").AsDate().Nullable()
        .WithColumn("DateTimeField").AsDateTime().Nullable()
        .WithColumn("TimeField").AsTime().Nullable()
        .WithColumn("BitField").AsBoolean().Nullable()
        .WithColumn("Deleted").AsBoolean().NotNullable();
    }

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

Some Best Practices

1. Number the migration version using yyyyMMddhhmm format as this keeps your migrations in sequential order.
2. To help organize your migrations for a product that has a long life-cycle and is undergoing improvements over time, consider adding a folder for the year and sub-folders for each month of the year.
3. A migration should ideally operate on only one table or view. Certainly multiple operations, such as creating columns, can be done, but consider writing multiple-table migrations as separate migrations. The main reason for doing this is that it helps to isolate what migration failed.
4. I am not religious about writing "down" migrations -- I rarely, if ever, have had to revert to a migration. However, your use case might be different.
5. Give your migration files a distinctive name that describes the reason for the migration. This is another good reason to keep multi-table migrations separate, as the reason for touching one table might be different for other tables.

The Code

Adding Fluent Migrator takes a wee bit of work. I prefer to have my migrations in a separate assembly rather than the main web API application. I also want to capture any errors, and oddly, Fluent Migrator doesn't make this easy to do -- I was not able to figure out how to add a different logger than what is provided with Fluent Migrator and one would think that they would at least provide a stream logger! The other thing that Fluent Migrator is missing is the ability to create the database, so you'll see how that's implemented separately.

Package Dependencies

The following packages are used:

Dapper, System.Data.SqlClient, and Newtonsoft.Json are essentially one-offs for the following reasons:

• Dapper - simply for the convenience of checking if the database already exists and creating it if it doesn't
• System.Data.SqlClient - because this is what Dapper uses
• Newtonsoft.Json - because Newtonsoft.Json is so much better than System.Text.Json

Program Startup

It's going to take me a while to get used to .NET 6. The first thing I did was disable that nullable nightmare in the .csproj:

<Nullable>disable</Nullable>

And I'm still getting used to implicit usings and not having namespaces and a Main. That said, here's the Program.cs file:

using System.Reflection;

using Microsoft.EntityFrameworkCore;

using FluentMigrator.Runner;
using Newtonsoft.Json;

using Clifton;
using Interfaces;

var builder = WebApplication.CreateBuilder(args);

var appSettings = new AppSettings();
builder.Configuration.Bind(appSettings);

builder.Services.AddControllers()
  .AddNewtonsoftJson(options =>
  {
    options.SerializerSettings.DefaultValueHandling = DefaultValueHandling.Ignore;
    options.SerializerSettings.Formatting = Formatting.Indented;
  });

var connection = builder.Configuration.GetConnectionString(appSettings.UseDatabase);
builder.Services.AddDbContext<AppDbContext>(options => options.UseSqlServer(connection));

builder.Services.AddScoped<IMigratorService, MigratorService>();

string migrationAssemblyPath = Path.Combine
(appSettings.ExecutingAssembly.Location.LeftOfRightmostOf("\\"), appSettings.MigrationAssembly);
Assembly migrationAssembly = Assembly.LoadFrom(migrationAssemblyPath);

builder.Services.AddFluentMigratorCore()
  .ConfigureRunner(rb => rb
    .AddSqlServer()
    .WithGlobalConnectionString(connection)
    .ScanIn(migrationAssembly).For.Migrations())
    .AddLogging(lb => lb.AddFluentMigratorConsole());

var app = builder.Build();
app.UseAuthorization();
app.MapControllers();
app.Run();

Besides the boilerplate, here we see that I'm adding the NewtonsoftJson controller, which I'm doing for the purposes of setting a couple options, including indented formatting so for purposes of this article, the JSON being returned is nicely formatted in the browser.

We also see that the MigratorService is added, as well as the FluentMigratorCore service and its configuration.

Notice the ScanIn call - this is important because it tells Fluent Migrator what assembly to scan for classes implementing the Migration attribute and base class.

Application Settings

The configuration comes from the appsettings.json file, so we have an AppSettings class to which the JSON configuration is bound:

using System.Reflection;

namespace Clifton
{
  public class AppSettings
  {
    public static AppSettings Settings { get; set; }
    public string UseDatabase { get; set; }
    public string MigrationAssembly { get; set; }
    public Assembly ExecutingAssembly => Assembly.GetExecutingAssembly();

    public AppSettings()
    {
      Settings = this;
    }
  }
}

In appsettings.json, we have these declarations:

"UseDatabase": "DefaultConnection",
"MigrationAssembly": "Migrations.dll",

"ConnectionStrings": {
  "DefaultConnection": "Server=localhost;Database=Test;Integrated Security=True;",
  "MasterConnection": "Server=localhost;Database=master;Integrated Security=True;"
}

• UseDatabase: In case you want to support different database connections for testing, development, production, etc.
• MigrationAssembly: The name of the assembly holding the migrations.
• MasterConnection: This is hard-coded in the migrator service and is used to check if the database exists and create it if it doesn't exit.

The Controller

The controller implements:

1. a migrate up endpoint
2. a migrate down endpoint
3. an endpoint to list all migrations
4. an endpoint to get the version of our controller/service, which I find useful simply to make sure that the API is working

using Microsoft.AspNetCore.Mvc;

using Interfaces;

namespace Clifton
{
  [ApiController]
  [Route("[controller]")]
  public class MigratorController : ControllerBase
  {
    private readonly IMigratorService ms;
    private readonly AppDbContext context;

    public MigratorController(IMigratorService ms, AppDbContext context)
    {
      this.ms = ms;
      this.context = context;
    }

    [HttpGet]
    public ActionResult Version()
    {
      return Ok(new { Version = "1.00" });
    }

    [HttpGet("VersionInfo")]
    public ActionResult VersionInfo()
    {
      var recs = context.VersionInfo.OrderByDescending(v => v.Version);

      return Ok(recs);
    }

    [HttpGet("MigrateUp")]
    public ActionResult MigrateUp()
    {
      var resp = ms.MigrateUp();

      return Ok(resp);
    }

    [HttpGet("MigrateDown/{version}")]
    public ActionResult MigrateDown(long version)
    {
      var resp = ms.MigrateDown(version);

      return Ok(resp);
    }
  }
}

The Service

The service implements the migration behavior for the migrate up and migrate down endpoints.

using System.Data.SqlClient;
using System.Text;

using Dapper;
using FluentMigrator.Runner;

using Interfaces;

namespace Clifton
{
  public class MigratorService : IMigratorService
  {
    private IMigrationRunner runner;
    private IConfiguration cfg;

    public MigratorService(IMigrationRunner runner, IConfiguration cfg)
    {
      this.runner = runner;
      this.cfg = cfg;
    }

    public string MigrateUp()
    {
      EnsureDatabase();

      var errs = ConsoleHook(() => runner.MigrateUp());
      var result = String.IsNullOrEmpty(errs) ? "Success" : errs;

      return result;
    }

    // Migrate down *to* the version.
    // If you want to migrate down the first migration, 
    // use any version # prior to that first migration.
    public string MigrateDown(long version)
    {
      var errs = ConsoleHook(() => runner.MigrateDown(version));
      var result = String.IsNullOrEmpty(errs) ? "Success" : errs;

      return result;
    }

    private void EnsureDatabase()
    {
      var cs = cfg.GetConnectionString(AppSettings.Settings.UseDatabase);
      var dbName = cs.RightOf("Database=").LeftOf(";");
      var master = cfg.GetConnectionString("MasterConnection");

      var parameters = new DynamicParameters();
      parameters.Add("name", dbName);
      using var connection = new SqlConnection(master);
      var records = connection.Query
                    ("SELECT name FROM sys.databases WHERE name = @name", parameters);

      if (!records.Any())
      {
        connection.Execute($"CREATE DATABASE [{dbName}]");
      }
    }

    private string ConsoleHook(Action action)
    {
      var saved = Console.Out;
      var sb = new StringBuilder();
      var tw = new StringWriter(sb);
      Console.SetOut(tw);

      try
      {
        action();
      }
      catch(Exception ex)
      {
        Console.WriteLine(ex.Message);
      }

      tw.Close();

      // Restore the default console out.
      Console.SetOut(saved);

      var errs = sb.ToString();

      return errs;
    }
  }
}

The interesting things about the code above is:

1. The EnsureDatabase method which queries the system table databases to see if the database exists and create it if it doesn't.
2. The console hook, which capture the console output into a stream that writes to a StringBuilder.
3. Oddly, some errors are handled by Fluent Migrator and do not throw an exception, other errors do throw an exception, at least from what I've seen. So the exception handler writes the exception message to the console to be captured by the StringBuilder stream. In older versions of Fluent Migrator, there used to be a way to suppress exceptions but I can't find where that configuration option went.

Seeing Fluent Migrator in Action

Migrate Up

Using the example migration at the beginning of this article, we can update the database to the most current migration (well, we only have one) using the endpoint (your port may be different in Visual Studio):

localhost:5000/migrator/migrateup

and we see:

View Migrations

We can inspect the migrations (again, there's only one) using:

localhost:5000/migrator/versioninfo

and we see:

And yes, we see that the Test database and Test table were created:

Also note that the table VersionInfo was created automatically by Fluent Migrator.

And yes, the columns were created in the Test table as well:

Migrate Down

We can migrate down to a specific version as well. If we want to migrate down to before the first migration we simply using an earlier migration version number:

http://localhost:5000/migrator/migratedown/202101011201

Refreshing the tables in SSMS, we see that the table Test has been removed:

Error Reporting

Errors are reported not as an exception but simply as a return string. For example, here I deleted the VersionInfo record so that Fluent Migrator thinks the migration hasn't been run, but the table already exists, which forces an error:

You may want to wrap the success and error status in an actual JSON object.

Conclusion

Implementing database migrations as an endpoint in a web API makes it easy to run migrations, as opposed to running a separate migration application. This is useful in all environments -- your locally hosted development environment as well as test, QA, and production environments. It should be noted that one would probably add authentication / authorization to the controller endpoints -- you certainly do not want someone inadvertently migrating a production database all the way down to day 0!

History

• 5^th February, 2022: Initial version