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;';

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.

Using sqlite-utils

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

sqlite-utils enable-wal *.db

Created 2020-08-09T20:23:23-07:00, updated 2020-12-29T13:55:23-08:00 · History · Edit