HN Reader

Show HN: Embed an SQLite database in your PostgreSQL table

175

108

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

> 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

3 days agoby sgarland

Ok, hear me out: what if we make something that takes a postgres database dir, tars it together and encodes it as a binary blob in SQLite?

We could have SQLite within postgres within sqlite within postgres! Is it practical or even slightly useful? Of course not - but it's SQL databases all the way down. Not that this is a good thing in itself.

3 days agoby aerzen

What are the use cases for this? I can't imagine designing a database schemas to use this in a typical product. Is it intended for hybrid applications to back up local user data directly with their account info?

3 days agoby robertclaus

I think SQLite columns for SQLite would be superior to SQLite’s JSON columns whose operators are a whole ‘nother query language you need to learn and seem comparatively limited.

3 days agoby ecuaflo

They /tmp file mechanism sounds like a bit of a hack, is that definitely necessary?

It may be possible to create a SQLite in-memory database instead and then load the binary blob data into it using the backup API or some kind of trick with VACUUM INTO.

3 days agoby simonw

Yo, dawg, I heard you like databases...

This is nuts. I can't think of a use for it, but I'm sure it's "a solution that will eventually find a problem".

3 days agoby bitwize

"multitenancy has been solved" - I'm confused, can you not use multiple SQLite files??

2 days agoby abrookewood

1NF crime against humanity?

3 days agoby bni

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';

3 days agoby arkh

I’m trying to think through when I’d reach for this over jsonb… I guess the fact that there’s an enforced schema? And that you could do aggregations on your SQLite db? Or maybe if you wanted to send the whole delete db to a client??

3 days agoby zekenie

If you’re using Postgres, multi tenancy has been solved with row level security. It’s super easy to add a tenant id column to every table and a policy that only allows connections to see data from one tenant

2 days agoby klysm

This is likely great for serving SQLite data to frontends using the SQLite http-vfs.

Would be great combined with functions/triggers/views to mirror specific data/queries from Postgres as SQLite.

3 days agoby rustman123

What, no operators? I want indexes on these columns, and some weird and wonderful operator syntax for doing cross-database joins between multiple DATABASE columns! =)

2 days agoby Pinus

This is not as crazy as it sounds but I'd rather have PG-in-PG than the hetrogeneous arrangement here. PG in S3 would be useful too.

2 days agoby dboreham

Are there still reasons to use PostgreSQL?

I like the simplicity of SQLite's "a file is all you need" approach so much, that I started to converge all my projects to SQLite. So far, I have not come across any roadblocks.

Can anyone think of a use case where PostgreSQL is better suited than SQLite?

3 days agoby TekMol

Please let us embed that sqlite-hosting postgres in a TXT DNS record. We really need that.

2 days agoby tacone

A query on very ugly tables will count 26 thousands occurrences of the apostrophe, I love it

3 days agoby foul

If you do the same for DuckDB you could call it pgducken. ;-)

2 days agoby snthpy

That’s fun, but I think I'll just use an SQLite foreign data wrapper instead.

3 days agoby Apreche

Someone, somewhere will eventually find a legitimate use case for it.

3 days agoby BiteCode_dev