Sequel Migrations

Migrations are one of the nicer things in the database world these days. I first saw migrations in ActiveRecord (Ruby on Rails) and was completely blown away by what an elegant solution they were. Sequel also has migrations and we'll take a look here at how to use them in the very simplest cases. To run these, you're going going to need to have Sequel version 2.10 or later. To find out what version you're using do a sequel --version. If you're below 2.10, do a sudo gem update sequel (leave out the sudo on Windows) to get the latest version which is currently 2.11.0. The other thing you're going to need is the SQLite Manager Firefox plugin (assuming you're using sqlite and Firefox. If not, you're on your own here.). You can get this plugin here. You don't strictly need this, but it will help to check to make sure everything works as advertised.

We'll use the book/author example from our previous post. The first file will be called 001_BookAuthormigration.rb. It is important that the file start with a number (the migration number) and have an "_" after the number. This is how sequel will know what to migrate. Here's the file:

#
# Create three tables, the books table, the authors table, and the authors_books table.
#
class CreateBooksAuthorsTable < Sequel::Migration

    # For the up we want to create the three tables.
    def up
        # Create the books table with a primary key and a title.
        create_table(:books) do
            primary_key :id
            String :title, :unique=>true 
        end

        # Create the authors table with a primary key and a name.
        create_table(:authors) do
            primary_key :id
            String :name, :unique=>true 
        end

        # Create the authors_books table with a primary key and two foreign keys.
        create_table(:authors_books) do
            primary_key :id
            foreign_key :book_id, :books
            foreign_key :author_id, :authors
        end
    end

    # For the down we want to remove the three tables.
    def down
        drop_table(:books)
        drop_table(:authors)
        drop_table(:authors_books)
    end
end

There are two methods in the class, an up and a down. When we want to add this particular migration, we call the up method and when we want to remove a migration, the down method. Here's how we would run the first migration:

sequel -m . -M 1 sqlite://bookauthor.db

the "-m ." says to run the migration on the local directory and the "-M 1" says to go to migration 1. The final piece, sqlite://bookauthor.db says to use the sqlite database and call it bookauthor.db. If you set this up correctly and then run the sequel command, you should be able to go to Firefox/Tools/Sqlite Manager and then open the bookauthor.db. In the window you should see under tables, the three tables we created (there's also two more that we didn't) and each of the tables should have the correct columns in them.

Assuming the above worked for you, you can then create the second migration, 002_BookAuthorMigration.rb

#
# Add an ISBN column to the books table
#
class AddISBNColumn < Sequel::Migration
    # For the up, alter the table to add the isbn number.
    def up
        alter_table :books do
            add_column :isbn_number, String
        end
    end

    # For the up, alter the table to remove the isbn number.
    def down
        alter_table :books do
            drop_column :isbn_number
        end
    end
end

This uses the alter_table and add_column to add an ISBN number to the books table. To add this to the database, run the following

sequel -m . -M 2 sqlite://bookauthor.db

If you still have the Sqlite Manager open, simply hit the refresh button and then you should see the new ISBN column in the books table.

Finally, if you decide that you don't want the ISBN column again and need to roll it back, then just rerun the

sequel -m . -M 1 sqlite://bookauthor.db

command and it will run the drop method in the 002_BookAuthorMigration.rb file to remove the column and roll back the database to our version 1.

Once again thanks to the sequel-talk mail list in general and Jeremy Evans in particular for helping me work through all of this. Let me know if you have questions or comments. It looks like I'm going to be going to the LA Ruby Conference, so if you're also going, be sure and say "hi".