HN
Today

SQLite as a Document Database (2020)

SQLite's recent addition of generated columns transforms it into a surprisingly capable document database. This feature allows embedded SQLite to extract and index data directly from JSON, offering a lightweight yet robust solution for managing semi-structured data with SQL's power. It's a game-changer for developers seeking flexible and performant local data storage without a separate NoSQL system.

7
Score
0
Comments
#2
Highest Rank
15h
on Front Page
First Seen
Aug 29, 3:00 PM
Last Seen
Aug 30, 5:00 AM
Rank Over Time
13222368978910111212

The Lowdown

SQLite, known for its embedded simplicity and reliability, has evolved to effectively function as a document database, thanks to enhanced JSON support and the introduction of generated columns in version 3.31.0. This advancement provides a powerful way to store raw JSON data and then extract, validate, and index specific fields, merging the flexibility of document stores with the robust capabilities of a relational database.

  • Generated Columns: The core innovation is GENERATED ALWAYS AS, which allows SQLite to automatically parse and extract values from a TEXT column containing JSON. These extracted values can then be treated as regular columns.
  • Automatic Validation: Using json_extract within a generated column inherently validates JSON upon insertion, rejecting malformed JSON with an error and enforcing data integrity.
  • Schema Enforcement: Constraints like NOT NULL can be applied to generated columns, ensuring specific fields are present within the JSON document, providing a level of schema enforcement typically found in relational databases.
  • Virtual vs. Stored: Generated columns can be VIRTUAL (computed on read) or STORED (persisted on write). While STORED columns cache values for performance, VIRTUAL columns offer more flexibility, such as being addable via ALTER TABLE.
  • Indexing: Even VIRTUAL generated columns can be indexed, allowing for efficient querying and search performance on extracted JSON fields. The query planner will utilize these indexes, as demonstrated by EXPLAIN QUERY PLAN.
  • Flexible Workflow: This setup enables a highly flexible workflow, particularly useful for scenarios like storing raw webhook data. Developers can initially store entire JSON payloads and then progressively define and index specific fields as their data needs become clearer, without needing to change the original storage mechanism.

In essence, SQLite's combination of strong JSON support and generated columns provides a compelling, lightweight solution for handling document-style data, offering developers the best of both worlds: the flexibility of schemaless storage and the power of SQL's querying and indexing capabilities, all within a familiar, embedded database.