Leveraging SQLite as a Document Database (2020)

While SQLite is traditionally a relational database, it has supported JSON for some time. However, a "killer feature" was introduced in version 3.31.0 (released January 22, 2020): Generated Columns.

This update allows developers to ingest raw JSON and have SQLite automatically extract and index specific fields. While this functionality exists in PostgreSQL or dedicated engines like Elasticsearch, having it in an embedded database is a massive win for lightweight applications.

🚀 Getting Started

To implement this, you first need a compatible version of SQLite.

Setup Checklist

• Install SQLite $\ge$ 3.31.0
• Initialize a database
• Define a table with a generated column

Note on Installation: Obtaining a recent version can be tricky. As of this writing, macOS users can use Homebrew, while others might need to rely on unstable channels like nixpkgs-unstable.

Basic Implementation

Here is how you can set up a table where a column is automatically derived from a JSON blob:

-- Create a table with a VIRTUAL generated column
CREATE TABLE t ( 
    body TEXT, 
    d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL
);

-- Insert a JSON string
INSERT INTO t VALUES(json('{"d":"42"}'));

-- Query using the generated column
SELECT * FROM t WHERE d = 42;

Result: {"d":"42"}|42

The logic can be represented mathematically as: \text{Column}_d = \text{extract}(\text{body}, \text{path} \rightarrow \text{'$.d'})

---

🛡️ Validation and Constraints

Usually, it is recommended to use the json() function to minify and validate data upon insertion. However, since SQLite lacks a native JSON type, it will strictly forbid will allow any text by default.

By using GENERATED ALWAYS with json_extract, you gain an implicit validation layer. If the JSON is malformed, SQLite throws an Error: malformed JSON during the INSERT process.

Enforcing Data Presence

You can further harden your "schema-less" documents by adding NOT NULL constraints to these generated columns:

CREATE TABLE x ( 
    body TEXT, 
    id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL
);

-- This fails: malformed JSON
INSERT INTO x VALUES(''); 

-- This fails: NOT NULL constraint failed: x.id
INSERT INTO x VALUES('{}'); 

---

⚙️ Storage and Performance

When defining generated columns, you have two primary choices: VIRTUAL and STORED.

Indexing for Speed

Even if a column is VIRTUAL, you can still create an index on it to ensure high performance.

CREATE INDEX xid ON x(id);

-- Verify the index is being used
EXPLAIN QUERY PLAN SELECT * FROM x WHERE id='foo';
-- Output: QUERY PLAN `--SEARCH TABLE x USING INDEX xid (id=?)`

Data Flow Diagram

---

📈 Evolutionary Schema

One of the greatest strengths of this approach is the ability to evolve your data model. You can start with a bare-bones table consisting of a single JSON column and add structure as you discover which fields are most valuable.

Example Workflow:

1. Start simple: Just a body column.
2. Identify a field: You realize text is frequently queried.
3. Expand:

   ALTER TABLE x ADD COLUMN text TEXT GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL;
   INSERT INTO x VALUES(json('{"id":43, "text":"test"}'));
   CREATE INDEX xtext ON x(text);
   

This pattern is exceptionally useful for webhooks. You can dump all incoming payload data directly into the database and only "promote" specific fields to indexed columns once you've determined they are necessary for your application's logic.