GraphQL to SQL translation

[archenroot]

Hi guys,

I am going to use GraphQL on my new project (FEs love it to utilize over REST). My initial task is to create routing service to datawarehouse which will support query anything in general. The storage is quite massive, so I look about how to automate things.

I am not "yet" expert on GraphQL, but would like to prevent writing to much code and rather rely on code generation if possible, so I plan to use this interesting project to generate GraphQL types from exisiting database: https://github.com/ebridges/rdbms-to-graphql

So now I have schemas, I can also easily generate all POJOs covering all entities in database and covered in GraphQL schemas.

What I am looking for is some kind of GraphQL to SQL translator, I found 3 of those but written in node or python, most promissing is Hasura https://medium.com/@HasuraHQ

Do you have experience how to quickly expose large database via graphql with low coding?

Thank again for your always valuable inputs.

NOTE: I must write this time some public articles on your framework on medium or elsewhere to get some attention of your nice work

[stevehu]

As you are dealing with a huge database so caching might not be an option. There are two issues that we need to address. First, we need to generate a schema from the database and it seems the solution is pretty good. And light-graphql-4j can scaffold the project based on the schema. The second issue is how to translate the query/mutation into a SQL statement and execute it. This is something one of our teams is investigating with light-rule and https://github.com/JetBrains/Exposed. We can work together if you are interested.

[archenroot]

I am more than interested. Its something like StrongLoop - Loopback functionality. Best would be to take the code from Hasura and port it :-) I am analyzing the project.

Additionally I need to test during week the rdbms-to-graphql as I think it only generates the Types in GraphQL, but I would like to generate also the queries for any kind of types variation... will look at Exposed in meanwhile...

[stevehu]

I am working on Kotlin these days and the DSL function is so powerful. The light-rule and light-workflow will be written in Kotlin DSL and potentially rewritten the light-bot.

[archenroot]

...aaah Kotlin, so I won't escape it anyway :D (maybe the time has come to learn it finally) I will do some research about GraphQL to SQL project in the wild even based on other languages...

But in general I don't have problem with Kotlin of course I always liked DSL concept

[archenroot]

Additionally I am going to test rdbm-to-graphql and will share output + sample postgres schema, i might be using later not only Postgres, but also Hive but over Postgres protocol anyway, but that is just dialect and in case of query not big deal...

[archenroot]

Interestingly I also found this article related to Kotlin as well, but targeting OGM which I will need to do in second step: https://www.excusethedisruption.com/introducing-kotlin-gremlin-ogm/

[archenroot]

But back to relational stuff first, I started discussion with Lukas Eder about https://github.com/changelog-software/graphql-jooq

[stevehu]

These are all interesting projects and I hope we can work together to build some useful applications. Light-graphql-4j can be a very fast HTTP server.

[archenroot]

@stevehu - I think same, could be very nice project for community as well.

I have tested so far the rdbms-to-graphql with sample postgres database: https://github.com/ghusta/docker-postgres-world-db

So it generates as expected only the types:

type city {
        id: Int
        name: String
        country_code: String
        district: String
        population: Int
}

type country_language {
        country_code: String
        language: String
        is_official: Boolean
        percentage: Float
}
type country {
        code: String
        name: String
        continent: String
        region: String
        surface_area: Float
        indep_year: Int
        population: Int
        life_expectancy: Float
        gnp: Float
        gnp_old: Float
        local_name: String
        government_form: String
        head_of_state: String
        capital: Int
        code2: String
}

So it doesn't generate queries at moment, so it will be task 1 to generate queries as well into separate schemas...but I will first think about this.

Second task is to have the actual Entities generated from database annotated...

[archenroot]

Database classes generation is common task and there exist multiple implementations....

@stevehu - maybe we will do some call about the approach during week...

I will extend the generator for generate queries as well and maybe I will do simple poc all together with that Jetbrains project, just to see it working in completely dummy way hardcoded for this simple schema... I also see that the fields should be converted from _ delimited to camelCase...(small sanity convertor)

[stevehu]

The generated result looks very promising. If you look at the schema file we need to generate the light-graphql-4j project, the only thing missing is the schema and QueryType.

https://github.com/networknt/model-config/blob/master/graphql/starwars/schema.graphqls

[archenroot]

thx for refference, i will work on extending the generator to fit the sample.

For your model-config, do you have real SQL database create script, so I can test generate against schema.graphqls sample you provided to meet it at 100%?

[stevehu]

So far all the examples we have are to demo the light-graphql-4j server and generator. There is not database examples. For the config.json file, you can find a lot of examples with a database enabled in model-config. https://github.com/networknt/model-config/blob/master/rest/openapi/multidb/config.json

[ddobrin]

I will look at this project as well (thank you for the link) : https://github.com/JetBrains/Exposed Need to build for my client 2 different types of GraphQL microservices and this will come in handy.

I would be interested as well in a Google Hangouts or other type of chat, if this can be arranged.