The File IS the Database

One file, byte-stable since 2004

A SQLite database is a single file with a well-defined, stable, cross-platform binary format. The format has been stable since version 3.0.0 (2004) and is committed by the SQLite team to remain compatible through at least 2050. A file created on macOS opens identically on Windows, Linux, a Raspberry Pi, or an Android phone — byte for byte.

Common extensions are .db, .sqlite, .sqlite3 — but any extension works. SQLite does not care about filenames; it inspects the file header to identify itself.

When you turn on WAL (Write-Ahead Logging) mode — which you almost always should for any concurrent workload — you'll see two side-files alongside your database:

myapp.db-wal — the Write-Ahead Log, containing recent changes not yet checkpointed into the main file.
myapp.db-shm — a shared memory file used to coordinate concurrent readers.

These files are managed automatically by SQLite. When the last connection closes cleanly they are typically removed. Always copy all three files together when backing up a WAL-mode database, or use the official .backup command which serializes a consistent snapshot.

Warning: Naive cp of a WAL-mode database while writers are active can produce a corrupt copy. Use sqlite3 source.db ".backup target.db" or the C-level sqlite3_backup API. Cron + cp is a classic source of "my backups silently corrupted for two years" stories.

Code

What's actually on disk·bash

ls -la myapp.db*
# -rw-r--r--  myapp.db       28672 bytes  (the database)
# -rw-r--r--  myapp.db-wal   45280 bytes  (recent changes, WAL mode)
# -rw-r--r--  myapp.db-shm   32768 bytes  (shared-memory index for WAL)

# Inspect the file header — the magic string is literally 'SQLite format 3\0'
head -c 16 myapp.db | xxd
# 00000000: 5351 4c69 7465 2066 6f72 6d61 7420 3300
#           S Q L i t e   f o r m a t   3 .

Safe backup with the .backup command·bash

# Online backup — works while writers are active
sqlite3 myapp.db ".backup nightly-$(date -I).db"

# Same thing from Python — uses the C-level sqlite3_backup API
python3 -c "
import sqlite3, datetime
src = sqlite3.connect('myapp.db')
dst = sqlite3.connect(f'nightly-{datetime.date.today()}.db')
src.backup(dst)
src.close(); dst.close()
"

Exercise

Create a small SQLite database with a few rows, switch it to WAL mode (PRAGMA journal_mode=WAL;), then try three backup approaches while a Python loop continuously writes new rows: (1) raw cp, (2) sqlite3 .backup, (3) sqlite3_backup from Python. Open each backup and check whether row counts and integrity match. Note which methods produced consistent snapshots and which did not.

The File IS the Database

One file, byte-stable since 2004

Code

External links

Exercise

Progress

Comments 0