4 ms·

You could get a more complete solution with PostgreSQL + PostgREST. Regarding the concerns: > Security: we don’t want people messing up our data Row level sec

You could get a more complete solution with PostgreSQL + PostgREST. Regarding the concerns: > Security: we don’t want people messing up our data Row level security + roles/grants should be enough to cover this one. > On performance: SQLite has a mechanism for canceling queries that take longer than a certain threshold. With a timeout, what you'll need is to request the expensive queries in parallel to ddos the service. Would be better to filter by the query plan, in PostgreSQL there's an extension for this https://github.com/pgexperts/pg_plan_filter https://github.com/pgexperts/pg_plan_filter. Though pg also has a `statement_timeout`. > Hiding implementation details You can expose a dedicated schema for you API that doesn't contain a single table but only views and stored functions. Migrating this schema should be easy thanks to transactional DDL. That being said, I agree with the sentiment about GraphQL, besides the extra query language, there's an additional impedance mismatch since now you have to map your DB schema to the GraphQL schema.

copperx 8y ago
I'm always surprised that PostgREST isn't the industry standard for creating an API. It looks like it would save so much work and the corner cases can be handled without writing code in a different language. What am I missing?
- tempotemporary 8y ago
  PostgraphQL
- mmt 8y ago
  If I had to guess, this is due to a combination of only being a few years old and marketing (initially, the major version number was 0).
- ufmace 8y ago
  I tried it, and still have it running on a little toy service I built. It's cool, but IMO, it doesn't really seem production-ready for a large-scale service. Last time I updated it, I had to spend a full day sorting out the changes they made to how the server config and user authentication works. Last I checked, they don't even have a suggested script to run it as a service. Going by the contributor graph, it's still essentially a 1-person project.
  - mmt 8y ago
    IIRC, this was what the connection poolers (pgpool, pgbouncer), and perhaps all now-popular postgres extensions/enhancements, looked like in their initial years, as well. In the context of a "large-scale service", my guess is that there's a chicken-and-egg problem, where an operators of such a service has to decide they want it to be production-ready enough to throw money and/or engineering resources at the project, which can attract yet more contributors.
    - profalseidol 8y ago
      > a chicken-and-egg problem The way I see it. Large corporations have the golden egg laying hen. And plebian project like PostgREST have little to no chance.
eksemplar 8y ago
I don’t think mapping your DB is necessarily a disadvantage. It provides a lot of benefits in modern web- and mobile-development which is where you’d use something like GraphQL. JavaScript works better when it knows the DB schemes, not only is it more efficient for your developers but it’s also higher quality because it allows fewer fuckups. If you’re building your DB schemes anyway, you may as well take advantage of GraphQLs other features. And it’s in a familiar language, any web developer can do a GraphQL query, and it’ll be efficient too. I think most developers can do a SQL queries too, but not always efficient. You can get a long way by going to W3 or google and hacking your way through a query to get what you want. Our business analytics and our LEAN process people do it, but I’ve seen some real horrors build that way by people who knew SQL but not databases. Build what you want, but there is a reason GraphQL is popular.
tpetry 8y ago
But how do you solve a typical multi-tenant SaaS with users and teams? You can‘t create roles for every login user because (s)he may have special access control rights for every project (s)he is participating in.
- CuriouslyC 8y ago
  Can you give a good example? I've found that the combination of row level security and role inheritance in Postgres can handle some fairly complex cases.

copperx 8y ago

I'm always surprised that PostgREST isn't the industry standard for creating an API. It looks like it would save so much work and the corner cases can be handled without writing code in a different language. What am I missing?

tempotemporary 8y ago
PostgraphQL
mmt 8y ago
If I had to guess, this is due to a combination of only being a few years old and marketing (initially, the major version number was 0).
ufmace 8y ago
I tried it, and still have it running on a little toy service I built. It's cool, but IMO, it doesn't really seem production-ready for a large-scale service. Last time I updated it, I had to spend a full day sorting out the changes they made to how the server config and user authentication works. Last I checked, they don't even have a suggested script to run it as a service. Going by the contributor graph, it's still essentially a 1-person project.
- mmt 8y ago
  IIRC, this was what the connection poolers (pgpool, pgbouncer), and perhaps all now-popular postgres extensions/enhancements, looked like in their initial years, as well. In the context of a "large-scale service", my guess is that there's a chicken-and-egg problem, where an operators of such a service has to decide they want it to be production-ready enough to throw money and/or engineering resources at the project, which can attract yet more contributors.
  - profalseidol 8y ago
    > a chicken-and-egg problem The way I see it. Large corporations have the golden egg laying hen. And plebian project like PostgREST have little to no chance.

eksemplar 8y ago

I don’t think mapping your DB is necessarily a disadvantage. It provides a lot of benefits in modern web- and mobile-development which is where you’d use something like GraphQL. JavaScript works better when it knows the DB schemes, not only is it more efficient for your developers but it’s also higher quality because it allows fewer fuckups. If you’re building your DB schemes anyway, you may as well take advantage of GraphQLs other features. And it’s in a familiar language, any web developer can do a GraphQL query, and it’ll be efficient too. I think most developers can do a SQL queries too, but not always efficient. You can get a long way by going to W3 or google and hacking your way through a query to get what you want. Our business analytics and our LEAN process people do it, but I’ve seen some real horrors build that way by people who knew SQL but not databases. Build what you want, but there is a reason GraphQL is popular.

tpetry 8y ago

But how do you solve a typical multi-tenant SaaS with users and teams? You can‘t create roles for every login user because (s)he may have special access control rights for every project (s)he is participating in.

CuriouslyC 8y ago
Can you give a good example? I've found that the combination of row level security and role inheritance in Postgres can handle some fairly complex cases.