SQLite as a Document Database (2020)

Introduction

SQLite is often seen as a simple, lightweight relational database, perfect for mobile or embedded applications. However, with the introduction of new features like JSON support and generated columns (introduced in version 3.31.0 in January 2020), SQLite has taken a significant step forward, enabling its use as a document database.

Why SQLite as a Document Database?

Document databases are popular for their ability to store semi-structured data. Traditionally, solutions like MongoDB or CouchDB dominate this domain. However, SQLite presents an interesting alternative for less intensive use cases or where integration is crucial.

JSON Support

SQLite has supported JSON storage for a while, but the innovation lies in the ability to extract and index data directly from JSON using generated columns. For example:

``sql CREATE TABLE documents ( content TEXT, extracted_value INT GENERATED ALWAYS AS (json_extract(content, '$.value')) VIRTUAL ); ``

This table allows you to insert JSON objects and directly treat the extracted data as regular columns.

Advantages of Generated Columns

Generated columns simplify working with JSON data, ensuring that inserted data complies with a certain structure and is valid. This reduces data errors and improves access performance through indexing.

Data Validation

Using GENERATED ALWAYS, SQLite triggers an error when inserted JSON is malformed, ensuring data integrity right at insertion:

``sql CREATE TABLE examples ( data TEXT, id TEXT GENERATED ALWAYS AS (json_extract(data, '$.id')) VIRTUAL NOT NULL ); ``

This table will not allow JSON insertion without the id attribute, ensuring data consistency.

Use Cases

Mobile and Embedded Applications

Thanks to its lightweight nature, SQLite is widely deployed in mobile applications. JSON support and generated columns allow storing API responses directly and extracting relevant information later.

Webhook Processing

Webhooks often send JSON payloads that you can store directly in SQLite. You can then index and extract the necessary data without prior processing.

Performance and Limitations

While SQLite is incredibly flexible, it is not designed for massive workloads. For distributed applications or high availability needs, dedicated document databases remain preferable.

Conclusion

With its enhancements, SQLite offers a simple yet powerful solution for developers looking to manage semi-structured data without the need for heavy infrastructure.

Let's discuss your project in 15 minutes.