SQLite Reference: CLI, WAL Mode, JSON, Window Functions, Transactions & Litestream

# sqlite3 CLI:
sqlite3 mydb.db          # open or create
sqlite3 :memory:         # in-memory database (gone when process exits)
sqlite3 mydb.db "SELECT * FROM users LIMIT 5;"  # one-shot query

# Useful dot commands:
.help                    # list all dot commands
.tables                  # list tables
.schema users            # show CREATE TABLE for users
.mode column             # aligned column output
.mode table              # box-drawing output (3.33+)
.headers on              # show column names
.output result.csv       # redirect output to file
.mode csv                # CSV output
.import data.csv users   # import CSV into table
.quit

# SQLite type affinity (not strict types — stored as best-fit):
-- INTEGER, REAL, TEXT, BLOB, NUMERIC
CREATE TABLE users (
    id       INTEGER PRIMARY KEY,   -- auto-increment when NULL inserted
    name     TEXT NOT NULL,
    email    TEXT UNIQUE NOT NULL,
    score    REAL DEFAULT 0.0,
    metadata BLOB,                  -- arbitrary binary
    created  TEXT DEFAULT (datetime('now'))  -- ISO 8601 string
);

-- STRICT tables (SQLite 3.37+ — enforce actual types):
CREATE TABLE events (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    ts    INTEGER NOT NULL          -- Unix timestamp
) STRICT;

-- JSON column (native JSON functions in SQLite 3.38+):
CREATE TABLE items (
    id   INTEGER PRIMARY KEY,
    data TEXT CHECK(json_valid(data))  -- validate JSON on insert
);

-- INSERT with RETURNING (SQLite 3.35+):
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
RETURNING id, created;

-- Upsert (INSERT OR REPLACE vs ON CONFLICT):
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'new@example.com')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;

-- CTE (WITH clause):
WITH active_users AS (
    SELECT * FROM users WHERE last_login > date('now', '-30 days')
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
    FROM active_users
)
SELECT * FROM ranked WHERE rank <= 10;

-- Window functions (SQLite 3.25+):
SELECT name, score,
    RANK()         OVER (ORDER BY score DESC)           AS rank,
    LAG(score, 1)  OVER (ORDER BY created)              AS prev_score,
    SUM(score)     OVER (PARTITION BY team ORDER BY created) AS running_total
FROM users;

-- JSON functions (SQLite 3.38+):
SELECT json_extract(data, '$.name') AS name,
       json_extract(data, '$.address.city') AS city
FROM items;

-- json_each: iterate JSON array:
SELECT value FROM items, json_each(items.data, '$.tags');

-- Full-text search (FTS5):
CREATE VIRTUAL TABLE docs_fts USING fts5(title, body, content=docs);
SELECT * FROM docs_fts WHERE docs_fts MATCH 'kubernetes AND deploy';
SELECT highlight(docs_fts, 1, '[', ']') FROM docs_fts WHERE docs_fts MATCH 'deploy';

-- WAL mode: allows concurrent reads while writing (use for all production apps):
PRAGMA journal_mode = WAL;       -- set once, persisted in DB file
PRAGMA synchronous = NORMAL;     -- safer than OFF, faster than FULL with WAL
PRAGMA cache_size = -64000;      -- 64MB page cache (negative = KB)
PRAGMA foreign_keys = ON;        -- OFF by default! Always enable.
PRAGMA temp_store = MEMORY;      -- temp tables in memory

-- Indexes:
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_team_score ON users(team, score DESC);  -- compound
CREATE INDEX idx_users_active ON users(last_login) WHERE active = 1;  -- partial

-- EXPLAIN QUERY PLAN (check if indexes are used):
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'alice@example.com';
-- Good: SEARCH users USING INDEX idx_users_email (email=?)
-- Bad:  SCAN users (full table scan)

-- Covering index (includes all columns needed — no table lookup):
CREATE INDEX idx_covering ON users(team, score, name);
SELECT name, score FROM users WHERE team = 'eng' ORDER BY score DESC;
-- Uses index only, never touches the table row

-- ANALYZE (update query planner statistics):
ANALYZE;        -- run after bulk inserts

-- VACUUM (reclaim space after many deletes):
VACUUM;         -- rewrites whole DB — can be slow; use VACUUM INTO for backup
VACUUM INTO 'backup.db';

-- Transaction types (important — SQLite locking model is different from Postgres):
BEGIN;                          -- deferred: read lock on first read, write lock on first write
BEGIN IMMEDIATE;                -- write lock immediately (prevents "database is locked" on concurrent writes)
BEGIN EXCLUSIVE;                -- exclusive lock, blocks all other connections

-- Best practice for writes: always use BEGIN IMMEDIATE:
BEGIN IMMEDIATE;
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);
UPDATE inventory SET qty = qty - 1 WHERE product_id = 42;
COMMIT;
-- ROLLBACK on error

-- Savepoints (nested transactions):
SAVEPOINT my_save;
-- ... operations ...
ROLLBACK TO SAVEPOINT my_save;   -- roll back to savepoint only
RELEASE SAVEPOINT my_save;       -- commit the savepoint

-- WAL concurrency model:
-- Multiple readers: YES, simultaneously
-- One writer + multiple readers: YES (WAL mode only)
-- Multiple writers: NO — serialized at DB level
-- Implication: SQLite handles up to ~10K writes/sec on SSD with WAL

-- Python connection settings for concurrent web apps:
import sqlite3
conn = sqlite3.connect("mydb.db", check_same_thread=False, timeout=30)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA foreign_keys=ON")
conn.execute("PRAGMA synchronous=NORMAL")
conn.row_factory = sqlite3.Row     # dict-like row access: row["name"]

-- Schema versioning with user_version PRAGMA:
PRAGMA user_version;                    -- read current version
PRAGMA user_version = 3;               -- set to version 3

-- Migration runner pattern (check version, apply needed migrations):
-- Python:
version = conn.execute("PRAGMA user_version").fetchone()[0]
if version < 1:
    conn.executescript("""
        ALTER TABLE users ADD COLUMN avatar_url TEXT;
        PRAGMA user_version = 1;
    """)
if version < 2:
    conn.executescript("""
        CREATE INDEX idx_users_created ON users(created);
        PRAGMA user_version = 2;
    """)

-- Online backup (safe to run while DB is in use):
sqlite3 mydb.db ".backup backup.db"
-- Or in Python:
backup_conn = sqlite3.connect("backup.db")
conn.backup(backup_conn)    # copies while DB is live

-- Litestream (stream WAL to S3/GCS — continuous replication, near-zero RPO):
# litestream.yml:
# dbs:
#   - path: /data/mydb.db
#     replicas:
#       - url: s3://my-bucket/mydb
# Run: litestream replicate -config litestream.yml
# Restore: litestream restore -config litestream.yml s3://my-bucket/mydb /data/mydb.db

-- Turso (distributed SQLite — edge replicas, HTTP API):
-- libsql driver, drop-in replacement for sqlite3 driver

-- When to use SQLite in production (2026):
-- YES: single-server apps, <10K writes/sec, read-heavy, edge/serverless (Cloudflare D1)
-- YES: embedded analytics, feature flags, config, session storage
-- NO: multiple write-heavy servers, need row-level locking, complex access control