Something I found non-obvious about WAL mode in SQLite is that it's actually a property of the database file itself.
When you run "PRAGMA journal_mode=wal;" against a database file the mode is permanently changed for that file - and the .db-wal and .db-shm files for that database will appear in the same directory as it.
Any future connections to that database will use it in WAL mode - until you switch the mode on it back, at which point it will go back to journal mode.
It makes sense when you think about it - of course a database can only be in one or the other modes, not both, so the setting must be at the database file level. But it took me a while to understand.
There is a significant warning about the use of WAL mode, very plain in the documentation:
"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."
The other odd thing is that the journal_mode is only persistent for WAL, I believe. The DELETE, TRUNCATE, & PERSIST modes are per-connection. It makes sense though since those 3 modes deal with the rollback journal and are compatible with each other while the WAL is totally separate. https://www.sqlite.org/pragma.html#pragma_journal_mode
I was reading up on WAL mode this week and came across a mailing list where you asked for clarification on this same topic. That post helped me understand, thanks for asking the question! The confusing thing here for me is that the majority of other PRAGMAs (that I know of) are per-request, so this one differs from that pattern
When you run "PRAGMA journal_mode=wal;" against a database file the mode is permanently changed for that file - and the .db-wal and .db-shm files for that database will appear in the same directory as it.
Any future connections to that database will use it in WAL mode - until you switch the mode on it back, at which point it will go back to journal mode.
It makes sense when you think about it - of course a database can only be in one or the other modes, not both, so the setting must be at the database file level. But it took me a while to understand.
I wrote some notes on this here: https://til.simonwillison.net/sqlite/enabling-wal-mode