[feature] Support WAL mode for sqlite databases.

[zaphar]

Is your feature request related to a problem ?

Currently gotosocial opens sqlite databases in shared cache mode. This mode is considered deprecated and also conflicts with it's recommended replacement: WAL mode. See https://www.sqlite.org/sharedcache.html for details.

This prevents you from using a tool like litestream to keep a backup of your sqlite database since it requires WAL mode to work.

The line here: https://github.com/superseriousbusiness/gotosocial/blob/main/internal/db/bundb/bundb.go#L243 forces shared_cache mode on. I would happily put together a PR to change it but I'm not exactly sure why it was set in the first place or if there are issues with using sqlite WAL mode instead.

I'm not super familiar with the pure go modernc port either so if that doesn't support this then this may be a non-starter for the moment.

Describe the solution you'd like.

Ideally gotosocial would be able to open and use sqlite DBs in WAL mode and cache=shared would be a configuration option instead of required.

Describe alternatives you've considered.

Gotosocial works just fine without this and you could cobble together backups using other methods. However litestream is probably the best solution out there for more robust sqlite backup solutions and makes the operational overhead of sqlite much easier to manage.

Additional context.

No response

[tsmethurst]

this will be closed by #1247 when we finish it :)

[zaphar]

@tsmethurst Awesome. If I can help even just with testing let me know.