SQLite as a Document Database (2020)

In January 2020, SQLite introduced a notable feature: generated columns. Released in version 3.31.0 on January 22, 2020, this capability enables the insertion of JSON data directly into SQLite and subsequently indexing and extracting data from it. This functionality, previously available in PostgreSQL and services like Elastic, proves beneficial for lightweight applications utilizing an embedded database.

To illustrate, <source>CREATE TABLE t ( body TEXT, d INT GENERATED ALWAYS AS (json_extract(body, $.d )) VIRTUAL);</source> illustrates the creation of a table with a generated column 'd' extracted from the 'body' JSON column. Inserting JSON data via <source>insert into t values(json({ d : 42 }));</source> and subsequently querying the table with <source>select * from t WHERE d = 42;</source> demonstrates the ease of accessing the extracted data.

This approach can be particularly advantageous for webhooks, allowing the simultaneous insertion of raw JSON data and subsequent extraction of relevant information.

The source emphasizes that while the process enforces JSON validity during insertion, it does not inherently validate the data, which may necessitate additional constraints. The use of <source>GENERATED ALWAYS AS (json_extract(body, $.id )) VIRTUAL NOT NULL</source> ensures that crucial data elements are mandatory, as exemplified by the error message returned when attempting to insert empty JSON or a missing 'id' field.

This approach offers a flexible and efficient method for treating SQLite as a document database, enabling the addition of indices and columns as needed.