Using FluentMigrations with Postgres

On a new project we were using a Postgres database so we needed a way to apply get the schema setup and maintained - had used DbUp in the past but somebody suggested using FluentMigrations this time and I was impressed with it. We've deployed several times now and never had an issue with the migrations (well unless somebody included a script where there was some conflict with existing data).

You configure it

using FluentMigrator.Runner;
using FluentMigrator.Runner.Initialization;

...

services.
  ConfigureRunner(r =>
  {      
      r.AddPostgres()
          .WithGlobalConnectionString([ConnectionString])
          .ScanIn(typeof(Program).Assembly).For.All();
  })
  .Configure<RunnerOptions>(opt =>
  {
      opt.Tags = [logicalEnvironment];
  });

using var scope = app.Services.CreateScope();
var runner = scope.ServiceProvider.GetRequiredService<IMigrationRunner>();
runner.MigrateUp();

and then create migration scripts that inherit from Migration which gives you Up and Down methods.

  [Migration(20240509090002)]
public class InitialAgencyDbSetupMigration : Migration
{
    public override void Up()
    {
        Create.Table("user")
            .WithColumn("id").AsGuid().PrimaryKey("pk_node")
            .WithColumn("name").AsString().NotNullable()
            .WithColumn("email").AsString().NotNullable()
            .WithColumn("a_nullable_property").AsString().Nullable()               .WithColumn("created").AsDateTime().NotNullable().WithDefault(SystemMethods.CurrentDateTime)            .WithColumn("updated").AsDateTime().NotNullable().WithDefault(SystemMethods.CurrentDateTime);

        Execute.Sql("....");
    } 

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

Maintenance scripts

BeforeAll migration to take a lock on the DB so if there are multiple instances of your app running (load balanced) they are blocked until this one finishes.

[Maintenance(MigrationStage.BeforeAll, TransactionBehavior.None)]
public class BeforeAll(ILogger<BeforeAll> logger) : Migration
{
    public override void Up()
    {
        logger.LogInformation("Starting BeforeAll migration");
        Execute.Sql($"SELECT pg_advisory_lock({Maintenance.AdvisoryLockId})");
    }
    public override void Down() => throw new NotImplementedException();
}

AfterAll migration that runs at the end to release the lock.

[Maintenance(MigrationStage.AfterAll, TransactionBehavior.None)]
public class FinalAfterAll(ILogger<FinalAfterAll> logger) : Migration
{
    public override void Up()
    {
        logger.LogInformation("Starting AfterAll migration");
        Execute.Sql($"SELECT pg_advisory_unlock({Maintenance.AdvisoryLockId})");
    }
    public override void Down() => throw new NotImplementedException();
}

An AfterAll migration that runs when all migrations have completed but only when on the dev instance - tags allow you to limit the scripts that run. We had certain things that needed to be setup in Dev (granting access for devs to the DB, creating test data etc).

[Maintenance(MigrationStage.AfterAll)]
[FluentMigrator.Tags("dev")]
public class AfterAllDev() : Migration
{
    public override void Up()
    {
        //do things in dev
    }

    public override void Down() => throw new NotImplementedException();
}

We also had AfterAll migration for the production environment as we had some things that only needed doing when going to prod.

[Maintenance(MigrationStage.AfterAll)]
[FluentMigrator.Tags("pre", "prod")]
public class AfterAllPreAndProd() : Migration
{
    public override void Up()
    {
        //do things only in pre and prod envs
    }

    public override void Down() => throw new NotImplementedException();
}

For more information take a look at the really good quick start documentation.