Last month I needed somewhere to dump webhook payloads from my AI agent infrastructure. Events arrive as JSON blobs, the schema changes depending on the event type, and I only ever query two or three fields from each payload. My first instinct was the one I have had for a decade: spin up MongoDB, or maybe Postgres with a JSONB column.
Then a 2020 blog post by David Glider, SQLite as a Document Database, resurfaced on Hacker News this week. It has been making the rounds twice in the past few days, and the core trick it describes is still one of the most underused features in any database: store the whole JSON document in one column, then use SQLite generated columns to pull out and index just the fields you actually query. I ran the whole pattern on my VPS before writing this, and every query below is verified.
Full disclosure: I had read about this pattern before but never actually deployed it. I did that today, on SQLite 3.45.1 running on my own server. Here is the complete walkthrough.
The problem with document stores for small workloads
Webhook payloads are the classic case. The data is semi-structured, you do not control the schema, and most of it is write-once. You could set up MongoDB, but now you are running another database process, configuring auth, adding a backup job, and remembering to update it. For a side project or an internal tool, that operational cost dwarfs the actual workload.
SQLite flips the equation. The database is a single file. No daemon, no connection pool, no user management. And since version 3.31.0, released January 2020, it has generated columns, which is the feature that makes the document pattern work.
Step 1: The table is just a JSON column
Start embarrassingly simple. Here is the table I actually created for my event log:
CREATE TABLE events (
id INTEGER PRIMARY KEY,
received_at TEXT DEFAULT (datetime('now')),
body TEXT
);
Insert payloads with json() so invalid JSON fails loudly instead of silently polluting your data:
INSERT INTO events (body) VALUES
('{"type":"deploy","service":"api","status":"ok","duration_ms":4120,"actor":"ci"}'),
('{"type":"alert","service":"api","status":"fired","duration_ms":null,"actor":"monitor"}'),
('{"type":"deploy","service":"worker","status":"ok","duration_ms":9310,"actor":"ci"}');
That is the entire document store. No collection setup, no schema designer. The JSON goes in as text and you can retrieve it wholesale any time.
Step 2: Generated columns, the feature that changes everything
Now suppose you keep querying by event type. Add a generated column that extracts it:
ALTER TABLE events ADD COLUMN
event_type TEXT GENERATED ALWAYS AS (json_extract(body, '$.type')) VIRTUAL;
The column is not stored. It does not exist on disk. Every time you read it, SQLite evaluates json_extract on the fly. When you write, nothing changes: you still insert raw JSON into body, and the column fills itself in.
You can do this as many times as you like, for any field you later decide matters:
ALTER TABLE events ADD COLUMN
service TEXT GENERATED ALWAYS AS (json_extract(body, '$.service')) VIRTUAL;
This is the "schema-last" workflow the original blog post describes. Start with a single JSON column. Discover which fields you actually need. Promote them to generated columns one at a time. Your ingestion code never changes.
One sharp edge I hit while testing: generated columns come in two flavors, VIRTUAL and STORED. STORED computes once at write time and saves the result. That sounds better, but you cannot add a STORED column to an existing table with ALTER TABLE. I tried; SQLite rejects it with "cannot add a STORED column". VIRTUAL columns can be added freely, and for json_extract on a small field, the read-time cost is negligible. Use VIRTUAL.
Step 3: Index it like a real column
Here is where it stops being a toy. Because the generated column is a real column to the query planner, you can index it:
CREATE INDEX idx_events_type ON events(event_type);
CREATE INDEX idx_events_service ON events(service);
And now this query is an index lookup, not a full table scan:
SELECT id, event_type, service
FROM events
WHERE event_type = 'deploy';
I ran EXPLAIN QUERY PLAN on my VPS to confirm SQLite actually uses it:
QUERY PLAN
`--SEARCH events USING INDEX idx_events_type (event_type=?)
SEARCH, not SCAN. This is the piece most people assume is impossible: an index over a field inside a JSON document. MongoDB made its name on exactly this. SQLite does it with two lines of SQL.
Step 4: JSONB, if you are on a recent version
Since version 3.45.0, released January 2024, SQLite has a JSONB format: the database's internal binary parse-tree representation of JSON, stored as a BLOB. Functions prefixed jsonb_ work on it, and per the official JSON documentation, it skips the parse step on read and takes slightly less disk space.
SELECT jsonb_extract('{"a":{"b":5}}', '$.a.b'); -- returns 5
One caution from the docs: SQLite's JSONB shares a name with PostgreSQL's JSONB but the on-disk format is completely different and incompatible. Do not expect portable files between the two.
For a write-once webhook log, text JSON is honestly fine. JSONB matters more when you are reading and updating JSON fields repeatedly. I mention it so you know it exists, not because you need it on day one.
Step 5: Full-text search over the documents
This is the part that surprised me most. Document stores usually sell you on flexible queries; search is where they pull you in deeper. SQLite has that too, with FTS5:
CREATE VIRTUAL TABLE events_fts USING fts5(
body, content='events', content_rowid='id'
);
INSERT INTO events_fts(events_fts) VALUES('rebuild');
The content= option makes it an external-content table: the index lives in events_fts, but the text lives in your original events table, so nothing is duplicated. The rebuild command backfills the index from existing rows.
Now you can search across all payloads:
SELECT snippet(events_fts, 0, '[', ']', '...', 12)
FROM events_fts
WHERE events_fts MATCH 'monitor';
That query, run on my machine against the demo data, returned the alert event with the match highlighted:
{"type":"alert","service":"api","status":"fired",...,"actor":"[monitor]"}
You get bm25() relevance ranking, highlight(), and snippet() for free. A webhook log with indexed fields AND full-text search, in one file, with zero extra services.
Bonus: shredding JSON arrays
When a payload contains an array you need as rows, json_each handles it:
SELECT json_extract(value, '$.type')
FROM json_each('[{"type":"a"},{"type":"b"}]');
Returns one row per element. No application-side parsing.
When NOT to do this
The save-worthy part. This pattern is powerful, but it has a failure mode: stretching SQLite into a job it was never meant for. Here is the checklist I use:
- Multiple writers over the network? Use a client-server database. SQLite allows one writer at a time. Perfect for one app server, painful for five.
- Querying dozens of fields from every document? Stop adding generated columns after about five. If most of the document becomes generated columns, just define a real table. You have discovered your schema.
- Huge documents with hot nested updates? Text JSON rewrites the whole value on update. Consider JSONB columns or a document store.
- Need ad-hoc queries by other teams, dashboards, replication? Postgres with JSONB gives you the same pattern with client-server infrastructure.
- Single writer, schema evolving, want search? This pattern. Every time.
My event log fits the last row: one writer process, evolving payload shapes, occasional search. SQLite wins because the operational cost is zero.
Why this resurfacing matters
The blog post is from June 2020, and the Hacker News thread about it from that year pulled 239 points. Six years later it is being reposted and discussed again, and the comments are the same as they ever were: people describing side projects that have run on SQLite-as-document-store for years without a hiccup. Tools like LiteFS and Litestream have grown around SQLite in the meantime, solving replication and backup, which were its last real gaps for small-server deployment.
The lesson is not "SQLite replaces MongoDB". It is that for a huge category of workloads, the small internal tools, the side projects, the webhook sinks and audit logs, the boring database you already have is enough, and the two features that make it enough, generated columns and FTS5, have been sitting in your sqlite3 binary for years.
I write about backend engineering, databases, and AI infrastructure every week. Subscribe, it's free.
What about you? Have you used SQLite as a document store in production, or did you reach for MongoDB first and regret the operational overhead? Tell me in the responses.
