4 ms·

I feel like Postgres is so powerful I could damn near build an entire web app backend with JUST Postgres (i'm only sorta kidding here). If that's standard SQL,

by jtms 7y ago

I feel like Postgres is so powerful I could damn near build an entire web app backend with JUST Postgres (i'm only sorta kidding here). If that's standard SQL, well then I really like standard SQL :-)

paulddraper 7y ago
You think you're kidding. https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest https://wiki.postgresql.org/wiki/Python https://wiki.postgresql.org/wiki/Python https://github.com/plv8/plv8 https://github.com/plv8/plv8 https://www.graphile.org/ https://www.graphile.org/
- jtms 7y ago
  Graphile looks quite intriguing! any experience with it? Recommended?
  - Munksgaard 7y ago
    I'm using Graphile in deployment at the moment, and it works quite nicely. Granted, we have a limited amount of users thus far, so I don't really know how well it handles big loads, but it allowed us to get off the ground with a PoC _really_ quickly.
  - dafrie 7y ago
    I would also have a look at Hasura GraphQLEngine. Have used it now for various projects and is extremely nice, especially with the superb subscriptions support and eventing for the odd requirement that cant be handled within Postgres
- ar-jan 7y ago
  I'm keeping an eye on https://github.com/aquametalabs/aquameta https://github.com/aquametalabs/aquameta "Web development platform built entirely in PostgreSQL"
CuriouslyC 7y ago
I've done pretty much this a few times, and it's amazing if you're working in the context of enterprise data systems that need to provide extensive capabilities to a "small" userbase (i.e. concurrent in the thousands). You just need a small shim layer for security, to transform results sets, and handle browser -> database connectivity. Postgrest works very well as this shim layer, though I've moved on to writing sql directly in the client and communicating through a web socket shim. In terms of reducing code complexity and improving performance this is absolutely unbeatable, you just need to parse incoming sql to sanitize it and make sure there is no role escalation. Because of postgres's foreign data wrappers this method can provide a consistent surface for basically all your enterprise data. The only gotcha with FDWs is that some of them don't "push down" many query clauses, so you end up doing much slower queries on the remote system and filtering locally, which is terrible for obvious reasons. That being said, the FDWs are pretty much all open source, so you can just implement push down support for those missing clauses yourself.
- jtms 7y ago
  Thank you for posting this. I have been thinking about using something along these lines for awhile now. Next project that fits the bill I will see about building a proof of concept implementation and go from there. Have you had any exposure to any of the other projects similar to PostgREST that you have any thoughts about?
  - CuriouslyC 7y ago
    I've played with Graphile, it's not a bad choice if you are already invested in GraphQL. I'm not a GraphQL fan for a number of reasons, but it's a definitely a solid project.
- menssen 7y ago
  > you just need to parse incoming sql to sanitize it and make sure there is no role escalation "Just"? I guess I'm skeptical of a statement that begins "you just have to parse sql". Is this actually easier than I'm imagining it? I'd be curious to hear more about the security and authorization model of this approach.
deleted 7y ago
[deleted]

by jtms 7y ago

paulddraper 7y ago

You think you're kidding. https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest https://wiki.postgresql.org/wiki/Python https://wiki.postgresql.org/wiki/Python https://github.com/plv8/plv8 https://github.com/plv8/plv8 https://www.graphile.org/ https://www.graphile.org/

jtms 7y ago
Graphile looks quite intriguing! any experience with it? Recommended?
- Munksgaard 7y ago
  I'm using Graphile in deployment at the moment, and it works quite nicely. Granted, we have a limited amount of users thus far, so I don't really know how well it handles big loads, but it allowed us to get off the ground with a PoC _really_ quickly.
- dafrie 7y ago
  I would also have a look at Hasura GraphQLEngine. Have used it now for various projects and is extremely nice, especially with the superb subscriptions support and eventing for the odd requirement that cant be handled within Postgres
ar-jan 7y ago
I'm keeping an eye on https://github.com/aquametalabs/aquameta https://github.com/aquametalabs/aquameta "Web development platform built entirely in PostgreSQL"

CuriouslyC 7y ago

I've done pretty much this a few times, and it's amazing if you're working in the context of enterprise data systems that need to provide extensive capabilities to a "small" userbase (i.e. concurrent in the thousands). You just need a small shim layer for security, to transform results sets, and handle browser -> database connectivity. Postgrest works very well as this shim layer, though I've moved on to writing sql directly in the client and communicating through a web socket shim. In terms of reducing code complexity and improving performance this is absolutely unbeatable, you just need to parse incoming sql to sanitize it and make sure there is no role escalation. Because of postgres's foreign data wrappers this method can provide a consistent surface for basically all your enterprise data. The only gotcha with FDWs is that some of them don't "push down" many query clauses, so you end up doing much slower queries on the remote system and filtering locally, which is terrible for obvious reasons. That being said, the FDWs are pretty much all open source, so you can just implement push down support for those missing clauses yourself.

jtms 7y ago
Thank you for posting this. I have been thinking about using something along these lines for awhile now. Next project that fits the bill I will see about building a proof of concept implementation and go from there. Have you had any exposure to any of the other projects similar to PostgREST that you have any thoughts about?
- CuriouslyC 7y ago
  I've played with Graphile, it's not a bad choice if you are already invested in GraphQL. I'm not a GraphQL fan for a number of reasons, but it's a definitely a solid project.
menssen 7y ago
> you just need to parse incoming sql to sanitize it and make sure there is no role escalation "Just"? I guess I'm skeptical of a statement that begins "you just have to parse sql". Is this actually easier than I'm imagining it? I'd be curious to hear more about the security and authorization model of this approach.

deleted 7y ago

[deleted]