I want to warn that one huge issue with SQLite in WAL mode is that if your site gets a high load, the WAL file will grow unboundedly. I ran a stress test of 14k RPS (that's the maximum my PC can pull off in my Go application, but it probably can happen with a more modest RPS) and the WAL file quickly exploded to tens of gigabytes, which can render your machine inoperable (it almost broke my PC). The default checkpointer can't properly function if the DB is continuously written to, due to the internal limitations of SQLite.
I managed to solve it by running a separate goroutine (thread) which monitors the WAL size on disk every second: if it goes above the target size of 8 MB, my framework initiates the "slow down" mode where all reads and writes are slowed down by artificially calling sleep(), starting with 16ms and gradually increasing the sleep time according to a few heuristics. This allows the application to have small time gaps where no reads or writes happen and the checkpointer can actually proceed (the goroutine activates it manually). The slow down mode is deactivated when the WAL size is within the target size again.
I think SQLite in WAL mode is not really fit for production without this kind of hack.
I managed to solve it by running a separate goroutine (thread) which monitors the WAL size on disk every second: if it goes above the target size of 8 MB, my framework initiates the "slow down" mode where all reads and writes are slowed down by artificially calling sleep(), starting with 16ms and gradually increasing the sleep time according to a few heuristics. This allows the application to have small time gaps where no reads or writes happen and the checkpointer can actually proceed (the goroutine activates it manually). The slow down mode is deactivated when the WAL size is within the target size again.
I think SQLite in WAL mode is not really fit for production without this kind of hack.