cl-migrations is a port of the database migrations feature of Ruby on Rails to Common Lisp. cl-migrations is intended to provide a simple way to version-control your database changes. As a web project grows, it's database will go through numerous structural changes. Even if the schema is available as an sql file for creating a database instance, it can quickly get out of sync with the actual database. Also, if things go wrong with a new version, there should be a proper way to disable new changes to database and bring it back to a previously known good position. cl-migrations creates a new file with a version number everytime a new database change to the database structure is to be made. It can revert, or forward to a given version number, or move the database to the latest version if no version number is given.

How to get it

The easiest way to get cl-migrations is through asdf-install. Just do this in your repl:

(asdf-install:install :cl-migrations)

If is down, it might not work. Then do this:

(asdf-install:install "")

If you're interested in browsing through the code or committing patches, do a darcs get:

darcs get

How to use it

These steps assume that you have successfully installed and setup clsql. After this, create a file called .migrate.conf in your home directory (notice the dot in front). You can setup your database configuration something similar to this:

((:postgresql ("localhost" "clsql-test" "username" "password"))
 (:migration-dir ("/home/username/migrations/")))

If you haven't done so already, load up cl-migrations:

(asdf:operate 'asdf:load-op :cl-migrations)

Or, if you're on SBCL, this should do:

(require :cl-migrations)

Now, you can start generating migrations:

(cl-migrations:generate "add-employees-table")

This will create a file called 1-add-employees-table.lisp in your migrations directory. You can populate the empty stub with something like this:

;; Auto-generated stub by cl-migrations.
;; 1-create-employees-table.lisp
(:UP (("create table employees (id serial, name varchar(100))")
      ("create table products (id serial, name varchar(100))"))
     :DOWN (("drop table products")
         ("drop table employees")))

:up will always contain the ddl which will create a table, add a column or other such modifications. :down should contain the statements which reverts this change. Create a bunch of such files, and each will be given an increasing version number. So if you want to move your database to the latest version, you can simply do this:


This will show some output on the sql being executed, how many migrations are done, etc. All these changes are done within a transaction, so you need not worry about half-done migrations in case of an sql error. Anyways, it is recommended that you run the sql statement first to check your errors before pasting it on to the :up> or :down elements.

Anytime you want to move to a specific version, you can supply the version number like this:

(cl-migrations:migrate :version 12)

cl-migrations will then either migrate up, or down to reach the exact version number you have specified. when it is migrating up, only statements in :up will be executed, and when it is migrating down, only statements in :down will the executed. If you are facing any problems or need any help, please send a mail to the mailing lists specified below. Bug reports/patches are very much welcome.

To Do

cl-migrations does not yet have the ability to generate a migration out of an existing database schema. This feature will be added soon.

Mailing lists





cl-migrations depends on CLSQL for connecting to database, executing queries and making migrations work over different database engines. Many thanks to Kevin Rosenberg for maintaining this excellent piece of software.

Powered by

Valid XHTML 1.0 Strict