SQL Migrations

SQL migrations can be written in two ways: either as .sql files, or as .edn files.

edn

Basics

An edn migration file consists of one or more migration maps. A migration map consists of three keys:

• :id - a unique identifier for the migration
• :up - a vector of SQL statements that apply the migration
• :down - a vector of SQL statements that roll back the migration

So a basic migration file may contain:

[{:id   "create-table-foo"
  :up   ["CREATE TABLE foo (name VARCHAR(100))"]
  :down ["DROP TABLE foo"]}]

If there is only one migration in the file, the outer vector may be omitted. The :id key may also be omitted, in which case the ID will be taken from the filename. For example, a file create-table-foo.edn could contain:

{:up   ["CREATE TABLE foo (name VARCHAR(100))"]
 :down ["DROP TABLE foo"]}

:do notation

These sorts of migrations are common, and since version 0.10.0, Ragtime provides a further advance in the form of the :do key:

[{:id "create-table-foo"
  :do [[:create-table foo [name "VARCHAR(100)"]]]}]

The :do key contains a collection of vectors that represent reversible commands. The above example is equivalent to the previous examples that used :up and :down.

You can also omit the migration map entirely:

[[:create-table foo [name "VARCHAR(100)"]]]

For migrations defined solely by a vector, the :id is generated from the command. In the above case, it would be create-table-foo - again, equivalent to all our previous examples.

Ragtime currently supports the following commands for the :do key:

command	description
[:create-table name & columns]	Create a table with the supplied name and columns. Each column is a vector in the form: [name definition]
[:drop-table name]	Drop the table with the supplied name.
[:add-column table name definition]	Add a new column to an existing table with the supplied name and definition.
[:rename-column table old-name new-name]	Rename an existing column in an existing table.
[:drop-column table name]	Drop an existing column on a table with the supplied name.
[:create-index index-name table columns]	Create a new index with the supplied name given a table and a vector of column names.
[:drop-index index-name]	Drop an existing index.

Mixing :do and :up/:down

It's possible to have some migrations that use :do and some migrations that use :up and :down instead. This can be necessary if more complex migrations are required. However, be careful about mixing them, as the :drop-* commands rely on searching for their equivalent :create-* commands in order to determine how to generate their rollback SQL.

For example:

[[:create-table foo [name "VARCHAR(100)"]]
 [:drop-table foo]]

Will produce:

[{:id   "create-table-foo"
  :up   ["CREATE TABLE foo (name VARCHAR(100))"]
  :down ["DROP TABLE foo"]}
 {:id   "drop-table-foo"
  :up   ["DROP TABLE foo"]
  :down ["CREATE TABLE foo (name VARCHAR(100))"]}]

Note that in order to generate the :down key of the second migration, we need to know the original definition of the table. Ragtime is smart enough to account for column changes, but can't parse custom SQL. This means a migration file like this will fail:

[{:id   "create-table-foo"
  :up   ["CREATE TABLE foo (name VARCHAR(100))"]
  :down ["DROP TABLE foo"]}
 [:drop-table foo]]   ;; will fail, as it doesn't know how to recreate the table

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 files 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.