We published a blog post about running SQLite in production. Four days later, a stranger posted it to Hacker News. The community read it, picked it apart, and found a real bug in our approach.
This is the story of what they caught, how we fixed it, and the backup script we should have had all along.
Correction — July 26, 2026. The backup script originally published in this post was broken. It pointed at a storage directory that has never existed in this application, so every database took the "file not found" branch, printed
SKIP, and the script exited 0. It reported success while backing up nothing, and it did that for three months. The script below has been corrected, and the write-up of what went wrong is at the end of this post. Thecp→sqlite3 .backupadvice was right. The script we wrapped around it was not.
What We Said
Near the end of the original post, tossed off as a SQLite benefit over Postgres:
Backups are
cp production.sqlite3 backup.sqlite3.
One line. No pg_dump, no connection strings. Simple. Elegant.
Wrong.
What HN Told Us
User infamia flagged it:
SQLite has a '.backup' command that you should always use to backup a SQLite DB. You're risking data loss/corruption using 'cp' to backup your database as prescribed in the article.
They're right. Here's why.
SQLite in WAL mode maintains three files: the main database, the write-ahead log (-wal), and a shared-memory file (-shm). The log is the critical piece — it holds every recent write that hasn't been checkpointed back into the main database yet.
cp on just the database file gives you a snapshot without the uncommitted log entries. If the last checkpoint was an hour ago, you've lost an hour of data. Worse: if a write is in progress during the copy, you capture a half-written page. The file opens without complaint. Data is silently missing, or corrupted, or both.
Copying all three files doesn't fix it either. They're only consistent relative to each other at one instant, and copying them sequentially means the log can gain entries before you finish. You'd need to freeze writes, copy atomically, then release — which is exactly what the .backup API does for you.
The irony: the same post had an entire section on WAL mode, describing how writers append to the log instead of modifying the database directly. Three sections later, it recommended copying just the main file as if WAL didn't exist. The community noticed.
The Right Way: sqlite3 .backup
SQLite's .backup command (and its C API equivalent, sqlite3_backup_init) creates a consistent snapshot while the database is being written to. It takes the necessary locks, copies all pages including pending WAL entries, and produces a self-contained file.
The whole fix is one line:
# BEFORE: naive copy (risks corruption under WAL)
cp storage/production.sqlite3 backups/production-$(date +%Y%m%d).sqlite3
# AFTER: safe backup via sqlite3 .backup API
sqlite3 storage/production.sqlite3 ".backup backups/production-$(date +%Y%m%d).sqlite3"
Same length. Same result on a quiescent database. Very different behavior under load.
Under the hood, .backup opens a read transaction on the source, giving it a consistent view of every page, then copies those pages — log content included — into the destination. If the source is written to mid-copy, it detects the change and restarts. The result is always a consistent, self-contained database file.
cp knows none of this. It copies bytes. If those bytes are mid-rearrangement during a WAL checkpoint, you get a file that opens fine and has missing pages you won't discover until you read the affected rows.
Building a Real Backup Script
Once you accept that .backup is the right primitive, you want a script that handles all four of our SQLite databases, timestamps them, and cleans up old ones. The core of our bin/backup:
#!/usr/bin/env bash
set -euo pipefail
# Database paths are READ FROM config/database.yml — the same file Rails reads.
# Do not hardcode a second copy. (See the correction at the end of this post.)
DB_PATHS=()
while IFS= read -r line; do
[ -n "$line" ] && DB_PATHS+=("$line")
done < <(ruby -ryaml -e '
cfg = YAML.safe_load(File.read("config/database.yml"), aliases: true)
block = cfg[ENV.fetch("RAILS_ENV", "production")]
entries = block.key?("database") ? [ block ] : block.values.grep(Hash)
puts entries.map { |e| e["database"] }.compact.uniq
')
# Backups get their own directory, never the live database directory —
# otherwise a cleanup glob can reach a real database file.
BACKUP_DIR="$(dirname "${DB_PATHS[0]}")/backups"
TIMESTAMP="$(date -u +%Y%m%d_%H%M%S)"
mkdir -p "$BACKUP_DIR"
for db_path in "${DB_PATHS[@]}"; do
db_file="$(basename "$db_path")"
backup_path="${BACKUP_DIR}/${db_file%.sqlite3}_backup_${TIMESTAMP}.sqlite3"
sqlite3 "$db_path" ".backup '${backup_path}'"
# Verify the COPY, not the source. A backup nobody has read is not a backup.
integrity="$(sqlite3 "$backup_path" "PRAGMA integrity_check;" | head -1)"
[ "$integrity" = "ok" ] || { echo " FAIL ${db_file} (${integrity})" >&2; exit 1; }
echo " OK ${db_file} → $(basename "$backup_path")"
done
The full script also has --list and --clean N. Four databases — primary, cache, queue, cable. The last two regenerate on restart, so you could skip them, but having all four lets you restore an exact point-in-time state when you're debugging a deploy that went sideways at 2am.
One non-obvious ordering detail: cleanup runs before the new backups are written, not after. A reclaim step gated behind the operation it protects can't recover a disk that's already full — we learned that separately, at some cost. Retention is three days rather than the seven we first wrote down: a full set is ~380 MB, and the real disk pressure on this box is Docker images.
The Other Feedback
The backup issue was the most critical correction, but it wasn't the only useful feedback from the thread.
sgbeal clarified SQLite's JSON operator nuances. We'd mentioned that json_extract returns native types and the need to CAST to text. sgbeal went deeper: -> returns the value as JSON text, ->> extracts it as a SQL-native type. Knowing which is which prevents a whole class of comparison bugs where integer 1 doesn't match string "1":
-- -> returns JSON text (always a string)
SELECT typeof('{"id":1}' -> '$.id'); -- 'text'
-- ->> returns the native SQL type
SELECT typeof('{"id":1}' ->> '$.id'); -- 'integer'
This matters every time you write a WHERE clause against JSON-extracted values. CAST(json_extract(...) AS TEXT) works as a safety net, but knowing the operator semantics is the better fix.
leosanchez suggested gobackup — a self-hosted tool that runs as a container and ships backups offsite on a schedule. For teams that want automated rotation without writing shell scripts, it's a more complete answer than what we built above. We haven't adopted it; a single-server setup doesn't justify the container overhead yet.
Update, July 2026: The Script We Published Was Broken
Three months after this post went up, an audit of our own tooling found that the production databases had never been backed up. Not once. Three independent faults, none of which threw an error.
The path was wrong. STORAGE_DIR="storage/production" — a directory that has never existed in this application. The databases live one level up. Every one of them hit the if [ ! -f "$db_path" ] branch, printed SKIP, and returned 0. The script printed its summary and exited 0. Nothing in the output said "failure," because as far as the script was concerned, nothing had failed.
Nothing ran it. No scheduler, no cron entry, no deploy step. A carefully reviewed script that lived in bin/ and was invoked by nobody.
Nobody had ever read a backup. There were none to read. The only disk snapshots that existed predated the store taking its first order.
The community caught the wrong primitive because the primitive was the part we published. Nobody could catch the wiring, because the wiring wasn't in the post — and we couldn't catch it either, because a wrong path in a shell script fails by doing nothing.
Three rules came out of the fix.
Derive paths, don't retype them. config/database.yml is where Rails looks for these files. A second hardcoded copy is a copy that can rot, and this one rotted on day one — it was wrong the moment it shipped. There is now one source of truth, and it's the one the application itself depends on being correct.
A skip is not a success. The dangerous branch was never the failure path — it was the "nothing to do here" path. Skipping a missing file is reasonable for a script that handles optional databases. It is catastrophic for a script whose entire job is to produce files, running unattended, with nobody reading the log. A run that produced zero backups should exit non-zero and say so.
Verify the copy, not the source. Every backup is now re-opened and checked with PRAGMA integrity_check, plus a row count against a table we know is non-empty. We proved the check is load-bearing rather than decorative by corrupting a page in a source database: .backup still succeeded, and the integrity check failed the run. A backup nobody has read is not a backup — it's a file with a promising name.
Why This Matters Beyond Backups
A stranger found our post interesting enough to share. A community of engineers read it, and one of them caught a bug we'd missed. We didn't pay for that code review, and we didn't schedule it. Someone told us we were wrong, and they were right.
That's the argument for publishing honestly about how your system works. The update above is also its limit: readers can only review what you show them, and what we showed them was the interesting one-line decision, not the plumbing wrapped around it. The plumbing is where it broke.
So: if you're running SQLite in production, switch from cp to sqlite3 .backup today. It's a one-line change. Then run your backup script, read every line of its output, and restore a real database from the file it produced. The first part is advice. The second is the only thing that proves any of it is true.
Next time: Stripe Webhooks in Rails — the gotchas nobody warns you about, from dual-path payment races to the three API calls it takes to extract a single fee amount.
