SQL Migrations

SQL migrations can be written in two ways.

edn

The first way is to create a .edn file that contains a map that has :up and :down keys. Both keys should contain an ordered collection of SQL statements.

For example, we might create a file resources/migrations/001-create-foo.edn:

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

SQL

Alternatively, we can write the SQL directly. The "up" part of the migration will go into a file resources/migrations/001-create-foo.up.sql:

CREATE TABLE foo (name VARCHAR(100));
INSERT INTO foo VALUES ('alice'), ('bob');

And the "down" part of the migration will go in resources/migrations/001-create-foo.down.sql:

DROP TABLE foo;

Ragtime uses the extension (.up.sql or .down.sql) to determine whether the file represents the "up" or "down" part of the migration.

However, different SQL databases have different restrictions on the commands that can be sent in a single message, so sometimes we need to split up a migration in order to get the database to accept it. This can be done in two ways, the first being to insert a marker ;;-- between statements. For example:

CREATE TABLE foo (name VARCHAR(100));
;;--
INSERT INTO foo VALUES ('alice'), ('bob');

The second way is to split the migration file into several, numbered to indicate the order they should be applied. For example, we could split up 001-create-foo.up.sql into 001-create-foo.up.1.sql:

CREATE TABLE foo (name VARCHAR(100));

And 001-create foo.up.2.sql:

INSERT INTO foo VALUES ('alice'), ('bob');

When splitting up a migration like this, the files should end with up.<n>.sql or down.<n>.sql, where <n> is sequence of numeric digits. Note that alphanumeric ordering is used, so if a single migration has more than 9 parts, then start off with 01, 02, etc.