SQLite

sqlite3 app.db                                    # interactive; creates the file on first write
sqlite3 -readonly app.db                          # refuse writes
sqlite3 -header -column app.db 'SELECT * FROM users LIMIT 5'
sqlite3 -json app.db 'SELECT id, name FROM users' | jq '.[0]'
sqlite3 -csv -header app.db 'SELECT * FROM users' > users.csv
sqlite3 app.db < schema.sql                       # run a script; add -bail to stop at the first error
sqlite3 :memory: 'SELECT sqlite_version()'        # in-memory scratch database

.tables                    -- also .tables 'user%'
.schema users              -- CREATE statements; .schema --indent for formatting
.fullschema                -- includes the sqlite_stat tables
.indexes users
.mode box                  -- box, table, column, markdown, json, csv, tabs, line, insert, quote
.headers on
.width 8 30 0              -- fixed column widths in column mode; 0 is auto
.nullvalue NULL            -- render NULL visibly
.timer on                  -- CPU and wall time per statement
.eqp on                    -- print EXPLAIN QUERY PLAN before each statement; .eqp full includes the bytecode
.changes on                -- print rows changed after each statement
.import --csv users.csv users            -- creates the table with TEXT columns if it does not exist; otherwise columns must match
.import --csv --skip 1 users.csv users   -- skip the header when the table already exists
.output report.txt         -- redirect until .output stdout; .once writes only the next statement's output
.dump users                -- SQL text for one table; .dump alone for the whole database
.read migration.sql
.backup app-backup.db      -- online backup through the backup API: consistent while others write
.restore app-backup.db     -- overwrite the open database from a backup
.recover                   -- best-effort SQL from a corrupted file
.dbinfo                    -- page size, page count, freelist, schema cookie
.databases                 -- attached databases and their files
.parameter set :id 42      -- bind a parameter for the following statements
.shell ls -l               -- run a shell command
.exit

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,                 -- alias for the 64-bit rowid: fastest key, reused after delete unless AUTOINCREMENT
  email      TEXT NOT NULL UNIQUE COLLATE NOCASE, -- case-insensitive uniqueness and lookup
  name       TEXT NOT NULL,
  active     INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1)),   -- there is no boolean type; TRUE and FALSE are 1 and 0
  balance    INTEGER NOT NULL DEFAULT 0,          -- cents; REAL is binary floating point
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),   -- ISO-8601 UTC text sorts correctly
  settings   TEXT CHECK (settings IS NULL OR json_valid(settings))
) STRICT;                                         -- 3.37+: rejects values that do not fit the declared type

CREATE TABLE tags (name TEXT PRIMARY KEY, colour TEXT) WITHOUT ROWID;   -- clustered on the key; good for small-key lookup tables

SELECT typeof(id), typeof(email), typeof(balance) FROM users LIMIT 1;    -- integer text integer
SELECT '42' = 42, 42 = 42.0, 'a' < 1, x'00' > 'z';                       -- 0 1 0 1: NULL < INTEGER/REAL < TEXT < BLOB in comparisons

PRAGMA journal_mode = WAL;             -- returns 'wal' on success; needs no other connection to hold a lock
PRAGMA synchronous = NORMAL;           -- per connection: fsync at checkpoint, not every commit; durable against process crash, may lose the last transactions on power loss
PRAGMA busy_timeout = 5000;            -- per connection: retry a locked database for up to 5 s instead of SQLITE_BUSY immediately
PRAGMA foreign_keys = ON;              -- per connection
PRAGMA cache_size = -65536;            -- per connection: 64 MiB of page cache (negative is KiB, positive is pages)
PRAGMA temp_store = MEMORY;            -- sorts and temp tables in RAM
PRAGMA mmap_size = 268435456;          -- read pages through a 256 MiB memory map; skips a copy
PRAGMA wal_autocheckpoint = 1000;      -- default: checkpoint when the WAL passes 1000 pages (about 4 MB)
PRAGMA wal_checkpoint(TRUNCATE);       -- force a checkpoint and reset the WAL to zero bytes; blocks until readers finish
PRAGMA journal_size_limit = 67108864;  -- cap the WAL file at 64 MiB after checkpoint
PRAGMA auto_vacuum = INCREMENTAL;      -- must be set before any table exists, or run VACUUM after changing it
PRAGMA page_size = 4096;               -- default since 3.12; only changes with VACUUM
PRAGMA secure_delete = ON;             -- overwrite deleted content; slower
PRAGMA journal_mode;                   -- query any pragma by omitting the value

BEGIN;                                  -- DEFERRED: takes no lock until the first read or write
BEGIN IMMEDIATE;                        -- take the write lock now; fails fast with SQLITE_BUSY or waits busy_timeout
BEGIN EXCLUSIVE;                        -- in WAL mode the same as IMMEDIATE; in rollback mode also blocks readers
INSERT INTO users (email, name) VALUES ('a@example.com', 'A');
SAVEPOINT s1; UPDATE users SET active = 0 WHERE id = 1; ROLLBACK TO s1; RELEASE s1;
COMMIT;

INSERT INTO users (email, name, settings) VALUES ('b@example.com', 'B', '{"theme":"dark","tags":["ops","db"],"limits":{"rows":500}}');

SELECT settings ->> 'theme',                       -- 'dark'   (->> returns SQL text/number; -> returns JSON text with quotes)
       settings -> 'limits' ->> 'rows',            -- 500
       json_extract(settings, '$.tags[0]'),        -- 'ops'
       json_array_length(settings, '$.tags'),      -- 2
       json_type(settings, '$.limits')             -- 'object'
FROM users WHERE email = 'b@example.com';

UPDATE users SET settings = json_set(settings, '$.theme', 'light', '$.limits.rows', 1000);     -- set or insert
UPDATE users SET settings = json_insert(settings, '$.beta', true);                             -- insert only if absent
UPDATE users SET settings = json_remove(settings, '$.tags[1]');
UPDATE users SET settings = json_patch(settings, '{"limits":{"rows":null}}');                 -- RFC 7396 merge patch: null deletes

SELECT u.email, t.value FROM users u, json_each(u.settings, '$.tags') t;                     -- one row per array element
SELECT key, value, type, fullkey FROM json_tree('{"a":{"b":[1,2]}}');                         -- recursive walk
SELECT json_group_array(json_object('id', id, 'email', email)) FROM users WHERE active = 1;   -- rows to a JSON array
SELECT json_group_object(email, name) FROM users;

CREATE INDEX users_theme ON users (settings ->> 'theme');                                    -- expression index; the query must use the same expression
SELECT * FROM users WHERE settings ->> 'theme' = 'dark';
CREATE TABLE events (id INTEGER PRIMARY KEY, body TEXT, kind TEXT GENERATED ALWAYS AS (body ->> 'kind') VIRTUAL);   -- 3.31+: index the generated column

CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, body TEXT, published_at TEXT);

CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, body,
  content = 'articles', content_rowid = 'id',     -- external content: read title and body from articles
  tokenize = 'porter unicode61 remove_diacritics 2'   -- stemming, Unicode case folding, accent-insensitive
);

-- keep the index in sync
CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  INSERT INTO articles_fts(articles_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
  INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles_fts(articles_fts) VALUES ('rebuild');    -- index existing rows once

SELECT a.id, a.title,
       bm25(articles_fts) AS score,                              -- lower is better
       snippet(articles_fts, 1, '<b>', '</b>', '...', 12) AS excerpt,   -- column 1 = body, 12 tokens
       highlight(articles_fts, 0, '<b>', '</b>') AS title_hl
FROM articles_fts
JOIN articles a ON a.id = articles_fts.rowid
WHERE articles_fts MATCH 'wal AND (checkpoint OR "write ahead")'
ORDER BY score
LIMIT 20;

SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'title : sqlite';   -- restrict to a column
SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'check*';           -- prefix
SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'NEAR(wal checkpoint, 5)';
SELECT rowid FROM articles_fts WHERE articles_fts MATCH 'sqlite NOT mysql';
INSERT INTO articles_fts(articles_fts) VALUES ('optimize');                  -- merge b-tree segments after bulk loads
INSERT INTO articles_fts(articles_fts, rank) VALUES ('rank', 'bm25(10.0, 1.0)');   -- weight title 10x body for ORDER BY rank

sqlite3 app.db ".backup 'backups/app-$(date +%F).db'"           # page-level copy, same size and settings as the source
sqlite3 app.db "VACUUM INTO 'backups/app-$(date +%F).db'"        # 3.27+: compacted copy without free pages
sqlite3 app.db .dump | gzip > "backups/app-$(date +%F).sql.gz"  # portable SQL text; slowest, smallest
gunzip -c backups/app-2026-09-24.sql.gz | sqlite3 restored.db    # restore from SQL
sqlite3 restored.db 'PRAGMA integrity_check; PRAGMA journal_mode;'   # WAL setting is carried by .backup, not by .dump

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'a@example.com';
-- SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)       good
-- SCAN users                                                        full table scan
-- USE TEMP B-TREE FOR ORDER BY                                      sort not served by an index
-- SEARCH ... USING COVERING INDEX                                   no table lookup needed

ANALYZE;                                   -- gather statistics into sqlite_stat1; rerun after large changes
PRAGMA optimize;                           -- 3.18+: run ANALYZE only where useful; call before closing long-lived connections
CREATE INDEX orders_user_created ON orders (user_id, created_at DESC);       -- leftmost prefix rule as in every B-tree
CREATE INDEX orders_open ON orders (user_id) WHERE closed_at IS NULL;         -- partial index
CREATE INDEX users_email_lower ON users (lower(email));                        -- expression index

package main

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	_ "modernc.org/sqlite"
)

func open(path string) (*sql.DB, error) {
	// _pragma applies per connection; journal_mode persists in the file but is harmless to repeat.
	dsn := fmt.Sprintf("file:%s?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(1)&_txlock=immediate", path)
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(4)                    // readers; writes serialise on the file lock anyway
	db.SetConnMaxIdleTime(5 * time.Minute)
	if err := db.PingContext(context.Background()); err != nil {
		return nil, err
	}
	return db, nil
}

func insertUsers(ctx context.Context, db *sql.DB, users []struct{ Email, Name string }) error {
	tx, err := db.BeginTx(ctx, nil)          // _txlock=immediate makes this BEGIN IMMEDIATE
	if err != nil {
		return err
	}
	defer tx.Rollback()                      // no-op after Commit
	stmt, err := tx.PrepareContext(ctx, `INSERT INTO users (email, name) VALUES (?, ?)
		ON CONFLICT (email) DO UPDATE SET name = excluded.name`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	for _, u := range users {
		if _, err := stmt.ExecContext(ctx, u.Email, u.Name); err != nil {
			return fmt.Errorf("insert %s: %w", u.Email, err)
		}
	}
	return tx.Commit()
}

import sqlite3
from contextlib import closing

def connect(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path, timeout=5.0, autocommit=False)   # timeout = busy_timeout in seconds; 3.12+ autocommit
    conn.row_factory = sqlite3.Row                                 # rows indexable by name
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

with closing(connect("app.db")) as conn:
    with conn:                                                     # commits on success, rolls back on exception; does not close
        conn.executemany(
            "INSERT INTO users (email, name) VALUES (:email, :name) ON CONFLICT (email) DO UPDATE SET name = excluded.name",
            [{"email": "a@example.com", "name": "A"}, {"email": "b@example.com", "name": "B"}],
        )
    for row in conn.execute("SELECT id, email FROM users WHERE active = ? ORDER BY id", (1,)):
        print(row["id"], row["email"])

    # consistent online backup to another file
    with closing(sqlite3.connect("app-backup.db")) as dst:
        conn.backup(dst, pages=1024)                               # copies in steps so writers keep going

# ad-hoc queries from the command line with the module's shell (3.12+)
# python -m sqlite3 app.db "SELECT count(*) FROM users"

# Version of the library the shell uses (features depend on this, not the shell version)
sqlite3 :memory: 'SELECT sqlite_version()'

# Row counts for every table
sqlite3 app.db "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'" | while read -r t; do printf '%s\t' "$t"; sqlite3 app.db "SELECT count(*) FROM \"$t\""; done

# Size on disk by table and index (needs a build with the dbstat module; most distro builds have it)
sqlite3 app.db "SELECT name, sum(pgsize)/1024 AS kib FROM dbstat GROUP BY name ORDER BY kib DESC LIMIT 15"

# Free pages that VACUUM would reclaim, and the page size
sqlite3 app.db 'PRAGMA freelist_count; PRAGMA page_size;'

# Journal mode, and whether a WAL file is lingering
sqlite3 app.db 'PRAGMA journal_mode;' && ls -l app.db-wal app.db-shm 2>/dev/null

# Checkpoint and truncate the WAL right now
sqlite3 app.db 'PRAGMA wal_checkpoint(TRUNCATE);'

# Who has the file open (long readers pin the WAL)
fuser -v app.db app.db-wal 2>&1 | head

# Query plan without running the statement
sqlite3 app.db 'EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;'

# Indexes that exist and their columns
sqlite3 app.db "SELECT tbl_name, name, sql FROM sqlite_master WHERE type='index' AND sql IS NOT NULL"

# Compare two databases' schemas
diff <(sqlite3 a.db .schema) <(sqlite3 b.db .schema)

# Diff the data too (sqldiff ships with the sqlite tools package)
sqldiff a.db b.db | head

# Import a CSV into a new table, then look at what came in
sqlite3 app.db '.import --csv users.csv users_raw' 'SELECT * FROM users_raw LIMIT 3'

# Export a query as JSON for jq
sqlite3 -json app.db 'SELECT id, email FROM users' | jq -r '.[] | .email'

# Export one table as CSV with a header
sqlite3 -csv -header app.db 'SELECT * FROM users' > users.csv

# Load a SQL dump into a fresh file, stopping at the first error
sqlite3 -bail new.db < app.sql

# Integrity check with a non-zero exit when it fails
[ "$(sqlite3 app.db 'PRAGMA integrity_check;')" = ok ]

# Recover what can be read from a corrupt file
sqlite3 broken.db .recover | sqlite3 recovered.db

# Set the schema version the application uses for migrations
sqlite3 app.db 'PRAGMA user_version = 7;'

# Foreign key violations in existing data
sqlite3 app.db 'PRAGMA foreign_key_check;'

# Shrink an in-place copy without touching the original
sqlite3 app.db "VACUUM INTO 'app-compact.db'"

# Run a read-only ad-hoc report against a live database
sqlite3 -readonly -box app.db 'SELECT date(created_at) d, count(*) FROM orders GROUP BY d ORDER BY d DESC LIMIT 14'

# Time a query
sqlite3 app.db '.timer on' 'SELECT count(*) FROM orders WHERE created_at >= date("now", "-30 days");'

# Show compile options that matter (FTS5, JSON, RTREE, THREADSAFE)
sqlite3 :memory: 'PRAGMA compile_options;' | grep -E 'FTS5|JSON|RTREE|THREADSAFE|DBSTAT'

#!/usr/bin/env bash
# usage: sqlite-backup.sh DB_PATH BACKUP_DIR [KEEP]   default KEEP=14
set -euo pipefail
db=${1:?database path}; dir=${2:?backup dir}; keep=${3:-14}
[[ -f $db ]] || { printf 'no such database: %s\n' "$db" >&2; exit 2; }
mkdir -p "$dir"
name=$(basename "$db" .db)
out="$dir/$name-$(date +%Y%m%dT%H%M%S).db"

sqlite3 "$db" ".backup '$out'"                       # consistent snapshot; writers continue in WAL mode
check=$(sqlite3 "$out" 'PRAGMA integrity_check;')
[[ $check == ok ]] || { printf 'integrity check failed on %s: %s\n' "$out" "$check" >&2; rm -f "$out"; exit 1; }
gzip -9 "$out"
printf 'wrote %s (%s)\n' "$out.gz" "$(du -h "$out.gz" | cut -f1)"

# rotate: keep the newest $keep, delete the rest
ls -1t "$dir/$name-"*.db.gz 2>/dev/null | tail -n +"$((keep + 1))" | while IFS= read -r old; do
  rm -f -- "$old"; printf 'removed %s\n' "$old"
done

#!/usr/bin/env bash
# usage: sqlite-health.sh DB_PATH
set -euo pipefail
db=${1:?database path}
q() { sqlite3 -readonly "$db" "$1"; }

printf 'file       %s (%s)\n' "$db" "$(du -h "$db" | cut -f1)"
printf 'version    %s\n' "$(q 'SELECT sqlite_version();')"
printf 'journal    %s' "$(q 'PRAGMA journal_mode;')"
[[ -f $db-wal ]] && printf '  wal=%s' "$(du -h "$db-wal" | cut -f1)"; echo
printf 'pages      %s total, %s free (page_size %s)\n' "$(q 'PRAGMA page_count;')" "$(q 'PRAGMA freelist_count;')" "$(q 'PRAGMA page_size;')"
printf 'quick_check %s\n' "$(q 'PRAGMA quick_check;' | head -1)"
printf 'fk violations %s\n' "$(q 'PRAGMA foreign_key_check;' | wc -l)"
printf 'stats      %s\n' "$(q "SELECT CASE WHEN EXISTS (SELECT 1 FROM sqlite_master WHERE name='sqlite_stat1') THEN 'present' ELSE 'missing: run ANALYZE' END;")"
echo 'tables without an explicit primary key:'
q "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND sql NOT LIKE '%PRIMARY KEY%' AND sql NOT LIKE '%WITHOUT ROWID%'" | sed 's/^/  /'
echo 'largest objects:'
q "SELECT '  ' || name || ' ' || (sum(pgsize)/1024) || ' KiB' FROM dbstat GROUP BY name ORDER BY sum(pgsize) DESC LIMIT 8" 2>/dev/null || echo '  (dbstat module unavailable)'

#!/usr/bin/env python3
"""usage: migrate.py DB_PATH MIGRATIONS_DIR   (files named 0001_name.sql, 0002_name.sql, ...)"""
import re
import sqlite3
import sys
from pathlib import Path

db_path, mig_dir = sys.argv[1], Path(sys.argv[2])
conn = sqlite3.connect(db_path, timeout=10, autocommit=True)
conn.execute("PRAGMA foreign_keys = ON")
current = conn.execute("PRAGMA user_version").fetchone()[0]

files = sorted(mig_dir.glob("[0-9]*.sql"), key=lambda p: int(re.match(r"\d+", p.name).group()))
for f in files:
    version = int(re.match(r"\d+", f.name).group())
    if version <= current:
        continue
    print(f"applying {f.name}")
    conn.execute("BEGIN IMMEDIATE")
    try:
        conn.executescript(f.read_text())          # executescript would COMMIT first in autocommit=False mode; autocommit=True avoids that
        conn.execute(f"PRAGMA user_version = {version}")
        conn.execute("COMMIT")
    except Exception:
        conn.execute("ROLLBACK")
        print(f"failed at {f.name}; database remains at version {current}", file=sys.stderr)
        raise
    current = version
print(f"schema at version {current}")