Jonathan Banashek

F# PostgreSQL migrations using FluentMigrator on Heroku

Why Heroku?

On my latest Suave project, I decided that instead of dealing with managing my deployments with docker-compose (and some of the complexity that comes with getting a robust production system in place), I'd try out a PaaS solution.

For a Suave application, it looks like my options were Azure or Heroku. For most of the CRUD applications I write, I like to have a custom domain name, SSL, and a database so that I can have my users sign in securely (Most applications I want to write have users in it somewhere).

On Azure, the minimum price for a B1 App Service (custom domain and ssl) and the basic tier of AzureDb is $60/month. On Heroku, the Hobby tier with the Hobby postgres is $7/month. Even if I didn't have previous bad experience with Azure support, the cost difference for a project I'm just feeling out is a no brainer.

Figuring out database migrations was probably the most tricky part of getting set up on heroku, and was more because of the choice and documentation of F# options rather than anything to do with heroku itself.

The problem

I've worked as a Build/Release engineer and as a DevOps engineer previous to working in development, and have had experience with a few different database versioning techniques.

The problem that a database migration system attempts to solve is that of managing the schema of the application'so database in a reliable manner while providing features to ease development.

This can include, but is not limited to:

All changes are tracked in version control
Changes are reversible in the case that a change is incorrect or breaks something
A specific set of changes can be created for certain environments (test, integration, production)

Options for handling database migrations

When researching the options for this, there seemed to be 3 types:

.Net solutions (FluentMigrator, EF migrations)
Using another language project just for the migrations tool (ActiveRecord Migrations, node-pg-migrate, LiquiBase)
Using a sql file based approach (sqitch, custom solution)

I have the most experience with #3, but really wanted to use #1.

Setting up FluentMigrator

I chose FluentMigrator partly because I've enjoyed most Fluent-anything libraries I've used in C#, but mostly because it had a FAKE Helper.

There isn't much documentation for the FAKE-fluentmigrations other than a sample which didn't use PostgreSQL and didn't match up with how I wanted to run the migrations.

The first thing that needs to be done is to add the required packages to your project. If you're using PostgreSQL, then this is: FAKE.FluentMigrator, FluentMigrator, Npgsql, and FluentMigrator.Tools

Step two, create the migrations. I do this under a /db/migrations/ folder.

CreateUsersTable.cs

using FluentMigrator;

namespace MyProj
{
    [Migration(1, "Create users table")]	
    public class CreateUsersTable : AutoReversingMigration
    {
        public override void Up()
        {
            Create.Table("Users")
                .WithColumn("Id").AsInt32().NotNullable().PrimaryKey().Identity()
                .WithColumn("Username").AsString()
                .WithColumn("Email").AsString()
                .WithColumn("EmailConfirmed").AsBoolean().NotNullable().WithDefaultValue(false);
        }
    }
}

The migrations are created as C# files. After creating this file, add the file to the project below the other project files with a line like this:
<None Include="db/migrations/CreateUsersTable.cs" />

Documentation for how these migrations work can be found in the FluentMigrator documentation wiki here.

Setting up the FAKE target

Setting up the FAKE target involves a few steps:

Reference the required libraries
Configure the values needed for the CscHelper (to compile our C# to a dll for FAKE.FluentMigrator to use)
Create a compile function to compile the C# files to a dll
Convert the DATABASE_URL environment variable to the appropriate SQL string for FluentMigrator
Create the FAKE target to run the migrations

Referencing the required libraries

#r "./packages/FAKE.FluentMigrator/tools/Fake.FluentMigrator.dll"
#r "./packages/FAKE.FluentMigrator/tools/FluentMigrator.Runner.dll"
#r "./packages/Npgsql/lib/net45/Npgsql.dll"
open Npgsql
open Fake.CscHelper
open Fake.FluentMigratorHelper

Configure the values needed for compiling C# files

For me this looks like this:

let rootDir = __SOURCE_DIRECTORY__
let fluentMigratorDll = rootDir @@ "packages/FluentMigrator/lib/40/FluentMigrator.dll"
let migrationsAssemblyOut = rootDir @@ "MyProj.migrations.dll"
let migrations = [
  rootDir @@ "db/migrations/CreateUsersTable.cs"
]
let options = DefaultMigrationOptions

Create a compile function to compile the C# files to a dll

let compile() =
  migrations |> Csc (fun p -> {p with References = [fluentMigratorDll]; Output = migrationsAssemblyOut; Target = Library; Debug = true})

Convert the `DATABASE_URL` environment variable to the appropriate SQL string for FluentMigrator

I figured out the scheme of the connection URL expected by FluentMigrator by looking here. I keep a dburi environment variable on local for this project. This can be improved but this was the minimum I did to get it to work (it was late at night =P).

let connection =
  let postgresdburl = Environment.GetEnvironmentVariable("DATABASE_URL")
  if postgresdburl <> null then
    let m = Regex.Match(postgresdburl, "postgres://(.*):(.*)@(.*):(.*)/(.*)")
    let cstring =
      "Server="   + m.Groups.[3].Value + ";" +
      "Port="     + m.Groups.[4].Value + ";" + 
      "Database=" + m.Groups.[5].Value + ";" +
      "User Id="  + m.Groups.[1].Value + ";" +
      "Password=" + m.Groups.[2].Value + ";"
    ConnectionString(cstring, PostgreSQL)
  else
    ConnectionString(Environment.GetEnvironmentVariable("dburi"), PostgreSQL)

Create the FAKE target to run the migrations

There are multiple targets available. I just use MigrateToLatest here.

// Migration Targets
Target "MigrateLatest" (fun _ ->
  compile()
  MigrateToLatest connection [migrationsAssemblyOut] options
)

How to run on local

./build.sh MigrateLatest

How to run on a heroku deployment


web master % heroku maintenance:on
web master % heroku run ./build.sh MigrateLatest
web master % heroku maintenance:off

Wrapping up and troubleshooting tips

With this configured we can run migrations and keep our application's database schema up to date in a robust manner.

I ran into quite a few errors while trying to get this work. If you get errors when compiling the C# code, double check that all references are added and in their correct locations.

The Debug = true parameter given to the FAKE.CscHelper will show you the actual mcs (mono c# handler) command that was run. If you copy and paste this into your terminal you can get more detailed errors that help with C# specific problems.

With errors regarding the postgres connection, again make sure that all the required libraries are referenced correctly, and that the PostgreSQL connection string matches what FluentMigrator is expecting.

Thanks

Thanks to everyone who worked on FluentMigrator and the FAKE helper!