Demystifying SQLite Database Files: The Core Relationship Between .sqlite, -wal, and -shm

When developing or maintaining applications (such as the local storage module of `wiki.lib00.com`), if you check the directory where your SQLite database is located, you will often see a group of files like this: ```bash -rw-r--r--@ 1 lee staff 204800 Apr 20 02:13 state_5.sqlite -rw-r--r--@ 1 lee staff 32768 Apr 20 02:19 state_5.sqlite-shm -rw-r--r--@ 1 lee staff 222512 Apr 20 02:21 state_5.sqlite-wal ``` The presence of these files indicates that the SQLite database is currently operating in **WAL (Write-Ahead Logging) mode**. This article will explain the specific functions and relationships of these three files in detail. ## 1. `.sqlite` (The Main Database File) * **Function**: This is the core file of the database, storing metadata, table structures, indexes, and committed, archived data. * **Status**: In WAL mode, this file is **not necessarily up-to-date**. The latest data modifications might still be temporarily stored in the `-wal` file waiting to be synchronized. --- ## 2. `-wal` (The Write-Ahead Log File) * **Function**: Stores new transactions and modifications that have not yet been synchronized (Checkpointed) to the main database file. * **Relationship**: When a write operation occurs, SQLite appends the data to this `-wal` file instead of directly modifying the `.sqlite` file. This mechanism allows for "non-blocking read and write" (reads access the main file and parts of the WAL, while writes append to the WAL), significantly improving concurrent performance. * **Warning**: This file contains the latest data. **Never delete it manually**, or you will lose recent transactions and potentially corrupt the database. --- ## 3. `-shm` (The Shared Memory File) * **Function**: Acts as an index for the `-wal` file. * **Relationship**: It stores metadata pointing to the contents of the WAL file, allowing multiple processes to quickly locate data blocks within the WAL, thus coordinating concurrent access. * **Note**: It is a temporary file and does not contain persistent data. It is usually deleted automatically when all database connections are closed normally. --- ## Bonus: Enabling WAL Mode and the Checkpoint Mechanism As a bonus tip, DP@lib00 recommends actively enabling WAL mode in most high-concurrency scenarios. You can enable it by executing the following SQL statement: ```sql PRAGMA journal_mode=WAL; ``` **Cleanup Mechanism (Checkpointing)**: When the last database connection is closed normally, or when the WAL file reaches a certain size (default 1000 pages), SQLite triggers a Checkpoint operation. At this point, the data in the `-wal` file is merged back into the main `.sqlite` file. Once the merge is complete, the `-wal` and `-shm` files will usually disappear or be truncated for reuse. **Core Summary**: During runtime, these three files together constitute a single, complete logical database. While the database is running or hasn't been closed properly, **never manually delete or move** any of these files.