With Go it's quite straightforward actually: use WAL mode + two connection pools, one for reads and the other, with MaxConnections set to 1, for writes. This way you should never encounter any concurrency issues, and Go will serialise writes for you too
Setting MaxOpenConns to 1 essentially limits the number of concurrently running (write) transactions to 1, which is exactly what we want. Whenever a concurrent thread wants to open a new transaction it'll have to wait.
Note that the application needs to be aware of that there are two pools — one for write operations and one for reads (the latter with no or high connection limit). The separation can be ensured on SQLite level too by adding ?_query_only=1 to connection parameters or setting the respective pragmas in the read-only pool.
