SQLite Doubles as a Document Store with Generated Columns and JSON

SQLite has supported JSON for years, but the 3.31.0 release (January 2020) added generated columns, which together turn the embedded engine into a serviceable document database. You define a column as GENERATED ALWAYS AS (json_extract(body, ‘$.field’)), insert raw JSON into a single text column, and let SQLite pull out and index individual fields on demand. PostgreSQL and search engines like Elasticsearch already do this, but getting it in a zero-dependency embedded database is a meaningful convenience for lightweight applications.

The approach carries some useful side effects. Because json_extract runs at insert time, malformed JSON is rejected immediately rather than silently stored, effectively giving you validation without a dedicated JSON type. Pairing generated columns with NOT NULL and other constraints lets you enforce that specific keys are present in incoming documents. Columns can be VIRTUAL (computed on read) or STORED (cached but not addable via ALTER TABLE), and either kind can be indexed—EXPLAIN QUERY PLAN confirms the index is actually used for lookups.

The practical payoff is schema flexibility that grows with your needs: start with a table that is little more than a JSON blob, then add extracted columns and indexes as patterns emerge in the data. The author points to webhook ingestion as a natural fit—dump whatever a service sends you straight into a table, then surface the fields you care about later. The main friction is availability, since you need a recent enough SQLite build, which at the time meant Homebrew or unstable package channels.