Generate classes for keys with jOOQ

user_980915 0
Viewed 284

Sometimes it can get hairy when functions have signature like this:

fun doStuff(firstKey: UUID, secondKey: UUID, ...)

To the compiler all UUIDs are the same, so a problem here is hopefully caught at run time by the database.

I like how jOOQ catches many problems at compile time, and I would like to tackle this one too. My goal is to have for every key of every table its own class, and to have the pojos generated correctly with these fields too.

What would be the best way of achieving that? I've come up with the following:

  • Full blown JavaGenerator implementation
  • Converters with lot's of forced type mappings, and manually created key classes

Does anybody have experience with something like that?

jooq kotlin
2 Answers

The jOOQ feature on the roadmap

You're not the first one with this idea. There's a pending feature request to generate such "key wrapper" classes out of the box (not available in 3.11 yet): https://github.com/jOOQ/jOOQ/issues/6124

Or this discussion: https://groups.google.com/forum/#!topic/jooq-user/53RZqoewa3g

There are additional applications to yours for this feature. Once such types exist:

  • You can only join by matching primary keys / foreign keys, as they would no longer be modelled by arbitrary numeric types
  • It will be impossible to forget a column in a composite key when joining or filtering, as the composite key will become one value

The composite key case is the one that makes this tricky for jOOQ to support out of the box, as a variety of additional features needs to be implemented first in order to group several columns into a synthetic column. Also, both unique keys and foreign keys can overlap, so there are quite a few edge cases that have to be taken into account.

Implementing it yourself

You can implement this yourself. If your schema only has single column surrogate keys, then you could override the JavaGenerator class and generate an additional class per table and add the relevant forcedType configurations and Converter implementations programmatically to your code generator configuration.

Others may have done something like this, but I'm not aware of any publicly available implementation.

Implementing it in the database

In principle, you could also implement this in the database directly, e.g. if you're using PostgreSQL. You could wrap your UUID in a composite type and use that for your primary keys / foreign keys:

create type pk_a as (id bigint);
create type pk_b as (id bigint);

create table t_a(id pk_a primary key);
create table t_b(id pk_b primary key, a pk_a references t_a);

insert into t_a values(row(1)::pk_a);
insert into t_b values(row(2)::pk_b, row(1)::pk_a);

The jOOQ code generator should pick up these types and do the right thing for you. Of course, there are probably quite a few caveats, given that I've hardly ever seen this practice in the wild :-)

user_5217990

Does anybody have experience with something like that?

Yes, I've done it for Java and Kotlin projects. All tables use UUID-based surrogate keys and follow these naming conventions:

  • Primary key: ADDRESS.ID
  • Foreign key: ORDER.ADDRESS_ID or ORDER.SHIPPING_ADDRESS_ID

For the Kotlin project, I used

  • manually written XXXId classes, but it takes no more than 4 lines of Kotlin to define an Id-Class and its factory object.
  • manually written XXXJooqIdConverter classes, 1 line each. All the hard parts and utilities are tucked away into a generic superclass which reflectively extracts the target type from its generic parameters.
  • a static list of all forcedType-mappings (1 line each with a small helper function that takes just the fully qualified Id class name), simply because it is dead-simple. You can generate these mappings by relying on naming conventions, if you like.

This takes about 2 day to set up properly and gave us a tremendous boost in type safety and code clarity. IMHO it is well worth the effort for a project estimated >1 person-year.

(I actually went a step further and wrote a small "code scaffolding" tool that would generate the above three artifacts. This saves me from fiddling with files in different places, which may be error-prone but is mostly annoying. This took me another day to write, although this depends largely on your project setup.)

The reason we chose not to solve this via a custom JooqGenerator implementation is that we use the same XXXId classes in domain code as well, which must not depend on data-layer artifacts for architectural reasons. We use a near-identical approach for enums, where it has proven especially useful to use the domain type in the JOOQ model instead of vice-versa. For example, it is trivial to document a manually defined enum value in code, something I would miss in a generated class. We also were able to trivially define subtype relationships between a ContactId and CustomerId, something that would be very cumbersome with generated classes.

I can provide bits and pieces to give you a better idea, but unfortunately not the entire implementation since that is proprietary code I don't own myself.

user_4842930
Related