SQLite3 in Ruby 1.9: be sure your Ruby data is UTF-8

SQLite3 is not finding your records? SQLite3 is inserting binary data instead of plain-text strings? If you're using Ruby 1.9 or later, you must be sure your Ruby data is encoded as UTF-8 before submit it to SQLite3.

Text data in SQLite is UTF-8. Before Ruby 1.9, Strings had no encoding; so that was not a issue; all data get same handling. From Ruby 1.9, Strings must be encoded as UTF-8 to be compatible with SQLite data; otherwise your text will be considered binary content to SQLite, and binary content won't match plain-text content, even when they are the very same bytes.

Maybe the source of your text data encodes them as other than UTF-8. To make sure your data is UTF-8, try using String#encode or String#force_encoding before submit it to SQLite 3. Cheers!

Sequel blocking SQLite3?

I was often getting SQLite::BusyException (reraised as Sequel::DatabaseError) or Sequel::PoolTimeout exceptions when using Sequel to handle SQLite3 databases in a program with little concurrency, in a way that that exceptions are not expected (specially on reads). I rewrote my code to use the SQLite3 gem instead of Sequel, just for test, and I had no more exceptions.

There are things to be investigated, but this tip can be useful to someone as a quick (temporary?) solution.

How to install sqlite3-ruby gem on linux

If you are having trouble to install the sqlite3-ruby gem, try the following:

In systems with apt-get:

# apt-get install libsqlite3-dev

In systems with yum:

# yum install sqlite-devel