# Modern SQLite: Features You Didn't Know It Had

> Modern SQLite (3.35+) ships JSON querying, FTS5 full-text search, window functions, strict typing, generated columns, and WAL mode, removing many external services.

Published: 2026-04-02
URL: https://daniliants.com/insights/modern-sqlite-features-you-didn-t-know-it-had/
Tags: sqlite, databases, full-text-search, json, window-functions, wal, local-first, backend

---

## 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.