PostGraphQL: PostgreSQL meets GraphQL
compose.com
compose.com
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.
Why would you need to write the same amount of code? The only code that would remain the same is the validation logic.
You could answer the data out of a flatfile for all that the endpoint cares.
It also allows to secure access against arbitrary data requests, since on the server end, the resources only see the individual requests to the DB.
In my experience, it's much easier and less verbose to validate GraphQL after parsing than just validating any SQL you plan to pass to the backend.
There is an issue here where as you give more sophisticated querying capabilities to the client, you need to have correspondingly more sophisticated validation of those queries on the server, which offsets some of the gain.
With every fad that has hit backend development, ORM/move-everything-to-frontend/merge frontend and backend a la Meteor/GraphQL/etc, there have been promises of simplicity and less code.
The problem, as you discovered, is that the overwhelming majority of non-CRUD code on the backend is directly related to security and cannot be abstracted away or moved anywhere else. You end up dancing the same dance, no matter what paradigm you use and some of them make that dance a bit more awkward.
Personally, I've settled on Swagger a few years back. There's no fancy paradigm, it's just plain REST with an open sec. Because you define your API ahead of time, you can automate CRUD, automate validation, testing, and documentation. All that's left is the nitty-gritty which you have to do no matter what and it doesn't have an opinion on how that should be done, that part is up to you.
The real upside of GraphQL is that it has become popular enough that there are parsers in most languages, and that you don't waste time designing and testing a new standard. If you use it for a public API, you can also simply point people to the existing documentation.
It's definitely not a widely accepted standard, but still better than rolling your own!
You probably don't want to do that for security reasons. So either way you need a parser, and at that point, it makes sense to use something that is database agnostic and allows you to model relationships without complex joins or otherwise exposing the internal representation of the data.
http://githubengineering.com/the-github-graphql-api/
It's not really GraphQL vs SQL, it's GraphQL vs RESTful APIs.
In real life there will usually be a lot more going on to serve a GraphQL query than simply translating it to SQL.
The data you'll send might come from many different sources, including any kind of database, caches, or just constants in your code.
As commented here, permissions over data is a big topic, and if you were accepting raw SQL, you'd have to parse / tokenize it, and somehow validate it before executing it. This is what GraphQL does, but instead of trimming down and restricting the usage of SQL, it defines a language that is similar, close to being a subset of it, with a much simpler syntax.
Doing one round-trip instead of several, no matter how deep and complicated your data tree is, may be the interesting part.
GraphQL is one way to do just that.
REST has the virtues of uniformity and discoverability. I don't yet understand whether GraphQL possesses any of these too, though.
Check out Github's GraphQL explorer for a great example of this: https://developer.github.com/early-access/graphql/explorer/
"REST" as consistently and independent of use case a trivial CRUD layer over base tables is...well, almost as from actual REST as the JSON-based RPC over HTTP version of "REST".
That's not to discount GraphQL, because I think it clearly has it's use cases, especially at Facebook size.
Are we talking about something like:
CREATE TABLE person (id INT, name TEXT, birthdate DATE);
CREATE TABLE car (id INT, brand TEXT, model TEXT, licence TEXT);
CREATE TABLE p_c (pid INT, cid INT, FOREIGN KEY pid REFERENCES person (id), FOREIGN KEY cid REFERENCES car (id));
And for a given person, we want to return their details plus the cars they own?
gopher://example.com/person/{id}
# Get person ID
1, Joe, 1970-01-01
gopher://example.com/car/{id}
# Get car ID
1, Ford, T, 1313
gopher://example.com/person/{id}/cars
# Get all cars owned by person ID
1, Joe, 1970-01-01, 1 Ford, T, 1313
,,, 2 Ford, S, 1717
gopher://example.com/person/{id}/cars/{id}
# Get specific car
gopher://example.com/car/{id}/owners
# Get owner(s) of car ID
gopher://example.com/car/{id}/owners/{id}
# Get specific owner
gopher://example.com/car/{id}/owners/{id}/birthdate
# Get specific owner's birthdate
gopher://example.com/car/{id}/owners/birthdate
# Get birthdate of all owners (assuming no domain overlap between IDs and field names, other solutions possible otherwise)
If this is the sort of thing we're talking about, I've got that t-shirt and assumed everyone had too, so I guess I'm missing the point here. By how much?
Is it possible to get all that information in one round trip ? I believe that is one of the benefits of graphQL.
If we're talking about the same thing, yes.
One pattern that I use in my APIs is as follows:
Assume a JSON object like:
{ name: "Joe", colour: [ {red: 0, green: 128, blue: 90}, {red: 35, green: 88, blue: 199} ], hair: { length: { value: 9, uom: "cm"} }
I have on occasion provided an API like:
# Assume that {id} returns an object like the above.
GET /api/{id} # Returns the full object
POST /api/{id} # Replaces the object
PUT /api/{id} # Overwrites properties in the object
GET /api/{id}/name # Returns "Joe"
POST /api/{id}/name # Overwrites the name
PUT /api/{id}/name # Overwrites the name (also)
DELETE /api/{id}/name # Removes the "name" property
GET /api/{id}/colour/0/green # Returns "green". Other methods as above.
* /api/{id}/hair/length/value
PUT /api/{id}/hair/colour # Creates a new property
GET /api/{id}/colour/0;2 # Returns the first and third items in the array
GET /api/{id}/colour/1/red;green # Returns those two properties from the object. Note that this imposes some restrictions on property naming (no semicolons)
And this I never implemented, but I would if I had a need:
GET /api/{id}/colour/(0/red;green);(1/blue) # Returns [ { red: 0, green: 128}, {blue: 199} ]
I'm not sure what the correct type actually is, though. For JSON the closest RFC1436 type is probably 0, though I would be inclined to consider using (non-standard) type j instead.
</pedantic>
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.
By special handling, I meant:
https://example.com/friends?fields=name,pic,posts&posts_len=10
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.
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?
(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.
This is what the JSONAPI spec suggest too.
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).
By providing a clear and powerfull interface you can significantly reduce inter-team communication leading to both higher throughput and faster iteration cycles.
From a technical perspective reducing round trips is important, especially on mobile devices and GraphQL makes this trivially easy.
These two points I suspect is the primary reason many medium+ sized development teams end up implementing their own bastardised version of GraphQL in their existing rest api. Many people we talk to at http://graph.cool are interested in deploying a thin GraphQL wrapper on top of their existing api for exactly this reason.
REST is more like a NoSQL database where you write all the joins by hand: on the plus side, you get to control the query execution, on the negative side, you have to control the query execution (also you have round-trips and lack some possible internal optimizations).
The "performance" of GraphQL refers to the fact that most things can be queried in one round-trip, rather than many for RPC, REST, or vanilla HTTP. Each of these are abstractions, not implementations.
So the maximum performance of GraphQL is better than the maximum performance of REST, particularly over mobile networks...as you said though, the devil is in the implementation.
One benefit of committing to PostgreSQL as a backend: It already supplies a rich set of features for fine-grained security, and with views it's easily possible to add the filtering features.
This can reduce the need for much of the app middleware that does this traditionally. This is the thinking behind PostgREST, for example.
In the system that I most recently worked on we had to implement our own access control logic as table level was too coarse. It was a datacenter management system which supported multiple users, each with a set of roles, and each user belonging to several companies. The data center inventory that would be returned by queries depended both upon your role (sysadm vs customer admin vs regular Joe) and upon your company affiliation. I guess I'm mostly writing this to illustrate how quickly this kind of thing becomes closely intertangled with the domain and with your business logic.
Will try to answer your questions to the best of my ability:
1) The amount of users is not a scalability issue but the number of requests may be, of course. I'll assume that's what you mean. My best advice would be to profile your system to see how it behaves at great load. If access control turns out to be a problem, maybe you are doing too complex joins. One path forward may be to "denormalize" your database scheme, i.e. accept that the same information is stored in several places for the sake of efficiency. Another idea is to spin up an Elastic Search box to help with the query that is your #1 bottleneck.
2) As for the question about individual database connections, well, I think I'd pool them or I would build upon some framework or app server which pools them. It's an easy win with standardized solutions.
Caveat: Bear in mind that although I seem to be posing as some kind of expert right now, I'm really not. I'm more of an all-round full stack kind of guy. So seek advice from multiple sources :)
(I know some of those might not be the same, just throwing in a bunch of names vaguely clustered in my head)
For the JSON developer though you would need to combine it with a nice JSON-LD formater which is not yet common among implementations.
I'm not sure what other people do but on field resolvers we check for the current users access level and see if they should be seeing this thing. If not, the resolvers return errors.
In a more elegant approach, we have some of our object types duplicated / inherited. Say, "User" and "UserAdminView". If an admin queries user(id:3) they receive "UserAdminView". If a user does that, they receive a watered down "User" type, which exposes only the fields users can see. UI then selects the appropriate fragment.
Maybe there are much better ways though.
It makes it very easy to switch backends, or reuse the same query on the client or the server. Microservices have one common standard language to query data, to whatever it wants.
ORMs tried to do that but they work well only for relational data. Plus unless you use JS everywhere, you can use it on the client and the server.
The next advantage is of course letting the client define what he wants to retrieve in a single query. Whether this is useful depends on your application I think. For mine - while it was cool to use - it had no big benefit compared to having a set of orthogonal RPC APIs that the client could use to fetch the information it needs. Less round trips are a reason, but if you have your API on HTTP/2 or something else which supports small queries efficiently it might not have a too large impact.
The other large difference is that with a "lots of small APIs" approach you have to compose the data on the client while with GraphQL it's done on the server. One advantage of doing it on the server makes the load of it more predictable, since the complexity of the queries is well known and there can't be clients which try to issue queries with an abnormal amount of complexity.
What I disliked most about GraphQL were mutations. The ability to include multiple mutations into a request made it sometimes hard to reason about which of those succeed or errored based on the query result. I also don't see a huge benefit of specifying a complex query result for a mutation - as I am also now one of the believers that queries should be separated from mutations/commands and that the mutation mostly needs to report back whether an operation succeeded.
So all in all I now think it is an interesting operation for a Query API, if the target application requires complex user-defined queries. But I would rather not use it if clients mostly query the same amount of data or for command/control APIs.
However, what we do : we use PG role row level access. E.g. each database user has explicitly defined the subset of data ih has access to. All you need to ensure your application business level views do not mix/confuse logical view of partial data. And! You choose to use correct database user in your back-end data stream/feed.
Have I already mentioned the PG is a great tool and the feature gap between MS Sql, Oracle and PG constantly narrows down as we speak?
_Edit_ typos & errors
I'm sorry I don't understand.
Graphs are not a thing for running webapps/services. They are a subset of CS/maths aimed to solve specifics problems that can be expressed as graphs.
If you don't have a graph problem to solve, you don't need a graph database.
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.
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.
{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.
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.
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.
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.
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
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?
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.
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 :)
What would you like to see? (PG dev here)
No defensiveness, no push back, just a prompt to start a conversation with a potential user to better their product. :)
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.
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!
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 :)
We are currently developing a security model for http://graph.cool that sounds very similar to what you describe wanting for Neo4j. If you have time I would love to show it to you and get your feedback (email in profile).
You mention the security model of Firebase as an example to emulate. Unfortunately firebase only allows permission rules to consider path elements to the data, so in reality it actually ends up being fairly restricted and difficult to maintain.
Are there any negatives that might not be obvious from just reading the docs?
Any application where a user might want/need bits and pieces of various objects pieced together would be appropriate and that's a lot of applications.
For example if you are loading a user's dashboard view you may need basic user info, their unread messages, the list of objects they are looking at (e.g. list of invoices) and things like that.
Without GraphQL you'd be making separate requests to pull all of that together, with GraphQL it can happen in one round-trip.
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.
Ref Non-Imitable competitive advantages
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.
I like GraphQL, but I'm a front-end dev. Apollo-Client is pure gold. optimistic UI, streamed results, batching...