Support GraphQL query language

[ruslantalpa]

I think it would be useful to extend PostgREST to understand GraphQL (https://facebook.github.io/react/blog/2015/05/01/graphql-introduction.html)
Example of GraphQL (my understanding of it, the specs are still evolving)

{
  projects (active: true) {
    id,
    name,
    active,
    description,
    client {
        id,
        name
    },
    tasks (completed: false) {
        id,
        name
    }
  }
}

This type of query allows big flexibility in extracting/filtering the data you need, in the shape that you want.
My understanding is that PostgREST currently supports only something like this

{
  project (<conditions go here>) {
    * <you get back all the fields, no way to shape the result>
  }
}

Currently, to get back the data describe in the first example, it would mean to make 3 http request, in 2 stages (get the list of the projects, get the tasks and the clients in parallel based on the project ids).

Things that need to be implemented to achieve this:

1. Create a GraphQL parser that translates it to a tree data structure like the one below (i am just learning
   Haskell, so please ignore the example it it's stupid :)

type Condition = (Field, Operator, Value)
data Operator  = Eq | Gt | Lt
data Value = RString String | RInt Int | RBool Bool
data Relation = Root | Parent | Child
data RNode = RNode {name::String, relation::Relation, columns::[String], conditions::[Condition], children::[RNode]}

1. Have a "fields" parameter that would allow to shape the response (get only the fields you need), having this feature, the system can now process requests related to a single "node"
2. • Create function that recursively fetches the data for a node, extracts the returned ids, and add them as conditions for child nodes (and proceed with fetching the child nodes). Cons for this is that the queries have to be done sequentially and also the json encoding can not be offloaded to the database server since we need to extract the ids and later recombine the results from each node in a JSON tree data structure. This would also eliminate the EXPLAIN/413 functionality since there is no way to predict the cost of the "tasks" node before doing a full query on "projects" node.
   • Could this request be implemented by creating temporary views for each node (the views for tasks/clients would reference the projects view to filter the result) then get the data from each of those views in parallel and recombine in the result in a tree data structure?
3. The relation between nodes (parent/child) can be determined by detecting the word is singular or plural

[ruslantalpa]

Seems 2 can be implemented in a single query http://bender.io/2013/09/22/returning-hierarchical-data-in-a-single-sql-query/ (just wow, PostgreSQL is powerfull)

[trinque]

Ugh... please no.

Adding a translation layer for some other query language sounds like a wonderful spot for bugs to collect.

[ruslantalpa]

Not doing something just because there may be bugs there does not seem like a logical thing. Please read what GraphQL is about before dismissing the idea. And even if someone does not like graphql, postgrest currently dose not support requesting tree like data (or even selecting only the columns you need), and until it can support that it's not going to be used in production.

[trinque]

Sure it supports tree-like data; pass it out as a JSON object if you like. Furthermore I do use it in production, so your point there holds no water. Obviously bugs can work their way in anywhere; however, in this case in particular, you are asking for a translation layer that converts between two separate sets of abstractions.

It would be far better to write a separate system which exposes a PostgreSQL server as GraphQL, rather than bifurcating the code to support it. I've written this same API server several times in other languages; you do not want mixed abstractions here; it will only befuddle reasoning about the way it operates. The interface here should solely be a clean, clear interface into a limited subset of SQL necessary for traversing over result sets. For everything more complicated, write a view. The API server needn't have its own detailed query language for that very reason.

[trinque]

Or otherwise why don't we throw in "odata" and every other API standard?

[ruslantalpa]

You said "it supports tree-like data", can you give an example of how i would request that (i might've completely missed that)?
For example in the gui demo, on this page http://marmelab.com/ng-admin-postgrest/#/list/sessions,
can you request the sessions along with speaker info in one request (currently it seems to make a lot of requests but that might be a fronted code problem).
Having a separate system expose postgrest as a graphql might be very well the way to go but for that to happen postgrest must first support returning returning tree like results (actually that's the core of the feature i am proposing/requesting, GraphQL just seemed like a nice way to express such a request)
Can you share the project you are using it for?
I used the "in production" thing wrong, it's not to say that the code is not good (it's rock solid) but the features it provides (basically querying a single table per request, if my understanding is correct) does not allow for a lot of flexibility (unless your frontend has very simple "questions" for the API).

GraphQL is "a limited subset of SQL necessary for traversing over result sets" :), there's no joining or aggregating going on there

[trinque]

It supports any data which can come out of a view. As far as writing tree traversal in SQL, there's the "with recursive" mechanism. Or if you're just talking about doing a simple join, there's that too. You shouldn't try to use this thing to be what it isn't, a sort of object-store. This is a relational database, and a powerful one at that, and you should leverage the full power of SQL here.

To the extent that you try to put an additional layer between yourself and the functionality of the database, you will only limit yourself to the subset of functionality which the intermediary layer supports.

[trinque]

Oh, also... check out json_agg. That I think along with an inner join solves precisely your problem.

[ruslantalpa]

What i understand from your response is that i should create another view to get the data in the format i described, is that correct? If that is the case then i do not agree with this approach. You are assuming that i am the only user of my API and whenever i need some new type of data (new shape) i can just create a new view. Yes i can use the views to present some complicated query/join as a flat table but if i have 2 tables that have a "one to many" relation, i should not have to create a special view just to get the data from both of them at the same time.
You say that i should not use this thing for what it is not, but it it does present itself as a thin layer between the db and a (js) frontend, and if that is the case then what it currently provides is not enough (from my point of view) because the only way to get data out of the system now is to make multiple sequential requests.
A slightly more complicated api needs to have features like here https://github.com/paymoapp/api/blob/master/sections/includes.md
PS: this is starting to feel like a flame war :) so i am going to leave it at this, maybe others can comment on the issue

[trinque]

There's no flame war; asking for a big wad o' complexity should involve vigorous debate.

[begriffs]

@ruslantalpa thanks for your detailed suggestion. Right now I think the proposal is too complicated. There is a lot of work remaining to get the basic things working properly first.

I'd suggest creating views in your db to provide customized table results. In your example you could join tasks projects and clients. Happy to discuss your specific scenario in more detail and how it could be implemented with existing postgrest features.