sqlite

sqlite

Zero-Config Embedded DB

• Single-file database — no server, no config, no setup.
• sqlite3 my.db opens or creates a database.
• Perfect for mobile, desktop, embedded, and test environments.
• PRAGMA journal_mode=WAL; — Write-Ahead Log for concurrent reads.

CLI

• .tables — list tables; .schema tablename — DDL for a table.
• .dump — full SQL dump for backup; .import file.csv table — CSV import.
• .headers on / .mode column for formatted output.
• .output file.sql — redirect query results to file.

SQL Features

• Window functions: RANK() OVER (ORDER BY score DESC), LAG, LEAD.
• CTEs: WITH RECURSIVE for hierarchical data (org trees, graphs).
• JSON: json_extract, json_array, json_object, json_each.
• UPSERT — INSERT ... ON CONFLICT DO UPDATE SET ....
• Generated columns, partial indexes, RETURNING clause.

Performance Pragmas

• PRAGMA cache_size = -8000 — 8MB cache; PRAGMA page_size = 4096.
• PRAGMA synchronous = NORMAL — balance safety and speed.
• PRAGMA foreign_keys = ON — enforce FK constraints (off by default).
• PRAGMA journal_mode = WAL — better concurrency.

Extensions

• FTS5: CREATE VIRTUAL TABLE docs USING fts5(title, content) for full-text search.
• SpatiaLite: spatial SQL functions and geometry types.
• sqlean bundle: crypto, math, fileio, regexp, stats extensions.

Backup / Restore

• .backup main backup.db — online backup (safe during writes).
• VACUUM INTO 'new.db' — compact + copy.
• .restore from backup file.