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

21–30 of 63 posts

Re: SQLite as a Document Database (2020)

#21

Post by inigyou »

nchmy wrote: post #20

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…

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

Re: SQLite as a Document Database (2020)

#23

Post by bbkane »

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

Re: SQLite as a Document Database (2020)

#24

Post by inigyou »

alterom wrote: post #15

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…

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

Re: SQLite as a Document Database (2020)

#25

Post by vidarh »

nchmy wrote: post #20

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…

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

Re: SQLite as a Document Database (2020)

#26

Post by Calavar »

WhitneyLand wrote: post #14

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

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.

Re: SQLite as a Document Database (2020)

#30

Post by 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.

Post reply on HN