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

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
        run_orig(result, &block)

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])

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

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

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
Finished in 0.002673 seconds.

3 tests, 4 assertions, 0 failures, 0 errors