north: Database Migrations🔗ℹ

1 Introduction🔗ℹ

north is a database migration tool written in Racket.

1.1 Features🔗ℹ

• Programming language agnostic.

• SQL-based DSL for writing schema migrations.

• PostgreSQL and SQLite support.

• Migrations are each run inside individual transactions.

• Migrations have a strict ordering based on revision ids. Individual migration files can have arbitrary names, which can be changed at any point.

• All operations perform dry runs by default, resulting in executable SQL that you can send to your DBA for approval.

2 Installation🔗ℹ

Assuming you’ve already installed Racket, run the following command to install north.

  $ raco pkg install north

3 Tutorial🔗ℹ

For the purposes of this tutorial, we’re going to export a DATABASE_URL environment variable that will be implictly used by each of the commands that follow:

  $ export DATABASE_URL=sqlite:db.sqlite

This tells north to execute operations against an SQLite database located in the current working directory called "db.sqlite". To use PostgreSQL instead of SQLite, you can provide a database URL that looks like this instead:

By default, north looks for migrations inside a folder called "migrations" in the current working directory, so you have to create that folder before moving on:

  $ mkdir migrations

Let’s try creating our first migration:

  $ raco north create add-users-table

Running the above command will create a new SQL file inside your "migrations" folder with the suffix -add-users-table. Open it up in your favorite text editor and you should see something along these lines:

The revision number in your migration will be different to what’s shown here.

#lang north

-- @revision: 2f00470a20a53ff3f3b12b79a005070e

-- @description: Creates some table.

-- @up {

create table example();

-- }

-- @down {

drop table example;

-- }

The #lang north line declares that this is a north migration. These types of files are made up of definitions where each definition is a line starting with -- followed by an @ sign and the name of the binding being defined followed by either a colon or an open bracket. If the name is followed by a colon then everything after that colon until the end of the line represents the string value of that binding. If the name is followed by an open bracket, then everything between the start of the next line and the next occurrence of -- } represents the string value of that binding.

The reason this syntax was chosen is because it is compatible with standard SQL syntax. Each -- line is just a comment in SQL. This makes it easy to use whatever text editor you prefer to edit these files since most editors come with some sort of a SQL mode.

This particular migration has a revision id of "2f00470a20a53ff3f3b12b79a005070e", a description and up and down scripts. The up scripts are run when applying a migration and the down scripts are run when rolling back a migration. The down scripts are optional.

Change the up script so it creates a new table named users:

-- @up {

create table users(

id serial primary key,

username text not null unique,

password_hash text not null,

constraint users_username_is_lowercase check(username = lower(username))

);

-- }

And the down script so it drops the table:

-- @down {

drop table users;

-- }

If we tell north to migrate the database now, it’ll display the pending migrations, but will not modify the database:

  $ raco north migrate

Note how the dry run output is valid SQL.

As noted, this output represents a dry run. The database was not actually modified. Unless we explicitly pass the -f flag to the raco north migrate command, none of the pending changes will be applied.

Let’s force it to migrate the DB:

  $ raco north migrate -f

If you inspect the schema of your users table now it should look like this:

  $ echo ".schema users" | sqlite3 db.sqlite

Next, add a last-login column to the users table:

  $ raco north create add-last-login-column

The new migration should contain content that looks like this:

The revision and parent numbers in your migration will be different to what’s shown here.

#lang north

-- @revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- @parent: 2f00470a20a53ff3f3b12b79a005070e

-- @description: Alters some table.

-- @up {

alter table example add column created_at timestamp not null default now();

-- }

-- @down {

alter table example drop column created_at;

-- }

Not much different from the first migration, but note the introduction of the parent binding. This tells north that this migration follows the previous one (its parent).

Alter its up script:

-- @up {

alter table users add column last_login timestamp;

-- }

And remove its down script since SQLite does not support dropping columns.

If we call migrate now, we’ll get the following output:

Apply that migration:

  $ raco north migrate -f

Never roll back a production database. It will almost always result in some kind of data loss. This command is intended for testing & local development only.

To roll back the last migration we can run:

  $ raco north rollback

Of course, there’s not much point in doing that since our last revision doesn’t contain a down script. We can tell rollback which revision it should roll back to and we can even tell it to roll back all the way back to before the first revision:

  $ raco north rollback base

If we force a full rollback then all our changes to the database will be dropped.

  $ raco north rollback -f base

And that’s pretty much it. There are a couple other commands, but you can find out about them by running:

  $ raco north help

4 Reference🔗ℹ

4.1 Database URLs🔗ℹ

Database URLs, whether provided via the DATABASE_URL environment variable or the -u flags to the raco north migrate and raco north rollback commands, must follow this format:

The protocol must be either sqlite or postgres.

The sslmode parameter only applies to PostgreSQL connections. When not provided, the default is disable. Its values have the following meanings:

• prefer – try TLS and fall back to plain text if it’s not available,

• require – fail if a TLS connection cannot be established,

• disable – don’t attempt to use TLS.