HN

Comments (42)

• 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.[1] https://sqlite.org/jsonb.html
• wwalexander
  > it added a killer feature: generated columnsIt would be super cool if somehow SwiftData could translate computed properties of @Model objects into these generated columns via the #Expression macro!
• Menu

  WhitneyLand
  Why do people say document database when they really just mean json database?
• Menu

  nchmy
  obviously the example is contrived, but it seems strange me that they are not storing the json in its own column - just extracting a single key from it and storing in a generated column. Why not do that in the app code if youre just going to discard the rest of the json?Here's an example i saw yesterday from mariadb, which is improving its json support in its upcoming releases.https://mariadb.com/docs/server/ha-and-performance/optimizat...``` CREATE TABLE t1 (json_data JSON); INSERT INTO t1 VALUES('{"column1": 1234}'); INSERT INTO t1 ... ```In order to do efficient queries over data in JSON, you can add a virtual column, and an index on that column:``` ALTER TABLE t1 ADD COLUMN vcol1 INT AS (cast(json_value(json_data, '$.column1') AS INTEGER)), ADD INDEX(vcol1);```
• 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.
• 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.
• 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.
• retropragma
  I created `kindstore` for exactly this (only supports Bun currently). I never promoted the project until this comment. Curious if anyone is intrigued?https://github.com/alloc/kindstore
• rcarmo
  I'm doing both "documents" and documents (JSON and gzip compressed blobs for emails, office docs, etc.), with the plaintext extracted and run through FTS5. I can't think of another database that would let me do this, plus vector indexing as well.JSON extensibility and virtual columns help _a lot_ with variable metadata.
• Menu

  tolerance
  Is this not similar to what Simon Willison has written about before?https://sqlite-tutorial-pycon-2023.readthedocs.io/en/latest/...https://simonwillison.net/2021/Jul/28/baked-data/
• Menu

  pmkary
  I remember this getting to the top of HN for at least two more times.
• Menu

  delduca
  In my personal project (a game) I use an indexed key + a JSONB to store the save state, which comes from Lua.So I can have cassette.propxyz = {1, 2, “abc”, true}Andlocal anotherprop = cassette.leprop
• Menu

  mayankbpatel
  Why don’t you use MongoDB? MongoDB is web scale.https://youtu.be/b2F-DItXtZs?is=HlayyJ_DPb8NzbS4(lol! couldn’t resist)
• Menu

  alterom
  A long time ago, I wrote an ORM to serialize/deserialize object data seamlessly into SQLite, with massive arrays of floats being stored as blobs.In code, I could effectively mark which class members need to be stored/restored, and optionally provide a custom serialization function for them if needed.The latter was effectively never necessary, because all the bases types and multi-dimensional arrays were handled by templates.Really wish I open-sourced that thing then, but the corporate bureaucracy around that was tricky.I remember sufficiently little about implementation details now that I think I can get to writing it again, without producing a copypasta of that code - and maybe I should :)
• Menu

  smalltorch
  Is this new or something? Works amazing as a document backend.
• Menu

  antonvs
  > (Aside: The hard bit may be getting a new enough SQLite, at the time of writing Homebrew on macOS has it, else you likely need to use an unstable source like nixpkgs-unstable.)Or just download the source and build it! (Gasp!)
• desktopentree
  [flagged]
• sohaibqasem
  [dead]