Working with FluentMigrator

Author
Casper Broeren
Categories
Publish date
share image for the article 'Working with FluentMigrator'

There's more to being a developer than starting up new projects using the latest technology. It also includes working on projects that have been live for several years, but that require adjustments. These projects can have missing standards that were developed long after it went live. One such missing standard in one of my projects is a proper way of getting database changes in the different environments ergo database migrations.

While working on a feature, I promised myself to improve on database migrations. Good database change management was crucial in my functional change. And so I informed the client about the maintenance and went to work.

First I looked at the current state of this project:

  • A solution with 3 databases, which are in full production with no formal database change process.
  • Data access is all over the place; part EntityFramework, part ADO, part Dapper.
  • Rolling out to Test, Accept and Production had to be by a specific migration user to keep things secure.
  • Journal must be kept in the database.

I could not use EF migrations because there were also parts accessed by ADO and Dapper. Also, I do not like the tight coupling of entities with a database when using EF migrations.

A quick survey amongst colleagues and the web resulted in three viable options:

Out of these three options I chose FluentMigrator. My reasoning was:

  • It looked mature.
  • it was documented well.
  • it had a fluent option.

The fluent option makes changes less SQL and more readable to humans. All though I like using raw SQL as a migration, I also like just readable code. I did not go for DB up for the reason of the fluent option.

Although the documentation is extensive and easy to understand I came across two challenges that the docs didn't offer a solution for.

Challenge 1: multiple databases

My first challenge was the multiple databases. I really would like to combine and execute my migrations in one go.

FluentMigrator works with one global database connectionstring. This is set in the startup with ‘WithGlobalConnectionString’. The docs don’t give examples on how to run migrations for multiple databases. After some searching on the web, I devised a solution by applying tags and multiple runs per connectionstring.

The tags in FluentMigrator are attributes that can be decorated on your migration class. First, I define my databases as tags

public sealed class SolutionDatabase
    {
        internal const string Site = nameof(Site);
        internal const string Stats = nameof(Stats);
        internal const string Products = nameof(Products);
    }

This collection can be used for the tags and the collection of databases when running migrations. Here you see the migration for adding some columns to existing tables.

[Tags(SolutionDatabase.Site)]
[Migration(1, "Adds columns person table ")] 
public class SiteMigration1_AddJobAndBirthDayToPerson : Migration
    { 
        public override void Up()
        { 
            Create.Column("Job").OnTable("Person").AsString().Nullable();
            Create.Column("Birthday").OnTable("Person").AsDate(); 
        }
        public override void Down()
        {
             Delete.Column("Job").FromTable("Person");
             Delete.Column("Birthday").FromTable("Person");
        }
    }

I’ve introduced a naming convention in the migration class name. This enables me to manage it more efficiently in Visual studio. The naming convention consists of four parts, in order these are:

  1. The database; Site, Stats or Products.
  2. Static text; Migration.
  3. The version as seen in the attribute Migration on the class. This helps to see the order and available migrations on disk without looking into each class.
  4. The description of the change.

In the setup of the program, I had to make a dictionary of my databases and connectionstrings

static void Main()
        {
            var connectionStrings = new Dictionary<string, string>()
            {
                { SolutionDatabase.Site, ConfigurationManager.ConnectionStrings["Site"].ConnectionString },
                { SolutionDatabase.Stats, ConfigurationManager.ConnectionStrings["Stats"].ConnectionString },
                { SolutionDatabase.Products, ConfigurationManager.ConnectionStrings["Products"].ConnectionString }
            };
            foreach (var connectionString in connectionStrings)
            {
                var serviceProvider = CreateServices(connectionString);
                using (var scope = serviceProvider.CreateScope())
                {
                    UpdateDatabase(connectionString.Key, scope.ServiceProvider);
                }
            }
        }

Last up is the CreateServices method. Here the interesting part is runner options which have the tag set to the database key. Fluent Migrator only selects the migration with the specific tag when looking for migrations to execute. So, when executing the database connection string is correctly set for that migration.

private static IServiceProvider CreateServices(KeyValuePair<string, string> connectionString)
        { 
            return new ServiceCollection()
                .AddFluentMigratorCore()
                .ConfigureRunner(rb => rb
                    .AddSqlServer()
                    .WithGlobalConnectionString(connectionString.Value)
                    .ScanIn(typeof(Program).Assembly).For.Migrations())
                .Configure<RunnerOptions>(opt => {
                    opt.Tags = new[] { connectionString.Key};
                })
                .AddLogging(lb => lb.AddFluentMigratorConsole())
                .BuildServiceProvider(false);

The last method to explain is UpdateDatabase. This straightforward calling them up. Here I could add a command parameter to down my migrations. But this suits me now.

private static void UpdateDatabase(string database, IServiceProvider serviceProvider)
        {
            try
            {
                var runner = serviceProvider.GetRequiredService<IMigrationRunner>();
                runner.MigrateUp();
            }
            catch (MissingMigrationsException e)
            {
                Console.WriteLine($"{e.Message} for {database}");
            }

        }

Why didn’t you use FluentMigrator Profiles for this use case?
Profiles are used for environments, not for different databases. Also, I didn’t want to call my migrator for each database separately.

Why write your own runner?
I do think this would be possible with FluentMigrator Console runner. But instead, I wanted my own runner to do exactly what it should do without opening other functionalities such as downgrading. This runner will be used in our CI/CD pipeline

Challenge 2: CI/CD

My other challenge was integration in the current CI/CD in Azure Devops. Thanks to Bas Roovers I created a Task Group which has 4 steps. The correct connectionstring is set to a variable group so there is no recollection of securables in either the code, task group or the pipeline. I could easily integrate this into all 4 deployment environments. Here is the definition of the task group:

  • Step 1 and 2 are transforming the config files to an environment-specific config.
  • Step 3 is calling it from my deployment group. All output is in the console and presented while executing the step.
  • Step 4 is cleaning all runtime code so there are no lingering executables and connectionstrings.

Conclusion

In conclusion, I think FluentMigrator really solved my problem well. I hope people can learn from my experiences so they too can use Fluent in an elegant way.

Contact Casper Broeren

If you have any remarks, recommendations or questions, feel free to e-mail me at casper@vicompany.nl.

Back to top

Accept cookies?

We are actively scouting for new talent to join us and would like to remind you outside of Vi. With your consent, we place small files (also known as 'cookies') on your computer that will allow us to do that.

Find out more about cookies or .

Manage cookie preferences