Provide exposed-like type-safe SQL API without code generation

[sdeleuze]

See my tweet and my former blog post about that. We could maybe make it SQL engine agnostic by building a library that would generate SQL in order to be able to use it for JDBC and R2DBC.

[encircled]

Hi, Sounds pretty interesting ;) So, you would like to have something like QueryDsl, but with functional-style API and supporting both JDBC and R2DBC without JPA, right?

[pull-vert]

Hi,

Edit : I realise my initial proposal is not type-safe, I will try to achieve this and follow @sdeleuze comment below ! Will come back soon with something better.

[sdeleuze]

@encircled It would be similar to QueryDsl or Jooq but without code generation since Kotlin typing system allows that as demonstrated by Exposed.

@pull-vert Thanks for starting experimenting, I can maybe share some thoughts about the design. I tend to think it should be possible to build a standalone library (that could be integrated with Spring Data JDBC/R2DBC via extensions but would not require them) similar to Exposed but that would just focus on defining tables structure (with object) and based on that allow schema generation and typesafe SQL requests. So it would be a subset of Exposed usable with any SQL library. Does that make sense?

[encircled]

@sdeleuze and why exactly can't we stick with Exposed? :)

[pull-vert]

@sdeleuze thanks for clarification, I continued experimenting, and released Kotysa. As suggested, it is totally standalone, and integrate via an Extension function directly on DatabaseClient for R2DBC (more supported targets will come later)

Kotysa is an additional dependency you can add to your Spring project, hosted on jcenter. It is an extension to Spring Data R2DBC, and does not replace it.

repositories {
    jcenter()
}

dependencies {
    implementation 'org.ufoss.kotysa:kotysa-spring-data-r2dbc:0.1.3'
    
    implementation "org.springframework.data:spring-data-r2dbc"
}

Feedbacks are welcome :)

[tm-guimaraes]

@sdeleuze

Kotlin typing system allows that as demonstrated by Exposed.

Yes, but you still need to define you database model in koltin,, why do you want to do that? You solve code generation with a bigger problem that is keeping DB and kotlin code consistent. Then migrations and all that....

I think avoiding generation this way is a mistake. What's so bad about generation? If you are running a versioning system like flyway, adding Jooq shouldn't be a problem, and you would get errors at compile time. Basing your database on a Kotlin model might not be ideal once things are running. I think this blog post explains it better than me: https://blog.jooq.org/2018/06/06/truth-first-or-why-you-should-mostly-implement-database-first-designs/

F# has type providers, that seems like an interesting alternative approach (the compiler generates the types at compile time, by looking at an external source, in this case, a DB). But I'm not sure how feasible they are for production.

[pull-vert]

@tm-guimaraes I think each developper may have a preference, some like annotation based JPA that provide some really nice features, but can be heavy and complex. Some prefer Kotlin DSL like Exposed or my Kotysa library. And last option is code generation with Jooq.

I continue to work on kotysa and now have support for R2DBC with PostgreSQL and H2, and Android with its native SQLite. Two samples are available in Kotysa repository that are using spring-fu so you can see what it looks like for reactive R2DBC and coroutines + R2DBC