My driver (Wasm based) does many of these things by default.
Foreign keys are default enabled, default synchronous mode is FULL for rollback and NORMAL for WAL, shared cache is disabled, the default busy timeout when using database/sql is 60 seconds.
About the default huge busy timeout: my driver can easily afford this because context cancellation actually works and interrupt both long running queries and filesystem locks. I don't think the mattn driver can use a (e.g.) WithDeadline(100ms) context to interrupt early a connection blocked on a busy timeout of 5 seconds: your goroutine will be stuck in a Cgo loop without anything to interrupt it.
Doing this properly requires a custom busy handler, the overhead of which might be to big for Cgo (not sure). Having done this, I also implemented the Ruby approach to improve tail latencies: https://fractaledmind.com/2023/12/06/sqlite-on-rails-improvi...
Another important thing that I don't think mattn does, is have a default transaction "kind" and then allow specific transactions to override it. For SQLite, arguably, the default transaction should be immediate, with only read-only transactions being deferred. I make it possible to pick the transaction type with BeginTx.
Also, recommendations on PRAGMA optimize vary. If you do it when opening connections, you're supposed to use specific flags: optimize uses previous work by the connection to guide what to do, and there's none at this point. This also adds latency to opening connections. Another possibility is doing it when a connection is closed. My driver makes adding a close callback easy.
https://github.com/ncruces/go-sqlite3
Foreign keys are default enabled, default synchronous mode is FULL for rollback and NORMAL for WAL, shared cache is disabled, the default busy timeout when using database/sql is 60 seconds.
About the default huge busy timeout: my driver can easily afford this because context cancellation actually works and interrupt both long running queries and filesystem locks. I don't think the mattn driver can use a (e.g.) WithDeadline(100ms) context to interrupt early a connection blocked on a busy timeout of 5 seconds: your goroutine will be stuck in a Cgo loop without anything to interrupt it.
Doing this properly requires a custom busy handler, the overhead of which might be to big for Cgo (not sure). Having done this, I also implemented the Ruby approach to improve tail latencies: https://fractaledmind.com/2023/12/06/sqlite-on-rails-improvi...
Another important thing that I don't think mattn does, is have a default transaction "kind" and then allow specific transactions to override it. For SQLite, arguably, the default transaction should be immediate, with only read-only transactions being deferred. I make it possible to pick the transaction type with BeginTx.
Also, recommendations on PRAGMA optimize vary. If you do it when opening connections, you're supposed to use specific flags: optimize uses previous work by the connection to guide what to do, and there's none at this point. This also adds latency to opening connections. Another possibility is doing it when a connection is closed. My driver makes adding a close callback easy.
https://github.com/ncruces/go-sqlite3