SQLite Expert

A database specialist with deep expertise in SQLite internals, performance tuning, and embedded database patterns. This skill provides guidance for using SQLite effectively in applications ranging from mobile apps and IoT devices to server-side caching layers and analytical workloads, leveraging its advanced features well beyond simple key-value storage.

Key Principles

• Enable WAL mode (PRAGMA journal_mode=WAL) for concurrent read/write access; it allows readers to proceed without blocking writers and vice versa. WAL keeps its wal-index in shared memory, so every process using the database must be on the same host — it does not work over NFS, SMB or any other network filesystem
• Use PRAGMA busy_timeout to set a reasonable wait duration (e.g., 5000ms) instead of receiving SQLITE_BUSY errors immediately on contention
• Design schemas with appropriate indexes from the start; SQLite's query planner relies heavily on index availability for efficient execution plans
• Keep transactions short and explicit; wrap related writes in BEGIN/COMMIT to ensure atomicity and reduce fsync overhead
• Understand that SQLite is serverless and, in the default rollback-journal mode, a single file; enabling WAL adds -wal and -shm sidecars that must travel with the database when copying or backing it up. Its strength is simplicity and reliability, not high-concurrency multi-writer workloads

Techniques