sqlite

Topics in the source

• SQLite
• Quick Start
• When to Use
• Best Practices
• Schema
• Concurrency
• Performance
• Durability

Embedded relational storage with SQLite. Local/desktop/mobile storage Single-machine apps with small data Prototypes and embedded tools Read-heavy offline datasets Use INTEGER PRIMARY KEY or explicit rowid Prefer TEXT for dates (ISO) or REAL for timestamps Add UNIQUE and CHECK constraints Use STRICT tables (SQLite 3.37+) for typed data Enable WAL mode for concurrent readers Use short write transactions Set busytimeout to avoid SQLITEBUSY Avoid long-running read transactions Index columns used in WHERE/JOIN Use PRAGMA to tune (cachesize, mmap) Batch writes in transactions VACUUM periodically to reclaim space Choose journal mode by need (WAL vs DELETE) Set synchronous=NORMAL in WAL for speed Back up via .backup or VACUUM INTO Test on the target filesystem 1. Choose file location and open with pragmas (WAL). 2. Create schema with constraints and indexes. 3. Write with short transactions and busytimeout. 4. Query with parameters to avoid injection. 5. Tune cache and synchronous for the workload. 6. Back up with .backup/VACUUM INTO. 7. Vacuum and re-analyze periodically. 8. Test concurrency on the target platform. 1. Writes and reads are consistent 2. Concurrent reads work in WAL mode 3

This is an excerpt. Refer to the complete source and its current requirements.