SQLite as a Document Store: Optimizing JSON Query Performance on the Edge

SQLite is traditionally viewed as a simple relational database, ideal for lightweight client storage. But in building a local-first desktop application that indexes files, tools, and screen context, we required the flexibility of a schema-less document store coupled with strict ACID compliance. The solution lay in SQLite's robust JSON1 extension suite.

"Why choose between the structured safety of relations and the fluid grace of documents? SQLite lets you have both on the user's SSD."

In standard setups, developers reach for heavyweight document databases like MongoDB or post-processing systems. For a local desktop client, launching a secondary database process adds substantial resource footprints. By utilizing SQLite's native JSON capabilities, we built a zero-dependency document store capable of processing thousands of queries under 2ms.

Generated Columns and Indexing

SQLite allows creating virtual or stored generated columns derived from JSON expressions. By placing indexes directly on these virtual columns, we can run highly complex nested filters without scanning the entire database. Let's look at the schema layout:

Figure 1: SQLite Virtual JSON Columns Indexing

Performance Comparison

Storage Paradigm	Average Read Latency	Memory Footprint	ACID Reliability
MongoDB Local Process	12.4 ms	145 MB	High (Process isolation overhead)
SQLite raw JSON scan	42.1 ms	8 MB	Extreme (No indices on fields)
SQLite Virtual Indexed JSON	1.2 ms	8 MB	Extreme (Local-first optimal)

SQLite Virtual Columns Schema Definition

Here is the exact schema and index query definition used to index workspace documents dynamically:

-- Create a document table with virtual fields extracted from JSON
CREATE TABLE workspace_documents (
    id TEXT PRIMARY KEY,
    workspace_id TEXT,
    raw_payload TEXT, -- Raw JSON payload
    
    -- Virtual generated columns extracted from JSON
    doc_type TEXT GENERATED ALWAYS AS (json_extract(raw_payload, '$.metadata.type')) VIRTUAL,
    doc_word_count INTEGER GENERATED ALWAYS AS (json_extract(raw_payload, '$.stats.words')) VIRTUAL
);

-- Index the virtual columns directly for sub-millisecond retrieval
CREATE INDEX idx_docs_type_words ON workspace_documents(doc_type, doc_word_count);

-- Sub-millisecond indexed JSON queries
SELECT id, doc_type FROM workspace_documents 
WHERE doc_type = 'meeting_note' AND doc_word_count > 500;