Transactional in memory database tests with sequel and sqlite

Mar 13 2008

Transactional in-memory database tests with Sequel and SQLite

Instant feedback is one of the prominent features I look for when referring to "good test code". Tests that involve a database often lack this quality. Here, I am referring to a test's start up time, rather then the actual time a test takes to execute. This needn't be the case when coding in Ruby, given the negligible lag related to firing up an MRI interpreter and the equally fast start up of in-memory SQLite.

Using an in-memory database for testing is a common technique for speeding up functional tests that hit the database. Sequel makes using SQLite in its in-memory mode particularly easy.

require 'rubygems'
require 'sequel'

DB = Sequel.sqlite  %p
          Database setup code can follow this step.

DB.create_table :items do
  column :name, :string
end

The above is for the sake of simplicity, and in a real world scenario it would involve running migrations against the application's current schema.

Another useful feature is the ability to run these tests transactionally, that is, never actually change the database state and avoid having to deal with unnecessary database clean up. As an added benefit, a relative speed bump is achieved by not performing database write operations. A simple extension to Test::Unit::TestCase will do the trick.

class Test::Unit::TestCase
  alias run_orig run
  def run(result, &block)
    DB.transaction do
      begin
        run_orig(result, &block)
      ensure
        rollback!
      end
    end
  end
end

Following are some sample tests, with nothing out of the ordinary about them.

class SomeTest < Test::Unit::TestCase
  def test_rock
    items = DB[:items]
    items.insert(:name => 'rock')
    assert_equal(1, items.count)
    assert_equal('rock', items[1][:name])
  end

  def test_coast_is_clear
    assert_equal(0, DB[:items].size)
  end

  def test_insert_ten_items
    items = DB[:items]
    10.times { |i| items.insert(:name => "item_#{i}") }
    assert_equal(10, items.size)
  end
end

These tests not only execute in milliseconds, but also largely eliminate any noticeable lag before they run.

TW-MacBook-Pro:Desktop gmalamid$ ruby some_test.rb 
Loaded suite some_test
Started
...
Finished in 0.002673 seconds.

3 tests, 4 assertions, 0 failures, 0 errors

nutrun

Transactional in-memory database tests with Sequel and SQLite