4 ms·

We currently make use of Supabase and it's been fantastic. It's enabled us to completely get away from having a traditional backend/API by utilizing RLS and it'

We currently make use of Supabase and it's been fantastic. It's enabled us to completely get away from having a traditional backend/API by utilizing RLS and it's realtime nature to have data directly in the UI. Rough numbers are showing we cut development time by a third vs a traditional approach.

pgroves 5y ago
I'm on a POC project that's using PostgREST and it's been extremely fast to get a big complicated data model working with an API in front of it. But I guess I don't get how to really use this thing in reality? What does devops look like? Do you have sophisticated db migrations with every deploy? Is all the SQL in version control? I also don't really get where the users get created in postgres that have all the row-level permissions. The docs are all about auth for users that are already in there.
- kiwicopple 5y ago
  In Supabase we use a separate Auth server [0]. This stores the user in an `auth` schema, and these users can login to receive a JWT. Inside the JWT is a "role", which is, in fact, a PostgreSQL role ("authenticated") that has certain grants associated to it, and the user ID (a UUID). Inside your RLS Policies you can use anything stored inside the JWT. My cofounder made a video [1] on this which is quite concise. Our way of handling this is just an extension of the PostgREST Auth recommendations: https://postgrest.org/en/v9.0/auth.html https://postgrest.org/en/v9.0/auth.html [0] Auth server: https://github.com/supabase/gotrue https://github.com/supabase/gotrue [1] RLS Video: https://supabase.com/docs/learn/auth-deep-dive/auth-row-level-security https://supabase.com/docs/learn/auth-deep-dive/auth-row-leve...
- michelpp 5y ago
  This is my personal experience with using PostgREST (I haven't had the full supabase experience yet): > What does devops look like? I usually spin PostgREST workers up in some kind of managed container service, like Google Compute Engine. PostgREST is stateless, so other than upgrades, you never really need to cycle the services. As for resources PostgREST is extremely lean, I usually try to run 4 to 8 workers per gigabyte of RAM. > Do you have sophisticated db migrations with every deploy? You can use whatever migration tool your want. Sqitch is quite popular. I've even worked on projects that were migrated by Django but PostgREST did the API service. > Is all the SQL in version control? Yes this is a good approach, but it means needing a migration tool to apply the migrations in the right order, this is what Sqitch does and many ORMy libraries have migration sort of half-baked in. It's worth noting that because many of the objects that PostgREST deals with are views, which have no persistent state, the migration of the views can be decoupled from the migration of the persistent objects like tables. Replacing a view (with CREATE OR REPLACE VIEW) can be done very quickly without locking tables as long as you don't change the view's schema.
iwebdevfromhome 5y ago
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 ?
- polskibus 5y ago
  You can do business logic in stored procedures, functions and views.
  - twoquestions 5y ago
    This is extremely painful to debug and troubleshoot compared to normal code.
- michelpp 5y ago
  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.
  - cpursley 5y ago
    Do you have more info on the WALRUS stuff? Is that part of the Elixir lib?
    - kiwicopple 5y ago
      WALRUS is all SQL, so you could use it with anything (probably with a bit of extra code-wrangling): https://github.com/supabase/walrus https://github.com/supabase/walrus

pgroves 5y ago

I'm on a POC project that's using PostgREST and it's been extremely fast to get a big complicated data model working with an API in front of it. But I guess I don't get how to really use this thing in reality? What does devops look like? Do you have sophisticated db migrations with every deploy? Is all the SQL in version control? I also don't really get where the users get created in postgres that have all the row-level permissions. The docs are all about auth for users that are already in there.

kiwicopple 5y ago
In Supabase we use a separate Auth server [0]. This stores the user in an `auth` schema, and these users can login to receive a JWT. Inside the JWT is a "role", which is, in fact, a PostgreSQL role ("authenticated") that has certain grants associated to it, and the user ID (a UUID). Inside your RLS Policies you can use anything stored inside the JWT. My cofounder made a video [1] on this which is quite concise. Our way of handling this is just an extension of the PostgREST Auth recommendations: https://postgrest.org/en/v9.0/auth.html https://postgrest.org/en/v9.0/auth.html [0] Auth server: https://github.com/supabase/gotrue https://github.com/supabase/gotrue [1] RLS Video: https://supabase.com/docs/learn/auth-deep-dive/auth-row-level-security https://supabase.com/docs/learn/auth-deep-dive/auth-row-leve...
michelpp 5y ago
This is my personal experience with using PostgREST (I haven't had the full supabase experience yet): > What does devops look like? I usually spin PostgREST workers up in some kind of managed container service, like Google Compute Engine. PostgREST is stateless, so other than upgrades, you never really need to cycle the services. As for resources PostgREST is extremely lean, I usually try to run 4 to 8 workers per gigabyte of RAM. > Do you have sophisticated db migrations with every deploy? You can use whatever migration tool your want. Sqitch is quite popular. I've even worked on projects that were migrated by Django but PostgREST did the API service. > Is all the SQL in version control? Yes this is a good approach, but it means needing a migration tool to apply the migrations in the right order, this is what Sqitch does and many ORMy libraries have migration sort of half-baked in. It's worth noting that because many of the objects that PostgREST deals with are views, which have no persistent state, the migration of the views can be decoupled from the migration of the persistent objects like tables. Replacing a view (with CREATE OR REPLACE VIEW) can be done very quickly without locking tables as long as you don't change the view's schema.

iwebdevfromhome 5y ago

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 ?

polskibus 5y ago
You can do business logic in stored procedures, functions and views.
- twoquestions 5y ago
  This is extremely painful to debug and troubleshoot compared to normal code.
michelpp 5y ago
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.
- cpursley 5y ago
  Do you have more info on the WALRUS stuff? Is that part of the Elixir lib?
  - kiwicopple 5y ago
    WALRUS is all SQL, so you could use it with anything (probably with a bit of extra code-wrangling): https://github.com/supabase/walrus https://github.com/supabase/walrus