Guides / SQLite WAL mode

SQLite WAL mode: how it works, and how to back it up without losing writes

Updated 12 October 2026 · commands run with SQLite 3.43.2 and Python 3

The short version. PRAGMA journal_mode=WAL; makes SQLite write new transactions to a separate app.db-wal file and fold them into app.db later. Readers and the writer stop blocking each other, which is why most web apps on SQLite turn it on.

The catch for backups: until that fold (a checkpoint) happens, your newest rows exist only in the -wal file. Copying app.db on its own misses them. Back up with sqlite3 app.db ".backup backup.db" or VACUUM INTO instead, and check the copy with PRAGMA quick_check.

What WAL mode changes

In SQLite's default mode (delete, a rollback journal), a write changes app.db in place and keeps the old pages in a journal file so it can undo. While it writes, nobody reads.

In WAL mode the database file is left alone during a write. The new pages are appended to app.db-wal, and a reader that starts afterwards looks in the WAL first, then in the database file. Every so often SQLite copies the WAL's pages back into app.db. That is a checkpoint. By default it runs when the WAL reaches 1,000 pages (about 4 MB with 4 KB pages), and once more when the last connection closes.

Readers see app.db plus the WAL. A file copy sees only what is in app.db.

Turning it on

sqlite3 app.db "PRAGMA journal_mode=WAL;"
wal

It prints wal when the switch worked, or the old mode if it did not. The setting is stored in the database file, so you run it once, not on every connection. You will now see two more files beside the database while it is open:

File	What it holds	Back it up?
`app.db`	The database as of the last checkpoint	Not on its own while the app runs
`app.db-wal`	Committed transactions not yet checkpointed	Yes, if you copy files at all. Never delete it to "clean up"
`app.db-shm`	An index into the WAL, rebuilt on open (usually 32 KB)	No

When not to use it

The database is on a network file system (NFS, SMB, or a cloud volume mounted over the network). WAL needs every process to share memory through the -shm file, which only works on one machine.
Single transactions of hundreds of megabytes. The WAL grows to hold the whole transaction before it can be checkpointed.
The file ships on read-only media. Switch back with PRAGMA journal_mode=DELETE; first.

One more thing to watch: a checkpoint cannot finish past a reader that is still open. An app that never lets go of a read transaction (a long-lived cursor, a stuck background job) keeps the WAL growing. If app.db-wal is gigabytes, look for that first.

Why `cp app.db` loses data: a test

We wrote 1,000 rows, forced a checkpoint, wrote 500 more and, with the app's connection still open, copied app.db the way a cron job or a volume snapshot would.

import sqlite3, shutil

db = sqlite3.connect("app.db")
db.execute("PRAGMA journal_mode=WAL")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER)")
db.executemany("INSERT INTO orders (total) VALUES (?)", [(i,) for i in range(1000)])
db.commit()
db.execute("PRAGMA wal_checkpoint(TRUNCATE)")    # 1,000 rows now in app.db
db.executemany("INSERT INTO orders (total) VALUES (?)", [(i,) for i in range(500)])
db.commit()                                      # 500 rows only in app.db-wal

shutil.copy("app.db", "copy.db")
print("rows in app.db: ", db.execute("SELECT count(*) FROM orders").fetchone()[0])
print("rows in copy.db:", sqlite3.connect("copy.db").execute("SELECT count(*) FROM orders").fetchone()[0])

rows in app.db:  1500
rows in copy.db: 1000

Run on macOS with SQLite 3.43.2. The 500 committed rows were in the 16 KB app.db-wal file, which the copy left behind.

Nothing errors. The copy opens, passes an integrity check and looks fine. It is a valid database from a few minutes ago, and you find out on the day you restore it. Copying app.db-wal in a second step does not fix it either: the app can write or checkpoint between the two copies, and the pair no longer matches.

Three backups that do not lose writes

1. The `.backup` command

sqlite3 /var/lib/app/app.db ".backup /var/backups/app-$(date +%F).db"

This uses SQLite's online backup API. It reads the database through SQLite, WAL included, from one consistent snapshot, and the app keeps writing while it runs. Against the same test database it copied all 1,500 rows. Python's sqlite3.Connection.backup() and most language drivers expose the same API, if you would rather run it from your app.

2. `VACUUM INTO`

sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-$(date +%F).db'"

Also a consistent copy, and also 1,500 rows in our test. It writes a compacted file with free pages dropped, so it is usually smaller than the database. It rebuilds the whole file, so it does more I/O than .backup, and the target file must not exist. Needs SQLite 3.27 or newer.

3. Stop the app, then copy

When the last connection closes cleanly, SQLite checkpoints and deletes the WAL, and app.db is the whole database. Stop every process that opens it, check with lsof app.db app.db-wal that nothing still holds it, then copy. If an app.db-wal file is still there, something did not close cleanly: copy it along with the database, or open the database once with sqlite3 and close it to checkpoint.

Check the copy, not the original

sqlite3 /var/backups/app-2026-10-12.db "PRAGMA quick_check; SELECT count(*) FROM orders;"
ok
1500

quick_check catches a torn or truncated file. It does not catch a copy that is valid and old, which is the failure above. Compare something you know about the live database: a row count, the newest created_at, the latest migration version. Run PRAGMA integrity_check on a schedule for the slower, full check.

Restoring

Stop the app.
Move app.db, app.db-wal and app.db-shm aside. Leaving an old -wal next to a restored database lets SQLite replay pages that do not belong to it.
Copy the backup to app.db, owned by the user the app runs as.
Run sqlite3 app.db "PRAGMA journal_mode;". A .backup copy keeps WAL mode; if it prints delete, run PRAGMA journal_mode=WAL; before you start the app.
Start the app and look at the newest record you expect.

Running it nightly

#!/bin/sh
set -eu
out=/var/backups/app-$(date +%F).db
sqlite3 /var/lib/app/app.db ".backup $out"
test "$(sqlite3 "$out" 'PRAGMA quick_check;')" = ok
gzip "$out"
find /var/backups -name 'app-*.db.gz' -mtime +14 -delete

That keeps two weeks on the same disk, which protects you from a bad migration and not from a lost server. Copy the file somewhere else after the gzip, encrypted if it holds customer data. The PostgreSQL to S3 guide has an encrypted upload to a locked bucket that works the same way for this file.

How SafeGrd does it

SafeGrd reads the database through SQLite inside one read transaction, so the WAL's commits are in the copy and writers carry on. The copy goes through PRAGMA integrity_check before anything is uploaded, then it is encrypted on your host and written to a bucket under Object Lock. Each run uploads only the 64 KiB chunks whose pages changed: a 228 MB database with 400 rows updated since the last run uploaded 6.8 MB. On a schedule, SafeGrd restores the newest backup into a new file and checks it.

curl -fsSL https://safegrd.dev/install.sh | sh
safegrd backup --database-url sqlite:///var/lib/app/app.db

safegrd doctor tells you which journal mode the database is in. Every option is in the SQLite docs.

SQLite behaviour from sqlite.org/wal.html and the online backup API.