SQLite

**Judgement:** This skill encapsulates strong, accurate, and practice-oriented knowledge of SQLite’s real-world behavior, especially around concurrency, PRAGMAs, types, and maintenance. It is well-suited for serious use but assumes some technical literacy. **Strengths & Evidence** - **Concurrency realism:** Emphasizes the one-writer constraint, explains WAL mode, busy_timeout, and when SQLite is a poor fit (high-write web workloads). This matches SQLite’s official documentation and prevents common misuse. - **Critical PRAGMAs:** Correctly calls out `PRAGMA foreign_keys=ON` per-connection (off by default), WAL configuration, `busy_timeout`, `synchronous=NORMAL`, `temp_store=MEMORY`, and `PRAGMA optimize`. These are the ones that most developers get wrong. - **Type system accuracy:** Explains affinity vs strict typing, STRICT tables (3.37+), lack of native DATE/TIME/BOOLEAN, and appropriate encodings (ISO8601 text or Unix timestamps, INTEGER 0/1 for booleans). This prevents subtle data-quality bugs. - **Schema and migration guidance:** Correctly states the limits of `ALTER TABLE` and the canonical pattern: create new table → copy data → drop old → rename, wrapped in a transaction. Addresses constraints and column changes. - **Maintenance & backups:** Notes VACUUM behavior (file size, 2× disk requirement), auto_vacuum/incremental_vacuum, and safe backup methods (`.backup`, backup API, `VACUUM INTO`, and WAL file copying). This reduces corruption risk. - **Indexing & EXPLAIN:** Mentions covering, partial, and expression indexes and encourages `EXPLAIN QUERY PLAN`, which is appropriate and effective for SQLite. **Key Risks & Limitations** - **Not for heavy concurrency:** Even with WAL and timeouts, a single writer can bottleneck multi-user web apps. The skill does flag this, but users must heed the warning and choose PostgreSQL or similar for high write concurrency. - **Version assumptions:** Some features (STRICT tables, `VACUUM INTO`, advanced index features) depend on recent SQLite versions. Deployments embedding older SQLite may not support them. - **Operational complexity with WAL:** Correctly warns that WAL also creates `-wal` and `-shm` files that must be handled during backup and migration—missteps here can still cause corruption if users don’t follow instructions carefully. **Recommended Scenarios** - Local/embedded databases for desktop, CLI, or mobile apps where writes are moderate and controlled. - Small internal services, prototypes, and single-tenant tools that benefit from a simple file-based DB but still need integrity and decent performance. - ETL/batch tasks, analytics snapshots, and log ingestion where bulk inserts and reads dominate and concurrency is manageable. **Avoid or Use With Caution** - High-write, multi-tenant web applications or multi-process workloads with many concurrent writers. - Environments with very old SQLite versions where suggested pragmas and features may be unavailable or behave differently.