SQLite as a Production Document Store Using Native JSON Functions

For years, the default response to unstructured data was spinning up a MongoDB instance or provisioning a PostgreSQL cluster with JSONB columns. While PostgreSQL is excellent for optimizing query performance on large scale tables, it adds overhead for local-first applications. SQLite has changed. Since version 3.38.0, SQLite has shipped with native JSON functions and operators that match PostgreSQL's syntax.

If you are building a local-first application, an edge-deployed service, or a desktop app, you don't need the overhead of a separate database server. You can use SQLite as a fast, reliable, and zero-configuration document store.

Here is how to set up, query, index, and optimize SQLite for document-heavy production workloads.

The Minimalist Document Schema

In a dedicated document database, you write documents to collections. In SQLite, we represent a collection as a table with a primary key and a text column containing the JSON payload.

Here is the baseline table structure:

CREATE TABLE users (
    id TEXT PRIMARY KEY,
    data TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_json CHECK (json_valid(data))
);

The magic lies in the CHECK (json_valid(data)) constraint. SQLite doesn't have a dedicated JSON data type. Instead, it stores JSON as standard text. The json_valid() function runs every time you insert or update a row. If the payload isn't valid JSON, the database rejects the write. This gives you schema flexibility without risking corrupt or malformed data.

Reading Nested Data

To query fields inside your JSON documents, SQLite provides two primary operators: -> and ->>. These behave exactly like their PostgreSQL equivalents.

• -> extracts a sub-document or value and returns it as a JSON string.
• ->> extracts a value and returns it as a SQL scalar (text, integer, real, or null).

Let's insert a sample document to see this in action:

INSERT INTO users (id, data) VALUES (
    'usr_90210',
    '{
        "name": "Jane Doe",
        "email": "jane@example.com",
        "settings": {
            "theme": "dark",
            "notifications": true
        },
        "tags": ["admin", "beta-tester"]
    }'
);

If you want to query the user's name and their theme setting, use the ->> operator to get clean SQL values:

SELECT 
    data->>'$.name' AS name,
    data->>'$.settings.theme' AS theme
FROM users;

Notice the ``.` prefix. SQLite uses JSONPath syntax. The ``` represents the root of the document, and the dots navigate through nested keys.

If you used the -> operator instead, the query would return the string values wrapped in JSON quotes (e.g., "Jane Doe" instead of Jane Doe). Use -> when you need to extract nested objects or arrays to pass to other JSON functions. Use ->> when you want to display the value or compare it in a WHERE clause.

Modifying Documents on the Fly

You don't have to pull a document into your application memory, parse it, modify the fields, and write the whole string back to the database. SQLite provides functions to update JSON paths directly inside the database engine.

The most common mutation functions are json_set(), json_insert(), and json_replace().

• json_set() inserts a value if the path doesn't exist, or updates it if it does.
• json_insert() inserts a value only if the path does not exist.
• json_replace() updates a value only if the path already exists.

Here is how to update a user's theme preference and add a new configuration value:

UPDATE users
SET data = json_set(data, '`.settings.theme', 'light', '`.settings.fontSize', 14)
WHERE id = 'usr_90210';

You can also remove keys using json_remove():

UPDATE users
SET data = json_remove(data, '$.settings.notifications')
WHERE id = 'usr_90210';

These operations happen in-place within the database page, minimizing disk I/O and preventing race conditions where two application processes try to overwrite the same document.

Indexing JSON for Production Performance

If you query a JSON field without an index, SQLite must perform a full table scan. It reads every row from disk, parses the JSON string, extracts the value, and checks if it matches your condition. This works fine for a few thousand rows, but it degrades quickly at scale. In some cases, unoptimized queries or bad limits can cause silent failures, as detailed in the postmortem on how bounding database reads broke primary features.

To make document queries fast, we use expression indexes. You can index the result of a JSON extraction function directly.

Let's build an index on the email field:

CREATE INDEX idx_users_email ON users(data->>'$.email');

Now, let's run a query and check the execution plan:

EXPLAIN QUERY PLAN
SELECT id FROM users WHERE data->>'$.email' = 'jane@example.com';

The output confirms that SQLite bypasses the table scan and performs a targeted index lookup:

QUERY PLAN
`-SEARCH users USING INDEX idx_users_email (<expr>=?)

Virtual Columns as an Alternative

If you have complex extraction logic or want to make your SQL queries cleaner, you can use generated columns. These are columns that automatically compute their values from the JSON document.

ALTER TABLE users ADD COLUMN theme TEXT GENERATED ALWAYS AS (data->>'$.settings.theme') VIRTUAL;

The VIRTUAL keyword means the value isn't stored on disk. Instead, SQLite calculates it on the fly when you query the column. The benefit is that you can index this virtual column just like a normal one:

CREATE INDEX idx_users_theme ON users(theme);

This keeps your database footprint small while giving you the speed of a indexed relational table.

Querying Arrays and Nested Lists

Handling arrays inside JSON documents is notoriously tricky in relational databases. SQLite handles this with table-valued functions like json_each() and json_tree().

The json_each() function parses a JSON array or object and returns a virtual table where each element is a row.

Suppose you want to find all users who have the "admin" tag. You can join your main table against the output of json_each() running on the tags array:

SELECT users.id, users.data->>'$.name' AS name
FROM users, json_each(users.data, '$.tags')
WHERE json_each.value = 'admin';

If you need to search for a value anywhere in a deeply nested document, use json_tree(). It recursively walks the entire JSON structure and returns paths, values, and parent keys for every element.

Here is how to find any document that contains the value "beta-tester" anywhere in its structure:

SELECT DISTINCT users.id
FROM users, json_tree(users.data)
WHERE json_tree.value = 'beta-tester';

While powerful, these table-valued functions require scanning the JSON data structure. If you need to query arrays frequently in a high-throughput environment, consider breaking those values out into a traditional join table.

When to Use SQLite as a Document Store

Using SQLite as a document store simplifies your stack, but it isn't a silver bullet. You need to understand the trade-offs before deploying it.

The Good

• Zero Latency: Because SQLite runs in-process, there is no network round-trip. A query that takes 2ms on a local PostgreSQL instance might take 0.1ms on SQLite.
• Simple Backups: Your entire database, including your indexes and JSON schemas, lives in a single file. Backing up your document store is as simple as copying that file to an S3 bucket.
• Transactional Integrity: You get full ACID compliance. You can update a traditional relational column and a JSON document in the same transaction, guaranteeing consistency.

The Bad

• Single-Writer Limit: SQLite locks the database file during writes. If your application requires hundreds of concurrent write operations per second from different servers, SQLite will throw busy errors. Understanding database locking mechanics is crucial here, as SQLite's file-level locking is much coarser than the row-level locks found in engines like PostgreSQL.
• No Schema Enforcement for Nested Keys: While json_valid() ensures the syntax is correct, it doesn't stop you from writing { "name": "John" } in one row and { "first_name": "John" } in another. You must handle schema validation in your application code.

Production Tuning

If you run this setup in production, you must optimize SQLite's default settings. By default, SQLite is configured for low-memory footprint devices, not high-performance servers.

Run these commands immediately after opening your database connection:

- Enable Write-Ahead Logging for better concurrency
PRAGMA journal_mode = WAL;
 
- Synchronize database states safely but quickly
PRAGMA synchronous = NORMAL;
 
- Keep the cache in memory (adjust size based on your RAM)
PRAGMA cache_size = -64000; - Approx 64MB
 
- Store temporary tables in memory
PRAGMA temp_store = memory;

Enabling WAL mode allows readers to access the database while a write operation is happening, which mitigates the single-writer bottleneck. Setting synchronous = NORMAL reduces disk sync calls while maintaining safety against corruption during application crashes.

SQLite is no longer just a simple tool for prototypes. With native JSON operators, expression indexes, and generated columns, it functions as a highly capable document store. It lets you build fast, local-first architectures without the complexity of managing a separate database server.