When I think of postgrest, supabase and other tools that allows you skip the backend completely and go straight to the DB; is how do you handle business logic that doesn't make sense to have in either the frontend or the DB ?
I have the same question. Since Supabase still exposes the connection parameters to the database, we could spin up another Flask server to deal with routes that are not expressible as simple CRUD operations. The JWT secret shared by the auth service (GoTrue) with Postgrest is available too, so I could also use it to authenticate the users myself.
@supabase developers, is this a reasonable way to do things or am I missing something?
This is completely reasonable an almost encouraged. We give you a full PG database so that you can use it in any way you wish, with any combination of Supabase features that want or don't want.
For example, we have a lot of people who use Prisma with Supabase, rather than the APIs we provide.
My go-to approach is to insert jobs into a queue table, and then have backend workers that consume items from the queue. This has a number of advantages:
1. Faster user experience, the user isn't waiting for the business logic to complete, inserting in the queue should only take a couple of milliseconds.
2. It's more secure, the web worker can have minimal privileges (INSERT only on the queue table) but the backend workers can have much more privilege because they are not user facing.
3. You can scale the web workers orthogonal to the queue workers, as they'll likely have very different scaling properties.
Supabase also has a WAL decoding tool called WALRUS that I have not tried yet, and that could be the most efficient approach going forward. The tradeoff with queue tables is that the tables are persistent and a bit more resilient to failure/retry.
I actually extracted out the WAL listening bit of code from Supabase and use that to listen to changes in Postgres then do callback style business logic in Elixir. If you like Elixir, this could be an option.
I haven't had time to work on it myself until recently, but I brought up the idea of making that bit a separate library (and I understand that the Supabase folks are super busy).
There are lots of simple things that are normally easier to do in the web framework that are suddenly easier to do in the database (with the side effect that you can do DB optimizations much easier).
But the other consideration is that you likely need to do a lot with a reverse-proxy like traefik to have much control of what you are really exposing to the outside world. PostgREST is not Spring, it doesn't have explicit control over every little thing so you're likely to need something in front of it. Anyway, point is that having a simple Flask server with a few endpoints running wouldn't complicate the architecture very much b/c you are better off with something in front of it doing routing already (and ssl termination, etc).
