5 ms·

I looked into Supabase a while back but left confused on how to do a basic REST API. They auto-generate an API to interact with the tables you create which soun

I looked into Supabase a while back but left confused on how to do a basic REST API. They auto-generate an API to interact with the tables you create which sounds neat, but like, where does the business logic live? I then checked out their edge functions but it wasn't clear if they are meant to be used that way since the examples are more oriented for tasks. Seems like I'm not understanding something simple here.

Mortiffer 3y ago
core element is https://postgrest.org/en/stable/ https://postgrest.org/en/stable/ . I use this in production in large corporate projects on k8s. For a large number of use cases you can put logic into stored procedures SQL. PG can also do JS or Py stored procedures but you get a better developer experience if your logic code is deployed through regular CI/CD containers or functions (we use both extensively together depending on cost trade offs either one.) Supabase suggests you to use their DENO serverless functions which is cool and all but i think most people would rather deploy node functions on cloudflare for webprojects. That being said the target customer group are those that want to have 99% of their logic in JS frontend. Backend just does CRUD and Auth.
- trevor-e 3y ago
  Interesting, thanks for the info. I thought they were targeting mobile developers since they claim to replace Firebase, but sounds like a mobile app API wouldn't fit their platform very well. That explains why I was very confused trying to use their mobile SDK for iOS lol.
- refulgentis 3y ago
  AFAIK Supabase serverless is Cloudflare, or at least I thought...
  - pcnc 3y ago
    It previously ran on Deno, but now we run our own edge runtime! https://github.com/supabase/edge-runtime https://github.com/supabase/edge-runtime
- fulafel 3y ago
  It seems Supabase only supports JS and PL/pgSQL, not Python or the rest of PG languages. But still you could use compile-to-js languages like ClojureScript.
  - pcnc 3y ago
    Unfortunately python for Postgres is only available as an untrusted language extension, which can provide avenues for things like privilege escalation[0] We’ve decided to only bundle trusted language extensions so that there is a balance between flexibility when it comes to users writing their own procedures, all while maintaining security. [0] https://www.postgresql.org/docs/current/plpython.html https://www.postgresql.org/docs/current/plpython.html
    - fulafel 3y ago
      Oh, interesting. Is it related related to any inherent property of CPython? As there's also trusted Perl, Tcl, Lua etc: https://wiki.postgresql.org/wiki/PL_Matrix https://wiki.postgresql.org/wiki/PL_Matrix
  - cultofmetatron 3y ago
    I've written a fair bit of pl/pgsql for my startup.. not exactly my frst choice of langauge but I've turned to it for certain optomizations in or system. definitly NOT the choice I'd make for most situations. the language is incredibly clumsy and there isn't much material out there to learn it well. a lot of the ps/pgsl i know comes from reading code and guessing how things should work.
yesimahuman 3y ago
You can access the database from anywhere (client and server depending on your config). I use nextjs so many of my database calls are in Next serverless functions. However, I’ll probably explore moving some of that logic to supabase functions to keep them as close as possible to the database, but I haven’t wanted to move to deno. When you access supabase on the server you can either use their PostgREST features (basically an autogenerated REST API on top of your db which the supabase clients use), or just access Postgres directly though a typical pg lib
- trevor-e 3y ago
  Got it, thanks. This actually fits one of my side projects really well, will have to try it out with NextJS.
kiwicopple 3y ago
(supabase team) you have a few options: 1. connect to Postgres like you do with any other Postgres database. Supabase is just postgres 2. connect to PostgREST, the autogenerated REST API that you mention 3. connect using Edge Functions (Deno) Most people are fine with 1. You can use 2 & 3 if you want to, they are just another tool in the shed
zoogeny 3y ago
One thing to keep in mind which I found using Postgrest interface: you will end up having to put logic into stored procedures. The rest APIs are actually very convenient for aggregating data like joins, but I started to get stuck as soon as I wanted things like transactions. I also found that Row Level Security (RLS) for role based access was a chore and the developer experience of it left much to be desired. If your DB needs are simple then the REST api is very convenient. But if you are planning anything of complexity then you'll have to bone up on your PL/pgsql or go for a regular db connection instead.
- phanimahesh 3y ago
  What about plv8? Write js in postgres! I tried it out once for a project of relatively low complexity for maintainability reasons, nobody else knew pl/pgsql. Worked great.
  - zoogeny 3y ago
    I'm sure it works fine, its just another thing to add to your stack (in some sense). Just a few days ago I saw a comment where a business owner was bragging about how his entire business was run on SQL stored procedures. He had made the technical decision to move all business logic into the database using triggers and stored procedures. That is certainly an option. Otherwise, you end up with a mix of business logic between your code and your database. This can cause confusion and can lead to hard-to-debug systems. In that sense, if you are like the business owner who swears by SQL and making the database the core business-logic layer of your system, then you might even appreciate that Postgrest forces you do move that kind of logic into the database. It is just something to be aware of before you make the decision so you that you aren't surprised when it happens.
- refulgentis 3y ago
  I'm really curious about more of your perspective on RLS: I spent most of my career on mobile and rely heavily on Supabase to give me server superpowers. RLS _seems_ really cool to me (just write a one liner to define access rules as simple as complex as you need!), but I'm guessing I'm missing something. Especially because I don't actually have users yet ;)
teaearlgraycold 3y ago
Yeah I’m sticking with RDS and such
cpursley 3y ago
What we do is much of the business logic in Postgres (triggers, constraints, etc). But then there’s all the other stuff like external integrations, etc. We handled that by having an event system built on the Postgres WAL that we use like a callback system. I put together a little library in Elixir (that originally started out as forked Supabase realtime) for this: https://github.com/cpursley/walex https://github.com/cpursley/walex Recently added the ability to configure WalEx to forward events to webhooks or EventRelay (so you don’t need to know Elixir).
nsonha 3y ago
I don’t understand generated API. It’s useless and doesn’t save much typing to begin with

Mortiffer 3y ago

core element is https://postgrest.org/en/stable/ https://postgrest.org/en/stable/ . I use this in production in large corporate projects on k8s. For a large number of use cases you can put logic into stored procedures SQL. PG can also do JS or Py stored procedures but you get a better developer experience if your logic code is deployed through regular CI/CD containers or functions (we use both extensively together depending on cost trade offs either one.) Supabase suggests you to use their DENO serverless functions which is cool and all but i think most people would rather deploy node functions on cloudflare for webprojects. That being said the target customer group are those that want to have 99% of their logic in JS frontend. Backend just does CRUD and Auth.

trevor-e 3y ago
Interesting, thanks for the info. I thought they were targeting mobile developers since they claim to replace Firebase, but sounds like a mobile app API wouldn't fit their platform very well. That explains why I was very confused trying to use their mobile SDK for iOS lol.
refulgentis 3y ago
AFAIK Supabase serverless is Cloudflare, or at least I thought...
- pcnc 3y ago
  It previously ran on Deno, but now we run our own edge runtime! https://github.com/supabase/edge-runtime https://github.com/supabase/edge-runtime
fulafel 3y ago
It seems Supabase only supports JS and PL/pgSQL, not Python or the rest of PG languages. But still you could use compile-to-js languages like ClojureScript.
- pcnc 3y ago
  Unfortunately python for Postgres is only available as an untrusted language extension, which can provide avenues for things like privilege escalation[0] We’ve decided to only bundle trusted language extensions so that there is a balance between flexibility when it comes to users writing their own procedures, all while maintaining security. [0] https://www.postgresql.org/docs/current/plpython.html https://www.postgresql.org/docs/current/plpython.html
  - fulafel 3y ago
    Oh, interesting. Is it related related to any inherent property of CPython? As there's also trusted Perl, Tcl, Lua etc: https://wiki.postgresql.org/wiki/PL_Matrix https://wiki.postgresql.org/wiki/PL_Matrix
- cultofmetatron 3y ago
  I've written a fair bit of pl/pgsql for my startup.. not exactly my frst choice of langauge but I've turned to it for certain optomizations in or system. definitly NOT the choice I'd make for most situations. the language is incredibly clumsy and there isn't much material out there to learn it well. a lot of the ps/pgsl i know comes from reading code and guessing how things should work.

yesimahuman 3y ago

You can access the database from anywhere (client and server depending on your config). I use nextjs so many of my database calls are in Next serverless functions. However, I’ll probably explore moving some of that logic to supabase functions to keep them as close as possible to the database, but I haven’t wanted to move to deno. When you access supabase on the server you can either use their PostgREST features (basically an autogenerated REST API on top of your db which the supabase clients use), or just access Postgres directly though a typical pg lib

trevor-e 3y ago
Got it, thanks. This actually fits one of my side projects really well, will have to try it out with NextJS.

kiwicopple 3y ago

(supabase team) you have a few options: 1. connect to Postgres like you do with any other Postgres database. Supabase is just postgres 2. connect to PostgREST, the autogenerated REST API that you mention 3. connect using Edge Functions (Deno) Most people are fine with 1. You can use 2 & 3 if you want to, they are just another tool in the shed

zoogeny 3y ago

One thing to keep in mind which I found using Postgrest interface: you will end up having to put logic into stored procedures. The rest APIs are actually very convenient for aggregating data like joins, but I started to get stuck as soon as I wanted things like transactions. I also found that Row Level Security (RLS) for role based access was a chore and the developer experience of it left much to be desired. If your DB needs are simple then the REST api is very convenient. But if you are planning anything of complexity then you'll have to bone up on your PL/pgsql or go for a regular db connection instead.

phanimahesh 3y ago
What about plv8? Write js in postgres! I tried it out once for a project of relatively low complexity for maintainability reasons, nobody else knew pl/pgsql. Worked great.
- zoogeny 3y ago
  I'm sure it works fine, its just another thing to add to your stack (in some sense). Just a few days ago I saw a comment where a business owner was bragging about how his entire business was run on SQL stored procedures. He had made the technical decision to move all business logic into the database using triggers and stored procedures. That is certainly an option. Otherwise, you end up with a mix of business logic between your code and your database. This can cause confusion and can lead to hard-to-debug systems. In that sense, if you are like the business owner who swears by SQL and making the database the core business-logic layer of your system, then you might even appreciate that Postgrest forces you do move that kind of logic into the database. It is just something to be aware of before you make the decision so you that you aren't surprised when it happens.
refulgentis 3y ago
I'm really curious about more of your perspective on RLS: I spent most of my career on mobile and rely heavily on Supabase to give me server superpowers. RLS _seems_ really cool to me (just write a one liner to define access rules as simple as complex as you need!), but I'm guessing I'm missing something. Especially because I don't actually have users yet ;)

teaearlgraycold 3y ago

Yeah I’m sticking with RDS and such

cpursley 3y ago

What we do is much of the business logic in Postgres (triggers, constraints, etc). But then there’s all the other stuff like external integrations, etc. We handled that by having an event system built on the Postgres WAL that we use like a callback system. I put together a little library in Elixir (that originally started out as forked Supabase realtime) for this: https://github.com/cpursley/walex https://github.com/cpursley/walex Recently added the ability to configure WalEx to forward events to webhooks or EventRelay (so you don’t need to know Elixir).

nsonha 3y ago

I don’t understand generated API. It’s useless and doesn’t save much typing to begin with