Why?
Show HN: Embed an SQLite database in your PostgreSQL table
111–116 of 116 posts
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#112Earlier quoted context omitted.
Your link already points out that this isn't followed anymore since Json has been added as a default SQL feature
No? It says that SQL99 allows non-atomic types, and SQL16 allows JSON. That doesn’t mean that 1NF is dead, or even that JSON is allowed in 1NF, only that the standard (which RDBMS providers may choose to implement in part or whole) allows for their existence. Atomicity of values has been debated for a long time. I’ve come around to the idea that flat arrays can be included in a 1NF table, because they don’t imply any…
Allowing Json means that you're able to store any amount of data, this means you can effectively store full tables (plural!) in a single cell
I.e. { entities: {1: { id:1, name: "abc"}, 2: ... }
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#113What 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’m thinking maybe you’d like to use litefs for multi-tenant dbs close to the tenant. But perhaps you’ll want a centralized billing/reports database under postgres as well? 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.
Mixmaster Mike, what’cha got say??
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#114If 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
Multi-tenancy causes performance issues that simply don't exist if each customer's data is in it's own database.
Yeah really depends on the volume of the data and how sensitive the workload is to a few milliseconds. For a lot of business use cases, it's totally worth it to maintain just one database.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#115Earlier quoted context omitted.
Curious - so how do you manage client-specific schemas then? Do you just have mappings in postgres (column1, column2, column3, etc.) or maybe store a client specific schema in bson per client?
It's all JSON in two Postgres tables: `collection` which represents a Notion Database and has a `collection.schema` JSONB column, and `block` which has a `block.properties` JSONB column that stores the property values - the stuff in the Notion Database cells - for each row. We apply "schema on read" when querying or rendering a Notion Database, and we have a service on the backend that builds indexes/caches for hot c…
Thanks! Super helpful.
Re: Show HN: Embed an SQLite database in your PostgreSQL table
#116Noob question: isn't it kind of defeating the main purpose of SQLite? Meaning having db running in the same process as the program?