SQLite as a Document Database
SQLite as a Document Database (2020)
SQLite's JSON support, combined with generated columns, lets you treat it as a document database. This post shows how to insert JSON, extract fields into virtual columns, enforce constraints, and index them—all in an embedded database. It's ideal for lightweight apps and webhooks, allowing flexible schemas without a separate document store.
It's that simple, the `d` column is extracted from the provided JSON.
- Insimwytim
If you applying NOT NULL constraint, why not extract it from json altogether as a separate column? What's the use of it in json?
You may construct it back on retrieval, if you need it in results.
- SleepyPenguin
I developed an application using zope on the backend and extjs (now Sencha) and SQLite on the front end in 2009. The form + data was stored as strings in the database and then synced to back-end when connected to the internet. It was targeting remote doctors in third world countries who were often offline. Several doctors at that time had told me they wanted to store the medical data in the same format as the intake form mostly because that was what they were used to with paper forms. Soon after, there was a big push for medical ERP and relational databases won over document databases. I bet it would be much easier to build an application like that now (data stored and displayed in same format as collected) but wonder what market would use it.
- wwalexander
> it added a killer feature: generated columns
It would be super cool if somehow SwiftData could translate computed properties of @Model objects into these generated columns via the #Expression macro!
- conception
Since sqlite people are probably in this thread - why is storing genomic data as a sqlite tar with all the metadata you want in tables a “Bad Idea” (tm)? Like toss a fastq or bam plus all the downstream data, grant info, experiment parameters, specimen info etc etc in a single file easily parsable.
- stanac
I am using SQLite as document db for a side project for years now. Made a custom repository base class that can also store blobs in separate columns, so this type of data is not part of the json document. Today there is also jsonb [1], as far as I remember all functions work the same for json and jsonb.
Also the repo class stores write and delete timestamps as separate columns so I can have CDC. CDC is used for building cached view models and is pushed to object storage every 5 minutes for backup as NDJSON. Another process on home server is restoring the db every couple of minutes for second backup and ready to use DB in case it's needed.
I know there are things like Litestream, I wanted something in process and something that can send alerts on failed backups.