Summer of Hawkwind (aka Running your rails tests using an in-memory database)

28 Apr 2010 – Denver, CO

2 weeks paternity leave from work means a couple of things. Firstly, it means I’ve been listening to a lot of music, and it seems the most fitting for Spring/Summer 2010 is some awesome space-rock. Order of the day seems to be Hawkwind.


Might get a mountain bike ride in on Friday. Mountain-biking + acid-rock == flashbacks whilst hurtling downhill at 30 mph.

Running Rails tests using an in-memory database

The second thing being off work enables is the cleaning up of old projects and things on the to-do list. One thing thats been bugging for a while is the length of time it takes to run my rails test cases (running MySQL):

	~/git/cs(master) $ rake test
	Finished in 264.208584 seconds.

	25 tests, 2697 assertions, 0 failures, 0 errors

Over 4 minutes, thats a long time when you’ve changed one line of code. A challenge! Onwards and upwards, heres how to get your rails test to run using sqlite3.

1. Install the sqlite gem

sudo gem install sqlite3-ruby

This may or may not go smoothly. Native extension compilation problems could lead to macports on OSX, or the equivalent apt-get on Ubuntu.

2. Modify database.xml

	   adapter: sqlite3 
	   database: ":memory:"

Speaks for itself. Keep the old one around in case you want to run comparison speed-tests.

3. Install the ‘in_memory_fix’ plugin

I grabbed it from the following svn repo:

./script/plugin install

But there are also a bunch of git forks floating around.

What does it do ? Well, the short of it is that because the db is in memory, it needs to be re-created each time, which is what the plugin does. You can find more info over here.

4. Differences between MySQL and Sqlite3

At last, some meat. You can take what you have and try to run your rake tests. There’s are chance you could be good to go. In my case, there were a couple of syntactic differences between the two databases that needed resolving:


First up was that the sqlite3 random function is called ‘RANDOM()’ whilst the MySQL one is called ‘RAND()’. This cause an active_record lookup like the following one to fail:

@bar = Foo.find(:all, :order => 'RAND()')                 

Couple of ways to fix this. Easiest from my point of view was simply to create the ‘RANDOM’ function in MySQL. Sounds like a migration:

	class CreateRandomFunction < ActiveRecord::Migration
	  def self.up            
	    adapter = User.connection.
	    if adapter == 'mysql'
	      execute "create function random() returns integer return rand();"

	  def self.down  
	    adapter = User.connection.
	    if adapter == 'mysql'
	      execute "drop function if exists random;"

Doesn’t this apply to test-cases ?

Err, I suspect that should be enough, but my test cases were failing because around the time that fixtures were loaded, the function got blown away again. I ended up with this in my test_helper.rb:

adapter = Rails.configuration.
if adapter == "mysql" && ENV["RAILS_ENV"] == 'test' 
  ActiveRecord::Base.connection.execute("drop function if exists random;");
  ActiveRecord::Base.connection.execute("create function random() returns integer return rand();");

Fixtures and “1” vs “t”

Almost out of the woods, but not yet. If you’re using fixtures, and you’ve auto-magically generated them (as I have), you might see the following entry for Booleans:

boolean_variable_in_mysql: "1"   

This is all and well in MySQL, but sqlite3 doesn’t like it. It stores its bools as the char “t” or “f” and you may need to convert them. true or false should work fine, i.e.

boolean_variable_in_mysql: true   

And thats it. My tests running in memory. Time to turn the space-rock up.