3 ms·

> Use Postgres to generate JSON in the database, write no server side code and directly give it to the API. Who does this? "give it to the API"... ? Still so

> Use Postgres to generate JSON in the database, write no server side code and directly give it to the API. Who does this? "give it to the API"... ? Still sounds like there's "server side code" there if there's an "API" involved. Surely they're not meaning let front-end code hit the database directly?

scrollaway 4y ago
It's possible to have Postgres serve HTTP, actually. I've seen many experiments like this. I don't remember the name, but... actually... Yeah, thanks ChatGPT - It's called PostgREST: https://postgrest.org/en/stable/ https://postgrest.org/en/stable/ I've obviously not used it and to be honest, I probably wouldn't. But IMO Postgres is a radically different type of framework. People just think of it as a SQL database, it can be much more though. Tooling is just a bit lacking. Edit: Heh, Retool is sponsoring them. "PostgREST for the backend, Retool for the frontend". Cool idea. I might reconsider...
- fnimick 4y ago
  You can go even farther and generate an entire GraphQL api with row level security, custom functions, etc. I've had a great experience developing with Postgraphile - https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
  - xrayarx 4y ago
    Looks and sounds great, but nodejs
- dragonwriter 4y ago
  PostgREST is an external web server; postgres isn’t serving HTTP, though its effectively doing everything else that the backend requires besides serving HTTP when you use PostgREST.
  - scrollaway 4y ago
    Yes, postgrest isn't the one I was actually remembering that DID serve HTTP itself. I know there was an extension that did this though. It doesn't matter anyway. Serving HTTP is not a problem particularly well solved by Postgres, and needing an extension instead of whatever hyper-optimized http server you usually use is increasing complexity, not decreasing it.
- qup 4y ago
  > thanks ChatGPT I find the thing quicker than asking google, pretty often. I really want a CLI interface to have an "ask the oracle" kind of feature.
  - scrollaway 4y ago
    In this particular case, it would actually have been slower. I can't phrase things on Google like I do on the chat; I first have to distill my query down. And when I actually am trying to remember something, it's easier for me to be descriptive, because the keywords Google needs can be the important ones I forgot. PostgREST is page two of "postgres http server" for me.
CharlesW 4y ago
> Surely they're not meaning let front-end code hit the database directly? In this case they mean "let Postgres generate the JSON for API responses" instead of having the API interface do it. The "Generating JSON in Postgres" section in the linked article shows how this is done.
- mgkimsal 4y ago
  "let Postgres generate the JSON for API responses" would have been a far less confusing way of expressing that idea. I had read the linked stuff and none of it looked like front-end code hitting a PostgreSQL db directly. yes, if you can create some JSON without having to transform in an intermediate code layer, that's handy/useful. I often don't run in to too many scenarios where things are trivial enough to allow for that - there's usually some app-level logic that comes in to play with respect to visibility/permissions/etc.
ako 4y ago
I do, works really well if you need json with nested objects, you can get an entire tree of objects with a single call and query to the database.
ako 4y ago
Oracle database can do all of these things as well, and they even have a complete low code development and hosting environment running in the database, Oracle Apex. I know there are a couple of companies that actually serve their home page straight from their oracle database.
- dathinab 4y ago
  but it's also oracle db i.e. expensive, especially expensive support and tones of hidden annoying complexity not publicly well documented. For most companies doing the same in Postgres is just cheaper even if there is a bit less low-code provided out of the box.
  - ako 4y ago
    Sure, my go to database is also Postgres rather than oracle. Good enough for most things, without the downsides of the company oracle. Just wanted to highlight that Postgres isn’t unique here, and that serving http from a database is actually not that special.
- xrayarx 4y ago
  Sad to say, but the number of companies leaving oracle is increasing in my local bubbles
dathinab 4y ago
> Surely they're not meaning let front-end code hit the database directly? Why not? You can make Postgres speak SQL or GraphQl it removes an additional network indirection and there are very clear ways about how Posgres does scale well and where it doesn't scale well. (EDIT: Or put a dump translation layer/service in-between they exist to and are cheap to run and manage.) You can write your core logic in JS, Rust etc. and plug it into your database as virtual tables or shared procedures (it's surprisingly simple). Any DDOS protection and similar is anyway in front of whatever you write as some form of proxyish thing. And just needing to scale Postgres instead of a bunch of different systems is so much more simple. Depending on how you run it and what input characteristics you have it might even be more efficient and cheaper to scale that way (or it can be noticeable more expensive) and it's most times cheaper to manage.

scrollaway 4y ago

It's possible to have Postgres serve HTTP, actually. I've seen many experiments like this. I don't remember the name, but... actually... Yeah, thanks ChatGPT - It's called PostgREST: https://postgrest.org/en/stable/ https://postgrest.org/en/stable/ I've obviously not used it and to be honest, I probably wouldn't. But IMO Postgres is a radically different type of framework. People just think of it as a SQL database, it can be much more though. Tooling is just a bit lacking. Edit: Heh, Retool is sponsoring them. "PostgREST for the backend, Retool for the frontend". Cool idea. I might reconsider...

fnimick 4y ago
You can go even farther and generate an entire GraphQL api with row level security, custom functions, etc. I've had a great experience developing with Postgraphile - https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/
- xrayarx 4y ago
  Looks and sounds great, but nodejs
dragonwriter 4y ago
PostgREST is an external web server; postgres isn’t serving HTTP, though its effectively doing everything else that the backend requires besides serving HTTP when you use PostgREST.
- scrollaway 4y ago
  Yes, postgrest isn't the one I was actually remembering that DID serve HTTP itself. I know there was an extension that did this though. It doesn't matter anyway. Serving HTTP is not a problem particularly well solved by Postgres, and needing an extension instead of whatever hyper-optimized http server you usually use is increasing complexity, not decreasing it.
qup 4y ago
> thanks ChatGPT I find the thing quicker than asking google, pretty often. I really want a CLI interface to have an "ask the oracle" kind of feature.
- scrollaway 4y ago
  In this particular case, it would actually have been slower. I can't phrase things on Google like I do on the chat; I first have to distill my query down. And when I actually am trying to remember something, it's easier for me to be descriptive, because the keywords Google needs can be the important ones I forgot. PostgREST is page two of "postgres http server" for me.

CharlesW 4y ago

> Surely they're not meaning let front-end code hit the database directly? In this case they mean "let Postgres generate the JSON for API responses" instead of having the API interface do it. The "Generating JSON in Postgres" section in the linked article shows how this is done.

mgkimsal 4y ago
"let Postgres generate the JSON for API responses" would have been a far less confusing way of expressing that idea. I had read the linked stuff and none of it looked like front-end code hitting a PostgreSQL db directly. yes, if you can create some JSON without having to transform in an intermediate code layer, that's handy/useful. I often don't run in to too many scenarios where things are trivial enough to allow for that - there's usually some app-level logic that comes in to play with respect to visibility/permissions/etc.

ako 4y ago

I do, works really well if you need json with nested objects, you can get an entire tree of objects with a single call and query to the database.

ako 4y ago

Oracle database can do all of these things as well, and they even have a complete low code development and hosting environment running in the database, Oracle Apex. I know there are a couple of companies that actually serve their home page straight from their oracle database.

dathinab 4y ago
but it's also oracle db i.e. expensive, especially expensive support and tones of hidden annoying complexity not publicly well documented. For most companies doing the same in Postgres is just cheaper even if there is a bit less low-code provided out of the box.
- ako 4y ago
  Sure, my go to database is also Postgres rather than oracle. Good enough for most things, without the downsides of the company oracle. Just wanted to highlight that Postgres isn’t unique here, and that serving http from a database is actually not that special.
xrayarx 4y ago
Sad to say, but the number of companies leaving oracle is increasing in my local bubbles

dathinab 4y ago

> Surely they're not meaning let front-end code hit the database directly? Why not? You can make Postgres speak SQL or GraphQl it removes an additional network indirection and there are very clear ways about how Posgres does scale well and where it doesn't scale well. (EDIT: Or put a dump translation layer/service in-between they exist to and are cheap to run and manage.) You can write your core logic in JS, Rust etc. and plug it into your database as virtual tables or shared procedures (it's surprisingly simple). Any DDOS protection and similar is anyway in front of whatever you write as some form of proxyish thing. And just needing to scale Postgres instead of a bunch of different systems is so much more simple. Depending on how you run it and what input characteristics you have it might even be more efficient and cheaper to scale that way (or it can be noticeable more expensive) and it's most times cheaper to manage.