Jooq: Add Kotlin infix and extension functions to a new jOOQ-kotlin extensions module

[lukaseder]

Ideas

Infix functions

There is some potential of improving the Kotlin / jOOQ experience by providing out-of-the-box infix functions in Kotlin, which allow for creating expressions like these:

where (
    (FIRST_NAME eq "John") and (LAST_NAME eq "Doe")
)

The fluency of writing jOOQ queries might improve. However, unfortunately, all these infix functions have the same operator precedence and are left associative. This could prove to be very confusing when mixing and and or, or even more complex expressions.

We should probably not rush to add this functionality, and observe these discussions instead:

• https://youtrack.jetbrains.com/issue/KT-1440
• https://discuss.kotlinlang.org/t/specify-operator-precedence-for-infix-functions/2685/5

Extension Functions

Another low hanging fruit are extension functions, which may simplify the jOOQ / Kotlin experience, for example:

@Support
inline fun <reified T: Any> Select<Record1<T>>.`as`(alias: String): Field<T> {
    return field(this).`as`(alias);
}

Which allows for calling:

// this
select(T.X).from(t).`as`("x")

// rather than this
field(select(T.X).from(T)).`as`("x")

Roadmap

The following improvements are under review (gathered from comments here and elsewhere) and will be implemented via this issue:

Implemented:

• [x] Add auxiliary functions to wrap Select<Record1<T>> in field(Select) for some common methods, like eq, ne, asc, desc etc.
• [x] Add extension methods to Field<Boolean>, from Condition
• [x] Array access array_column[index]: https://github.com/jOOQ/jOOQ/issues/229

Rejected:

• [x] ~Moving componentN() methods (added with #6245) into this extension module. That's a backwards incompatible change, but acceptable to reduce the API surface of Java applications~: The out of the box experience that works without adding the jOOQ-kotlin module is much more important.
• [x] ~Add auxiliary functions that help prevent using KotlinClass::class.java, reducing them to KotlinClass::class~: Won't be implemented, see: https://github.com/jOOQ/jOOQ/issues/6256#issuecomment-625175287
• [x] ~Some extensions that allow for a more native set { a = x; b = y } usage. Details: https://github.com/jOOQ/jOOQ/issues/6256#issuecomment-628010674~
• [x] ~Add operators for arithmetic on Select<Record1<T>>, such as plus(), times(), etc.~ (plus and minus are already operators on Iterable, so adding these methods would 1) break compatibility in case someone actually wants to run a UNION ALL in the client using select(a, b).from(t) + select(c, d).from(u) 2) be confusing, because the stdlib operator seems to take precedence here, so our own would be useless. We could still add times() and the others, but that would be equally confusing, if people started to use these operators and suddenly, accidentally use stdlib's plus

[lukaseder]

According to this quick, non-representative Twitter poll, it is quite idiomatic for such utilities to be placed directly in packages, not inside of classes:
https://twitter.com/lukaseder/status/1151430659744981002

[vojtapol]

@lukaseder we use Kotlin only for data classes. Our jOOQ queries are still written in Java.

Regarding the placement I definitely agree with the poll. The extensions should not be placed in companion objects, they should be top level functions.

What exact extensions would be valuable is a great question and I would suggest gathering more feedback and use cases before proceeding unless you are sure about their utility.

[lukaseder]

@lukaseder we use Kotlin only for data classes. Our jOOQ queries are still written in Java.

Oh wow, I would not have thought so :) I guess you're looking forward to Amber, then: https://cr.openjdk.java.net/~briangoetz/amber/datum.html

What exact extensions would be valuable is a great question and I would suggest gathering more feedback and use cases before proceeding unless you are sure about their utility.

Well, I'm sure about the utility of having them at all. It is more idiomatic to be able to fetchInto(KotlinClass::class) rather than the more verbose fetchInto(KotlinClass::class.java), for example. That's just one example. There may be many other, low hanging fruit.

Will definitely reach out for more feedback, though. Here's a post on the mailing list:
https://groups.google.com/forum/#!topic/jooq-user/NS5anAYmIgI

[JakeWharton]

Anywhere you want a class is likely better served by a reified function rather than accepting a KClass. This will produce into<KotlinClass>() and fetchInto<KotlinClass>().

[lukaseder]

Thanks for chiming in, @JakeWharton. But behind the scenes, we'd still have to pass a java.lang.Class to the jOOQ API at some point, so what would we gain from using a reified function?

[JakeWharton]

A better API at the call site. You can still get the class from T::class.java.

inline fun <reified T: Any, R: Record> Result<R>.into(): MutableList<T> {
    return this.into(T::class.java)
}

[lukaseder]

I'm assuming you have type inference in mind. It could work, but my first impression is that it's less user friendly. For example:

var ab =
ctx().select(value(1), value(2))
        .fetchOneInto<AB, Record2<Int, Int>>() // Additional type witness needed for R type

var ab: AB = // Additional explicit type declaration needed for inference to work
ctx().select(value(1), value(2))
        .fetchOneInto()

[lukaseder]

(of course, in such a case, the into term would be misleading, but that's a solveable problem)

[JakeWharton]

Does using a type parameter on this function for R serve any value over making the receiver Result<Record>?

[lukaseder]

It does not, indeed. This would work if my kotlin-fu does not trick me into erroneously believing <out Record> behaves similar to Java's use-site covariant <? extends Record> here:

inline fun <reified T: Any> Result<out Record>.into(): MutableList<T> {
    return this.into(T::class.java)
}

[antanas-arvasevicius]

Please don't forget coroutines and suspend functions powerfulness and adopt JOOQ for a nicer async programming.

[lukaseder]

@antanas-arvasevicius Thanks for your suggestion. This issue is only about convenience on top of jOOQ. Asynchronous jOOQ requires a much bigger change than just a few extension functions.

[lukaseder]

Another very interesting use-case is to auto-wrap scalar subqueries in DSL.field(Select):
https://stackoverflow.com/q/61525633/521799

E.g.

inline fun <T> Select<Record1<T>>.desc(): SortField<T> {
    return DSL.field(this).desc();
}

[lukaseder]

Regarding the moving of componentN() methods: I think the out of the box experience is much more important here. It is currently possible to destructure jOOQ records without adding any additional dependency. I won't move those methods into a new jOOQ-kotlin module, for now.

[lukaseder]

After a more thorough investigation, I'm not happy with the reified type suggestion by @JakeWharton here: https://github.com/jOOQ/jOOQ/issues/6256#issuecomment-512314479

It seems useful for that particular method, but the jOOQ API is much bigger than that. For example, we have methods like intoGroups(Class<? extends K>, Class<? extends V). Yes, this method could take two reified generic type parameters, but there are also overloads like intoGroups(Class<? extends K>, RecordMapper<? super R, V>) and intoGroups(RecordMapper<? super R, K>, Class<? extends V>). It would not be possible to find a consistent API design that works everywhere. Inconsistency is a high price to pay for the modest gain of a slightly more idiomatic usage for the Result.into<Type>() method.

Given how many overloads we currently have, offering another set of overloads just to avoid calling .java on X::class.java seems excessive even for the consideration of the addition of my own suggestion.

Users can very easily roll their own extension functions, so we're not in a hurry to implement these things. I do want to support extension functions where we can really add out of the box value, though.

[lukaseder]

... Then again, I'm not even 100% happy with the idea of adding things like:

@Support
inline fun <reified T: Any> Select<Record1<T>>.`as`(alias: String): Field<T> {
    return field(this).`as`(alias);
}

Which allows for calling:

// this
select(T.X).from(t).`as`("x")

// rather than this
field(select(T.X).from(T)).`as`("x")

Yes, that's useful, but it's also confusing, because that particular expression can be projected in a select() clause, but as soon as the alias is removed, it no longer works, surprisingly.

[lukaseder]

I was going to add operators on Select to allow things like (select 1, 2) = any (select 2, 3 union select 3, 4), but that could actually be added to the core library instead: #10176

[stellingsimon]

DSL.concat() might be another candidate for an inline function... I'm imagining

val suffixField = DSL.inline(".") + FILE.EXTENSION

instead of

val suffixField = DSL.concat(DSL.inline("."), FILE.EXTENSION)

:-)

[lukaseder]

You can already do that, since we have Field.plus(Field), but that's for addition, not concatenation.

[lukaseder]

This could be interesting:

inline fun <reified R: UpdatableRecordImpl<R>> UpdateSetStep<R>.set(change: R.() -> Unit): UpdateSetMoreStep<R> {
    val record: R = R::class.java.constructors.getOrNull(0)!!.newInstance() as R
    change(record)
    this.set(record)
    return this as UpdateSetMoreStep<R>
}

It would allow working with ad-hoc generated record instances in a perhaps more idiomatic way:

fun main() {
    println(
            DSL.using(SQLDialect.H2)
                    .update(PRECISION_TS_6)
                    .set { id = 1; value = null /* not setting ts = null */ }
                    .getSQL(ParamType.INLINED)

    )
}

The resulting SQL is

update "PRECISION_TS_6" set "PRECISION_TS_6"."ID" = 1, "PRECISION_TS_6"."VALUE" = null

Not 100% sure if this is a good idea. But I'm intrigued. Idea credit to @gortiz from https://twitter.com/gortizja/status/1260558837238693888

[lukaseder]

Array access is another one of these low hanging fruit: https://github.com/jOOQ/jOOQ/issues/229

@Support
inline operator fun <T> Field<Array<T>>.get(index: Int) = arrayGet(this, index)

@Support
inline operator fun <T> Field<Array<T>>.get(index: Field<Int>) = arrayGet(this, index)

Allowing for this usage:

kotlin: val a = field(unquotedName("a"), INTEGER.arrayDataType) assertEquals(arrayGet(a, 1), a[1])

[lukaseder]

We're done here for jOOQ 3.14. More features can be added in future jOOQ versions.

The experience has been sobering. A lot of stuff seems very tempting and cool at first, but upon closer inspection, it doesn't work well (KotlinClass::class.java vs KotlinClass::class), isn't idiomatic (those block lambdas seem cool at first, but they aren't beginner friendly, and they raise many questions about what's possible / reasonable inside of them), or just leads to confusion (Select.plus() can be confused with Iterable.plus()! What a pity that + was overloaded on Iterable, when + is a commutative operator in arithmetic, yet there seem to have been no lessons learned from the mistake that is overloading + for string concatenation).

We'll keep this in mind for the future, prior to adding more goodies like these.