Modern SQLite: Features You Didn't Know It Had

Summary

Modern SQLite: Features You Didn’t Know It Had Working with JSON data SQLite ships with a JSON extension that lets you store and query JSON documents directly in tables. You can keep your schema flexible while still using SQL to slice and dice structured data. Example: extracting fields from a JSON column: CREATE TABLE events ( id INTEGER PRIMARY KEY, payload TEXT NOT NULL -- JSON ); SELECT json_extract(payload, '$.user.id') AS user_id, json_extract(payload, '$.action') AS action, json_extract(payload, '$.metadata') AS metadata FROM events WHERE json_extract(payload, '$.action') = 'login'; You can also create indexes on JSON expressions, making queries over semi-structured data surprisingly fast. Full-text search with FTS5 SQLite’s FTS5 extension turns it into a capable full-text search engine. Instead of bolting on an external search service, you can keep everything in a single database file. Example: building a simple search index: CREATE VIRTUAL TABLE docs USING fts5( title, body, tokenize = "porter" ); INSERT INTO docs (title, body) VALUES ('SQLite Guide', 'Learn how to use SQLite effectively.'), ('Local-first Apps', 'Why local storage and sync matter.'); SELECT rowid, title FROM docs WHERE docs MATCH 'local NEAR/5 storage'; You get ranking, phrase queries, prefix searches, and more—without leaving SQLite or managing a separate service. Analytics with window functions and CTEs SQLite supports common table expressions (CTEs) and window functions, which unlock a whole class of analytical queries that used to require heavier databases. Example: computing running totals with a window function: SELECT user_id, created_at, amount, SUM(amount) OVER ( PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM payments ORDER BY user_id, created_at; Combine this with CTEs and you can build surprisingly rich reports and dashboards on top of a single SQLite file. Strict tables and better typing SQLite is famous (or infa...