Best practices for using jOOQ to generate entities

[Daniel Einspanjer]

We have been using some the jOOQ generator for a while to generate some sources for use as the underlying "implementation" of data entities for interacting with a database:

base/target/generated-sources/jooq/com.example.db1.generated/{enums,routines}/*.java

base/target/generated-sources/jooq/com.example.db1.generated/{tables/udt}/{interfaces,pojos,records}/*.java

base/target/generated-sources/jooq/com.example.db2.generated/{enums,routines}/*.java

base/target/generated-sources/jooq/com.example.db2.generated/{tables/udt}/{interfaces,pojos,records}/*.java

And then in the actual source of our project:

base/src/main/java/com.example.db1.entities/*.java

base/src/main/java/com.example.db2.entities//*.java

The entities usually inherit from a table interface generated by jOOQ and take advantage of that to allow us to use jOOQ to execute type safe statements.

Our problem is with the generation and maintenance of these jOOQ classes.  Ideally, we'd like to avoid requiring each developer to have to connect to these databases during build if they aren't touching the schema itself.  

We have been trying to use the XMLDatabase generator to let a developer pull down the project, and run a compile which would use the jOOQ generator to generate the source files from a committed copy of the schema in an XMLDatabase.  If the developer were making changes to the schema, then they would run a maven profile to generate a new XMLDatabase schema and commit that change.

There continue to be a few pieces missing in that solution that prevents it from fully working:

* lack of support for array types ( https://github.com/jOOQ/jOOQ/issues/6653 )

* Postgres enums don't seem to be generated into the XMLDatabase ( no issue yet, didn't see anything similar in searches )

* jOOQ version changes seem to break parsing of the XMLDatabase ( https://github.com/jOOQ/jOOQ/issues/7218 and maybe related to the fix in https://github.com/jOOQ/jOOQ/issues/6727 ? )

* UDTs seem to lose type information when generated from an XMLDatabase instead of directly from a Postgres DB ( needs further investigation on our end )

So, for now, we are doing something much less enjoyable which is for a developer with the ability to connect to both databases to run the generator and actually commit the generated sources in the target/generated-sources directory.

This isn't great because normally the target folder is excluded in .gitignore.

So now, my list of questions for ideas and "best practices":

* Do people normally generate source files to target/generated-sources or to their actual source tree?

* Do they commit the resulting generated source files in VCS?

* Are all developers typically expected to generate new sources on each build, even if they are potentially working with large databases that can be very slow to generate over a remote connection?

* Is the XMLDatabase mechanism intended to be used as a solution to this type of scenario or are we "abusing" it?

I welcome your feedback, thank you!

-Daniel

[Samir Faci]

This is just what we've been doing at work.

We maintain the delta SQL in the git repo.  Every commit is applied to the docker DB and applied on top of the base. 

Once the SQL is committed the code is run against the DB and auto-generated code is written out to target/packageName/

the library is then released as a jar to nexus and versioned.

Dev will then get the SQL delta released to prod and picks up the versioned auto-generated schema library that will ship with his/ her service changes.

I wouldn't use XML based generation personally.  I wouldn't even use HSQLDB over say postgres.  There are variation in databases enough 

that I don't trust these type of abstraction to generate a schema definition.  They can be useful at times for unit tests, but I wouldn't use it for schema generation as I said.

As far as my expectation of developers:

1. check in a SQL delta. get it code review by DBA.  get it merged.

2. Pickup released library updated version in project implemented changes, write unit test 

3. get SQL deployed to production and service changes.

No pattern is perfect, but this works for us.  Hope this was helpful.

--

Samir Faci

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thank you
Samir Faci

https://keybase.io/csgeek

[Lukas Eder]

I mostly agree with Samir's approach. I've also blogged about this topic here:

https://blog.jooq.org/2014/09/08/look-no-further-the-final-answer-to-where-to-put-generated-code/

While I appreciate that there are some people insisting that generated code is not part of the code base, I've never fully agreed to their arguments. While there might be an academic discussion on the pros / cons of each approach, I've just found checking in generated sources and / or build artefacts thereof much more practical. Samir has already pointed out advantages of checking in generated build artefacts. Checking in generated sources also helps keeping track of regressions in the generated source itself (e.g. jOOQ Converter configurations). Plus, on an informal level, you can associate meta data modifications with individual commits (e.g. which team member added / modified / removed which database column).

Regarding the XMLDatabase:

On Sat, Feb 24, 2018 at 6:07 AM, Daniel Einspanjer <daniel.einspanjer@designet.com> wrote:

Our problem is with the generation and maintenance of these jOOQ classes.  Ideally, we'd like to avoid requiring each developer to have to connect to these databases during build if they aren't touching the schema itself.  

A modern approach that I often advocate is to use a local copy of the actual production database itself. With tools like Docker, this shouldn't be too complicated unless your schema is huge. The advantage of this is that everyone can develop against their own snapshot version of the development schema, rather than sharing a schema among developers.

 

We have been trying to use the XMLDatabase generator to let a developer pull down the project, and run a compile which would use the jOOQ generator to generate the source files from a committed copy of the schema in an XMLDatabase.  If the developer were making changes to the schema, then they would run a maven profile to generate a new XMLDatabase schema and commit that change.

There continue to be a few pieces missing in that solution that prevents it from fully working:

* lack of support for array types ( https://github.com/jOOQ/jOOQ/issues/6653 )

Indeed, those should be added at some point. Array types are standardised by the SQL standard, and I'm sure it will be possible to model them appropriately in the jOOQ XML information_schema.

 

* Postgres enums don't seem to be generated into the XMLDatabase ( no issue yet, didn't see anything similar in searches )

I've created an issue for this:

https://github.com/jOOQ/jOOQ/issues/7223

I can't promise anything about the priority of implementing it, though. Implementing a quick win is not going to be a good idea here. There are many ways to declare vendor-specific data types in different databases. The XML schema will need to take into account all the possibilities, not just enums.

 

* jOOQ version changes seem to break parsing of the XMLDatabase ( https://github.com/jOOQ/jOOQ/issues/7218 and maybe related to the fix in https://github.com/jOOQ/jOOQ/issues/6727 ? )

Yes, thanks for reporting that. That will be fixed, soon.

 

* UDTs seem to lose type information when generated from an XMLDatabase instead of directly from a Postgres DB ( needs further investigation on our end )

Same as the new issue #7223

So, for now, we are doing something much less enjoyable which is for a developer with the ability to connect to both databases to run the generator and actually commit the generated sources in the target/generated-sources directory.

This isn't great because normally the target folder is excluded in .gitignore.

The solution is to not generate into target/generated-sources, but into src/main/java . See also discussion with Samir.

 

So now, my list of questions for ideas and "best practices":

* Do people normally generate source files to target/generated-sources or to their actual source tree?

* Do they commit the resulting generated source files in VCS?

* Are all developers typically expected to generate new sources on each build, even if they are potentially working with large databases that can be very slow to generate over a remote connection?

One additional advantage of maintaining the generated sources inside of your VCS is that you can profit from the SchemaVersionProvider feature (which can be plugged into Flyway, for instance), to prevent regenerating things if nothing has changed on the server:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-version-providers

 

* Is the XMLDatabase mechanism intended to be used as a solution to this type of scenario or are we "abusing" it?

I think in your case, you shouldn't use it. All three of XMLDatabase, DDLDatabase (at least not yet), and JPADatabase work best if you're not using a lot of vendor-specific functionality.

I hope this helps,

Lukas