Superfast Testing How To: In-Memory SQLite3
Posted by Mathew Abonyi Sun, 26 Nov 2006 21:15:49 GMT
Audience: This is for both application and plugin developers. Especially plugin developers. Always use SQLite3 in-memory databases as your default for plugin tests which require a database.
Some of you may have heard of SQLite3, played with it and decided it is too limited or don’t like the files which it creates in your project tree. There is a less-sung alternative which is much, much faster (actual benchmarks vary because of memory speeds, but for me PostgreSQL is about 33% slower and MySQL about 10% slower). Over a large testing library with thousands of assertions, you will feel that meagre 10% by a comfortable minute. Slight disadvantage: you can’t get that coffee during testing anymore!
There are a handful of sites which have spoken of SQLite3 in-memory databases this year, but to get it working you’ll find the information is a little scattered. The following is a step-by-step ‘how to’ on making your application and plugin tests superfast and lightweight, but please read over these pages first:
- TopFunky on fixing Rails support for SQLite in memory
- Martin Fowler on memory databases
- Why on Being Alert on SQLite
- Rails Wiki on using SQLite
Set Up
A lot of this doesn’t need much explanation, so I’m just going to dump out the procedure to get you from MySQL/PostgreSQL/whatever testing to SQLite3 in-memory database testing, making you wait less and code more.
Step 1: Install SQLite3 and friends
If you do not have SQLite3 already, you need to go to the SQLite website and download the appropriate package. If you use Mac OS X, you need to install the SWIG library too.
Once you have the latest SQLite3 distribution, you need to install the sqlite3-ruby gem:
user@host railsproj$ sudo gem install sqlite3-ruby --source code.whytheluckystiff.netSelect the highest ‘(ruby)’ version (1.1.0.1 at the time of this article). You should see it compile a native extension and install without a hitch.
Note: If you already have sqlite3-ruby installed but do not remember seeing a native extension being compiled, you should uninstall (`gem uninstall sqlite3-ruby`) and install it again with the SWIG library.
Step 2: Install the Memory Test Fix plugin
user@host railsproj$ ./script/plugin install http://topfunky.net/svn/plugins/memory_test_fixStep 3: Change RAILS_ROOT/config/database.yml
test:
adapter: sqlite3
database: ":memory:"
verbosity: quietNote: remove `verbosity: quiet` at the beginning to make sure SQLite3 is loading your db/schema.rb file. If you are not using Migrations or db/schema.rb, you will also need to set `config.active_record.schema_format = :sql` in your environment files and point Rails to the correct schema file.
Step 4: Test it
user@host railsproj$ rake testNotes
As TopFunky says: if you use database specific SQL, using SQLite3 will only get in the way.
Thanks for the thorough tutorial.
[...] Superfast Testing How To: In-Memory SQLite3 (tags: ruby rails database sqlite programming) [...]
My colleague Chris wrote a comprehensive post about this in February this year. It might be worth comparing notes…
Looks like you don’t allow anchor tags in comments, so here’s the url for Chris’ article http://blog.seagul.co.uk/articles/2006/02/08/in-memory-sqlite-database-for-rails-testing
I remember reading it. The memory_test_fix by TopFunky does precisely what he talks about in a tidy little plugin. It just forces the loading of the schema stored in db/schema.rb, so once you have memory_test_fix installed and your sqlite3 :memory: definition, you’re all set to use the memory database for testing.