Sqlite

SQLite Expert Skill

SQLite is a self-contained, serverless, zero-configuration SQL database engine. It's the right choice for local tools, CLI apps, AI agents, edge deployments (Cloudflare D1, Turso), and embedded databases in SaaS products.

Quick Decision Tree

What do you need?
├── TypeScript + Bun project?          → references/TYPESCRIPT_DRIZZLE.md
├── Python / FastAPI project?          → references/PYTHON.md
├── Performance, WAL, PRAGMA tuning?  → references/PERFORMANCE.md
├── Full-text search (FTS5)?          → references/FTS5_VECTOR.md  §FTS5
├── AI vector embeddings?             → references/FTS5_VECTOR.md  §sqlite-vec
├── Cloud SQLite (Turso / D1)?        → references/CLOUD.md
├── Building a stateful CLI Skill?    → references/CLI_SKILL_PATTERN.md
├── Schema design & normalization?    → This file §Schema Design
└── Backup, vacuum, integrity?        → This file §Maintenance

Read the referenced file ONLY when relevant. Don't load all references at once.

Core Concepts

Driver Selection

Always-On PRAGMAs

Run these immediately after opening any connection:

PRAGMA journal_mode = WAL;       -- Concurrent readers + writer
PRAGMA synchronous = NORMAL;     -- Safe + fast (vs FULL)
PRAGMA foreign_keys = ON;        -- Enforce FK constraints
PRAGMA cache_size = -64000;      -- 64 MB page cache
PRAGMA temp_store = MEMORY;      -- Temp tables in RAM
PRAGMA mmap_size = 268435456;    -- 256 MB memory-mapped I/O

⚠️ WAL is a file-level setting — it persists across connections. Set once, check after.

Schema Design

Naming Conventions

-- Tables: snake_case, plural
CREATE TABLE user_sessions (...);

-- Columns: snake_case
created_at, updated_at, deleted_at

-- PKs: always integer with AUTOINCREMENT or WITHOUT ROWID
id INTEGER PRIMARY KEY AUTOINCREMENT

-- FKs: <table_singular>_id
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE

Type Affinity Rules

SQLite has flexible typing — use these mappings:

Timestamp Pattern (Recommended)

created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch())

Use triggers for updated_at:

CREATE TRIGGER set_updated_at
AFTER UPDATE ON my_table
BEGIN
  UPDATE my_table SET updated_at = unixepoch() WHERE id = NEW.id;
END;

JSON Columns

SQLite 3.38+ supports the -> and ->> operators:

-- Store
INSERT INTO events (payload) VALUES ('{"type":"click","x":100}');

-- Extract (returns JSON)
SELECT payload -> '$.type' FROM events;

-- Extract as text (no quotes)
SELECT payload ->> '$.type' FROM events;

-- Index a JSON field
CREATE INDEX idx_events_type ON events(payload ->> '$.type');

Soft Delete Pattern

deleted_at INTEGER DEFAULT NULL  -- NULL = alive, unix timestamp = deleted

-- Query living rows
SELECT * FROM users WHERE deleted_at IS NULL;

-- Partial index for performance
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

Indexing Strategy

-- Single column
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Composite — order matters: equality first, range last
CREATE INDEX idx_sessions_user_created ON sessions(user_id, created_at);

-- Partial index — only index relevant rows
CREATE INDEX idx_jobs_pending ON jobs(created_at) WHERE status = 'pending';

-- Covering index — avoids table lookup entirely
CREATE INDEX idx_users_email_name ON users(email, name);

-- Inspect query plan
EXPLAIN QUERY PLAN SELECT * FROM posts WHERE user_id = 1 ORDER BY created_at DESC;

Index anti-patterns:

• Don't index every column — writes get slower
• Avoid indexing low-cardinality columns (boolean, enum with few values)
• Composite index column order matters: (a, b) helps WHERE a=? AND b=? and WHERE a=?, but NOT WHERE b=?

Transactions

-- Explicit transaction (fastest for bulk inserts)
BEGIN;
INSERT INTO logs VALUES (...);
INSERT INTO logs VALUES (...);
COMMIT;

-- Savepoints (nested transactions)
SAVEPOINT sp1;
-- ... operations ...
ROLLBACK TO sp1;  -- or RELEASE sp1 to commit

-- Immediate write lock (prevents SQLITE_BUSY in WAL mode)
BEGIN IMMEDIATE;

Bulk insert pattern (10-100x faster than individual inserts):

// Bun / better-sqlite3
const insert = db.prepare('INSERT INTO items (name, value) VALUES (?, ?)');
const insertMany = db.transaction((items) => {
  for (const item of items) insert.run(item.name, item.value);
});
insertMany(myArray);  // Single transaction

Maintenance

Vacuum & Analyze

-- Reclaim space after large deletes (creates new file, takes time)
VACUUM;

-- Incremental vacuum (WAL mode, run periodically)
PRAGMA incremental_vacuum(100);  -- Free 100 pages

-- Update query planner statistics
ANALYZE;

-- Integrity check
PRAGMA integrity_check;

-- Quick check (faster, less thorough)
PRAGMA quick_check;

Backup

# CLI backup (safe while DB is in use)
sqlite3 app.db ".backup backup_$(date +%Y%m%d).db"

# Hot backup via SQLite Online Backup API
sqlite3 app.db "VACUUM INTO 'backup.db';"

# Python: safe online backup
import sqlite3
src = sqlite3.connect('app.db')
dst = sqlite3.connect('backup.db')
src.backup(dst, pages=100)  # 100 pages at a time = non-blocking
dst.close(); src.close()

File Size Check

SELECT
  page_count * page_size AS total_bytes,
  freelist_count * page_size AS free_bytes,
  ROUND(freelist_count * 100.0 / page_count, 1) AS fragmentation_pct
FROM pragma_page_count(), pragma_page_size(), pragma_freelist_count();

Common Pitfalls

Reference Files