Using SQLite's immutable and mode flags to get around "database is locked" error

One great thing about SQLite is that it's a super-widely-used format, so you can fire up sqlite3 to poke around your applications' data, or use your favored programming language's SQLite library. However, oftentimes you'll get this error message (eg. when accessing Firefox's various *.sqlite files):

Error: database is locked

This is frustrating when all you want to do is read from the database! The solution I've settled upon for this is to use the immutable=1 parameter with SQLite URI filenames - so instead of sqlite3 $HOME/.mozilla/firefox/$PROFILE/places.sqlite, you'd do sqlite3 file://$HOME/.mozilla/firefox/$PROFILE/places.sqlite?immutable=1, but this comes with some important caveats!

While digging into this a bit deeper for this post, I found the following in the SQLite docs:

Setting the immutable property on a database file that does in fact change can result in incorrect query results and/or SQLITE_CORRUPT errors.

😬

When using this technique, I have noticed the occasional incorrect query result - I've seen results that are a little out-of-date, which has been acceptable for me. I haven't noticed any corruption, thankfully - probably because I'm not issuing any SQL statements that would modify the database! Assuming that it's data-modifying SQL that would result in corruption, there's another parameter we can set to protect ourselves from that: mode=ro!

Unlike immutable=1, which just sets up SQLite with the expectation that the database file is not writable (so it doesn't bother with WAL files and other things, which I think is the source of the error), mode=ro tells SQLite not to let the connection make any modifications.

mode=ro also addresses another small issue I've run into - sqlite3 file:///tmp/data.db?immutable=1 will create /tmp/data.db if it doesn't exist unless you also specify mode=ro, which I learned about when I fixed a bug with my zsh SQLite completion recently.