mirror of
https://github.com/pocket-id/pocket-id.git
synced 2025-12-06 09:13:19 +03:00
[PR #388] [MERGED] fix: use WAL for SQLite by default and set busy_timeout #831
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
📋 Pull Request Information
Original PR: https://github.com/pocket-id/pocket-id/pull/388
Author: @ItalyPaleAle
Created: 3/27/2025
Status: ✅ Merged
Merged: 3/30/2025
Merged by: @stonith404
Base:
main← Head:sqlite-connstring📝 Commits (7)
fc16a75fix: use WAL for SQLite by default and set busy_timeout6ceff7bUpdated as requested565a52aMerge branch 'main' into sqlite-connstring85684c2Merge branch 'main' into sqlite-connstring725e678Merge branch 'main' into sqlite-connstring912b1dbUse a separate migration method734acfeMerge branch 'main' into sqlite-connstring📊 Changes
5 files changed (+64 additions, -15 deletions)
View changed files
📝
.github/workflows/e2e-tests.yml(+1 -1)📝
backend/internal/bootstrap/bootstrap.go(+2 -0)➕
backend/internal/bootstrap/config_migration.go(+34 -0)📝
backend/internal/bootstrap/db_bootstrap.go(+18 -7)📝
backend/internal/common/env_config.go(+9 -7)📄 Description
When using SQLite in a web application, it's common to enable WAL which makes it possible to get better performance especially for concurrent access. WAL is supported by Gorm since under the hood it does use the C sqlite library, so it should be enabled by default.
Additionally, this PR makes sure to set the explicit
_txlock=immediatein the connection string unless the user set another value (not recommended). It also sets abusy_timeoutto 2500ms (unless another value is set by the user) to make SQLite wait if there's already a transaction/query that has locked the database - otherwise, SQLite would just error immediately, showing a failure to the user (not a good experimence). (Note that when WAL is enabled, SQLite creates 2 additional files next to the database calledpocket-id.db-shmandpocket-id.db-wal)One thing to note is that Pocket ID's configuration uses the terminology "database path" for SQLite, assuming that only paths to files can be passed. However, the underlying driver can accept either a path, or a connection string (starting with
file:), which allows passing additional options as query string args. This is what we rely on.Most users will not need to change a thing in how they use Pocket ID.
The only situation where one would want to change anything is to disable the WAL if the SQLite DB is running on a networked filesystem, such as NFS or SMB. However, even with the WAL disabled, running SQLite over a networked file system is strongly discouraged by the SQLite developers themselves, as it's risky in all cases.
🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.