←back to thread

172 points frectonz | 2 comments | | HN request time: 0s | 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
arkh ◴[] No.42185178[source]
You may want to use the $$ way to declare strings for your examples. Something like:

-- Create a todo for "frectonz"

UPDATE people

SET database = execute_sqlite(

    database,

    $sqlite$INSERT INTO todos VALUES ('solve multitenancy')$sqlite$
)

WHERE name = 'frectonz';

replies(1): >>42185191 #
1. frectonz ◴[] No.42185191[source]
oh nice, i didn't know this existed, thanks
replies(1): >>42185889 #
2. Tostino ◴[] No.42185889[source]
The string between the dollar signs can only be closed by another set of dollar quotes with the same string between them. So it allows you to do quotes within quotes within quotes if necessary.