←back to thread

172 points frectonz | 3 comments | | HN request time: 0.002s | source

pglite-fusion is a PostgreSQL extension that allows you to embed SQLite databases into your PostgreSQL tables by enabling the creation of columns with the `SQLITE` type. This means every row in the table can have an embedded SQLite database.

In addition to the PostgreSQL `SQLITE` type, pglite-fusion provides the `query_sqlite`` function for querying SQLite databases and the `execute_sqlite` function for updating them. Additional functions are listed in the project’s README.

The pglite-fusion extension is written in Rust using the pgrx framework [1].

----

Implementation Details

The PostgreSQL `SQLITE` type is stored as a CBOR-encoded `Vec<u8>`. When a query is made, this `Vec<u8>` is written to a random file in the `/tmp` directory. SQLite then loads the file, performs the query, and returns the result as a table containing a single row with an array of JSON-encoded values.

The `execute_sqlite` function follows a similar process. However, instead of returning query results, it returns the contents of the SQLite file (stored in `/tmp`) as a new `SQLITE` instance.

[1] https://github.com/pgcentralfoundation/pgrx

Show context
sgarland ◴[] No.42183625[source]
> Most relational database management systems do not support nested records, so tables are in first normal form by default. In particular, SQL does not have any facilities for creating or exploiting nested tables. [0]

“Not with that attitude.”

– frectonz

[0]: https://en.wikipedia.org/wiki/First_normal_form

replies(1): >>42191457 #
1. ffsm8 ◴[] No.42191457[source]
Your link already points out that this isn't followed anymore since Json has been added as a default SQL feature
replies(1): >>42194222 #
2. sgarland ◴[] No.42194222[source]
No? It says that SQL99 allows non-atomic types, and SQL16 allows JSON. That doesn’t mean that 1NF is dead, or even that JSON is allowed in 1NF, only that the standard (which RDBMS providers may choose to implement in part or whole) allows for their existence.

Atomicity of values has been debated for a long time. I’ve come around to the idea that flat arrays can be included in a 1NF table, because they don’t imply any additional structure to the schema. The problem with JSON is that it supports arbitrary K:V pairs as well as nesting, and so can introduce a schema within a schema, which is prone to referential integrity violations (not to mention generally poor performance in RDBMS).

Embedding an entire DB is of course beyond the pale, and my comment was an attempt at wit.

replies(1): >>42198936 #
3. ffsm8 ◴[] No.42198936[source]
Allowing Json means that you're able to store any amount of data, this means you can effectively store full tables (plural!) in a single cell

I.e. { entities: {1: { id:1, name: "abc"}, 2: ... }