About a month ago, I converted Podcastinet from using mito to postmodern as the database interface. One of the things I wanted was to have finer-grained control over the database. I was relying on mito's ability to automtaically migrate based on database introspection, which was useful, but also didn't allow migrating data between columns or other, more manual sorts of migrations. To enable that sort of thing, I wrote Postmodern Passenger Pigeon, which I still need to document/write a README for, but is basically functional.

It creates a table in your database named pigeon_migrations that stores a bunch of revisions and it works based on a linear idea of your database's state. That is, there are no branches in the revisions, it's a list, not a tree.

It can be configured with a pigeon.toml file that mostly just takes a migration-directory (but can also take a database-url, though it will look at the environment variable DATABASE_URL, too). And then you have a bunch of files in that directory named like [universal-time]-[hyphenized-name].lisp that house the actual revisions.

For example:

3727984190-create-people-table.lisp:

"Creates the people table."

(defun up ()
  (query (:create-table people
             ((id :type integer :primary-key t)
              (name :type (string 20))))))

(defun down ()
  (query (:drop-table 'people)))

The file can have a docstring at the top to describe the revision in more detail, and then expects an up and a down function to be defined. (The revisions are each evaluated in their own sandbox package, so you can refer to other packages by their full name, though I don't recommend it.) Postmodern is used by default.

Creating migrations

If you want to create a new revision, you can call (ppp.migration:new "Short description of my migration") and it will create the new file for you. If you decide that you don't need a migration you're working on, just revert the migration and delete the file and you can go ahead and make new ones. They're applied in numerical order of the timestamp.

Running migrations

To apply or revert migrations, use the ppp:migrate function. The first argument is the direction to migrate in: :up will apply new migrations, :down will revert existing migrations. You can give a number as the second argument to say how many you'd like to apply or revert, i.e. (ppp:migrate :up 1) will apply the next migration that hasn't been applied, and no more, (ppp:migrate :down 3) will revert 3 migrations, starting will the most recently applied.

There are also the special values :head and :base which mean "all the migrations applied" and "no migrations applied" (which should mean an empty db schema). In my site, I run (ppp:migrate :up :head) when it starts up to apply any migrations that came with the latest code push.