Seed Postgres Database with FluentMigrator

In this blog post, we will address how to seed your Postgres Database when using FluentMigrator.

In the example context, we have a Postges Database running on a docker container, and have a Web Api connecting to the database using Dapper. Dapper is a micro-orm, and while it is pretty good in what is does, it is quite limited and does not support features like Migration compared to Entity Framework.

Hence we would be using FluentMigrator for seeding and writing migrations for our database. FluentMigrator is a migration framework supported by .Net Framework and .Net core.

Creating Database

One of things, which FluentMigrator doesn’t do is creating the database itself. But since we are using Docker Containers in this example, creation of Database is quite easy while deploying the container. Here is what you need to do to initialize the Database.

  fluentmigrator.seed.postgres.db:
    image: postgres:14.1-alpine
    hostname: "fluentmigrator.seed.postgres.db"
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=Admin123
      - POSTGRES_DB=fmdemo
    ports:
      - '5432:5432'
    volumes:
      - fluentmigrator.seed.postgres.db.volume:/var/lib/postgresql/data
    networks:
      fluentmigrator.seed.postgres.network.common:

The POSTGRES_DB flag enables us to specify the Database which should be created by the container.

Creating Tables

As discussed earlier, we will be using FluentMigrator in our example. The first step is to ensure we have the necessary nuget packages added in our solution. Ensure all the mentioned packages are added to your solution.

FluentMigrator
FluentMigrator.Runner
FluentMigrator.Running.Postgres
Npgsql

So how do you define your migrations ? You do so by defining your migration classes, derieved from FluentMigrator.Migration classes. We will define the custom migration classes incrementally. This allows us to sort the migration files incrementally in the solution explorer. More importantly, we could use the same unique numerical Id as the identity of migration class, which is described by the MigrationAttribute.

For example, we could use a naming convension as MigrationYYYYmmddhhmm. Let us define a migration which would create a table User.

    [Migration(202301302120)]
    public class Migration202301302120 : Migration
    {
        public override void Down()
        {
            Delete.Table("User");
        }

        public override void Up()
        {
            Create.Table("User")
                .WithColumn("Id").AsGuid().NotNullable().Identity().PrimaryKey()
                .WithColumn("FirstName").AsString().NotNullable()
                .WithColumn("LastName").AsString();

        }
    }

Having defined your migration, the next step of course is to ensure it run. This can be ensured by adding it to the middleware pipelines. Let us go ahead and do so.

We will first add a migration runner to the services using the AddFluentMigrationCore() method. Furthermore, we will configure the running to use Postgres runner and use the connection string we specify. The migration runner is also configured to scan the current assembly for all availables migrations.

As an additional step, we will add a logger to ensure we could see the migration running exection logs.

var connectionString = builder.Configuration.GetConnectionString("DemoServer");
builder.Services.AddLogging(x => x.AddFluentMigratorConsole())
    .AddFluentMigratorCore()
    .ConfigureRunner(c => c.AddPostgres()
                        .WithGlobalConnectionString(connectionString)
                        .ScanIn((Assembly.GetExecutingAssembly())).For.Migrations());

The final step is to start the migration process, by calling the IMigrationRunner.Up() method. We will define an extension method IApplicationBuilder to make it more flexible.

    public static class MigrationsExtensions
    {
        public static IApplicationBuilder Migrate(this IApplicationBuilder app)
        {
            using var scope = app.ApplicationServices.CreateScope();
            var migrator = scope.ServiceProvider.GetService<IMigrationRunner>();
            migrator?.ListMigrations();
            migrator?.MigrateUp();
            return app;
        }
    }

We could invoke the extension now as the following.

app.Migrate();

So Seeding ?

So what about seeding ? We just created a table, we didn’t quite put in data yet. Of course we need define another migration (we could use the same one as well) to add seed data.

    [Migration(202301311807)]
    public class Migration202301311807 : Migration
    {
        public override void Down()
        {
            Delete.FromTable("User")
                .Row(new { FirstName = "Jia", LastName = "Anu" })
                .Row(new { FirstName = "Naina", LastName = "Anu" })
                .Row(new { FirstName = "Sreena", LastName = "Anu" })
                .Row(new { FirstName = "Anu", LastName = "Viswan" });
        }

        public override void Up()
        {
            Insert.IntoTable("User")
                .Row(new { FirstName = "Jia", LastName = "Anu" })
                .Row(new { FirstName = "Naina", LastName = "Anu" })
                .Row(new { FirstName = "Sreena", LastName = "Anu" })
                .Row(new { FirstName = "Anu", LastName = "Viswan" });
        }
    }

That’s all you need to seed your Postgres Database with FluentMigrator.