SQLite as a Document Database (2020) | Hacker News Reader

SQLite as a Document Database (2020)

dgl.cx

256 pointslioeters60 comments

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

If I create a view of a table containing JSON data (or any other kind of data), I can create columns from calculations on existing fields.

I'm guessing the advantage of generated columns is that they are real columns, not computed columns like in a view. This means that if an insert doesn't work with the new generated column an error will be generated? Is that a correct understanding ?

Is this perhaps the main advantage of this feature ?

The data creation and storage options, always computed or stored on write of dependant columns seems like a possible advantage too.

Main practical difference is that you can put index on generated column, and on view you cannot, SQLite has no materialized views. Constraint part you understood right, NOT NULL on generated column fails at insert, and note that VIRTUAL costs nothing on disk but can still be indexed, so STORED is mostly for when expression itself is expensive.

Why don’t you use MongoDB? MongoDB is web scale.

https://youtu.be/b2F-DItXtZs?is=HlayyJ_DPb8NzbS4

(lol! couldn’t resist)

Wait, are all of those shared online?

That was the part I really missed from my Google days.

That, insane achievement badges, and terrible-ideas-discuss (if anyone at Google is reading this, I have one word for you: dirigibles). It was like /r/NonCredibleDefense but for Google.

why is this downvoted...?

because document database hatred is passe

"hatred" is an... interesting... way to describe the joke the person was trying to make

If jokes are hate speech, then my life is a human rights violation.

hatred and hate speech are different things

Please remove everything starting from `is` or `si` from youtube links. The stuff after it is just for tracking.

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

JSON is just a textual representation of the internal data structures.

That is still not what I'd call a "document".

The 1st edition CouchDB book from 2010 explained it like this:

> We write software to improve our lives and the lives of others. Usually this involves taking some mundane information—such as contacts, invoices, or receipts—and manipulating it using a computer application. CouchDB is a great fit for common applications like this because it embraces the natural idea of evolving, self-contained documents as the very core of its data model.

> Self-Contained Data

> An invoice contains all the pertinent information about a single transaction—the seller, the buyer, the date, and a list of the items or services sold. As shown in Figure 1, “Self-contained documents”, there’s no abstract reference on this piece of paper that points to some other piece of paper with the seller’s name and address. Accountants appreciate the simplicity of having everything in one place. And given the choice, programmers appreciate that, too.

> Yet using references is exactly how we model our data in a relational database! Each invoice is stored in a table as a row that refers to other rows in other tables—one row for seller information, one for the buyer, one row for each item billed, and more rows still to describe the item details, manufacturer details, and so on and so forth.

https://guide.couchdb.org/editions/1/en/why.html

Iow, a document database stands in contrast to a relational db in that these JSON things we store in them are more stand-alone “documents” compared to storing data in rows and columns in a relational db like PostgreSQL or SQLite.

The thing that people always miss about this takeaway is that while it is a truism, most data is actually inherently relational. Even in your example given, the individual components that are made to assemble that document are better represented as relational datastores

Nothing here says the data isn't relational. It strongly disagrees, with reasons, why it's not better represented as relational.

Personally I prefer the relational stance, and there are a lot of people who don't get it who say things like "this data isn't relational", but that's not the argument GP made.

In the real world of business, generally you want to store these pieces of information together to establish a historical record, not have references or links to other tables, etc. Links and relations are fragile, as anyone who's clicked through to a 404 can attest. Standalone documents last as long as the media that stores them.

Immutable records which are retained for legal purposes should be in object storage, not a database.

I would argue most data is inherently(naively?) hierarchical(the document), relational structured data is a clever but unintuitive mechanism to introduce powerful analytic opportunities to a set of data.

Basically a document is a report, a large disjoint volume of information on a subject, I consider this the natural form of data because this is how it is collected and how most people think about it. relational is sort of like storing that data as vertical slices through your stack of reports. Not natural at all but much nicer for analysis across the data set.

The "relational" in relational databases is not about department number in employees referencing departments.

What the model calls relations are sets of n-tuples where each attribute value has a domain.

SQL databases call their version of relations tables, and, in that view, a database with a single table is still relational.

Now I don't think SQL databases are relational but the analogy still holds (I'd just say that a relational database can have just a single relation)

If you meant it this way and I misunderstood you, apologies, though I'm not sure I'd say most data is actually inherently relational (even though I do think the relational model is the best one we have so far for databases).

However, if you meant that most data has relationships (as the ones we enforce with foreign keys in sql databases) then I agree with you, and I think using database management systems that don't have good support for representing this type of relationships between data entities will only work in niche cases and will eventually cause more trouble than benefits in general-purpose use cases.

Here is a realisation from my recent work: it is not if the data is relational - because if you need you can always push the data into relations - just like you can push it into hierarchies if all you have is files and directories. It is about how much churn there is in the relations. You can model relations with just links or something and if there is not much churn you can keep it in git and it will work OK - the problem starts when your relations start churning (like when you have reviews that need to be updated on both review instruction change and document change) this is when you see yourself start building a relational db (badly).

It's a MongoDBism. The MongoDB community used document to mean the nonrelational equivalent of a row in a relational database. But over time there was definitional shift, and now it means a JSON blob, even if that blob is in a relational database.

It's a much older term, eg. Lotus Notes was described as a document database.

The "document" is sort of the native data type, Put everything in a big hierarchical structure, It is very flexible but analytics across the set can suffer. "relational" is another way to store data, break your big hierarchy into sets of related rows and store the rows as a table, If I were to describe it in geometric terms where the document is a report on a paper, the relation is a vertical slice through a stack of those reports. This is slightly non-intuitive but provides for interesting analysis opportunities.

But nothing prevents you from treating your relational database as a document database, set it up as a key-value store where each key is is the document title and each value is a large blob of document data. If your documents are fairly consistent it is also easy enough to build indexes and query features to regain some of the analytical ability of relational data.

1) The internal or wire representation of data from document DBs isn't necessarily JSON, though it's normally converted to such.

2) "Document" has undergone a bit of semantic drift thanks to HTML and XML. In an informational context it means "structured, hierarchical unit of data containing mostly text". The data from forms, invoices, and the like needs to be collected and stored, even if it isn't properly normalized and relationalized (or is en route to being such) so a "document database" is thought to be suited to this task

I dunno, whatever, I'm in the "just fucking use postgres until you can justify why you shouldn't" camp.

I think it originates with the early web. JSON being a replacement for XML, and "document" being the general description for the response to a web server.

> 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!

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/

Check the dates, the "before" part is inaccurate.

I'm sorry, I didn't mean before 'this'. I shouldn't have even said "before". That was me thinking to myself out loud.

What I'm trying to figure out is if they're related concepts. This may be a very naive question awkwardly asked.

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);

```

Postgres does even better and it's available right now. No virtual column needed.

    CREATE TABLE t1 (data JSONB);
    INSERT INTO t1 VALUES ('{"column1":1234}');
    CREATE INDEX t1column1 ON t1(data->'column1');
    SELECT * FROM t1 WHERE data1->'column1' = '1234'; // not sure about data type

I would not call an index format which gets slower with growth "better". And that vacuum issue on JSONB scales with your data size.

What do you mean slower with growth? What vacuum issue?

"body" is the "json in its own column" you're asking for. They are doing exactly what you're saying.

The point is exactly that it means you can selectively retrospectively add virtual columns, optionally backed with an index, as you decide which fields you need more structured access to.

The example you're giving is in principle the same as the "ALTER TABLE ... GENERATED ALWAYS AS ... VIRTUAL" example + a subsequent index in the Sqlite example.

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.

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

I do that in psql and it works really well. But your post got me thinking since I need to find a solution to store content of multiple documents, have a way to do FTS as well as vector similarity. I dont need that for all of the documents at once - I need to do it either for one document or at most couple of documents.

Now I'm thinking I could have a separate database file per "batch", store it in object storage and then download on demand and query it as I want. This way I'll not bloat my primary storage size as well as I dont need a special vector DB since sqlite vector search will be enough for up to 50k vectors.

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 :)

Did it end up becoming more painful than just writing the SQL?

It almost always does. People put so much effort into building layers of abstractions to avoid using SQL, while just writing it directly would be much faster, easier, more readable, easier to maintain, modify, and debug. I do not get it.

>It almost always does. People put so much effort into building layers of abstractions to avoid using SQL, while just writing it directly would be much faster, easier, more readable, easier to maintain, modify, and debug. I do not get it.

When your data is literally hierarchical and all you need to do is read/write one Big Object, "just using SQL" makes no sense.

I used SQLite because it makes a lot of sense to use as an application file format[1], i.e. as an alternative to writing directly to the filesystem.

The hierarchical data was a mix of parameters and huge blobs, so text-based JSON was a no-go, size-limited BSON was a no-go, at which point I asked myself if there was a reason to not just use SQLite, and found none.

[1] https://sqlite.org/appfileformat.html

No it did not. The data wasn't relational.

The ORM was the smallest part of it, I could have written/read from JSON too, but SQLite was just a far better choice for a mix of structured data and big blobs.

The thing it replaced was writing to a file at a predetermined offsets. Which became very fun as the file format changed between versions and backwards compatibility needed to be maintained.

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.

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.

https://sqlite.org/appfileformat.html

It is not a bad idea. It is a very flexible and stable format.

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.

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}

And

local anotherprop = cassette.leprop

Are you doing this because you also use SQLite for other relational data?

Possibly yes, otherwise I could use another simple solution

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

Just once. But related and similar links show up regularly.

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

Almost 6 years old. (2020).

> (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!)

Even easier: click the download link on the website. https://sqlite.org/download.html

The great thing about using a package manager is that it also handles updating and uninstalling software, not just the initial install

Sure, but the point is (1) that you don’t need a package manager to install software, which the comment I replied to seemed to assume, and (2) that for something you’re developing against like SQLite, installing it via package manager really isn’t that important. It doesn’t need to integrate with the rest of your system the way typical apps might.

What seems to be happening here is that people have learned that package managers are the right way to install software, but they don’t really understand the reasons, or where and how exceptions might apply. IMO your comment does that as well.

Even for a personal project such as the one in the blog post, I'd still rather use a package manager to install SQLite.

It's POSSIBLE to build it from source, and manually re-download the source and C compiler (and any dependencies, though I'm not sure SQLite has any) when I want to update and instruct any collaborators to do the same thing... But I'd still rather automate it all with a package manager.

Especially because I'm probably also handling other dependencies in the project and I value the uniform simplicity of using a package manager for all of them

Reply on news.ycombinator.com