About jOOQ and DGS

jOOQ is a really versatile SQL builder for JVM which I have found myself using more and more recently. My workflow usually involves evolving the database schema through liquibase, after which jOOQ is used to generate record classes from the DB schema.

This enables type safe database access and ensures that application level types are always in sync with database schema.

I recently used jOOQ with Netflix DGS to build a GraphQL API. Netflix DGS is a higher level abstraction over graphql-java which makes it easy to rapidly build GraphQL APIs in a schema-first manner.

Integration issues

One issue with using them together is that both of them provide code-generation support. DGS generates kotlin data classes (or POJOs for java) from GraphQL SDL where as jOOQ generates classes from database schema.

The simplest thing to do is to retrieve data from the DB using jOOQ DSL, populate them into jOOQ generated record classes and then map them to DGS generated DTO classes when returning from our services.

This is quite necessary for scenarios where there is substantial difference between the properties of our record classes (which usually mirror the table columns) and the fields in the exposed GraphQL types, and jOOQ does provide a record-mapping support to make this kind of mapping simpler.

However, in a vast majority of scenarios, our GraphQL type fields are either same as entity properties or are resolved through dedicated data fetchers (and hence do not need corresponding properties in DTO). So this object mapping step is wasteful and can be avoided.

GraphQL java & reflection

Folks familiar with graphql-java may wonder why is this even a problem ?

After all graphql-java is not aware of what classes are generated by DGS at all. It simply uses reflection to map instance properties to GrpahQL fields. So when there is sufficient overlap between the two, we can simply ditch the classes generated by DGS (or skip generating them by splitting up our graphql schema file) and just use the record classes generated by jOOQ.

This does work fine. Even having some extraneous fields in record class which are not present in the GraphQL type is not a problem because GraphQL resolver will cherry-pick only the fields which are defined in schema.

However this becomes a problem when we want to use some of the DTOs generated by DGS and not others.

Consider following example (taken from Relay docs):

{
  user {
    id
    name
    friends(first: 10, after: "opaqueCursor") {
      edges {
        cursor
        node {
          id
          name
        }
      }
      pageInfo {
        hasNextPage
      }
    }
  }
}

Our schema might look something like this:

type User {
    id: ID!
    name: String!
    friends(first: Int, after: Cursor): UserConnection
}

scalar Cursor

type UserConnection {
    pageInfo: PageInfo!
    edges: [UserEdge!]
}

type UserEdge {
    cursor: Cursor!
    node: User!
}

So now the generated data classes for UserEdge will be referencing the generated User class, and we won't be able to use it if are using a jOOQ generated UsersRecord instead of the User DTO.

typeMapping to rescue

Fortunately this is easy to address through support for typeMapping configuration in DGS.

We can configure jOOQ to use our record class for User instead of

generateJava {
   typeMapping = ["User": "org.jooq.generated.tables.records.UsersRecord"]
}

Problem solved. Now, wherever User type is needed, DGS generated DTOs will use our UsersRecord class. Of course, this works equally well if we want to use jOOQ generated POJOs/interfaces or other classes defined in the application.

Caveats

Only caveat here is that if the mapped class is not really structurally compatible with the the type defined in GraphQL schema, we will end up with a runtime error. Currently we don't have a statically verify this compatibility. However such bugs are easily caught in preliminary testing.

Alternatives

An alternative solution to address this would be to use an annotation driven approach. Annotation-heavy libraries are quite popular in the JVM ecosystem and we can use entity classes in our application which are annotated with both JPA annotations and GraphQL specific annotations (provided by graphql-spqr, MP GraphQL etc.)

In this approach both the GraphQL schema and database schema can be derived from the corresponding set of annotations and we also have type safety.

However I am currently not a proponent of this approach because I am not a big fan of the JPA spec., esp. the projection & criteria APIs. In contrast, the jOOQ DSL is explicit, maps closely to SQL and enables me to spend more of my time in pure java/kotlin code that I can easily step through in a debugger as opposed to an annotation based DSL.

In praise of schema-first development

Lastly, I prefer a schema first approach in larger teams because backend and frontend developers can agree upon the schema ahead of time and development can progress in parallel. Given a schema, it is for instance, straightforward to mock the server (and have the ui be built using that) while the real backend is being developed.