In-Memory Database for Testing Doesnt Buy Much

Installation

First, I'll walk through the installation steps.

My iMac with OSX 10.4 already has sqlite3 installed, although I appear to have installed it at some point with macports because doing "which sqlite3" tells me its in /opt/local/bin/sqlite3. If you don't have it, get it (see links in the references below).

Next install the sqlite3-ruby gem

$ sudo gem install sqlite3-ruby

and choose the most recent (2. sqlite3-ruby 1.2.1 (ruby))

Then, install the memory_test_fix plugin. This handy little beast builts the sql tables in your memory db based on your current schema.rb file

$ script/plugin install http://topfunky.net/svn/plugins/memory_test_fix 

Lastly, edit the database.yml file so the test database uses the sqlite3 adapter:

test:
  adapter: sqlite3
  database: ":memory:"
  #verbosity: quiet

You can uncomment the last line once you're sure things are working.

That's it. Run you tests, and they'll operate from the in-memory database.

Results

I use Rspec for my testing. My present test suite consists of 883 examples. Here are the timing results:

I only see 1/10th of a second difference! Actually, for the most part only my model specs use the database anyway. My controller specs all use mocks and stubs. With the Plain Text Stories, the in-memory db was actually slightly slower. Go figure.

Furthermore, I got a couple of errors in my specs when running with sqlite that aren't there with mysql. I'm not doing anything obviously database dependent (for example one of them is a validates_uniqueness_of with :case_sensitive => false). For the timing tests, I commented out the failing tests so that wouldn't be a factor.

Conclusion

As it was, I was hesitant to use a different database for testing than for development and production (in my case MySQL). Now I can just cross this idea off my list.

References

• http://nubyonrails.com/articles/san-francisco-sqlite3-memory-tests-asteroids
• http://www.mathewabonyi.com/articles/2006/11/26/superfast-testing-how-to-in-memory-sqlite3
• http://topfunky.net/svn/plugins/memory_test_fix/README