71–80 of 116 posts
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.
But after spending some time with a mixed-schema table at even modest scale, I’m wondering how often a better design could have cut the whole problem off.
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.
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?
So, instead of saving the client sqlite db of the org to cloud storage you save it to the centralized db column instead. Litefs probably doesn’t support it yet, but wouldn’t be too hard to add.
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';
oh nice, i didn't know this existed, thanks
Earlier quoted context omitted.
The top line of the README says: "Embed an SQLite database in your PostgreSQL table. AKA multitenancy has been solved." But I'm still having trouble trying to grok the intricacies of it. In a sense, I guess it has well isolated individual SQLite DBs and you'd have to go out of your way to join over them. With that said, does PostgreSQL manage and pool all the writes correctly? So you don't need to worry about SQLite…
You could join over them, but not really in the way you're thinking. Each of the columns that are databases would be updated when the functions execute. You could do weird crap like INSERT/DELETE as part of a postgres level SELECT.
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??
> enforced schema I have bad news for you [0] about SQLite’s view on schema consistency. [0]: https://www.sqlite.org/quirks.html
It's easy enough to replicate those constraints to the client if you want the client to do ahead of time validation, but your source of truth lives in the database...
I wouldn't survive with SQLite.
Earlier quoted context omitted.
I think the right approach would be to store the sqlite database as a varlena type that can be TOASTed and then "expanded" using the Expanded Datum API so that it's a live open database connection for the life of the transaction: https://www.postgresql.org/docs/17/xtypes.html#XTYPES-TOAST https://github.com/postgres/postgres/blob/master/src/include...
Thanks i will look into this more, /tmp stuff is most definitely a hack.
Someone, somewhere will eventually find a legitimate use case for it.
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.