[highwaylights]

I like PostgREST for some of it's use cases (views mostly), but the issue I have with it is that I don't often want a user to have direct access to the database, even if it's limited to their own data.

Mike can edit his name and his bio. He could edit some karma metric that he's got view access to but no write access to. That's fine, I can introduce an RLS policy to control this. Now Mike wants to edit his e-mail.

Now I need to send a confirmation e-mail to make sure the e-mail is valid, but at this point I can't protect the integrity of the database with RLS because the e-mail/receipt/confirm loop lives outside the database entirely. I can attach webhooks for this and use pg_net, but I could quickly have a lot of triggers firing webhooks inside my database and now most of my business logic is trapped in SQL and is at the mercy of how far pg_net will scale the increasing amount of triggers on a growing database.

Even for simple CRUD apps, there's so much else happening outside of the database that makes this get really gnarly really fast.

[whstl]

> Now I need to send a confirmation e-mail to make sure the e-mail is valid, but at this point I can't protect the integrity of the database with RLS because the e-mail/receipt/confirm loop lives outside the database entirely

Congratulations: that's not basic CRUD anymore, so you ran into the 5% of cases not covered by an automatic CRUD API.

And I don't see what's the dilemma here. Just use a normal endpoint. Keep using PostgREST to save time.

You don't have to throw the baby away with the bathwater just because it doesn't cover 5% of cases the way you want.

It's a rite of passage to realize that "use the right tool for the job" means you can use two tools at the same time for the same project. There are nails and screws. You can use a hammer and a screwdriver at the same time.

[no_wizard]

>You can use a hammer and a screwdriver at the same time

How do you balance the nail and screw? I'm serious, I'm trying to picture this, hammer in one hand, screwdriver in the other, and the problem I see here is the nail and screw need to be set first, which implies I can't completely use them both at the same time.

Perhaps my brain is too literal here, but I can't figure how to do this without starting with one or the other first

[SenHeng]

I'm going to answer this using Firebase, which Supabase is supposed to be a copy of.

There are 2 parts to using Fireabse, the client SDK and the admin SDK.

The client SDK is what's loaded in the front end and used for 95% of use cases like what u/whstl mentions.

The adminSDK can't be used in the browser. It's server only and is what you can use inside a custom REST API. In your use case, the email verification loop has to happen on a backend somewhere. That backend could be a simple AWS lambda that only spins up when it gets such a verification request.

You're now using a hammer for the front end and a screw driver for the finer details.

[whstl]

Yep. Exactly the same for PostgREST/Supabase.

The equivalent to Firebase's "Client SDK" is just the PostgREST API, which needs to be secured.

The equivalent to Firebase's "Admin SDK" is the PosgreSQL connection string that can be used like a normal PostgreSQL.

[whstl]

At the same time: in the same project.

Some projects require nails, other require screws, some might require both.

Instead of hammering screws (or in this case reinventing a screwdriver), just use an existing screwdriver. That’s what I mean: don’t reinvent the solved problem of CRUD endpoints when applicable to the endpoint. Nothing says you can’t use two techs per project.

[DidYaWipe]

Why would you hard-code queries into your client application?

[whstl]

I never said anything of the sort, what are you talking about?

[DidYaWipe]

"...an automatic CRUD API. And I don't see what's the dilemma here. Just use a normal endpoint. Keep using PostgREST to save time."

[whstl]

Where in my message does it say or imply that you should “hard code queries in your client application?”?

EDIT: What I’m advocating here is the opposite: use those tools for CRUD so that your frontend looks exactly the same as a frontend with a regular backend would. If the tool is not good for it (like the example), just use a regular endpoint in whatever backend language or framework. Don’t throw the baby (the 95%) with the bathwater (the 5%).

By “just use a normal endpoint” I mean “write a normal backend for the necessary cases”.

[DidYaWipe]

Of what use is an "automatic CRUD API" if you're not putting query logic in your client application?