Posts Tagged ‘sqlite3’

Running your Rails Test Database in Memory (RAM)

August 4, 2009

I recently read a blog post by Amr Mostafa that benchmarked running MySQL databases in memory. I’ve been trying to figure out how to do this, and he had the answer: use the tmpfs filesystem, which runs in memory, to store your database.  I’ll have to figure out just how difficult that is later, since I’m not a super DBA…or even really a DBA at all.

Amr is not a Rails developer, and the purpose of his benchmark was to simulate regular web traffic.  His results seemed ambiguous, but I noticed something missing in his trial: writes to the database.  His benchmark tests only used select statements, which read from the database.  While this is the majority of most database usage, I think the perfomance gain during writes would tip the scales decidedly in favor of running MySQL in memory, if you can afford the RAM.

This has an added benefit to us Ruby on Rails developers: we could potentially use it to dramatically increase the speed of our Test Driven Development, especially for those of use (should be all of us) using autotest!  TDD requires running tests every few minutes, or even seconds.  And writing to the database in tests is a lot bigger piece of the puzzle, since the database is recreated from scratch before every test.

For a lot of us, test suites are manageable.  Autotest only runs tests for changed files during normal development, occasionally running the entire test suite.  This, coupled with judicious mocking, stubbing and unit testing techniques can keep most test suites under control.  But larger apps use increasingly more tests, and higher-level tools like RSpec can be especially resource-intensive.

I tried to contact Mostafa about running some benchmarks for write speed, but my comment was considered spam!  I did get a smaller message through, so hopefully I’ll hear back.  If so, I’ll post a link to his thoughts/results.  Until then, I may dabble with doing this myself, and seeing what amateurish benchmarks I can run myself.


Ruby on Rails: Bool vs. Boolean in SQLite3

July 18, 2008

I was looking at a new (to me) project today, and stumbled across a rails “gotcha” that needs attention. I tried to run rake test, but it failed. I received this error on each and every test:

rake test
  SQLite3::SQLException: no such table

There are something like 60 migrations in this project, so I went (almost) straight to db/schema.rb which gives you the all-in-one look at your database structure. That’s where I stumbled on this:

  # Could not dump table "generic_table" because of following StandardError
  #   Unknown type 'bool' for column 'generic_column'

it turns out a field was created with the type bool. I’m using SQLite3 for this project, but I did flip over to MySQL during debugging. Here’s what I found:

Under MySQL, bool and boolean appear to work just fine as column types. schema.rb is fine, tests are fine, and these two descriptors are synonymous.

Under SQLite3, boolean is the correct field descriptor. bool won’t break your migration, but:

  • rake db:schema:dump (which is called by rake db:migrate) will fail SILENTLY, and your schema.rb file will be corrupted.
  • rake db:test:prepare uses schema.rb, so it will fail, also SILENTLY.
  • tests will fail
  • rake db:reset also uses schema.rb, and so it will recreate a broken database.
  • Oddly, the bool field in ruby will return ‘f’ for false, and ‘t’ for true. If you set it to true, it does the “magic” on the backend, and there will be a ‘t’ in that field. Therefore, setting something to false doesn’t really work, because that field contains ‘f’, which equates to true.

Rails will do all of this without complaint. Your migrations, db:test:prepare, even code will run without raising red flags. Scary. Tests are where things will start breaking.