SQLite in WSL: WAL, File Locks, and Safe Database Placement

SQLite is an embedded database, not a network server. Its database is a file accessed by the application through the SQLite library, and its correctness depends on the filesystem operations and locking behavior available to every process that opens that file. This distinction matters in WSL because one path may live on the distro’s Linux filesystem, another may be a Windows drive mounted under /mnt, and a third may be a network share mounted by Linux. A path that can be opened is not automatically a safe place for a concurrently accessed database.

For Linux applications running in WSL, a strong default is to keep the database under the distribution’s Linux filesystem alongside the application. That keeps the workload in one Linux filesystem domain and follows Microsoft’s guidance for Linux command-line projects. If a Windows process must access the same database, treat that as a cross-platform concurrency design that needs explicit testing, not just a path-conversion decision.

Understand the database file and its companions

In rollback-journal mode, SQLite uses journal data to support atomic transactions. In write-ahead logging mode, committed updates may reside in a separate -wal file until checkpointed into the main database. A -shm file can be used for WAL shared-memory coordination. These files are part of the database’s live state while connections are open. Copying only the main database file while writers are active can omit committed content that remains in the WAL or produce an inconsistent copy.

WAL mode changes concurrency characteristics, but it does not turn SQLite into a multi-writer server. Readers can often proceed while a writer appends to the WAL, but writers still serialize, and checkpointing has its own behavior. The SQLite WAL documentation explicitly says that all processes using a WAL database must be on the same host and that WAL does not work over a network filesystem. WSL is a local host boundary for this discussion, but crossing into a Windows-mounted or remote filesystem still requires understanding the actual filesystem implementation.

Inspect the database’s current journal mode rather than assuming it:

sqlite3 app.db 'PRAGMA journal_mode;'
sqlite3 app.db 'PRAGMA compile_options;'
ls -l app.db app.db-wal app.db-shm

Sidecar files may disappear after the final connection closes cleanly, and their presence is not itself evidence of corruption. Do not manually delete a WAL or shared-memory file while any process may still have the database open. Use SQLite’s documented close, checkpoint, and backup behavior.

Place the database according to the processes that access it

If the program, migration tool, test runner, and database library all run in WSL, store both project and database in the Linux filesystem, for example ~/src/app/data/app.db. This avoids a mixed model in which Linux code performs database locking and file updates over a Windows-mounted path while Windows software edits or copies the same files.

Using /mnt/c can be convenient when Windows-native applications need direct access to the same project, but do not infer database safety from successful reads and writes. Test the filesystem semantics that SQLite relies on, including locks, shared memory behavior, atomic replacement, renames, flushes, and concurrent processes. A one-process test on one mount configuration cannot establish correctness when a Windows editor, backup tool, antivirus scanner, sync client, or another distro accesses the same files.

Never use a network filesystem as a shared WAL database location. If processes on distinct machines need to read and write application data, use a database server or a service protocol designed for that topology. The same principle applies to cloud-synced folders: synchronization is not a transactional locking protocol, and delayed replication can produce divergent copies.

Configure and validate WAL intentionally

Enable WAL through SQLite itself and verify the returned journal mode:

sqlite3 app.db 'PRAGMA journal_mode=WAL;'
sqlite3 app.db 'PRAGMA journal_mode;'
sqlite3 app.db 'PRAGMA busy_timeout=5000;'

The busy timeout is connection-specific; setting it in a one-off CLI process does not permanently configure every application connection. Configure the timeout in the application or connection initialization path, and choose a value based on the workload’s latency budget. A busy timeout can make short writer contention more tolerable, but it does not make a long transaction or a stuck writer disappear.

Keep write transactions bounded. Do not hold a transaction open while waiting for user input, an external API, or a background job. Measure transaction duration and count lock-related errors under the application’s expected test concurrency. If multiple independent application processes write to one SQLite file, test that exact topology and decide whether the serialized writer model is appropriate.

Checkpoint behavior is part of WAL operations. Automatic checkpoints may occur based on page thresholds, but applications can also control checkpoints when they have a measured reason. Monitor both the database and WAL sizes; a large WAL may reflect a long-running reader that prevents checkpoint progress, not merely a broken database. Diagnose open connections and transactions before changing checkpoint parameters.

Back up through SQLite, not an arbitrary live copy

A cold copy of a fully closed database is straightforward. A live database requires a SQLite-aware backup method or a carefully controlled quiescence procedure. The SQLite command-line shell provides a .backup command that uses the online backup interface. The following sequence creates a separate backup, checks the copy, and records basic database integrity:

sqlite3 app.db '.backup app-backup.db'
sqlite3 app-backup.db 'PRAGMA integrity_check;'
sqlite3 app-backup.db 'PRAGMA quick_check;'

Do not treat a successful backup command as a recovery test. Restore the copy to a disposable target, open it with the application’s SQLite version, verify expected tables and rows, and run the project tests against that target. Keep backups outside the only VHDX containing the source database if they must survive loss or removal of that distribution.

If using a file-copy tool, first stop all processes that can write to the database and confirm their connections are closed. In WAL mode, ensure the database is checkpointed and the final connection has closed as expected. A copied main file separated from a live WAL can lose committed transactions or become corrupt. Do not copy just the file named app.db while the application continues using it.

Diagnose locks and integrity errors methodically

When SQLite reports database is locked or database is busy, determine whether the cause is expected writer serialization, an unclosed connection, an excessively long transaction, a competing process, or filesystem semantics. Identify every process that opens the path and print the canonical path from the app. If the issue occurs only under /mnt or while Windows is touching the file, reproduce with a Linux-filesystem copy before changing database settings.

Use SQLite’s own integrity checks on a quiescent copy and preserve the original before attempting repairs. PRAGMA integrity_check can report database structural issues, while quick_check performs a faster subset of checks. Neither verifies that the application-level data is semantically correct or that a backup can be restored.

Collect the SQLite library version and compile options from the same process that opens the database. A system sqlite3 CLI may use a different library build than a language runtime’s bundled SQLite. Compare the app’s SQLite runtime details with the CLI before concluding that the database behaves differently because of WSL.

Acceptance checks for a WSL SQLite workload

Accept the setup when the application and database path are Linux-native for a Linux workload, every process that shares the database runs on the intended host, and concurrency tests demonstrate the expected single-writer behavior. If using WAL, verify the mode at connection startup, confirm that sidecar files are allowed in the containing directory, and exercise a normal close and reopen.

Create an online backup while the app is in its expected operating state, restore it to a separate file, run integrity checks, and execute representative application queries against the restored copy. Confirm that a failed test or WSL stop does not trigger a cleanup routine that removes live database files. The exact durability guarantee comes from SQLite, the filesystem, and the shutdown path together; WSL’s local convenience does not replace backup verification.

If Windows and Linux applications must share a database file, document the exact mount type, WSL version, SQLite library versions, access concurrency, and test evidence. When that cross-boundary arrangement cannot be demonstrated safe for the actual workload, use one process owner or move to a database server rather than relying on file synchronization.

Related:

Sources: