Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite as a Document Database (2020)

dgl.cx

Post reply on HN
Print view View original

51–60 of 63 posts

Re: SQLite as a Document Database (2020)

#51

Post by antonvs »

bbkane wrote: post #23
antonvs wrote: post #8

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

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.

Re: SQLite as a Document Database (2020)

#52

Post by kreelman »

stanac wrote: post #2

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 build…

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.

Re: SQLite as a Document Database (2020)

#54

Post by artyomsv »

kreelman wrote: post #52
stanac wrote: post #2

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 build…

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 advant…

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.

Re: SQLite as a Document Database (2020)

#56

Post by zby »

SOLAR_FIELDS wrote: post #34
QuantumNomad_ wrote: post #19

Earlier quoted context omitted.

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 cor…

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

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

Re: SQLite as a Document Database (2020)

#58

Post by piterrro »

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.

Post reply on HN