SQLite in Production With Django: WAL, Backups, and One Comparison That Lies
For a single-box read-mostly site SQLite is a real production database. The failures are configuration and operations - plus one comparison that silently returns the wrong rows.
For a single-box, read-mostly site, SQLite is a legitimate production database, and we still reach for Postgres by default on anything else. The reasons SQLite "fails in production" are almost never SQLite. They are four PRAGMAs nobody set, a backup strategy that copies a file mid-write, and - the one that actually cost us a day - a comparison that returns wrong rows with no error and no warning. Everything below was run against real sqlite on this machine; the output is pasted as it came out.
The four PRAGMAs
journal_mode = WAL. In the default rollback-journal mode a writer blocks readers and a reader blocks the writer. With a write-ahead log, readers see a consistent snapshot while a write is in flight, so a single slow write no longer stalls every page on the site. This is the single biggest change and it persists in the database file, so you set it once.
synchronous = NORMAL. In WAL mode sqlite stops issuing an fsync on every commit and syncs at checkpoints instead. The trade is specific: you stay safe against an application crash, but a sudden power loss or kernel panic can lose the most recent transactions. You do not get corruption - that is the point - you get a slightly older, consistent database. Easy trade for a content site; if the last write is money, use FULL.
busy_timeout. The default is zero, and that default is the actual source of most database is locked errors people blame on SQLite. With a timeout of zero, a connection that finds the write lock held gives up instantly rather than waiting the few milliseconds the other writer needs.
foreign_keys = ON. Foreign key enforcement is off by default, and it is a per-connection setting, so every connection has to turn it on. If you have never set it, your foreign keys have been decorative.
In Django, the reliable place to apply all of these is the connection_created signal, because it fires for every connection including the ones the test runner and management commands open:
from django.db.backends.signals import connection_created
from django.dispatch import receiver
@receiver(connection_created)
def configure_sqlite(sender, connection, **kwargs):
if connection.vendor != "sqlite":
return
cursor = connection.cursor()
cursor.execute("PRAGMA journal_mode = WAL;")
cursor.execute("PRAGMA synchronous = NORMAL;")
cursor.execute("PRAGMA busy_timeout = 5000;")
cursor.execute("PRAGMA foreign_keys = ON;")
cursor.execute("PRAGMA temp_store = MEMORY;")
cursor.execute("PRAGMA mmap_size = 134217728;")
cursor.execute("PRAGMA cache_size = -16000;")
Import that module from your app config's ready(). Current Django versions also let you do most of it declaratively, which we prefer where it is available:
DATABASES = {
"default": {
"ENGINE": "django.db.backends.sqlite3",
"NAME": BASE_DIR / "db.sqlite3",
"OPTIONS": {
"init_command": (
"PRAGMA journal_mode=WAL;"
"PRAGMA synchronous=NORMAL;"
"PRAGMA busy_timeout=5000;"
"PRAGMA foreign_keys=ON;"
),
"transaction_mode": "IMMEDIATE",
},
}
}
That transaction_mode is not decoration. By default a transaction begins DEFERRED: no lock is taken until the first statement needs one. A request that reads, thinks, then writes therefore tries to upgrade a read transaction to a write transaction, and if another connection wrote in the meantime sqlite cannot grant it. It returns SQLITE_BUSY immediately, and busy_timeout cannot help, because waiting could only deadlock. BEGIN IMMEDIATE takes the write lock up front, so the wait happens where the busy handler can absorb it.
Here is that whole paragraph as observed behaviour. Two connections, one holding the write lock for a second:
journal_mode -> wal
synchronous -> 1
busy_timeout -> 5000
foreign_keys -> 1
busy_timeout=0: database is locked (after 0.00s)
busy_timeout=5000: write succeeded after 0.84s
deferred upgrade: database is locked (after 0.00s)
With busy_timeout=0, instant failure. With five seconds of patience the write lands after 0.84s - the time the other writer needed. And the deferred transaction trying to upgrade fails instantly despite a five-second timeout: the case people describe as "busy_timeout doesn't work".
The comparison that lies
This is the one to remember. SQLite does not have column types the way other databases do; it has storage classes, and when it compares two values of different storage classes it does not convert them. It orders them: NULL < INTEGER/REAL < TEXT < BLOB. Every number sorts before every string, regardless of what the string says.
Now take a price filter written as raw SQL, with a ceiling arriving from a query string - which means it arrives as a Python str, which binds as TEXT:
import sqlite3
con = sqlite3.connect("prices.sqlite3")
con.execute("""CREATE TABLE offer (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL,
price_eur REAL NULL
)""")
con.executemany(
"INSERT INTO offer (name, price, price_eur) VALUES (?, ?, ?)",
[("cheap", 9.0, None), ("mid", 18.0, 15.0), ("dear", 25.0, None)],
)
con.commit()
# This is how it arrives in real life: straight off the query string.
ceiling = "20.0"
print("raw comparison, ceiling =", repr(ceiling))
for row in con.execute(
"SELECT name, COALESCE(price_eur, price) FROM offer "
"WHERE COALESCE(price_eur, price) <= ? ORDER BY id", (ceiling,)
):
print(" ", row)
print("with CAST on both sides")
for row in con.execute(
"SELECT name, COALESCE(price_eur, price) FROM offer "
"WHERE CAST(COALESCE(price_eur, price) AS REAL) <= CAST(? AS REAL) ORDER BY id",
(ceiling,),
):
print(" ", row)
print("typeof() on the operands")
print(" ", con.execute(
"SELECT typeof(price), typeof(COALESCE(price_eur, price)), typeof(?) "
"FROM offer WHERE name = 'dear'", (ceiling,)
).fetchone())
print(" 25 <= '20.0' ->", con.execute("SELECT 25 <= '20.0'").fetchone()[0])
print(" 25 <= 20.0 ->", con.execute("SELECT 25 <= 20.0").fetchone()[0])
raw comparison, ceiling = '20.0'
('cheap', 9.0)
('mid', 15.0)
('dear', 25.0)
with CAST on both sides
('cheap', 9.0)
('mid', 15.0)
typeof() on the operands
('real', 'real', 'text')
25 <= '20.0' -> 1
25 <= 20.0 -> 0
A product priced at 25 came back from a filter asking for everything at 20 or under. No error, no warning, no exception - a plausible-looking result set that happens to be wrong. And the last two lines are the whole explanation: 25 <= '20.0' is TRUE, because the integer sorts before the string. 25 <= 20.0 is FALSE, as you would hope.
The reason it bites here specifically is COALESCE(). Column affinity converts a bound parameter to the column's type when you compare against a bare column, so price <= ? would have quietly worked. But COALESCE(price_eur, price) is an expression, expressions have no affinity, and nothing rescues the comparison. The fix is to stop relying on affinity and say what you mean on both sides:
SELECT name, COALESCE(price_eur, price) AS effective_price
FROM offer
WHERE CAST(COALESCE(price_eur, price) AS REAL) <= CAST(? AS REAL)
ORDER BY effective_price;
The diagnostic is SELECT typeof(...) on both operands, as above. Where else to look: any raw bound comparison against a numeric column, and in particular price floors and ceilings, rating thresholds, stock levels, and date cutoffs stored as text. Django's ORM is not affected - it knows its field types and adapts parameters - so this is strictly a raw-SQL and extra() problem.
The general point, which is bigger than SQLite. The same statement behaves correctly on MySQL and Postgres, which coerce '20.0' to a number or refuse the comparison outright. So a green test suite on SQLite does not prove your raw SQL is safe on Postgres, and a green suite on Postgres does not prove it is safe on SQLite. If you test on one engine and deploy on another, every piece of hand-written SQL is untested code. Either test against the engine you deploy on, or do not write raw SQL.
Backups
cp db.sqlite3 backup.sqlite3 on a live WAL-mode database is not a backup. You get a file copied over some interval while pages were being rewritten, without the matching -wal file, and you find out it is torn at the worst possible moment. Use the online backup API, which takes a consistent snapshot while the application keeps running - either sqlite3 db.sqlite3 ".backup out.sqlite3" or Connection.backup() from Python.
We tested exactly that: a thread inserting rows in a tight loop while the backup ran.
integrity_check -> ok
backup rows -> 1028
live rows -> 1032
backup is a consistent snapshot, not necessarily the newest one
Four rows landed after the snapshot point, which is correct: a backup is consistent as of a moment, not a freeze of the universe. The script we install:
#!/usr/bin/env bash
set -euo pipefail
DB=/srv/myapp/db.sqlite3
DEST=/var/backups/myapp
KEEP=14
STAMP=$(date -u +%Y%m%dT%H%M%SZ)
mkdir -p "$DEST"
# .backup uses the online backup API: consistent snapshot, app keeps running.
sqlite3 "$DB" ".backup '$DEST/db-$STAMP.sqlite3'"
sqlite3 "$DEST/db-$STAMP.sqlite3" 'PRAGMA integrity_check;' | grep -qx ok
gzip -9 "$DEST/db-$STAMP.sqlite3"
# Keep the newest $KEEP, delete the rest.
ls -1t "$DEST"/db-*.sqlite3.gz | tail -n +$((KEEP + 1)) | xargs -r rm --
echo "backup ok: $DEST/db-$STAMP.sqlite3.gz"
The integrity_check line is what turns a backup into a verified backup: set -euo pipefail plus that grep -qx ok means a bad snapshot fails the job loudly instead of rotating a good one away. Run it from cron or a systemd timer and copy the result off the box - a backup on the same disk as the database is not a backup either.
The restore drill takes thirty seconds, so there is no excuse for not having done it:
gunzip -c /var/backups/myapp/db-20261030T031500Z.sqlite3.gz > /tmp/restore.sqlite3
sqlite3 /tmp/restore.sqlite3 'PRAGMA integrity_check; SELECT COUNT(*) FROM rows_t;'
# ok
# 1032
Do it on a schedule, not after an incident. A backup you have never restored is a hypothesis.
The boundaries
"SQLite doesn't scale" is not a technical statement. The real constraints are three.
One writer at a time. WAL lets readers run concurrently with the writer, but writers serialise. If your steady state has sustained concurrent writes - order intake, event ingestion, anything append-heavy - you will spend your life tuning busy_timeout. As a rough marker, dozens of writes per second on a normal SSD is comfortable; hundreds sustained is not what this tool is for.
No network access. The database is a file on a local disk. The moment you want two app servers behind a load balancer, SQLite is over - and network filesystems are not a workaround, because their locking is not reliable enough to bet a database on. One box means one box.
No concurrent maintenance. No online ALTER on a large table, no replica to run analytics against, no connection pooler. Long-running reports compete with your web requests for the same file.
Inside those limits - one server, read-mostly, a database that fits comfortably on the disk - SQLite gives you no network round-trip, no second daemon to run, and a backup that is a single file. That is the shape of most of the Django and Python applications we build, and a large part of why they are cheap to keep running. When a project outgrows it, moving to Postgres is a migration rather than a rewrite - provided you did not sprinkle raw SQL everywhere on the way. The rest of what we work on follows the same principle.
When not to do this
Do not pick SQLite because it saves you setting up Postgres. If there is a real chance of a second app server inside a year, or the write pattern is bursty in a way you cannot characterise, start on Postgres. Do not pick it if you need concurrent writes, logical replication, or a read replica for reporting: none of those exist and no configuration creates them. And do not pick it if your hosting gives you an ephemeral filesystem - a database that vanishes on redeploy is not a database, and every platform marketing "just use SQLite" has this footnote. The question is not whether SQLite is good enough; it is whether your deployment shape is a single box with durable storage. If you cannot answer that with a straight yes, the answer is Postgres.
If you have a Django site on SQLite and you are not sure whether it is configured properly or backed up properly, those are both a short read of the settings file and the crontab. Start a project and we will look.
We ship and run our own products. We build yours the same way.
Start a project