Migratus is an API and plugin for Leiningen that automatically migrates, and rolls back, our database. In a nutshell, it allows us to create a series of SQL scripts, which will be executed in order (based on filename) against our database. We can migrate a defined set of scripts, or all scripts. Conversely, we can roll back a defined set of scripts (however, there does not exist an option to rollback the whole lot).

The beauty of Migratus is we can commit our SQL files in version control and other developers will receive them when they perform an update (or if we get a new machine, or want to sync between a desktop and laptop, and the like). All they have to do to migrate their database after a git pull command, for example, is run lein migratus from their project root. Migratus will then run any migration files against a target database that have not yet been run. Done.

The remainder of this book will use Migratus to manage our database schema changes. As such, it might be useful to know how to actually get it.

Migratus comes in two forms: an API (which is useful for having migration scripts run as part of a start-up process if you're deploying) and a plugin for Leiningen.

To get the API, add it to the list of :dependencies in your project.clj file:

[migratus "0.7.0"]

To get the Leiningen plugin (which we will be using), add the following code to the list of :plugins in the hipstr project.clj file:

[migratus-lein "0.1.0"]

Running a simple lein deps command from the command line will download the new dependencies.

Now that we've added the Migratus plugin, we also need to configure it. After all, Migratus is not magical; it doesn't just work without a bit of guidance. Migratus' configuration, like most configurations, is thrown in hipstr's project.clj file under the keyword :migratus. The configuration contains three key pieces of information: what we're migrating (the :store—in our case, a database), where the migration scripts are kept (:migration-dir), and any configuration settings for the store in question. Add the following code to the hipstr project.clj file:

;…
:migratus {
  :store         :database
  :migration-dir "migrations"
  :migration-table-name "_migrations"
  :db            {:classname   "org.postgresql.Driver"
    :subprotocol "postgresql"
    :subname     "//localhost/postgres"
    :user        "hipstr"
    :password    "p455w0rd"}}
;…

Let's take a look at the Migratus configuration keys from the preceding code:

We can test this configuration by running the Migratus plugin. Type the following in your terminal from the root of your hipstr directory:

# lein migratus migrate

This will migrate any yet-to-be-run migration script inside our migrations directory, and emit information about the status of each migration. In our case, because we haven't written any migrations yet, we should see something similar to the following:

In the preceding output, we can see that Migratus created the _migrations table we configured to keep track of migrations. We're informed that no migrations were found. Let's fix this!

To create the database tables for our hipstr application, we'll write good old fashioned SQL scripts. For each table, we'll create an up script, which Migratus executes while migrating our database, and a down script, which Migratus executes when rolling back.

Create a migrations directory at src/migrations. Next, create your first Migratus script, and call it 00000000000100-users.up.sql, and save it in the src/migrations directory (all subsequent Migratus scripts that we write will go in this directory).

The content of the Migratus script is just SQL. We'll create a simple user table that captures the data from our signup form, along with a couple of timestamps. Add the following SQL to the users.up.sql script:

CREATE TABLE users
(user_id     SERIAL      NOT NULL PRIMARY KEY,
 username    VARCHAR(30) NOT NULL,
 email       VARCHAR(60),
 password    VARCHAR(100),
 created_at TIMESTAMP   NOT NULL DEFAULT (now() AT TIME ZONE 'utc'),
 updated_at TIMESTAMP   NOT NULL DEFAULT (now() AT TIME ZONE 'utc'));
--;;
-- create a function which simply sets the update_date column to the -- current date/time.

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now() AT TIME ZONE 'utc';
  RETURN NEW;
END
$$ language 'plpgsql';
--;;
-- create an update trigger which updates our updated_at column by -- calling the above function
CREATE TRIGGER update_user_updated_at BEFORE UPDATE
ON users FOR EACH ROW EXECUTE PROCEDURE
update_updated_at();

There are three statements in this script, each of which are separated using the special --;; delimiter, which Migratus uses to split statements. The first statement creates the users table; the second statement creates a function responsible for keeping the updated_at column current; the third statement creates a BEFORE UPDATE trigger to ensure that the current date is set on the update_at column.

We can test the script by heading back over to our terminal and executing another lein migratus migrate command. You should see output similar to the following screenshot:

By all accounts, the migration appears to have completed successfully. Let's fire up the PostgreSQL management shell again and take a look at what was created.

From the terminal, launch the psql tool the same way we did earlier in this chapter in the Creating the Database Schema section:

# psql -U hipstr -d postgres -h localhost

Type the command dt, which lists all the tables in the current schema:

postgres=> dt

You should see something like the following:

There you go! Our users table is in the hipstr schema! You can also issue a d users command, to describe the table.

In the previous section, we created an up migration script. The up scripts are executed whenever we perform a lein migratus migrate or lein migratus up command. However, we should also create a down script, which is used when we want to roll back the database.

Create another migration script with the same name as the users.up script, but this time call it 00000000000100-users.down.sql. Migratus looks for the down portion of the filename when deciding which scripts to execute when rolling back, so don't forget this!

In the users.up script, we created a table, a function, and a trigger. In the down script, we'll want to remove these objects, but in the reverse order (lest we produce ill-fated SQL errors as a result of attempting to drop objects that are depended upon). This is achieved using the following code:

DROP TRIGGER update_user_updated_at ON users;
--;;
DROP FUNCTION update_updated_at();
--;;
DROP TABLE users;

The preceding code drops the three objects we created in the appropriate order.

Running the down scripts

We can run the down scripts by running lein migratus down [list of migration script ids]. For example:

# lein migratus down 100

Migratus will attempt to execute each down script matching the list of IDs. Much like lein migratus migrate, Migratus will emit output informing us of the status of each rollback, similar to the following screenshot:

There currently does not exist an equivalent to Migratus' lein migratus migrate for running all down scripts, and thus we are forced to specify the ID of each down script we wish to execute. This is an unfortunate limitation, however Migratus is open source, so perhaps some generous Clojure whiz will be kind enough to write a lein migratus down all *cough*thanks*cough*.

Before we move forward and start actually interacting with the database from code, it would be worth considering how to migrate and roll back the database, both using Leiningen as well as programatically.

Adding migrations to the hipstr initialization

Adding migrations to the hisptr application is easy. We can have Migratus run any required migrations upon application startup by adding the appropriate call in our hipstr.handler/init location. Let's do it now:

1. Add a Migratus config to our hipstr.handler namespace (we will refactor this later, I promise). For now, just copy and paste the :migratus value from the project.clj file:

   (def migratus-config
     {:store :database
      :migration-dir "migrations"
      :migration-table-name "_migrations"
      :db {:classname "org.postgresql.Driver"
           :subprotocol "postgresql"
           :subname "//localhost/postgres"
           :user "hipstr"
           :password "p455w0rd"}})

2. Next, add the function that will perform the actual migration using migratus.core/migrate, marked as #1. We'll wrap it in a try/catch function and emit any errors in case the migration fails:

   (defn migrate-db []
     (timbre/info "checking migrations")
     (try
       (migratus.core/migrate migratus-config) ;#1
       (catch Exception e
         (timbre/error "Failed to migrate" e)))
     (timbre/info "finished migrations"))

3. Lastly, add a call to migrate-db in the hipstr.handler/init function, right before we call session-manager/cleanup-job:

   ;...snipped for brevity
   (migrate-db)
   (cronj/start! session-manager/cleanup-job)

That's it! Now start the app with a lein ring server command (or restart it if you already have it running) and watch the output. You should see something similar to the following:

In the preceding image, you can see that Migratus ran any required migrations as part of the hipstr initialization.