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.
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?
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.
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.
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".
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
Noob question: isn't it kind of defeating the main purpose of SQLite? Meaning having db running in the same process as the program?
1NF crime against humanity?
"multitenancy has been solved" - I'm confused, can you not use multiple SQLite files??
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';
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??
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.
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! =)
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.
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?
A query on very ugly tables will count 26 thousands occurrences of the apostrophe, I love it
Please let us embed that sqlite-hosting postgres in a TXT DNS record. We really need that.
That’s fun, but I think I'll just use an SQLite foreign data wrapper instead.
Someone, somewhere will eventually find a legitimate use case for it.
If you do the same for DuckDB you could call it pgducken. ;-)
Speed, anyone?
How long does it take to update a table of, say, 1k rows? 1m rows? Same when subqueries and joins are involved to calculate what's to be updated?
Yo dawg, I heard you liked databases. So I put a database inside your database!
Exactly what we need from a Show HN.
Posts like this make me realize I never even scratched the surface of any technology ever.
The real win of this seems like schema divergence among one column?
Yet again SQlite is over represented here in the server context.
Why?
How would embedding a database inside another help someone?
Yo dawg I heard you liked databases so we put a database in your database.
[flagged]
> 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