April 2, 2026
Tiny database, big feelings
Modern SQLite: Features You Didn't Know It Had
SQLite’s “hidden” superpowers spark eye-rolls, hacks, and horror stories
TLDR: SQLite packs JSON, search, analytics, strict tables, and faster writes into one tiny file. Commenters alternated between “old news” eye-rolls and practical warnings—FTS may lack fuzzy matching, JSON differs across systems, and some features aren’t preinstalled—reminding teams to check what's actually enabled before betting a project.
SQLite—the tiny database tucked inside countless apps—got a shout-out for sneaky superpowers: JSON queries, built‑in search, spreadsheet‑style analytics, stricter schemas, auto‑generated fields, and a one‑line speed mode called WAL. The crowd? Split between “old news” veterans and “show me the receipts” pragmatists.
One veteran waved the gatekeeper flag: these tricks have been around forever, and sometimes the best “strict typing” is just using another database. Others piled on with practical ammo: enable fuzzy search with spellfix1, clean duplicates with ON CONFLICT, and yes—sometimes you have to compile extras like FTS5 yourself. Cue the gotcha montage.
Then came the war stories. One commenter groaned that “everybody has JSON, all slightly different,” after getting burned by a hosting provider stuck on an old MySQL—proof that JSON isn’t one-size-fits-all. Another slammed FTS5 for missing subword matches—searching “Daemon” won’t find “DaemonSet”—turning “full‑text search” into “full‑text nope” for some use cases. Meanwhile, a calmer voice praised STRICT tables as a much‑needed safety net, even if it rarely catches them.
The vibe: SQLite is a Swiss Army knife in a single file, but real‑world results depend on what’s actually enabled and how you use it. If you expect “Google‑level search” out of the box, prepare for a reality check—and maybe a quick trip to the docs for JSON and WAL.
Key Points
- •SQLite’s JSON extension supports querying and indexing JSON data directly within tables using functions like json_extract.
- •FTS5 enables built‑in full‑text search with virtual tables, tokenization (e.g., Porter), ranking, phrase, prefix, and proximity queries.
- •SQLite provides analytics features via CTEs and window functions, enabling queries such as running totals and richer reports.
- •STRICT tables enforce stronger type constraints, rejecting invalid types at insert time for more predictable schemas.
- •Generated columns (virtual or stored) and WAL mode improve data consistency, indexing, and concurrency; WAL is enabled with a single PRAGMA.