Database migrations with FluentMigrator

FileStore uses Dapper to read and write its file records. That still leaves another job: creating the database tables and changing them as the application evolves.

For this project, I used FluentMigrator. It lets me describe database changes in C#, give each change a version number, and run the pending migrations against SQL Server.

This is the third article in the FileStore series. The examples come from the repository's historical .NET Core 3.1 implementation, which references FluentMigrator.Runner 3.3.1. They are not installation instructions for the latest package version.

Dapper and migrations have different jobs

Dapper handles queries such as finding a file by its reference and client ID. FluentMigrator manages the schema those queries depend on.

They can work together without an EF Core model. I write the schema change explicitly, then let the runner track which version has been applied.

A migration has a version and an Up method

The first FileStore migration creates the API-client, allowed-file-type and file tables. Here is an extract showing the API-client table:

[Migration(20211212)]
public class InitializeTables_20211212 : Migration
{
    public override void Up()
    {
        Create.Table("ApiClient")
            .WithColumn("Id").AsGuid().NotNullable().PrimaryKey()
            .WithColumn("Name").AsString(50).NotNullable()
            .WithColumn("ApiKey").AsString(50).NotNullable()
            .WithColumn("Secret").AsString(128).NotNullable()
            .WithColumn("BasePath").AsString(255);

        // The original Up also creates FileType and File.
    }

    public override void Down()
    {
        Delete.Table("File");
        Delete.Table("FileType");
        Delete.Table("ApiClient");
    }
}

The Up excerpt is incomplete on purpose; the comment identifies the omitted table definitions. The Down body is from the full migration, where all three tables exist.

The number in [Migration(...)] identifies this change. The class name helps a reader, but it is the numeric version that the runner uses for ordering.

Describe the file mapping in the schema

The same initial migration defines the file table:

Create.Table("File")
    .WithColumn("Id").AsGuid().NotNullable().PrimaryKey()
    .WithColumn("Reference").AsGuid().NotNullable().Unique()
    .WithColumn("FileTypeId").AsGuid()
        .ForeignKey("FileType", "Id").OnDelete(System.Data.Rule.None)
    .WithColumn("ApiClientId").AsGuid().NotNullable()
        .ForeignKey("ApiClient", "Id").OnDelete(System.Data.Rule.Cascade)
    .WithColumn("FileName").AsString(255)
    .WithColumn("FullPath").AsString()
    .WithColumn("Size").AsInt64()
    .WithColumn("DateCreated").AsDateTime().WithDefaultValue(DateTime.Now)
    .WithColumn("MarkedForDeletion").AsBoolean().WithDefaultValue(false);

The unique Reference gives the API a file identifier. The foreign keys connect that record to its client and file-type configuration.

There are design decisions to read carefully here. Deleting an API-client row cascades to file metadata, but that database cascade does not delete the physical files. Also, DateTime.Now is evaluated by this C# migration expression; it should not be mistaken for an explicit SQL UTC timestamp function. I would revisit both details when updating the schema design.

Add a new migration when requirements change

Later, FileStore added provider configuration to the client table:

[Migration(202112131)]
public class Migration_202112131 : Migration
{
    public override void Up()
    {
        Alter.Table("ApiClient")
            .AddColumn("StorageType").AsInt16().WithDefaultValue(0);

        Alter.Table("ApiClient")
            .AddColumn("StorageSettings").AsString(int.MaxValue);
    }

    public override void Down()
    {
        Delete.Column("StorageType").FromTable("ApiClient");
        Delete.Column("StorageSettings").FromTable("ApiClient");
    }
}

The stored value 0 corresponds to the local-file provider in this project. The second column holds provider settings.

The useful habit is to add a new migration for the next change, rather than rewriting a migration that other environments have already applied. The runner tracks applied versions; editing an old file does not make an existing database replay it.

Keep version numbering consistent

The repository uses versions such as 20211212, 202112122 and 202112131. They look date-related, but they are numbers, not parsed dates.

Examples of consistent migration version numbers compared with the mixed-length version IDs in FileStore.

For new work, I would choose one consistent scheme, for example a fixed-width date plus sequence. I would also check new versions against the existing history before merging.

I would not renumber these historical migrations just to make them prettier. Once applied, their IDs are part of the database's migration history. A numbering cleanup needs more care than renaming classes.

Register the runner and scan the migration assembly

The repository configures FluentMigrator in the Dapper persistence project:

services.AddFluentMigratorCore()
    .ConfigureRunner(c => c.AddSqlServer2016()
        .WithGlobalConnectionString(
            configuration.GetConnectionString("FileStoreDb"))
        .ScanIn(Assembly.GetExecutingAssembly()).For.Migrations());

This selects a SQL Server processor, supplies the configured connection string and tells the runner where to find migration classes. AddSqlServer2016 is the processor configuration used by this version of the project, not a claim about the server a reader should provision today.

The current FluentMigrator runner documentation is the place to check setup for a newer version. FluentMigrator also maintains migration history, normally in a VersionInfo table, so later runs can identify pending changes. See its configuration documentation.

How FileStore runs migrations

The project creates a scope, resolves IMigrationRunner and runs the pending migrations:

using (var scope = host.Services.CreateScope())
{
    var migrationService = scope.ServiceProvider
        .GetRequiredService<IMigrationRunner>();

    migrationService.ListMigrations();
    migrationService.MigrateUp();
}

This excerpt leaves out the original catch-and-rethrow block. The startup code calls it before starting the host:

CreateHostBuilder(args)
    .Build()
    .MigrateDatabase()
    .Run();

That is how this repository is wired. For a deployment with several application instances, I would prefer a deliberate migration step with one responsible runner, clear logs and the required database permissions. I would not assume that starting every web instance is the right time to change the schema.

Down is not a backup

Another migration increases the length of FullPath:

public override void Up()
{
    Alter.Table("File").AlterColumn("FullPath").AsString(int.MaxValue);
}

public override void Down()
{
    Alter.Table("File").AlterColumn("FullPath").AsString(255);
}

The reverse schema operation is easy to write. Returning to the shorter limit is a different decision once the database contains longer values. Data may no longer fit, and the operation can fail or require an explicit data-handling plan.

The repository also has migrations whose Down throws NotImplementedException or does nothing. I would not describe that history as fully reversible. Dropping a table or column also does not bring its deleted data back when it is created again.

Keep secrets out of migration history

One historical seed migration contains client credential values. I am intentionally not copying that code into this article.

Schema changes belong in version control. Real credentials should be provisioned through an appropriate protected process, and credentials exposed in a public repository should be replaced if they remain active. Removing them from a later commit alone does not remove the earlier history.

What I would test

I would test the migration path against a disposable SQL Server database, including an empty database and an existing database at a known prior version.

Can a fresh database reach the expected schema?
Does a second run leave already-applied migrations alone?
Can existing file records survive the next change?
Are foreign-key and deletion rules doing what the application expects?
Does the deployment stop clearly on a migration failure?
Where rollback is supported, does it work with realistic data, not only empty tables?

FluentMigrator gives me a readable history of explicit schema changes. The operational work still matters: version discipline, reviewing data effects, backups and deciding when those changes are allowed to run.

Explore the FileStore migrations on GitHub, and the runner configuration.