enabling-wal-mode.md

Enabling WAL mode for SQLite database files

I was getting occasional Error: database is locked messages from a Datasette instance that was running against a bunch of different SQLite files that were updated by cron scripts (my personal Dogsheep).

I had read about SQLite's WAL mode but never fully understood how it works. I asked some clarifying questions on the SQLite forum and learned that WAL is actually a property of the database file itself, not of the connection to that database.

This means that turning on WAL is a thing you can do directly to a database file!

Here's the incantation:

sqlite3 github.db 'PRAGMA journal_mode=WAL;'

I ran this against all of the *.db files in a directory like this:

ls *.db | while read filename;
  do sqlite3 $filename 'PRAGMA journal_mode=WAL;';
done;

The first time I ran this it worked on all but one file, which showed the Error: database is locked message - so I kept trying against that file until it worked.

After running this each .db file has an accompanying .db-shm and .db-wal file. So far I've not seen the "database is locked" message, so I think it had the desired effect.

Turning WAL mode off again

If you want to turn WAL mode off and go back to the SQLite default, the unintuitive way to do that is:

PRAGMA journal_mode=delete;

Using sqlite-utils

I added a command to sqlite-utils 2.15 that does this:

sqlite-utils enable-wal *.db

The disable-Wal command disables it again.