north: Database Migrations

7.7

top ← prev up next →

north: Database Migrations

1 Introduction

north is a database migration tool written in Racket. It helps you keep your database schema in sync across all your environments.

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.
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 to be used with each of the following commands.

$ export DATABASE_URL=sqlite:db.sqlite

This tells north to execute operations against an SQLite database located in the current directory called "db.sqlite". DATABASE_URL must have the following format:

protocol://[username[:password]@]hostname[:port]/database_name

Assuming you wanted to use PostgreSQL instead of SQLite, your URL would look something like this:

postgres://example@127.0.0.1/example

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

$ mkdir migrations

Now, let’s try creating our first migration:

$ raco north create add-users-table

That should 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:

#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-style 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 a 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 in SQL is just a comment. 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. up scripts are run when applying a migration and down scripts are run when rolling back a migration. down scripts are optional.

Let’s 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 spit out a dry run of the pending migrations:

$ raco north migrate

Note how the dry run output is valid SQL.

-- Current revision: base

-- Target revision: 2f00470a20a53ff3f3b12b79a005070e

-- Applying revision: base

-- Applying revision: 2f00470a20a53ff3f3b12b79a005070e

-- Revision: 2f00470a20a53ff3f3b12b79a005070e

-- Parent: base

-- Path: /Users/bogdan/migrations/20190128-add-users-table.sql

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))

);

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

Let’s force it to migrate the DB:

$ raco north migrate -f

Current revision: base

Target revision: 2f00470a20a53ff3f3b12b79a005070e

Applying revision: base

Applying revision: 2f00470a20a53ff3f3b12b79a005070e

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

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

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))

);

Next, let’s 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:

#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 first one (its parent).

Let’s update 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:

-- Current revision: 2f00470a20a53ff3f3b12b79a005070e

-- Target revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Applying revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Parent: 2f00470a20a53ff3f3b12b79a005070e

-- Path: /Users/bogdan/migrations/20190128-add-last-login-column.sql

alter table users add column last_login timestamp;

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 local development only.

If we wanted to roll back the last migration we could run:

$ raco north rollback

-- WARNING: Never roll back a production database!

-- Current revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Target revision: 2f00470a20a53ff3f3b12b79a005070e

-- Rolling back revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Parent: 2f00470a20a53ff3f3b12b79a005070e

-- Path: /Users/bogdan/migrations/20190128-add-last-login-column.sql

-- no content --

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

-- WARNING: Never roll back a production database!

-- Current revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Target revision: base

-- Rolling back revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Revision: 91dc39c84aa496e5e0fda2d5a947eea3

-- Parent: 2f00470a20a53ff3f3b12b79a005070e

-- Path: /Users/bogdan/migrations/20190128-add-last-login-column.sql

-- no content --

-- Rolling back revision: 2f00470a20a53ff3f3b12b79a005070e

-- Revision: 2f00470a20a53ff3f3b12b79a005070e

-- Parent: base

-- Path: /Users/bogdan/migrations/20190128-add-users-table.sql

drop table users;

-- Rolling back revision: base

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

$ raco north rollback -f base

WARNING: Never roll back a production database!

Current revision: 91dc39c84aa496e5e0fda2d5a947eea3

Target revision: base

Rolling back revision: 91dc39c84aa496e5e0fda2d5a947eea3

Rolling back revision: 2f00470a20a53ff3f3b12b79a005070e

Rolling back revision: 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

top ← prev up next →