Rails Style Ruby Database Migrations with Sequel

If you've ever worked with ActiveRecord databases, you know how awesome it can be to manage changes to your database. Make some changes in an indexed migration file, and run rake db:migrate to apply all changes; your database stays up to date with each new git pull. Fortunately, there's a way to get the same functionality in regular Ruby code with a Rake task and the Sequel gem.

Before I figured this out, I was applying migrations in a very hacky way. I had a tasks method that was invoked when my global database class was instantiated:

    def db_tasks
        @db.exec("alter table users add column age integer") rescue false
        @db.exec("alter table users add column subscriber boolean") rescue false
    end

Every time I needed to modify or create a table, I'd add a line to it. This can get messy the more you need to change the database schema. Enter the Sequel gem.

Sequel is a ruby gem for getting ActiveRecord-like functionality into your Ruby code. Instead of UPDATE users SET AGE=27 WHERE NAME="Nick" you can define a model and run User.find_or_create(:name => 'Nick').update(:age => 27) without having to worry if the record exists yet. Or instead of SELECT * FROM users LIMIT 1 you can run User.first. But you don't even have to use those features to get access to my favorite Sequel feature, migrations.

I have a directory set up within my project's root db/migrations which has indexed migration files like 001_create_users_table.rb, 002_add_primary_key_to_users_table.rb, etc. The syntax for the files is pretty straight forward:

    Sequel.migration do
      change do
        create_table :users do
          String :name, :unique => true
          Integer :age
          Boolean :subscriber, :default => false
        end
      end
    end

To apply this from the command line, you run sequel -m /path/to/migrations/dir /path/to/db and migrations are applied in order. I prefer the standard Rails command rake db:migrate so I created a rake tasks to wrap the command line argument. This is what my Rakefile looks like:

    namespace :db do
      task :migrate do
        result = %x{sequel -m db/migrations/ -E postgres://localhost/db}
        puts result
      end

      task :create do
        result = %x{createdb db}
      end

      require 'highline/import'
      require_relative 'database_manager'
      require_relative 'settings'
      task :tasks do
        config_path = File.expand_path("../config/", __FILE__)
        @config       = App::Settings.new(path: config_path)
        @db           = App::DatabaseManager.new(settings: @config)
        @db.db_tasks
      end
    end

Now, to create the database I run rake db:create, to migrate changes I run rake db:migrate, and to run any arbitrary tasks that I still keep in my database class, I can run rake db:taskswithout having to worry about them executing every time I create an instance of the class.

When adding team members to my project, this gets them up and running with the latest database and schema without issues.

Sequel is an incredibly powerful gem that I plan to take better advantage of as I migrate my app from ruby-pg style SQL statements to ActiveRecord compatible queries. I highly recommend it to anyone who wants to get comfortable with Rails methodologies before making the switch from desktop -based coding.