5 ms·

I really like the idea of this, but the one hesitation I always have is where does the non-CRUD functionality go? Do you need to create a separate service to h

I really like the idea of this, but the one hesitation I always have is where does the non-CRUD functionality go? Do you need to create a separate service to handle your other business logic? I know technically you can store procedures in some DBs like Postgres, but that doesn’t seem ideal to me.

a-saleh 5y ago
This is the reason why postgrest is mostly solution looking for a problem to solve. I.e. last time I had great usecase, I ran into the problem that I needed good integration with our identity-service and didn't want to do the workaround that the project suggests.
- steve-chavez 5y ago
  If your identity-service can generate a JWT, then you can integrate your auth with PostgREST. There's a related example with Auth0[1] in the docs. [1]: https://postgrest.org/en/v9.0/auth.html#jwt-from-auth0 https://postgrest.org/en/v9.0/auth.html#jwt-from-auth0
rubyist5eva 5y ago
You use an api-gateway in front of this and send those requests to your application server, generally though you should try to keep your business logic in triggers and stored procedures.
- deleted 5y ago
  [deleted]
- lenzm 5y ago
  Writing all of my business logic in triggers and stored procedures seems like a high cost to avoid writing crud. All the value comes from business logic. Making the business logic easier to read and work with is usually my top priority.
  - throwawayboise 5y ago
    Databases tend to outlive front-end applications. How long has Postgres been around? How long has your currently fashionable front-end framework been around? I avoid triggers but put as much of my applications into stored procedures as I can.
    - IceDane 5y ago
      First of all, you usually don't put your business logic in your frontend. This makes your entire argument fall flat on its face. If we then take your argument and instead just apply it to, say, using JS on the backend.. well, JS is not going anywhere any time soon. pl/pgsql is absolutely awful and it is a disservice to other programming languages to call it a programming language. Writing actual, complicated business logic using this and maybe some mix of JS(because why not pull that into the databse?? great idea) is just going to make every single person who has to maintain this in the future hate your guts. I'm truly glad I don't have to work with anyone who genuinely thinks such a design would be a good idea.
      - sigstoat 5y ago
        > First of all, you usually don't put your business logic in your frontend. This makes your entire argument fall flat on its face. the comment makes sense if one interprets "frontend" as "all the shit in front of the database". which is a reasonable interpretation in the context of something like postgrest, if not perhaps satisfying to you. > pl/pgsql is absolutely awful who cares > Writing actual, complicated business logic using this and maybe some mix of JS(because why not pull that into the databse?? great idea) there are a number of other languages available for writing postgresql procedures, and you can add new ones
  - ratww 5y ago
    Wether it makes sense to put business logic in procedures really depends on what's involved in "business logic". For data-heavy apps with very complex schemas, your code can actually get more readable inside stored procedures. I used procedures for a content creation pipeline/headless CMS app that had flexible workflow templates (those also lived in the DB). It works quite well, and is simpler and more readable than most common backend code and much faster to develop. If you need to trigger async stuff, like network access or data processing, one pattern I enjoy is writing to a log-ish table that gets picked up and processed later by a separate service. This is nice for emails and other types of notifications: your users get a log of sent emails, and you can very easily do it inside a transaction. If the complexity is on the querying side, then you can get a lot of mileage from views. If you need caching for that, materialized views really help. However: If you're doing complex numeric/algorithmic stuff, rely on external libraries, or has just plain too much business logic... then doing things in a separate service is definitely better, no question about it. Triggers, I'm personally not a fan.
jmnicolas 5y ago
> PostgREST is a fast way to construct a RESTful API. Its default behavior is great for scaffolding in development. They say that it's great for production too, but I'm not so sure.
pzduniak 5y ago
We're currently developing a Supabase app that's backed by PostgREST. For our MVP, we built out everything that we could in SQL procedures (they're honestly not that terrible, especially in Postgres, just a bit verbose), we could also use `pgv8` and write them in JavaScript. If we need to trigger some special logic, we push data through webhooks to another service that also has access to the database. It works, but it's kind of messy and I'm somewhat worried about the performance of the HTTP client bundled with Postgres. This mechanism could also be implemented with the `LISTEN`/`NOTIFY` interface. It feels like the better way to do it is having an API gateway / a separate API accepting the same JWT requests as PostgREST and others. We'll try it for our next project and maybe we'll migrate this product too, if we run into any issues with HTP requests. It's been great for CRUD-heavy apps. Optimizing the RLSes sometimes gets tough, but you can always work around them with views / functions that avoid them.
- dinkleberg 5y ago
  Ah that’s interesting. I’ll have to actually give that a try at some point. I think the API gateway model makes more sense in my head.
agentultra 5y ago
There are a number of options. You can publish the WAL replication stream and write downstream services that react off of that. I wrote a library in Haskell [0] that can read such a stream. Libraries exist in Python and other languages as well. Or you can take advantage of the `LISTEN/NOTIFY` feature in Postgres and use an extension to push events out to various queues or webhooks [1]. [0] https://hackage.haskell.org/package/postgresql-replicant-0.1.0.1 https://hackage.haskell.org/package/postgresql-replicant-0.1... [1] https://postgrest.org/en/v9.0/ecosystem.html#external-notification https://postgrest.org/en/v9.0/ecosystem.html#external-notifi...
michelpp 5y ago
You insert a job into a work queue, and process it asynchronously.

a-saleh 5y ago

This is the reason why postgrest is mostly solution looking for a problem to solve. I.e. last time I had great usecase, I ran into the problem that I needed good integration with our identity-service and didn't want to do the workaround that the project suggests.

steve-chavez 5y ago
If your identity-service can generate a JWT, then you can integrate your auth with PostgREST. There's a related example with Auth0[1] in the docs. [1]: https://postgrest.org/en/v9.0/auth.html#jwt-from-auth0 https://postgrest.org/en/v9.0/auth.html#jwt-from-auth0

rubyist5eva 5y ago

You use an api-gateway in front of this and send those requests to your application server, generally though you should try to keep your business logic in triggers and stored procedures.

deleted 5y ago
[deleted]
lenzm 5y ago
Writing all of my business logic in triggers and stored procedures seems like a high cost to avoid writing crud. All the value comes from business logic. Making the business logic easier to read and work with is usually my top priority.
- throwawayboise 5y ago
  Databases tend to outlive front-end applications. How long has Postgres been around? How long has your currently fashionable front-end framework been around? I avoid triggers but put as much of my applications into stored procedures as I can.
  - IceDane 5y ago
    First of all, you usually don't put your business logic in your frontend. This makes your entire argument fall flat on its face. If we then take your argument and instead just apply it to, say, using JS on the backend.. well, JS is not going anywhere any time soon. pl/pgsql is absolutely awful and it is a disservice to other programming languages to call it a programming language. Writing actual, complicated business logic using this and maybe some mix of JS(because why not pull that into the databse?? great idea) is just going to make every single person who has to maintain this in the future hate your guts. I'm truly glad I don't have to work with anyone who genuinely thinks such a design would be a good idea.
    - sigstoat 5y ago
      > First of all, you usually don't put your business logic in your frontend. This makes your entire argument fall flat on its face. the comment makes sense if one interprets "frontend" as "all the shit in front of the database". which is a reasonable interpretation in the context of something like postgrest, if not perhaps satisfying to you. > pl/pgsql is absolutely awful who cares > Writing actual, complicated business logic using this and maybe some mix of JS(because why not pull that into the databse?? great idea) there are a number of other languages available for writing postgresql procedures, and you can add new ones
- ratww 5y ago
  Wether it makes sense to put business logic in procedures really depends on what's involved in "business logic". For data-heavy apps with very complex schemas, your code can actually get more readable inside stored procedures. I used procedures for a content creation pipeline/headless CMS app that had flexible workflow templates (those also lived in the DB). It works quite well, and is simpler and more readable than most common backend code and much faster to develop. If you need to trigger async stuff, like network access or data processing, one pattern I enjoy is writing to a log-ish table that gets picked up and processed later by a separate service. This is nice for emails and other types of notifications: your users get a log of sent emails, and you can very easily do it inside a transaction. If the complexity is on the querying side, then you can get a lot of mileage from views. If you need caching for that, materialized views really help. However: If you're doing complex numeric/algorithmic stuff, rely on external libraries, or has just plain too much business logic... then doing things in a separate service is definitely better, no question about it. Triggers, I'm personally not a fan.

jmnicolas 5y ago

> PostgREST is a fast way to construct a RESTful API. Its default behavior is great for scaffolding in development. They say that it's great for production too, but I'm not so sure.

pzduniak 5y ago

We're currently developing a Supabase app that's backed by PostgREST. For our MVP, we built out everything that we could in SQL procedures (they're honestly not that terrible, especially in Postgres, just a bit verbose), we could also use `pgv8` and write them in JavaScript. If we need to trigger some special logic, we push data through webhooks to another service that also has access to the database. It works, but it's kind of messy and I'm somewhat worried about the performance of the HTTP client bundled with Postgres. This mechanism could also be implemented with the `LISTEN`/`NOTIFY` interface. It feels like the better way to do it is having an API gateway / a separate API accepting the same JWT requests as PostgREST and others. We'll try it for our next project and maybe we'll migrate this product too, if we run into any issues with HTP requests. It's been great for CRUD-heavy apps. Optimizing the RLSes sometimes gets tough, but you can always work around them with views / functions that avoid them.

dinkleberg 5y ago
Ah that’s interesting. I’ll have to actually give that a try at some point. I think the API gateway model makes more sense in my head.

agentultra 5y ago

There are a number of options. You can publish the WAL replication stream and write downstream services that react off of that. I wrote a library in Haskell [0] that can read such a stream. Libraries exist in Python and other languages as well. Or you can take advantage of the `LISTEN/NOTIFY` feature in Postgres and use an extension to push events out to various queues or webhooks [1]. [0] https://hackage.haskell.org/package/postgresql-replicant-0.1.0.1 https://hackage.haskell.org/package/postgresql-replicant-0.1... [1] https://postgrest.org/en/v9.0/ecosystem.html#external-notification https://postgrest.org/en/v9.0/ecosystem.html#external-notifi...

michelpp 5y ago

You insert a job into a work queue, and process it asynchronously.