# SQLite in WAL mode in production: the backup that corrupted our database on every deploy

Our web app keeps everything in one SQLite file. The site reads from it, a background worker writes to it, and a crawler imports new records into it all day. For a single-server product this is a great setup: no database server to run, no network hop, backups are "just a file".

That last part turned out to be wrong. For a few hours one September day, every deploy we shipped left the database reporting `database disk image is malformed`. The culprit was a single `cp` line in the deploy script. This post is about how we got there and what we run now.

## Step one: the crawler kept dying with "database is locked"

SQLite's default journal mode is `DELETE` (a rollback journal). In that mode a writer needs exclusive access to the file while it commits, and readers block writers. With the web app holding read transactions and the crawler trying to insert rows, the crawler regularly failed with `database is locked`.

The fix is well known: switch to write-ahead logging. In WAL mode, writes go to a separate `-wal` file and readers keep reading the main file plus whatever part of the WAL is relevant to their snapshot. One writer and many readers can work at the same time.

We set the pragmas on every new connection, through a SQLAlchemy event hook:

```python
from sqlalchemy import create_engine, event

engine = create_engine(
    "sqlite:///data/app.db",
    connect_args={"check_same_thread": False, "timeout": 30},
)

@event.listens_for(engine, "connect")
def sqlite_pragmas(dbapi_conn, _record):
    cur = dbapi_conn.cursor()
    cur.execute("PRAGMA journal_mode=WAL")
    cur.execute("PRAGMA busy_timeout=30000")
    cur.execute("PRAGMA synchronous=NORMAL")
    cur.close()
```

Each line has a job:

- `journal_mode=WAL` is persistent: it is stored in the file itself. Setting it on every connection is harmless and protects you from a fresh copy of the database that was created without it.
- `busy_timeout=30000` tells SQLite to wait up to 30 seconds for a lock instead of failing at once. Two writers no longer collide; the second one waits its turn.
- `synchronous=NORMAL` is the usual partner of WAL. The database stays consistent after a crash; you can lose the last few transactions on power loss, but not corrupt the file. For a job board that trade is fine.

The crawler stopped crashing that day. What we did not think about was everything else on the server that touched the database file.

## Step two: the deploy script that copied the file

Our deploys are pull-based: a cron job on the server fetches `main` and resets the working tree. The database lives in the repository folder but is ignored by git. Out of caution, the script made a copy before resetting and put it back afterwards:

```bash
cp -f data/app.db /var/backups/app.db.bak
git reset --hard origin/main
cp -f /var/backups/app.db.bak data/app.db
```

In rollback-journal mode this was merely sloppy. In WAL mode it is destructive, for two reasons.

First, the copy is incomplete. With WAL enabled, the main file does not contain the latest committed data. Recent transactions live in `app.db-wal` until a checkpoint moves them back into the main file. Copying only `app.db` gives you a snapshot from some earlier checkpoint, possibly with pages from the middle of one.

Second, and much worse, the script copied that stale file back over a database that was open. The web process and the worker still had connections. Their WAL file and shared-memory index (`-shm`) described a newer state of the database than the file that had just appeared under them. SQLite trusts those files. The next reads mixed pages from the old main file with frames from the new WAL, and integrity checks started failing.

The pattern in our logs was impossible to miss once we looked: the malformed-image errors started right after each of three deploys that day. Nothing else in the code had changed.

## Why git reset was never the danger

The irony is that the copy-back was protecting against nothing. The database file is in `.gitignore`. `git reset --hard` only touches tracked files, so it never removes or rewrites an ignored database. The backup made sense as a backup. Restoring it on every run was the bug.

The general lesson: treat a live SQLite database in WAL mode as three files that belong together (`db`, `db-wal`, `db-shm`) and that are owned by whichever process has them open. Copying any one of them with filesystem tools while a connection is open is reading or writing someone else's memory.

## Step three: copying a live database the right way

SQLite ships an online backup API for exactly this. It copies the database page by page through a normal connection, so it sees a consistent snapshot, including everything still sitting in the WAL. Python exposes it as `Connection.backup()`:

```python
import sqlite3

def backup(src_path: str, dst_path: str) -> None:
    src = sqlite3.connect(src_path)
    dst = sqlite3.connect(dst_path)
    try:
        src.backup(dst)   # consistent copy, WAL included
    finally:
        dst.close()
        src.close()
```

The result is a single, self-contained file. It does not need a `-wal` next to it, and you can open it on another machine without surprises.

The deploy script now does three things with the database, in this order:

```bash
python -c "import sqlite3; s=sqlite3.connect('data/app.db'); \
  d=sqlite3.connect('/var/backups/app.db.bak'); s.backup(d); d.close(); s.close()"
git reset --hard origin/main
[ -s data/app.db ] || cp -f /var/backups/app.db.bak data/app.db
```

The restore only happens if the live file is missing or empty, which should never occur. Under normal operation the database is never written by anything except SQLite itself. The malformed-image errors stopped with the next deploy.

## Things we checked along the way

A few smaller facts that matter if you run SQLite this way.

**Backups during heavy writes.** For small databases `backup()` with default arguments copies everything in one step and holds a read lock briefly. For large ones you can pass `pages=` and a `sleep` so the copy yields to writers. If a write happens mid-copy from another connection, the backup restarts, so a very busy database may need the stepped version.

`VACUUM INTO` **is an alternative.** `VACUUM INTO 'backup.db'` also produces a consistent copy and compacts it on the way. It costs more CPU, so we keep it for occasional cold copies rather than the hot path of every deploy.

**Checkpoints happen on their own.** SQLite runs a passive checkpoint when the WAL reaches about 1,000 pages. A long-running reader can stop the WAL from being reset, so it keeps growing. If `-wal` is suddenly large, look for a connection that sits inside a transaction, not at SQLite.

**The connection pool is part of the story.** WAL lets readers run in parallel, which means the number of pooled connections becomes your real concurrency limit. Our first pool of 5 plus 10 overflow ran out under crawler traffic, and the logs showed 223 pool-timeout errors in a week before we raised it. SQLite handled the load; the pool was the bottleneck.

**Never put the database on a network filesystem.** WAL relies on shared memory between processes on the same host. That is fine for one server and a mistake on NFS.

## What we would tell our past selves

- Turn on WAL and `busy_timeout` on day one. "Database is locked" in a single-server app is almost always a missing busy timeout or a long transaction, not a reason to leave SQLite.
- Write down every process that touches the database file, including shell scripts. Ours had one we had forgotten about.
- Use `Connection.backup()` or `VACUUM INTO` for copies. Never `cp`, `rsync` or `tar` on an open WAL database, and never copy anything back over it.
- Keep a restore path, but make it conditional. A backup that restores itself on every run is a time machine pointed at production.

SQLite in production has been boring for us ever since, which is what you want from a database. We use this setup at [SpinHire](https://spinhire.io), a job board for the iGaming industry, where one SQLite file serves the site, the background worker and the crawler at the same time.
