SQLite gotcha

SQLite is great. No, really, I do mean it. If you ever need to work on structured data and editing it from the outside is a must, then SQLite is worth checking out and might be a superior choice to XML and will be a superior choice to your own proprietary format.

I cannot speak about performance - there are some outdated claims that SQLite is faster than postgres and mysql for the most common operations; but those benchmarks don't feature a single join, and that's where database implementation gets interesting and hard. I just cannot believe that SQLite stands up to any full scale DBMS.

I spent a few hours chasing down a bug in my own code after realising that I've been utterly stupid and put my parameters in the wrong order into my parameter list.

To boil it down, this is what happens in SQLite if I compare an integer field with a string in a WHERE part of a statement:

sqlite> CREATE TABLE my_favourite_numbers (number integer);
sqlite> SELECT * FROM my_favourite_numbers WHERE number = "foo";
sqlite>

You get no result. No error is thrown, like in Postgres:

# CREATE TABLE my_favourite_numbers (number integer);
# SELECT * FROM my_favourite_numbers WHERE number = 'bla';
ERROR: invalid input syntax for integer: "bla"

I know now what to look out for in the future.