
PostGraphQL: PostgreSQL meets GraphQL - otoolep
https://www.compose.com/articles/postgraphql-postgresql-meets-graphql
======
jeswin
I tried applying GraphQL to standard webapp/services projects, and could not
get really far. Maybe I'm missing something, help is appreciated.

GraphQL lets consumers (Web UIs, Mobile Apps etc) define the shape of data
they want to receive from a backend service. Theoretically, this is awesome
since at the time of writing the backend service you don't always know which
fields and relationships a consumer might require. The problem arrives when
you add security. There are plenty of things that a client is not supposed to
see, often based on the roles of the requesting user. That means that you
can't pass a GraphQL query directly to an underlying DB provider without
verifying what is being requested. You'd end up writing about the same amount
of code that you'd have written with a standard REST-style (or other)
interface.

I also considered using it for Server to DB data fetches, where my backend Web
Service would request an underlying GraphQL aware driver for some data. I did
not find it particularly more expressive than using SQL or using an ORM.

One good thing about GraphQL is that it sort of formalises the schema and the
relationship between entities. You could perform validations on incoming query
parameters and mutations. It also helps clients understand the underlying
schema and would serve as excellent documentation. This might be useful, but a
REST API also offers some level of intuitiveness, and ORMs (especially in
strongly-typed languages) offer some level of parameter and query validation.

These are probably early days, but it'd be nice to see some real world use
cases for GraphQL which lie right in the middle of simple todo-list type of
apps, and the unique needs of FaceBook.

~~~
paulddraper
The point isn't to write less code -- especially server-side code.

The point is to provide a consistent, feature-rich, declarative, verifiable
and performant API that is easy for the client to develop against.

SQL is an excellent analog -- no, it doesn't make the SQL server
implementation easy, but it's a consistent, declarative, and powerful API.
"Even" business analysts can write SQL queries.

If you don't have a strong incentive to make life easy for the client, the
case for GraphQL isn't as strong.

~~~
jeswin
GraphQL works really well for some edge cases, and those edge cases will be
more evident if you're FaceBook. For smaller projects, the benefits of
integrating GraphQL libraries on the client and the server v/s special
handling for a limited set of APIs is not obvious.

By special handling, I meant:

    
    
        https://example.com/friends?fields=name,pic,posts&posts_len=10
    

The vast majority of APIs will not require this kind of result-shaping. And
for those that do, passing comma separated fields (using a protocol teams
already understand) is worth trying before adopting something more drastic. Of
course, YMMV.

~~~
chacham15
Often what gets lost in these kinds of discussions is the difference between
what is theoretical and what is practical. Yes, you can have a perfectly
designed REST API which has all of these features.

What happens, though, when you want a new piece of data? Do you redesign the
API to fit this new piece of information into it or do you make a new
endpoint? What if the new piece of data doesnt actually belong with `friends`
but if you do the fetch for friends and this new piece of data at the same
time you can get a performance boost? Do you make a new endpoint
`friendsandfamily`?

Now, what if you want to migrate all calls over to that endpoint, you now need
to figure out if any of your frontend code or legacy systems are still using
that old endpoint before you remove it.

The maintenance of that system is much higher because you've conflated what
data you want with how you want to get it.

~~~
riffraff
how is this solved by GraphQL though?

You still need to add a new edge `friendsandfamily` (same as adding to a new
endpoint) or add a new a field to the existing object (same as adding a new
field to the existing endpoint).

What am I missing?

~~~
paulddraper
(1) GraphQL returns only what the client asks for. No need to worry that
adding to an existing resource will increase response times or bloat the
response for existing queries.

(2) If you decide to go the friendsandfamily route, you'll just be adding
`family`. You won't have `friends` endpoint and `family` endpoint and
`friendsandfamily` endpoint. A GraphQL schema doesn't increase combinatorially
in size; rather the possible compositions increase combinatorially.

~~~
riffraff
for 2) isn't this the same as adding an optional "include=family" to the
existing friends endpoint?

This is what the JSONAPI spec suggest too.

~~~
paulddraper
Yep. You can use query parameters to fix 1) and 2).

Go down the rabbit hole a little bit further, and you have GraphQL (if you're
lucky....if not, you'll just have a mess).

------
mileycyrusXOXO
Been lurking HN for years, created an account just to say this. I got into
GraphQL shortly after the reference implementation through a bit after Relay
was released. I was so excited for the technology but unfortunately there was
not much out there in terms of middleware, tutorials, support or adaptation. I
built a few toy apps with it, but work applications were designed as usual and
life went on. It is exciting to see GraphQL picking up speed and I cannot
express how happy I am that this exists.

~~~
smnplk
When you finally decide to pick a username, you pick that monstrosity xD

------
tommikaikkonen
Related: [https://edgedb.com/](https://edgedb.com/)

I can't remember where I stumbled upon that, but it implements an object
database over PostgreSQL and offers a GraphQL superset for querying. Don't
know if it's still in active development, but it looks interesting.

~~~
1st1
Development is very active, will release a public beta in a couple of months.

------
fabian2k
A quick search of the Github issues seems to indicate that PostGraphQL does
suffer from the N+1 query problem at least to some extent
([https://github.com/calebmer/postgraphql/issues/219](https://github.com/calebmer/postgraphql/issues/219)).

Which probably doesn't matter as long as you only have a few nested children,
but might seriously kill performance for some cases. I always find it
disappointing when that much of the power of a database like Postgres is
thrown away by an abstraction. I find the general approach of PostGraphQL (and
Postgrest, which is a similar project for a Postgres REST API) very
interesting, it would be nice if it also generated reasonable queries for this
kind of data.

~~~
sorenbs
The issue mentioned in that post - the fact that this query generates 6 sql
queries instead of 2

{allPosts(first:5){title}}

is a limitation of the implementation and not inherent to the architecture. We
spent a lot of time optimising these cases for graph.cool and the result is
that we actually have a more scalable system by issuing a few simple sql
queries instead of one giant join. It does open the door for the result to be
internally inconsistent, but that is no different from querying a bunch of
cached rest endpoints.

~~~
ruslan_talpa
You are doing the job of the database query planner. A good join (using
indexes) will always beat your 3 simple sequential queries. This mindset i
think is due to mysql 4.x which sucked at joins.

------
jjn2009
Is there any way to implement access control logic outside of row security
policies in Postgres? Or have any sort of logic in between requests based on
what type of request is coming in?

The automagical nature of this software seems great but any relatively complex
application would require not only CRUD manipulation but also side effects to
go along with it as well.

With express I suppose you could have a middleware fire off before hand to
parse the incoming query, figure out what it is, and take any extra action as
necessary such as denying a request or making some side effect of the query
happen. This would be a by default open policy however for those queries which
you have a postgres scheme for but lack the express javascript to parse the
incoming request.

~~~
ruslan_talpa
You use a combination to the roles system + RLS + views and you have all the
access control you need.

For side effects (like send an email) you have a lot of options (proxy,
database, external script that reacts to a event generated by the db) ... you
just have to get out the mindset that everything has to be done in one
place/framework/language and you'll end up writing a lot less code.

------
wolfgang42
What I'd like to know is how (or if!) it does query building. I recently had a
project where GraphQL looked like a perfect fit, since I needed an arbitrary
amount of possibly nested data about some entities. The problem I had was that
the GraphQL library basically requires you to do a SELECT *, and then picks
the fields the client wants from that. Unfortunately, the DB schema was
nowhere near 1:1 with the GraphQL schema I wanted, and getting certain fields
required some very expensive JOINs. There's a stagnant PR to add a query
planner, but at the moment it offers no way to assemble your query to match
the client's request: the library says 'get the data' with no details about
precisely which data must be gotten.

~~~
ruslan_talpa
It's true that graphql-js pushes you in a direction where the code that
fetches the data from the db is inefficient, but that is not a GraphQL
problem, that is an implementation problem. To be efficient, you need to
implement your own logic (in the resolvers) that analyses the entire graphql
AST, generates an optimal query, and sidesteps the default execution engine in
graphql-js.

------
rkv
This is awesome!

Lot's of confusion here on what GraphQL is actually trying to accomplish. In
no way were the authors of GraphQL attempting to replace or enhance SQL with
the GraphQL language; they are two completely different entities. GraphQL sets
out to solve annoyances that people run into while building and maintaining
large RESTful services.

------
waingake
I'm nearing completion of a real world commercial application of some
complexity that uses this along with React Relay. I knew SQL but not
PostgresSQL coming into the project.

I can say that this has been a joy to work with. This is how I will be
building apps for the a while now.

Huge thanks to Caleb Meredith for all the work he has done on
[https://github.com/calebmer/postgraphql](https://github.com/calebmer/postgraphql)

~~~
sayurichick
hi, did you use express?koa? or the httpserver for it?

I am using koa and was wondering if you ran into any gotchas that might help
me.

also, what % of your code is server-side, versus clientside react?

------
dustinmoorenet
I spent 4 days trying to implement this in a side project. My biggest
complaint was that it was too automatic. It created resources that I didn’t
want.

As an example, I had a table (A) that was linked to a user table (B) through a
many-to-many table (C). I needed the shape of table A available as a type so
that I could get all associated rows of A for users (B), but not let anyone
see all of table A. I had no way of telling PostGraphQL to hide table A but
allow the shape of A to be public.

I hope they expand it's configurability more in the future.

~~~
ruslan_talpa
Another way to say this is: PostGraphQL exposes as an api whatever it sees in
the specific database schema. It's your job to show it only the stuff it's
supposed to expose and there is a lot of flexibility in the database available
to help you do just that (role system, views, stored procedures, RLS,
constraints).

------
jakewins
This is super cool - but as others have said, you can't expose this to a
public URL, it needs a security model. The security model in PG is,
unfortunately, severely outdated.

Now, if you combined this with a good RBAC security model, particularly if you
baked that model into the GraphQL -> SQL conversion layer, so it sends SQL
queries that work on an allowed subset.. that'd be very cool.

The security mechanisms in Firebase might serve as an inspiration :)

~~~
anarazel
> The security model in PG is, unfortunately, severely outdated.

What would you like to see? (PG dev here)

~~~
jakewins
I should have said "the security model in most databases is outdated" \- the
bitterness comes from my own failure to argue for an alternative when we built
the security model in Neo4j.

Just like PG, we based security in Neo on role-based access. Users have roles,
those roles then have permissions to do lots of cool things.

What we should have done, had we had infinite time (and to be clear: it was
the correct choice to build a role-based system given the constraints, I just
wish there'd be more time in a day..), was to build a resource-based system;
instead of roles, you have graphs of direct and delegated privileges over
specific resources; resource-based access control.

If I build an app today, I don't want to say "Users can edit x,y,z columns on
the comments table". I want to say "Users can edit x,y,z columns on the
comments table, if they were the ones that wrote them and the comment does not
have a lock flag".

The bulk of web app code today, for small to mid-sized apps anyway, is about
dealing with this resource control. Which is ridiculous - imagine how many CPU
cycles are spent on secondary queries to look up ownership hierarchies from
dumb ORMs. There's no good reason that couldn't be modeled at the level of, or
below, the query planner, having it simply design the query plans to abide by
the per-user access constraints.

The cost of that would be massively higher than what the planner does today -
but looking at the performance of the whole system, it'd be amazing.

This is, kind of, what Firebase does, right - it rolls resource-based access
control into its query planner, combined with good federated user management.

~~~
floatboth
> Users can edit x,y,z columns on the comments table, if they were the ones
> that wrote them and the comment does not have a lock flag

GRANT UPDATE (subject, text) ON comments TO user;

CREATE POLICY comments_update_own_not_locked ON comments FOR UPDATE TO user

USING (locked IS NOT TRUE AND created_by =
current_setting('postgrest.claims.username'))

WITH CHECK (created_by = current_setting('postgrest.claims.username'));

Row-level security in Postgres is awesome!

~~~
jakewins
Holy crap, I hadn't realized just how powerful RLS ended up - last time I
looked at this was before 9.5 and it was roles all the way down.. That is
_fantastic_!

That's exactly what I wanted.

I guess it's true as they say, that the easiest way to get the right answer on
the internet is to publish the wrong answer :)

~~~
ruslan_talpa
The features provided by RLS are not really new, they just add a nice syntax
to define the rules. All the things you mentioned can be implemented using
roles and views.

------
WhitneyLand
GraphQL seems like a nice idea.

Are there any negatives that might not be obvious from just reading the docs?

~~~
tomdale
I tweetstormed about this once.
[https://mobile.twitter.com/tomdale/status/786950181522636801](https://mobile.twitter.com/tomdale/status/786950181522636801)

~~~
ec109685
What does something like this mean: "GraphQL will replace REST in the same way
MongoDB replaced PostgreSQL"

~~~
boubiyeah
Initially, MongoDB was marketed as a simpler, better, drop-in replacement for
SQL databases (PostgreSQL didn't support schema-less JSON at the time) It was
just crappy, dishonest marketting like the mongoDB team often produce.

GraphQL does the same when it compares itself to REST. It has problems of its
own and it's a false dichotomy. It's just marketting.

------
krosaen
I'm curious to hear how folks how adopt GraphQL handle server side schema
changes over time. I'm all for the idea of not breaking clients almost ever
[1] but sometimes you need to coordinate between client and server logic; how
do you do that if the dependency of any client can literally be your entire db
schema? Perhaps there is a subset of the server side schema that is designed
to be GraphQL-able and is kept very stable over time?

[1]
[https://www.youtube.com/watch?v=oyLBGkS5ICk](https://www.youtube.com/watch?v=oyLBGkS5ICk)

------
rojobuffalo
I'm working on a non-trivial app with this in the stack (React, Relay,
PostGraphQL, PostgreSQL). I'm a big fan. The author, @calebmer, is a great guy
to work with also.

~~~
unixhero
You may still keep the company alive if copying the code is not instantly
making it possible to replicate your business.

Ref Non-Imitable competitive advantages

------
lolive
Coming from the semweb community, and being the maintainer of a visual query
builder for its SPARQL query language (cf
[http://datao.net](http://datao.net)), I would like to mention the fact that
graph queries can easily be displayed (and authored) fully graphically. This
is a huge benefit to bridge the gap between IT and domain experts in
companies.

------
boubiyeah
For many of us here, GraphQL is a premature optimization that is quite
intrusive in the stack. Think hard whether you really need it.

~~~
kazagistar
If you have had negative experiences with it, it would be really interesting
to hear your perspective. We have all seen the ugly parts of REST and such,
and GraphQL looks like it could solve a lot of problems. If that is real, or
just not enough experience to undertake it's unique problems is the real
question.

~~~
patates
It's very hard to validate if you're doing validation on the application
layer. If you moved your validation to the database and your users are
actually db users, a joy to work with.

~~~
bpicolo
If your users are db users it's going to be hard to spread out in e.g. SOA,
and also take advantage of multiple forms of DB

------
zeronone
I am wondering if there are any implementations of translating Swagger API
configurations (or similarly RAML, JSON Schema) to GraphQL. For example, it
could be integrated with the API Gateway in order to translate GraphQL queries
to ordinary Rest API calls on the fly.

------
infogulch
The pitch on the project page for Schema Driven APIs[0] mentions "hide
implementation details with views of your data" (as in CREATE VIEW) and
"automatic relations with the REFERENCES constraint". Do both of these work
together? E.g. could I create an "api" schema that consists entirely of views
that query other schema(s), but still have the relations automatically
determined?

[https://github.com/calebmer/postgraphql#schema-driven-
apis](https://github.com/calebmer/postgraphql#schema-driven-apis)

~~~
ruslan_talpa
Yes you can. That is EXACTLY how you are supposed to do it (PostgREST and
PostGraphQL). Do not expose your tables, expose a schema containing only views
and functions.

There are small bugs related to relation detection between views but 95% of
the times it works and everything can be fixed and also there are workarounds
for those bugs.

------
k__
Finally the back-end devs have some hard feelings about frameworka too, lol.

I like GraphQL, but I'm a front-end dev. Apollo-Client is pure gold.
optimistic UI, streamed results, batching...

------
arasx
If there is an application level function that alters the data that is stored
before presenting to the client on the standard REST model, how is this case
handled? Is it handled on the client side as well?

------
chime
This would be a fantastic fit for open/public/research databases.

