Thursday, March 19, 2009

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".

12 comments:

  1. Thank you for the write up. I was doing postgres migrations, but was able to figure it out from your good description.

    ReplyDelete
  2. You're both quite welcome. Glad that it helped.

    ReplyDelete
  3. Awesome write up! I'm really happy I found such a useful post about sequel migrations that wasn't tied to Merb. :)

    ReplyDelete
  4. Steve, Glad it helped. Thanks for letting me know it was useful to you.

    ReplyDelete
  5. Doh, I was cobbling together my own script to run the migrations. Glad I found this before I'd spent much time on it :)

    ReplyDelete
  6. Vidar, Exactly why I'm trying to document all of this. Glad it helped.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. If you find that creating unique names for each migration class seems odd or annoying, note that Jeremy Evans (author of Sequel) recommends the following code to create anonymous classes instead:

    Class.new( Sequel::Migration ) do
        # ... Your migration code here
    end

    Thanks for putting this up, Scott: I needed the information, and knew you had it on your blog. :)

    ReplyDelete
  9. Thank you, especially for the command to apply the migration. Nobody else mentions how to apply the migration outside of rails.

    I have based the end of a ORM lesson on part of this and linked back to this article.

    http://github.com/munkymorgy/Code-Dojo/tree/master/ORM/lesson-01/

    ReplyDelete
  10. Great article slabounty, helped me along the way.

    It also helped me write this. Which isn't intended to nullify this article in anyway. But works quite well with it.

    Great stuff.

    ReplyDelete
  11. Lee, Nice article. I don't really think there can be too much information on this sort of thing. I'd highly encourage everyone to read your article too.

    ReplyDelete