Hacker News Reader

ruslan_talpaon PostgREST

Sure you can use it as two tier if that does the job but it does not mean you are forced to, you can very well have logic in the proxy layer and then have logic below the db layer (workers reacting to database changes). If i apply the same logic (two tier bad) that i can say every other way of building an api is two tier since you have the frontend and then the rest of the code in php/node... the db would not really count as a later since in those scenatios ppl use it as a dumb store

ruslan_talpaon PostgREST

You can’t just throw complexity at ppl from the first lines of documentation especially when talkin about new ideas

ruslan_talpaon PostgREST

Why do you think this is a two tier system?

Even here [1] i count 4 places to put logic into besides frontend

1. https://github.com/subzerocloud/postgrest-starter-kit/blob/m...

ruslan_talpaon PostgREST

Have you looked into postgrest's "select" parameter? Your comment about the "scope" of the query applies here too.

Also GraphQL is a protocol and you can't really say it will have poor performance, it all depends on the specific implementation of a particular schema. You can have a GraphQL schema where each query gets translated into a single sql query [1] jsut like with postgrest. One thing that is true is that the reference implementation for graphql servers (but only because it has to be general) pushes one in a direction where a single graphql request might generate a storm of sql queries but it's on developer to understand and write proper resolvers.

[1] https://github.com/subzerocloud/subzero-starter-kit

ruslan_talpaon PostgREST

you could use Auth0 [1] to authenticate your clients using all the identity providers (FB/GOOGLE...etc) and then just have the api clients use that JWT token when talking to postgrest

If however you do only want to authenticate the users yourself, the login function can be very simple [2]

1. http://postgrest.org/en/v6.0/auth.html#jwt-from-auth0

2. https://github.com/subzerocloud/postgrest-starter-kit/blob/m...

ruslan_talpaon PostgREST

actually .... :) Postgrest has the same "capabilities" as GraphQL so if one is not carefull and jsut (naively) exposes tables with millions of rows with just postgrest (and no propper indexes) it can get bad fast since the client could create queries that join and return a lot of data.

That's why one does not deploy postgrest alone but use it in a setting like this https://github.com/subzerocloud/postgrest-starter-kit

where you at the proxy level can say things like "return 400 if the client did not provide at least 2 filters for the table" or "allow filtering on column a and b because they have indexes but not on c"

But again, those are things to consider when you have datasets exceding hundreds of K or millions, you don't need to care about this when you have tables with 10s of Ks

ruslan_talpaon PostgREST

Let's start with the fact that most of the data centric apis have a 80/20 split on read write, so there is virtually not SP in 80% of your api, so no need to debug 80% of the code :)

so you "might" need stored procedures only for your write part and even then you need them when the input data needs to be split and sent to different tables.

The complex "rules" are nothing more then "constraints" on your data which are split between the columns of the table and become so simple that there is almost nothing to debug.

If it were the case that with postgrest one needs to write complicated stored procedures all over the place you'd be 100% correct. The thing is you don't need them in most cases and when you do they are short simple functions that deal with focused things so there is way less chance to get them wrong.

This has been my experience with using this type of stack for apps like project management/invoicing.... etc (basically basecamp+freshbooks)

ruslan_talpaon PostgREST

It's not doubling on the ORM part, it's not a orm replacement, it's a replacement for the entire api layer (that uses orm internally).

I agree with the other point, this is meant to be used when your client can not ahve direct access to the db (browsers, 3rd parties). If you can connect to the db and use SQL, do that.

ruslan_talpaon PostgREST

The row level security is a feature of the database (postgresql), those rules are written and enforced by the database, they have nothing to do with PostgREST and how it connects to the database

ruslan_talpaon PostgREST

when it comes to these tools, all your statements are wrong, give them 5 minutes and check our their capabilities (there is no coupling and joins in frontend) :)

ruslan_talpaon PostgREST

Thanks for this. People are always skeptical of this approach, not because the tried it and failed (or even thought 5 minutes about it) but because they read some blogpost somewhere.

Not to say though that this solves everything, there are cases where it does not work (as someone commented correctly and gave an example where they needed to use linear algebra over the data)

ruslan_talpaon PostgREST

It's true that a tool developed by 1-2 ppl recently is not a convenint as the tools developed by armies of developers over decades :) but, when it comes to PostgREST way of building apis, the debugging does not have the same meaning as in other ecosystems.

an api backed by postgres+postgrest is 80% tables and views declarations ... how do you debug a view ... it makes no sense. You just define it and say "select * from view" (even from your IDE) and see if you get what you expect, that's why one can do a lot (develop complex apis) with less (limited debug tools)

ruslan_talpaon PostgREST

That is all true but postgrest goes a step further then Dereks approach (where you need to basically write a lot of stored procedures) and gives the fronend access to safe subset of SQL, so that frontend is not strictly limited to the capabilites of the available procedures that live in the db (thus eliminating the need for most of them)

ruslan_talpaon PostgREST

between postgrest/postgraphile/hasura there are 30k stars, have all those ppl fallend for this "dumb" idea :)?

You say "expose db" and imply that one would directly give access to the core tables and in this way coupling the api to the data model (one should expose only views and stored procedures) and making the api inflexible.

Or are you saying this is insecure, and if so, can you describe how one would "break" this?

ruslan_talpaon PostgREST

reuse code where? In the db, why couldn't you? Or do you mean to reuse the logic in the db in some other place?

By this logic one can say "ruby is bad becasue i can't reuse logic i wrote for the backend in my frontend SPA". This is the type of (data logic) that you should not need to reuse in other parts.

Maybe this comment does a better job of explaing the architecture https://news.ycombinator.com/item?id=21436425

ruslan_talpaon PostgREST

Very well said (I'll have to remember this way of explaining it). IMO in most projects, the intermediate layers never do anything interesting so that's why postgrest (as a proxy) is a good fit

ruslan_talpaon PostgREST

I understand the problem you are describing, i would say that is a wrong type of view to create, if one plans to write filters on top of that view then it should be written so the filters can be inlined in the view (which would basically give you the finished query you would be sending from the layer above)

ruslan_talpaon PostgREST

https://github.com/subzerocloud/subzero-cli

your code is in files (so git ...) and you edit files adn save them and the new version is loaded to the database and you can make the new api call and see it in action

ruslan_talpaon PostgREST

I guess each person's oppinion is colored by personal past experiences, i too can come up with stories of how backend apis with 10s of thousands of lines got reduced to a handfull of views and stored procedures. But it's true this aproach does work when you have to do linear algebra on your data (my feeling is that this requirement does not comes up in so many projects).

On the debuging part it's a bit true, the workflow is not as polished (debuging in db) as oposed to other envs.

About the "fast" part, i would not say "comically wrong", if it were, there'd be no reason for sql beyond "select * from"

ruslan_talpaon PostgREST

you can write your stored procedures in JS of Python if you don't like plpgsql that much :)

ruslan_talpaon PostgREST

what do datatypes have to do with this. For both PostgREST and any other api layer the situation is the same. The data sits in the db/tabes (so both parties are restricted to the types available in pg) and on the other end you need to output json. The fact that you can have some "smart" internal datatypes in other api layers has 0 advanteges, both systems have to basically take data from the db and display it as json, the intermediate types are irelevant

ruslan_talpaon PostgREST

Becasue i know the codebase maybe :)? How does the tag line say "postgrest is a ORM" and how am i interpreting it in the exact oposite way?

ruslan_talpaon PostgREST

a view is nothing but a query, so if the view is "killing" the performace for you, running the same query from the client will not change anything, the porformance will get "killed" in the exact same way.

ruslan_talpaon PostgREST

postgrest is not (and does not use) a ORM

Postgrest is more like a compiler, it takes one language as input (REST) and outputs another language (SQL) as output.

It has 0 relation to the ORM concept

ruslan_talpaon PostgREST

You haven't really looked into postgrest, it does not force the database schema on to the client. You expose a schema with views that are tailored for the frontend and can be totally different from the underlying tables that hold the actual data

ruslan_talpaon PostgREST

Why would you prefer that? In all other intermediate layer you would write "imperative" stuff for how to transform the data and eforce it's correctnes. In the Db you "define" how it should look/behave/ and who has access.

As long as the "business" logic is directly related to the data in question, the code in the database will always be faster, shorter (by a lot) and easier to understand (because it's shorter by orders of magnitude).

I know your reaction comes form years of tutorials telling you not to do this, but this was way back when mysql didn't even have views, things are a lot different now, databases are not dumb stores.

Give it 5 minutes and try a small project, you'll be surprised by the power

ruslan_talpaon PostgREST

what's wrong with views (which should have been used formt he start)? What were the pain points?

ruslan_talpaon PostgREST

for the first one, read my other comments, this is solved. The second one, you can start here https://www.pgadmin.org/docs/pgadmin4/4.13/debugger.html