←back to thread

Show HN: Embed an SQLite database in your PostgreSQL table

(github.com)
172 points frectonz | 4 comments | 19 Nov 24 11:02 UTC | HN request time: 0.853s | 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
Apreche ◴[19 Nov 24 14:30 UTC] No.42183817[source]▶
That’s fun, but I think I'll just use an SQLite foreign data wrapper instead.
replies(2): >>42183865 #>>42184033 #
1. michelpp ◴[19 Nov 24 14:52 UTC] No.42184033[source]▶
Then you need one wrapper per database, with this approach you can have one database per row.
replies(1): >>42185400 #
2. ellisv ◴[19 Nov 24 16:41 UTC] No.42185400[source]▶
But can I have one row that holds all the databases?
replies(1): >>42185716 #
3. frectonz ◴[19 Nov 24 17:09 UTC] No.42185716[source]▶
yes you can

CREATE TABLE crime_against_humanity ( databases SQLITE[] );

replies(1): >>42186695 #
4. michelpp ◴[19 Nov 24 18:36 UTC] No.42186695{3}[source]▶
With the expanded datum api you can also work with subscriptable array types to only expand elements lazily as needed. It might already works if you try it, but support for it might be hardwired only to nested stock arrays, something to look into.