[PR #388] fix: use WAL for SQLite by default and set busy_timeout #835

New Issue

OVERLORD · 2025-10-07T00:23:11+03:00

OVERLORD commented

2025-10-07 00:23:11 +03:00

Original Pull Request: https://github.com/pocket-id/pocket-id/pull/388

State: closed
Merged: Yes

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=immediate in the connection string unless the user set another value (not recommended). It also sets a busy_timeout to 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 called pocket-id.db-shm and pocket-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.

**Original Pull Request:** https://github.com/pocket-id/pocket-id/pull/388 **State:** closed **Merged:** Yes --- When using SQLite in a web application, it's common to [enable WAL](https://www.sqlite.org/wal.html) 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=immediate` in the connection string unless the user set another value (not recommended). It also sets a `busy_timeout` to 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 called `pocket-id.db-shm` and `pocket-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](https://www.sqlite.org/faq.html#q5) by the SQLite developers themselves, as it's risky in all cases.

OVERLORD added the pull-request label 2025-10-07 00:23:11 +03:00

OVERLORD closed this issue

2025-10-07 00:23:12 +03:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/pocket-id#835