Sqlite Gotchas

I am in the process of writing a twitter wrapper wrapper. That is to say, something that can, given a small list of params, perform a giant twitter search for you, taking care of the iterations.

I'm using a sqlite db for the testing db. Coming from rails, it takes a bit of getting used to. That said, it works really well for the most part. It saves a lot of complexity at the low low cost of maintaining two schemas. It's not so bad. I have one dev/production schema set up by migrations, and a leaner version that is loaded by the test suite. So far, I can find only a couple of things to look out for.

1. Not the prettiest or clearest errors. This one comes to mind:

ActiveRecord::StatementInvalid: SQLite3::SQLException: SQL logic error or missing database

If you're thinking "that could mean damn near anything!" then you are right.

2. On some of the larger postgres tables, using this in the migration allowed for a high number of ids:

change_column :confidences, :id, :integer, :limit => 8 

This works great for postgres. For some reason, when you do this with sqlite, without warning, your ids will not save, which means you will have to find based on.... well, good luck.

Fortunately, my test data has no reason to be large enough to need a bigint. Problem solved.

For a gem-sized project, this setup has worked pretty well. I'll be rolling open sourcing it in a couple of weeks.

For a good reference on when to use sqlite, I'd check this out.