Ruby on Rails: Bool vs. Boolean in SQLite3

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.


Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: