Modern SQLite: Features You Didn't Know It Had

My notes

Summary

Modern SQLite (3.35+) ships with a surprising set of production-grade features that eliminate the need for external services in many use cases. JSON querying, FTS5 full-text search, window functions, strict typing, generated columns, and WAL mode are all built in. Most developers still treat SQLite as a toy database and reach for Postgres or Elasticsearch prematurely.

Key Insight

• JSON extension - store semi-structured data in TEXT columns, query with json_extract(), and create indexes on JSON expressions for fast lookups. No separate document store needed for moderate workloads.
• FTS5 full-text search - virtual tables with Porter stemming, phrase queries (NEAR/N), prefix search, and relevance ranking. Replaces Elasticsearch or Typesense for single-node apps and internal tools.
• Window functions + CTEs - SUM() OVER (PARTITION BY ...) running totals, LAG/LEAD, rank functions. Unlocks BI-quality analytics without moving data to a heavier DB.
• STRICT tables - opt-in per-table type enforcement. Mix flexible and strict tables in the same database. Catches insertion bugs at write time instead of at query time.
• Generated columns - computed fields stored (or virtual) alongside source data. Keeps derived logic in the schema, not scattered across application code. Index them like normal columns.
• WAL mode (PRAGMA journal_mode = WAL) - single pragma, non-blocking reads during writes. Default journal mode causes write locks that block readers; WAL removes that bottleneck for desktop/local-first apps.

Key non-obvious point: these features are composable. A single SQLite file can handle semi-structured storage, full-text search, analytics, and strict schemas simultaneously - replacing three or four separate services.