Clojure SQL Migration Libaries

It is generally recommended, if you’re developing an application that uses a database, that you manage changes to the schema of said database using some sort of migration system or another. Luckily, several intrepid developers in the clojure community have stepped up with their own solutions to this problem – all of which end up being very similar indeed. Read on for an overview of your options and to find out which one is right for you.

What is a migration libary anyhow? Modern datastore migration libraries tend to have a few tasks:

• Manage a list of migrations, each with up and down phases.
• Maintain some knowledge of the current database state.
• Offer the ability to up- or down-grade the database state to that of any migration in the chain, using the up and down phases as appropriate.

The four active-est migration libraries on Clojure can be hard to tell apart, but the best way to do so is to examine what differences exist. For the purposes of comparison, I’ll be sticking to just JDBC migrations. Chances are, if you use a more esoteric datastore than that, you won’t have the luxury of choice. The libraries we’ll be looking at are:

• Ragtime
• Migratus
• Drift
• Clj-sql-up

(I was going to look at Lobos, too, but the project seems to be dead). Another library you might come across is Joplin, which seems to be in working order but is built directly on top of Ragtime, and doesn’t offer much differentiation for the basic use case that we’ll be covering.

Managing the list of migrations

All of these libraries adopt the same general strategy: Pick a directory for migrations to live in, and create migration files in that directory. Writing the up and down SQL is left to the user.

For Ragtime, the file may be either a pair of .up.sql and .down.sql files (in SQL format) or a single EDN file defining the up and down migrations in the following format:

{:up   ["CREATE TABLE foo (name VARCHAR(100))"
        "INSERT INTO foo VALUES ('alice'), ('bob')"]
 :down ["DROP TABLE foo"]}

Migratus is similar, but only supports the .up.sql/.down.sql scheme.

Drift does more-or-less the same thing, except each migration is a clojure file containing up and down functions, which are expected to do the actual work. This is handy if you use an SQL Library that is capable of things like creating and altering tables, but might just end up with you running strings through core.jdbc anyhow.

Clj-sql-up migration files are also clojure files that export up and down functions. However, the functions are expected to return vectors of raw SQL strings, as in Ragtime’s EDN format:

(defn up []
  ["CREATE TABLE foo (name VARCHAR(100))"
        "INSERT INTO foo VALUES ('alice'), ('bob')"])

(defn down []
  ["DROP TABLE foo"])

Remembering the current migration state

Ragtime JDBC creates and uses a ragtime_migrations table in your database to maintain a log of migrations being applied. Likewise with Migratus and clj-sql-up, using tables called schema_migrations and clj_sql_migrations respectively instead.

Drift punts on this problem, instead leaving it up to you to persist the current migration id within the configuration that you give it. However, the github repository includes instructions for doing the above.

All of these libraries will use a yyyyMMddHHmmss-formatted timestamp as the migration identifier by default, although Drift will let you configure your own generator function.

Running migrations

In most projects, you won’t be using the Clojure API to run these migrations – you’ll likely instead use a Boot or Leiningen plugin to do so. So, let’s examine the state of those.

Ragtime offers fine support for both – an official semi-DIY solution for leiningen, and for boot, boot-ragtime. I’ve used the latter and can vouch that it does the job. It does not, however, provide an easy facility to reset to a particular state, only to migrate fully or roll back one at a time. The Boot version also lets you list upcoming migrations and generate bare sql files.

Migratus has a full-featured Leiningen plugin with support for those two functions, as well as generating migration files, running migrations up or down to an arbitrary state, and resetting the database.

Drift has a leiningen plugin that will simply migrate to the migration number that you give it (defaulting to the latest). lein migrate -version 0 undoes all migrations.

Clj-sql-up has a basic leiningen plugin that handles applying, rolling back, and generating migrations.

Here are the various plugins’ features in table form:

Conclusion

All of these libraries are fine choices. The main distinction is the format of the migration files, so you should use whatever fits your sensibilites best there (unless your project uses boot, then use Ragtime).

Hey you! Share this article via Twitter or Reddit