Hacker News Reader

Comment by colonwqbang | Hacker News Reader

> when you’ve validated the requirements, you can keep the existing façade and replace PostgREST with your custom-developed API.

Why? What is it about postgrest which makes it unsuitable for "real" usage? The author doesn't mention it.

View on HN

Postgres is amazing, but there are some issues you quickly hit with systems like this:

1. Side effects: Want to send an email when something happens

2. conditional permissions: Need access to data under certain circumstances not modelled by the security language?

3. Subset access: Access to parts of the row / document.

4. Aggregate access: Access to eg. a count of rows you can not access.

These are usually solved using serverless functions – or an API written in code.

Personally I err to the side of just writing some code, maybe because I enjoy that part of the project. Then I might be more inclined to use no-code solutions for the frontend, where others want the freedom and flexibility.

Supabase handles all of this.

I handle #1 with by listening to database changes then reacting as needed with a change capture / event-like system:

https://github.com/cpursley/walex

Does supabase do anything particular to address those or just hand you postgres's RLS? It's been a minute since I used supabase but when I last did you were basically on your own with that.

Which is not necessarily bad, RLS is sophisticated enough to handle all real world cases I've come across. But if it's just that I think it's more correct to say postgres already handles all of this.

The problem with event driven systems is that they can end up being a kind of spaghetti. It can be hard to track down all the side effects. There's no place where you can read some code to see what happens sequentially.

I would go the other way around.

Why start with an SQL database? When I start something new, I don't want to fiddle around with tables every time I change my model.

I agree but the baby of “joins” is often thrown out with the bathwater of “schemas”

Is there a relational schemaless DB I wonder?

Most of my tables have a column called “metadata”, “extra” or “ payload” with jsonb type… for the data i don’t want to model the rigid way.

It’s no longer relational vs. NoSQL, you can get the best of both worlds. Assuming of course you don’t need “planetscale”.

Nice, and by the looks of it you can still do joins to what is inside the jsonb. I presume indexes are possible too (or if not just pull that data out to actual columns).

Yup, it’s way more than a blob. You can query it.

Yeah it's postgres's jsonb type.

There are, but they’re pretty niche because their performance is really bad. Niche enough I can’t recall a single name today.

But really, my advice for early prototyping is split in two: either keep it in memory and serialize to disk, or use SQLite.

Note that even using serialized memory constructs will require data migrations, aka schema updates.

Sounds like a contradiction in terms to me.

No contradiction at all. Imagine a relational database where the only column type is a variant (union) of the usual datatypes, and where new tables and new columns are auto-created whenever an insert/update references them (and, possibly, unused tables and columns are garbage-collected). You’d still use regular SQL and have joins and everything, the tables would still be relations, but there’s no defined schema.

Then use a Postgres jsonb column. Makes it easier to migrate to a table when you have you model down.

Business logic in your preferred language paradigm, rather than sprocs/triggers

Agree. I've used PostgREST for production and it works great. No need to rewrite the API.

Reply on news.ycombinator.com