Two sqlite3 gotchas

Ran into a couple of odd problems when doing database schema migrations on my sqlite3 database.

Transactions don’t work like you’d expect in the sqlite3 command line tool! I wrote a few lines of SQL to create a new table and drop an old one, then fed that script to sqlite with the command line tool like this: sqlite3 my.db < script.sql. I was clever and wrapped my script.sql in a “begin transaction… commit” so that if there were any errors, nothing would be committed. Smart, right? Wrong. sqlite3 will gladly execute line n+1 in a script it’s fed even if line n fails. This makes sense for interactive use, but is a disaster for a script that wants a transaction; the commit at the end will always run. The solution is to put .bail on in the top of your script so any errors cause execution to terminate. See also this discussion.

I was using an “insert or replace” command to be sure I only had one row per key in my database, along with a unique constraint to tell the DB what the key was. Only I didn’t actually add the unique constraint during my migration, because it’s a PITA to do schema changes like that in sqlite3. unique constraints are only hints, right, it’s like a type checker! Well not if you rely on the constraint violation enabling an “insert or replace” to work. Oops.