I ran into a bit of a gotcha with using SQLite for Rails development this week. SQLite accepts the usual SQL datatype specifiers when you create a table (or in this case, it accepts the type provided via a Rails Migration) but it doesn’t enforce the type: for example you can insert a String into an integer column.
This is a feature, not a bug.
says the SQLite documentation. I’m prepared to accept that they have a reasonable argument there, but it can lead to unexpected behaviour when you move to a different database1. And moving to a different database easily is one of the benefits of using Migrations.
I guess what it really means is that I should write some better test cases that check for incorrect types being stored. If I think the type is important, that is.
fn1. I was actually attempting to deploy on JRuby, and using Derby as the database, but that’s another story.
