Database Migrations

[jhbertra]

Here is a summary of some of the options for managing database migrations.

Haskell Frameworks

persistent-migration

Manual migrations for the persistent library. Allows manual migrations that are not possible with the built-in migrations offered by persistent.

Benefits:

• Can be run on Application start, does not require a CLI
• Migrations are defined in Haskell

Drawbacks:

• Limits us to using persistent, which has a number of its own drawbacks (use of TH is a big drawback)

beam migration framework

The migrations framework from the beam ecosystem.

Benefits

• Same as persistent-migration

Drawbacks

• Limits us to using beam
• Documentation is incomplete

Groundhog

A Haskell ORM-like framework that supports migrations

Benefits

• Same as others

Drawbacks

• limits us to using groundhog, which is like a less-popular persistent that doesn't support joins...

External Tools

Sqitch

A CLI tool for managing database migrations using SQL scripts. Uses a git-like interface for managing migrations. https://sqitch.org/docs/manual/sqitchtutorial/

Benefits

• Very powerful
• Open source
• Extremely well documented

Drawbacks

• Learning curve
• External tool - migrations have to be managed in the deployment

Flyway

A relatively simple CLI tool for managing database migrations using SQL scripts.

Benefits

• Quite simple

Drawbacks

• Cannot rollback migrations
• External tool

Liquibase

A powerful tool for managing database migrations using a changelog file

Benefits

• Very powerful
• Supports rollbacks
• Extensive documentation

Drawbacks

• Cannot use plain SQL, migrations are written in XML, JSON, YAML, or an annotated SQL dialect.

What Do I Recommend?

I think we really should go with an external tool for managing migrations and schema changes. The Haskell ecosystem unfortunately does not offer a solution that comes even close to the maturity of these solutions. They are all tied to a framework that we may not want to use (persistent, groundhog), lacking documentation (beam), or lacking in functionality and flexibility (all of the above).

I am personally leaning towards sqitch, as it has the best documentation, a robust workflow that lends its self well to automation, and useful features like rollbacks, dependency specification, verification, and migration rebasing. Plus, you get to write migrations in plain SQL.

[bwbush]

FYI (and definitely not a recommendation), here is how cardano-db-sync handles migration: https://github.com/input-output-hk/cardano-db-sync/tree/master/schema. Historically, the migration scripts have failed about 25% of the time when I ran them for mainnet.

[jhbertra]

What do they use to run and manage these migrations?

[jhbertra]

sqitch also uses SQL scripts for migrations. However, it is explicit about dependencies, it provides mechanisms for rollbacks and conflict resolution (rebasing the migration plan), and it provides verification tests that check the migration applied successfully.

[bwbush]

What do they use to run and manage these migrations?

nix+bash

[bwbush]

I like sqitch because of its explicitness about dependencies and its being an external tool that doesn't impose a constraint on Haskell design and packages.

We're likely to have a stable schema, so I don't think we need the most sophisticated migration tools. However, an important conceptual test case for this design is when migrating an old databases (say, five schema versions back) to the latest schema.

[jhbertra]

I think an using external tool gives us the most flexibility. It also means we don't need to invent some hacky in-house migrations framework, which would be time-consuming and produce an inferior result...