[DISCUSSION] GraphQL query planning - compile query to jdbc or criteria api !?

Hello,

I keep wondering if there are any advantages regarding integration of graphql-java with Apache Calcite.

Calcite implements a query planner and some things that could be used in graphql-java.

I'm wondering if I could take a graphql schema and generate criteria api or jdbc queries from it to select just the fields that are needed from the back-end.

Having the ability to compile the query to something like SQL or JPA criteria API should be very efficient since only the required data will be selected from DB.

I think the idea behind Calcite: schema without bothering with storage is how GraphQL works.

Wdyt?

[1] https://calcite.apache.org/

I am doing something along these lines with Jooq.

I define a mapping between GraphQL types/interfaces and SQL columns or expressions. Joins are supported. This mapping is executed against the selection set, and the one-to-one relationship traversals are flattened out into a single SQL query. Since the selection set is used to extract the columns to query, only the requested fields are retrieved. The query result data is then 'un-flattened' and returned to the GraphQL layer.

It also supports the relay connection model with filtering and paging done in the DB. Quite a lot of work, really, and still in development.

I had not heard of Calcite.

@larrybud Can you please share more details about how you actually did it ? As it exactly matches my case - I would like to use graphql in order to provide a general interface for the client to fetch data which might be located in more than one table - so a join between them is needed, but on the other hand, there are cases when the requested fields will come from just a single table, so I would like to avoid joining it with another table as it's not needed.

I assume I will have to implement DataFetcher for each part of data which will come from a separate table, but if a customer requests data which comes from different DB tables, is it possible to generate a single sql query which will join both tables and will fetch the data in one go ? As I would like to avoid getting it in separate calls.

The basic idea is to extract a tree of selections from DataFetchingEnvironment, intersect that with a hierarchial mapping from GraphQL labels to fields, and combine it with a base query.

I am using JOOQ, Postgres and Kotlin. The uppercase fields below are JOOQ generated classes and fields. In the example code below I will use the Star Wars schema, but I will add the Planet object in order to demonstrate one-to-ones, and a Hobby object to demonstrate 'embedded' types.

# this is not the entire schema; see the link above

type Planet {
  id: ID!
  name: String
}

type Hobby {
  name: String
  rank: Int
}

type Human implements Character {
  # existing fields go here...
  # make homePlanet a reference to Planet
  homePlanet: Planet
  hobby: Hobby
}

Imagine that you had the following schema:

create table planet as (
  id                serial            primary key,
  name              text
);

create type character_type as enum { 'Droid', 'Human' };

create table character as (
  id               serial             primary key,
  type             character_type     not null,
  name             text               not null,
  secret_backstory text
);

create table droid as (
  id               integer            primary key references character,
  primary_function text
);

create table human as (
  id               integer            primary key references character,
  home_planet      integer            references planet,
  -- these fields will be extracted into an embedded GraphQL type
  hobby_name       text,
  hobby_rank       integer
);

Now I create bindings from GraphQL labels to fields. If you are not familiar with Kotlin, this may look a little strange, but it is really just a type-safe builder that constructs a tree. Again, the all-caps symbols are JOOQ generated tables and fields.

val planetBindings = objectBindings(PLANET) {
    "id"..PLANET.ID
    "name"..PLANET.NAME
}

val characterBindings = objectBindings(
    CHARACTER
        .leftJoin(DROID).using(CHARACTER.ID)
        .leftJoin(HUMAN).using(CHARACTER.ID)
        .leftJoin(PLANET).on(HUMAN.HOME_PLANET.eq(PLANET.ID))
) {
  // .always means that the field will always be fetched, even if it is not asked for
  "id".always..CHARACTER.ID
  // type is not a field in the GraphQL interface, but is needed for type resolution downstream
  "type".always..CHARACTER.TYPE

  "name"..CHARACTER.NAME
  "secretBackstory"..CHARACTER.SECRET_BACKSTORY

  // droid fields
  "primaryFunction"..DROID.PRIMARY_FUNCTION

  // human fields

  // this is a one-to-one association
  "homePlanet"..planetBindings {
      // join condition
      "id"..HUMAN.PLANET
  }
  // this is an embedded/composite field
  "hobby" {
    "name"..HUMAN.HOBBY_NAME
    "rank"..HUMAN.HOBBY_RANK
  }
}

The bindings are used to create 'fetchers', which are invoked by the wiring.

This:

query {
    allCharacters {
        id
        name
        ... on Human {
            homePlanet {
                name
            }
            hobby {
                rank
            }
        }
    }
}

will produce something like:

select c.id as "id",
       c.type as "type",
       c.name as "name",
       h.hobby_rank as "hobby.rank",
       p.name as "homePlanet.name"
from character as c left join
     droid as d on c.id = d.id left join
     human as h on c.id = h.id left join
     (select id, name from planet) as p on h.home_planet = p.id

I am counting on Postgres join elimination. This means that a left join will be 'dropped' if it can't possibly affect the query outcome. So if I select no fields from DROID I will not incur the cost of joining against it (N.B. only true if the join is on a unique key).

Fields in the query are aliased using the full graphql label path, e.g. hobby.name. After query execution, the result record(s) are transformed into trees (maps of maps, which graphql-java handles) by splitting on the periods.

One-to-ones are handled specially. If only the join field is requested, e.g. homePlanet.id, then no extra join is performed; HUMAN.PLANET is sufficient. If other planet fields are requested, then planetBindings is invoked with the relevant sub-selections, and the resulting query is joined against as a table.

One-to-manys are not supported in bindings, quite intentionally. These are handled by explicit data fetchers in the wiring.

The fields in the bindings do not have to be simple table fields, but can be any SQL expression. For example, if the GraphQL Character.name field was stored in the droid and human tables rather than in character, you could define the name mapping as a case statement:

"name"..choose(CHARACTER.TYPE)
    .`when`(CharacterType.DROID, DROID.NAME)
    .otherwise(HUMAN.NAME)

Obviously, the devil's in the details, and it takes a fair amount of code to fit all the pieces together. All of this was done for my employer, so I can't post the code without permission, but I'm sure they'd let me do so, if anyone's interested. Priorities shifted while I was working on this. so it's rough, and not well-tested.

There's also a filtering and sorting aspect at the GraphQL layer that gets mapped to the bindings. One can, for example, do order-bys with arbitrary SQL expressions. Finally, there's paging using the Relay connection model, which was probably more work than all of the above. Let me know if you'd like details on any of that.

Thanks @larrybud for a very comprehensive description - I'm still digesting it ! :)

The join elimination seems to be quite promising.

My feedback after some time:

I've considered implementing this back-end but out of time I'm going to try out join-monste . It does all of this on top of nodejs.

I do believe using Jooq is a better than Calcite because of the maturity of Jooq.

https://join-monster.readthedocs.io/en/latest/
https://github.com/acarl005/join-monster

@ieugen

closing this issue as there is no action for us at the moment.
@ieugen in terms of static analysis/query planner: have a look at https://graphql-analyzer.com and https://www.graphql.de/blog/static-query-analysis/. We will also add an explicit dependency graph to graphql java (see #1594) which allows for query optimizations/planning.

GraphQL Java itself will never do specific optimizations/planning but rather provide tools to build these.

@andimarek

Thank you @andimarek . Sounds reasonable to keep that out of core graphql-java . I haven't given up on the idea but I was and am too busy to start working on it.

@larrybud any chance you could share any of your code? If you made it an open source project I might be interested in contributing. I think there's a lot we could do in terms of code generation to make it easy for users

One thing to keep an eye on is graphqlize

Navigation Menu

Repository navigation

[DISCUSSION] GraphQL query planning - compile query to jdbc or criteria api !? #1180

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[DISCUSSION] GraphQL query planning - compile query to jdbc or criteria api !? #1180

Description

Activity

lfsmith9 commented on Aug 22, 2018

patrox commented on Nov 17, 2018

lfsmith9 commented on Nov 18, 2018

patrox commented on Nov 20, 2018

ieugen commented on Nov 21, 2018

andimarek commented on Jul 19, 2019

ieugen commented on Jul 19, 2019

benmccann commented on May 2, 2020

ieugen commented on May 2, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions