Skip to content

SQLite Operations & Internals - Primer

Why This Matters

SQLite is the most deployed database in the world — it runs on every smartphone, inside every web browser, and in countless embedded systems. But it is increasingly used as a serious application database. Litestream replicates SQLite to S3 continuously. LiteFS distributes it across a cluster. Turso offers managed edge SQLite. For DevOps teams, SQLite matters because it shows up everywhere: Prometheus uses it, Grafana uses it, many internal tools use it, and a growing number of production applications choose it over client-server databases for simplicity. Understanding WAL mode, backup strategies, concurrency limits, and performance tuning lets you support these workloads correctly.

Core Concepts

1. CLI Usage

# Open or create a database
sqlite3 myapp.db

# Execute a command
sqlite3 myapp.db "SELECT count(*) FROM users;"

# Dot commands (SQLite shell built-ins)
.databases       -- list attached databases
.tables          -- list tables
.schema users    -- show CREATE TABLE for users
.headers on      -- show column headers in output
.mode column     -- aligned column output
.mode csv        -- CSV output
.mode json       -- JSON output (SQLite 3.33+)

# Import CSV
.mode csv
.import users.csv users

# Export to CSV
.headers on
.mode csv
.output users_export.csv
SELECT * FROM users;
.output stdout

# Dump entire database as SQL
sqlite3 myapp.db .dump > backup.sql

# Load a SQL dump
sqlite3 newdb.db < backup.sql

# Run a SQL file
sqlite3 myapp.db < schema.sql

# Check database integrity
sqlite3 myapp.db "PRAGMA integrity_check;"

# Show database page size and stats
sqlite3 myapp.db "PRAGMA page_size;"
sqlite3 myapp.db "PRAGMA page_count;"
sqlite3 myapp.db "PRAGMA freelist_count;"

2. WAL Mode (Write-Ahead Logging)

By default, SQLite uses rollback journal mode — writers block readers and readers block writers. WAL mode changes this: readers never block writers and writers never block readers. This is the single most important performance setting.

-- Enable WAL mode (do this once, it persists)
PRAGMA journal_mode=WAL;

-- Check current journal mode
PRAGMA journal_mode;

-- WAL checkpoint (flush WAL to main database file)
PRAGMA wal_checkpoint(TRUNCATE);
-- PASSIVE: checkpoint what you can without blocking
-- FULL: wait for readers to finish, then checkpoint
-- TRUNCATE: same as FULL, then truncate WAL file to zero bytes

WAL mode trade-offs: - Readers and writers can operate concurrently (major win) - Still only one writer at a time (SQLite's fundamental limit) - WAL file can grow large under sustained write load — checkpoint regularly - Not suitable for network filesystems (NFS, SMB) — requires shared-memory primitives - WAL mode creates two extra files: myapp.db-wal and myapp.db-shm

-- Auto-checkpoint threshold (default 1000 pages)
PRAGMA wal_autocheckpoint = 1000;

3. Backup Strategies

# Online backup API (safe even during writes in WAL mode)
sqlite3 myapp.db ".backup /backups/myapp-$(date +%Y%m%d).db"

# Using the backup command from another connection
sqlite3 myapp.db "VACUUM INTO '/backups/myapp-snapshot.db';"
# VACUUM INTO creates a compacted copy without blocking writers

# File copy (only safe if NO writers are active)
# In WAL mode, you must copy .db, .db-wal, and .db-shm together
cp myapp.db myapp.db-wal myapp.db-shm /backups/

# Continuous replication with Litestream
# Install litestream, then configure:
# /etc/litestream.yml
# dbs:
#   - path: /data/myapp.db
#     replicas:
#       - url: s3://mybucket/myapp

# Start Litestream replication
litestream replicate -config /etc/litestream.yml

# Restore from S3
litestream restore -o /data/myapp.db s3://mybucket/myapp

4. Performance Tuning

-- Essential PRAGMAs for performance
PRAGMA journal_mode = WAL;          -- concurrent reads/writes
PRAGMA synchronous = NORMAL;        -- safe with WAL, faster than FULL
PRAGMA cache_size = -64000;         -- 64MB page cache (negative = KB)
PRAGMA temp_store = MEMORY;         -- temp tables in memory
PRAGMA mmap_size = 268435456;       -- 256MB memory-mapped I/O
PRAGMA busy_timeout = 5000;         -- wait 5s for locks instead of failing immediately

-- Analyze tables for query planner
ANALYZE;

-- Check query plan
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42;

-- Create indexes for common queries
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at DESC);

-- Covering index (index contains all needed columns)
CREATE INDEX idx_orders_cover ON orders(user_id, status, total);
-- Query "SELECT status, total FROM orders WHERE user_id = 42" uses only the index

-- Check index usage
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42 AND status = 'active';
-- Look for "USING INDEX" vs "SCAN TABLE"

-- Compact the database (reclaims free pages)
VACUUM;
-- Warning: VACUUM rewrites the entire database, needs 2x disk space temporarily

5. Concurrency and Locking

SQLite supports one writer at a time, regardless of mode. Understanding the locking model prevents mysterious SQLITE_BUSY errors.

Lock states: | State | Meaning | |-------|---------| | UNLOCKED | No locks held | | SHARED | Reading — multiple readers allowed | | RESERVED | Planning to write — one at a time, readers still allowed | | PENDING | Waiting for readers to finish before writing | | EXCLUSIVE | Writing — no other access |

-- Set busy timeout (wait instead of failing immediately)
PRAGMA busy_timeout = 5000;  -- 5 seconds

-- In application code, always handle SQLITE_BUSY
-- Retry logic is essential for concurrent access

Concurrency best practices: - Use WAL mode (readers do not block writers) - Keep write transactions short — long transactions hold RESERVED locks - Use BEGIN IMMEDIATE for write transactions to fail fast on contention - Set busy_timeout to avoid immediate SQLITE_BUSY errors - For high write concurrency, consider a write queue pattern (single writer goroutine/thread)

-- Immediate transaction (acquires RESERVED lock at BEGIN, not at first write)
BEGIN IMMEDIATE;
INSERT INTO events (type, data) VALUES ('click', '{"page": "/home"}');
COMMIT;

6. Embedding Patterns

SQLite in application code (Python example): 

import sqlite3
from contextlib import contextmanager

@contextmanager
def get_db(path="app.db"):
    conn = sqlite3.connect(path, timeout=5.0)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.execute("PRAGMA cache_size=-64000")
    conn.execute("PRAGMA busy_timeout=5000")
    conn.row_factory = sqlite3.Row  # access columns by name
    try:
        yield conn
    finally:
        conn.close()

# Usage
with get_db() as conn:
    cursor = conn.execute("SELECT * FROM users WHERE active = 1")
    users = cursor.fetchall()

7. Monitoring and Diagnostics

-- Database file size and fragmentation
PRAGMA page_size;         -- bytes per page (default 4096)
PRAGMA page_count;        -- total pages
PRAGMA freelist_count;    -- unused pages (fragmentation indicator)
-- If freelist_count is large relative to page_count, VACUUM

-- Compile-time options
PRAGMA compile_options;

-- Check for corruption
PRAGMA integrity_check;    -- thorough, can be slow on large DBs
PRAGMA quick_check;        -- faster, less thorough

-- Table statistics (after ANALYZE)
SELECT * FROM sqlite_stat1;

Quick Reference

# Database info
sqlite3 myapp.db "PRAGMA journal_mode; PRAGMA page_size; PRAGMA page_count;"

# Safe backup during writes
sqlite3 myapp.db ".backup /tmp/myapp-backup.db"

# Compact database
sqlite3 myapp.db "VACUUM;"

# Integrity check
sqlite3 myapp.db "PRAGMA integrity_check;"

# Performance PRAGMAs (run at connection open)
# journal_mode=WAL, synchronous=NORMAL, cache_size=-64000
# busy_timeout=5000, temp_store=MEMORY

# File management — WAL mode creates three files
# myapp.db (main), myapp.db-wal (write-ahead log), myapp.db-shm (shared memory)
# All three must be kept together; never delete -wal or -shm while DB is open

Wiki Navigation

Related Content