cl-migratum is a Common Lisp system, which provides facilities for performing database schema migrations.

• Quicklisp

Clone the cl-migratum repo in your Quicklisp local-projects directory.

git clone https://github.com/dnaeon/cl-migratum.git

Load the system.

CL-USER> (ql:quickload :cl-migratum)

The cl-migratum system uses the following concepts to describe the process of discovering and applying schema migrations, so it is important that you get familiar with them first.

A migration represents a resource that provides information about a schema change, e.g. it provides the unique id of the change, the required scripts that can be used to upgrade and/or downgrade the database.

Migration resources are discovered via providers and are being used by drivers during the process of upgrade/downgrade of the schema.

The provider is responsible for discovering migration resources.

For example a provider can discover migrations from local path by scanning files that match a given pattern or fetch migrations from a remote endpoint (e.g. HTTP service).

The provider is also responsible for creating new migration sequences.

The following providers are supported by cl-migratum.

The driver carries out the communication with the database against which schema changes will be applied.

It is responsible for applying schema changes, registering successfully applied migrations and unregistering them when reverting back to a previous state.

A driver uses a provider in order to discover migrations, which can be applied against the database it is connected to.

The following drivers are supported by cl-migratum.

The following section contains some examples to get you started.

First, we will create a new provider that can discover migration files from a local path. In order to create a local-path provider we need to load the system for the respective provider.

CL-USER> (ql:quickload :cl-migratum.provider.local-path)

Once you load the system we can create a local-path provider. The local-path provider can discover migrations from multiple paths.

Typical example where having multiple paths with migration resources might be useful is when you have a set of base migrations you want to apply to all environments, and then have a separate path for each environment with their own migrations. For example you might want to run specific migrations only in your dev environment, but don't want them in your production environment.

In that case it makes sense to separate the migration resources in multiple paths, for each environment respectively.

CL-USER> (defparameter *provider*
           (migratum.provider.local-path:make-local-path-provider (list #P"~/Projects/lisp/cl-migratum/t/migrations/")))
*PROVIDER*

The LOCAL-PATH-PROVIDER discovers migration files which match the following pattern.

A provider can optionally be initialized, which can be done using the MIGRATUM:PROVIDER-INIT generic function. Not all providers would require initialization, but some will and therefore it is good that you always initialize them first.

In order to list the migrations provided by a provider you can use the MIGRATUM:PROVIDER-LIST-MIGRATIONS generic function, e.g.

CL-USER> (migratum:provider-list-migrations *provider*)
(#<CL-MIGRATUM.PROVIDER.LOCAL-PATH:LOCAL-PATH-MIGRATION {100527E5D3}>
 #<CL-MIGRATUM.PROVIDER.LOCAL-PATH:LOCAL-PATH-MIGRATION {100527E673}>
 #<CL-MIGRATUM.PROVIDER.LOCAL-PATH:LOCAL-PATH-MIGRATION {100527E713}>)

The following generic functions can be used to interact with discovered migrations.

For example in order to collect the unique IDs of migration resources you can evaluate the following expression.

CL-USER> (mapcar #'migratum:migration-id
                 (migratum:provider-list-migrations *provider*))
(20200421180337 20200421173908 20200421173657)

Once we have a provider for discovering migration resources we need to create a driver, which can be used to communicate with the database we want to apply migrations on.

Here is how we can create a dbi driver. First, lets load the system, which provides that driver.

CL-USER> (ql:quickload :cl-migratum.driver.dbi)

The dbi driver uses CL-DBI interface to communicate with the database, so we will need to create a database connection.

CL-USER> (defparameter *conn*
           (dbi:connect :sqlite3 :database-name "/Users/dnaeon/cl-migratum.db"))
*CONN*

And now we can instantiate our SQL driver using the previously created provider and connection.

CL-USER> (defparameter *driver*
           (migratum.driver.dbi:make-driver *provider* *conn*))
*DRIVER*

The process to create an rdbms-postgresql driver is analogous. The driver needs a connection specification to create a DB connection. In this example we use the same environment variables which are used by the PostgreSQL client psql to override the defaults:

(ql:quickload :cl-migratum.driver.rdbms-postgresql)
(defparameter *driver*
  (migratum.driver.rdbms-postgresql:make-driver
   *provider*
   `(:host ,(or (uiop:getenv "PGHOST") "localhost")
     :database ,(or (uiop:getenv "PGDATABASE") "migratum")
     :user-name ,(or (uiop:getenv "PGUSER") "migratum")
     :password ,(or (uiop:getenv "PGPASSWORD") "tiger"))))

A driver and provider may require some initialization steps to be executed before being able to apply migrations, so make sure that you initialize them.

In order to initialize your provider use the MIGRATUM:PROVIDER-INIT function.

CL-USER> (migratum:provider-init *provider*)

An example requirement for a driver might be to create some required database schema used to track which migrations have been applied already, so lets initialize our driver first.

CL-USER> (migratum:driver-init *driver*)

In order to get the list of pending (not applied yet) migrations we can use the MIGRATUM:LIST-PENDING function, e.g.

CL-USER> (migratum:list-pending *driver*)
(#<CL-MIGRATUM.PROVIDER.LOCAL-PATH:LOCAL-PATH-MIGRATION {10052A0583}>
 #<CL-MIGRATUM.PROVIDER.LOCAL-PATH:LOCAL-PATH-MIGRATION {10052A0623}>
 #<CL-MIGRATUM.PROVIDER.LOCAL-PATH:LOCAL-PATH-MIGRATION {10052A06C3}>)

Or, we can display a table of the pending migrations using the MIGRATUM:DISPLAY-PENDING function instead.

CL-USER> (migratum:display-pending *driver*)
.-----------------------------------.
|        PENDING MIGRATIONS         |
+----------------+------------------+
| ID             | DESCRIPTION      |
+----------------+------------------+
| 20200421173657 | create_table_foo |
| 20200421173908 | create_table_bar |
| 20200421180337 | create_table_qux |
+----------------+------------------+
| TOTAL          |                3 |
+----------------+------------------+
NIL

The migrations will be sorted in the order they need to be applied.

The following functions are used for applying pending migrations.

• MIGRATUM:APPLY-PENDING - applies all pending migrations
• MIGRATUM:APPLY-NEXT - applies the next pending migration

This is how we can apply all pending migrations for example.

CL-USER> (migratum:apply-pending *driver*)
 <INFO> [18:10:14] cl-migratum.core core.lisp (apply-pending) -
  Found 3 pending migration(s) to be applied
 <INFO> [18:10:14] cl-migratum.core core.lisp (apply-and-register) -
  Applying migration 20200421173657 - create_table_foo
 <INFO> [18:10:14] cl-migratum.core core.lisp (apply-and-register) -
  Applying migration 20200421173908 - create_table_bar
 <INFO> [18:10:14] cl-migratum.core core.lisp (apply-and-register) -
  Applying migration 20200421180337 - create_table_qux
NIL

You can use the MIGRATUM:LATEST-MIGRATION function to get the latest applied migration, e.g.

CL-USER> (migratum:migration-id (migratum:latest-migration *driver*))
20200421180337

The MIGRATUM:CONTAINS-APPLIED-MIGRATIONS-P predicate can be used to query whether there are any migrations applied, e.g.

CL-USER> (migratum:contains-applied-migrations-p *driver*)
T

The following functions can be used to get and display the list of applied database migrations.

This is how we can get the list of applied migrations.

CL-USER> (migratum:driver-list-applied *driver*)
(#<CL-MIGRATUM.CORE:MIGRATION {1006095B23}>
 #<CL-MIGRATUM.CORE:MIGRATION {1006095B73}>
 #<CL-MIGRATUM.CORE:MIGRATION {1006095BC3}>)

Or we can display a nice table of the applied migrations instead.

CL-USER> (migratum:display-applied *driver*)
.---------------------------------------------------------.
|                   APPLIED MIGRATIONS                    |
+----------------+------------------+---------------------+
| ID             | DESCRIPTION      | APPLIED             |
+----------------+------------------+---------------------+
| 20200421180337 | create_table_qux | 2020-04-21 15:17:46 |
| 20200421173908 | create_table_bar | 2020-04-21 15:14:13 |
| 20200421173657 | create_table_foo | 2020-04-21 15:12:52 |
+----------------+------------------+---------------------+
|                | TOTAL            |                   3 |
+----------------+------------------+---------------------+
NIL

The output of these functions will be the applied migrations in descending order by their id, first one being the most recent one.

The dbi and rdbms-postgresql drivers by default will fetch the last 100 applied migrations. You can control this behaviour by using the :offset and :limit keyword parameters, which allows you to fetch applied migrations in pages.

For example, if you are interested only in the last ten applied migrations you can evaluate the following expression.

CL-USER> (migratum:display-applied *driver* :limit 10)

Or if you want to skip the first ten migrations, you can evaluate this expression instead.

CL-USER> (migratum.display-applied *driver* :offset 10 :limit 10)

Using the following functions you can step through migrations.

This is useful in situations when you don't want to apply all migrations at once, but rather do it one at a time. Both of these functions accept a keyword parameter :count which specifies the number of migrations to apply or revert.

Consider the following pending migrations.

CL-USER> (migratum:display-pending *driver*)
.-----------------------------------.
|        PENDING MIGRATIONS         |
+----------------+------------------+
| ID             | DESCRIPTION      |
+----------------+------------------+
| 20200421173657 | create_table_foo |
| 20200421173908 | create_table_bar |
| 20200421180337 | create_table_qux |
+----------------+------------------+
| TOTAL          |                3 |
+----------------+------------------+
NIL

We can apply them one by one and verify them as we go.

CL-USER> (migratum:apply-next *driver*)
 <INFO> [20:04:25] cl-migratum.core core.lisp (apply-and-register) -
  Applying migration 20200421173657 - create_table_foo
NIL
CL-USER> (migratum:apply-next *driver*)
 <INFO> [20:04:28] cl-migratum.core core.lisp (apply-and-register) -
  Applying migration 20200421173908 - create_table_bar
NIL
CL-USER> (migratum:apply-next *driver*)
 <INFO> [20:04:29] cl-migratum.core core.lisp (apply-and-register) -
  Applying migration 20200421180337 - create_table_qux
NIL

If we want to revert the last three migrations we can use the MIGRATUM:REVERT-LAST function, e.g.

CL-USER> (migratum:revert-last *driver* :count 3)
 <INFO> [20:06:00] cl-migratum.core core.lisp (revert-and-unregister) -
  Reverting migration 20200421180337 - create_table_qux
 <INFO> [20:06:00] cl-migratum.core core.lisp (revert-and-unregister) -
  Reverting migration 20200421173908 - create_table_bar
 <INFO> [20:06:00] cl-migratum.core core.lisp (revert-and-unregister) -
  Reverting migration 20200421173657 - create_table_foo
NIL

The MIGRATUM:PROVIDER-CREATE-MIGRATION generic function creates a new migration sequence, when supported by the provider that you are using.

Here's one example of creating a new migration, which also specifies the upgrade and downgrade scripts as part of the keyword parameters.

CL-USER> (defparameter *migration*
           (migratum:provider-create-migration *provider*
                                               :description "create table fubar"
                                               :up "CREATE TABLE fubar (id INTEGER PRIMARY KEY);"
                                               :down "DROP TABLE fubar;"))
*MIGRATION*

We can also inspect the newly created migration, e.g.

CL-USER> (migratum:migration-id *migration*)
20200421201406
CL-USER> (migratum:migration-description *migration*)
"create_table_fubar"
CL-USER> (migratum:migration-load-up-script *migration*)
"CREATE TABLE fubar (id INTEGER PRIMARY KEY);"
CL-USER> (migratum:migration-load-down-script *migration*)
"DROP TABLE fubar;"

If you need to run multiple statements when using the dbi or rdbms-postgresql driver you can separate each statement in the migration using the --;; separator.

The following example migration would create two tables as part of a single transaction.

CREATE TABLE foo (
    id INTEGER PRIMARY KEY
);
--;;
CREATE TABLE bar (
    id INTEGER PRIMARY KEY
);

cl-migratum uses log4cl, so you can enable debug logging, if needed.

CL-USER> (log:config :debug)

Once done with the migrations you should call the cleanup functions of the provider and driver.

CL-USER> (migratum:provider-shutdown *provider*)
CL-USER> (migratum:driver-shutdown *driver*)

Generally new migration resources will be implemented along with a provider, which discovers them.

You can implement a new migration resource by subclassing the MIGRATUM:BASE-MIGRATION class.

The following generic functions should be implemented on the newly defined class.

The following generic functions can be overriden, if needed.

Example implementation that loads migration resources from a remote HTTP server might look like this. The following code uses Dexador as the HTTP client.

(defun http-get (url)
  "HTTP GETs the given URL"
  (let ((contents (dex:get url :force-string t)))
    contents))

(defclass http-migration (base-migration)
  ((up-script-url
    :initarg :up-script-url
    :initform (error "Must specify URL to upgrade script")
    :accessor http-migration-up-script-url
    :documentation "URL to the upgrade script")
   (down-script-url
    :initarg :down-script-url
    :initform (error "Must specify URL to downgrade script")
    :accessor http-migration-down-script-url
    :documentation "URL to the downgrade script"))
  (:documentation "HTTP migration resource"))

(defmethod migration-load-up-script ((migration http-migration) &key)
  (http-get (http-migration-up-script-url migration)))

(defmethod migration-load-down-script ((migration http-migration) &key)
  (http-get (http-migration-down-script-url migration)))

A provider should simply MAKE-INSTANCE of the HTTP-MIGRATION class by providing values for the required slots while discovering migrations via the PROVIDER-LIST-MIGRATIONS function.

You can implement custom providers, which can discover migration resources from various sources, e.g. local path, remote HTTP endpoints, etc.

Each provider determines the rules, which identify a resource as a valid migration, so custom logic for discovering them can be implemented by using your own provider. For example the local-path provider considers files to be valid migrations, if they match a given regex pattern.

In order to create a new provider you can subclass the MIGRATUM:BASE-PROVIDER class and implement the following generic functions on your newly defined class.

The following methods can be overriden, if needed.

You can also check the local-path provider implementation for some example code.

A driver is responsible for communicating with the database we are migrating and actually executing the upgrade and downgrade scripts.

The driver also takes care of registering applied migrations after applying an upgrade script and also unregistering them during downgrade, thus it is the drivers' decision how to implement registering and unregistering. For example the dbi builtin driver registers applied migrations on the same database it is migrating, but a custom driver could choose a different stategy instead, e.g. use a key/value store, local files, or some remote endpoint instead.

New drivers can be implemented by subclassing the MIGRATUM:BASE-DRIVER class. The following methods should be implemented on drivers.

The following methods can be overriden, if needed.

You can check the dbi driver implementation for some example code.

Tests are provided as part of the cl-migratum.test system.

In order to run the tests you can evaluate the following expressions.

CL-USER> (ql:quickload :cl-migratum.test)
CL-USER> (asdf:test-system :cl-migratum.test)

Or you can run the tests in a Docker container instead.

First, build the Docker image.

docker build -t cl-migratum .

Run the tests.

docker run --rm cl-migratum

cl-migratum is hosted on Github. Please contribute by reporting issues, suggesting features or by sending patches using pull requests.

• Marin Atanasov Nikolov ([email protected])

This project is Open Source and licensed under the BSD License.