DEV Community

Ryosuke Hasebe
Ryosuke Hasebe

Posted on

Introducing Kuery Client for those who love writing SQL in Kotlin/JVM

#kotlin #r2dbc #jdbc #spring

First of all

How can it be written?

I'd like to start with a preamble, but first, let me give you a quick overview.

suspend fun search(status: String, vip: Boolean?): List<User> = kueryClient
    .sql {
        +"""
        SELECT * FROM users
        WHERE
        status = $status
        """
        if (vip != null) {
            +"vip = $vip"
        }
    }
    .list()
Enter fullscreen mode Exit fullscreen mode
  • You can concatenate and build SQL using + (unaryPlus)
    • If you want to build SQL dynamically, please use constructs like if
    • (Of course, if there is no need to build it dynamically, you can write it directly using a heredoc)
  • Use string interpolation to embed dynamic values
    • Naturally, you might think this could lead to SQL injection, but by implementing a Kotlin compiler plugin, it is evaluated as a placeholder

Motivation

Originally, I liked MyBatis because I could write SQL by hand

In the world of JVM, there are many database client libraries, but I preferred using MyBatis.

The reasons for this preference are roughly as follows:

  • I want to write SQL directly
    • Because it is used in a large-scale environment, I prefer to write SQL directly even if it takes a bit more effort
    • Writing SQL makes it easier to perform operations like Explain
    • Occasionally, there are cases where I want to specify an Index Hint, and I can respond quickly to such cases
  • I don't want to learn the library's unique syntax or DSL
    • It would be nice if knowing SQL alone is enough

Writing SQL directly means depending on a specific database, but cases of migrating databases (e.g., from MySQL to PostgreSQL) are virtually non-existent. Even if such an opportunity arises, I would take the time to thoroughly verify it (compared to this, fixing SQL is not much effort), so I don't see this as much of a disadvantage.

MyBatis does not support R2DBC

Recently, I have had more opportunities to write applications using Spring WebFlux & Coroutines for work, and this makes me want to use R2DBC.
(Previously, I was wrapping JDBC calls with withContext(Dispatchers.IO) {...})

Unfortunately, the aforementioned MyBatis does not support R2DBC.

R2DBC libraries that allow writing SQL by hand

Existing libraries that support R2DBC often use unique syntax/DSL, which do not match my preferences.

On the other hand, these DSLs have the advantage of being type-safe. They prevent mistakes such as inserting a string into an integer column.

However, personally, I write corresponding unit tests when I write SQL, so I am indifferent to this aspect.

(Bonus) sqlc and SQLDelight

Recently, sqlc has been getting attention and seems to match my preferences very well. (I like the gRPC-like concept)

However, although it seems to support Kotlin, it unfortunately only supports JDBC.

Looking at the generated code, it seems to have only the minimum implementation compared to the Go support of sqlc.

Also, in the case of Kotlin, there is a similar tool called SQLDelight. While it claims to support R2DBC, it is difficult to use connection pools as the arguments are not ConnectionFactory, and its Transaction support is also inadequate, giving the impression that it is still in development.

Furthermore, neither supports constructing dynamic queries. (There are varying opinions on the desirability of such dynamic queries)

I want to write using string templates & string interpolation

If I'm writing SQL by hand, I also want to write using string templates & string interpolation like Doobie, which is popular in Scala.

def find(n: String): ConnectionIO[Option[Country]] =
  sql"select code, name, population from country where name = $n".query[Country].option
Enter fullscreen mode Exit fullscreen mode

However, unfortunately, in Kotlin, you cannot customize the behavior of string interpolation.
(Although in Java, this has recently become possible...)

Incidentally, this topic has been discussed here:

Knowing Kotlin Compiler Plugin amidst all this

I originally focused on Kotlin/JVM, but recently I have become engrossed in KMP (Kotlin Multiplatform).
(The motivation is simple... it would be convenient if everything could be written in Kotlin...)

I noticed that libraries for KMP often provide features including Kotlin Compiler Plugin.
(Kotlin Serialization is a prime example)

I was already using the noarg plugin and allopen plugin, but I realized that even third parties are making them.

Perhaps, I thought, I could use this to change the behavior of string interpolation for specific methods...? And amidst all this, I came across the slides for the following presentation at Kotlin Fest 2024. (I couldn't attend due to a schedule conflict...)

https://speakerdeck.com/kitakkun/kotlin-fest-2024-motutokotlinwohao-kininaru-k2shi-dai-nokotlin-compiler-pluginkai-fa

As a result, I managed to create an SQL client that can be used as mentioned at the beginning.

How to Use Kuery Client

Like other libraries, just add it to your Gradle dependencies.
However, since a Kotlin Compiler Plugin is required, please also use the Gradle Plugin provided by Kuery Client.

plugins {
    id("dev.hsbrysk.kuery-client") version "{{version}}"
}

implementation("dev.hsbrysk.kuery-client:kuery-client-spring-data-r2dbc:{{version")
Enter fullscreen mode Exit fullscreen mode

Features of Kuery Client

Builder and String Interpolation

In Kotlin, a style of creating a Builder Scope and constructing dynamically within it, as represented by buildString or buildList, is often adopted.

buildString {
    append("hoge")
    if (...) {
        append("bar")
    }
}
Enter fullscreen mode Exit fullscreen mode

This writing style is also adopted in kotlinx.html.
Text nodes are added using +.

val body = document.body ?: error("No body")
body.append {
    div {
        p {
            +"Welcome!"
            +"Here is "
            a("https://kotlinlang.org") { +"official Kotlin site" }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Following this writing style, the style mentioned at the beginning is used. (Repost)

suspend fun search(status: String, vip: Boolean?): List<User> = kueryClient
    .sql {
        +"""
        SELECT * FROM users
        WHERE
        status = $status
        """
        if (vip != null) {
            +"vip = $vip"
        }
    }
    .list()
Enter fullscreen mode Exit fullscreen mode

Based on spring-data-r2dbc or spring-data-jdbc

Currently, it is implemented based on these widely used and proven technologies. So, it can be used with both R2DBC/JDBC.
(The original motivation was to create it for R2DBC, but I decided to also support JDBC)

Depending on these for the requirements of Kuery Client may be somewhat too much, but it allows for using transaction support and type conversion as is...

(If I feel like it, I might create a module that doesn't depend on these)

Transaction

You can use Spring's Transaction support as is.

For more details, please see here.

https://kuery-client.hsbrysk.dev/transaction.html

Observation

It supports Micrometer Observation, so it can handle both Metrics and Tracing.

For more details, please see here.

https://kuery-client.hsbrysk.dev/observation.html

Type Conversion

Since it is based on spring-data, please use Spring's type conversion.
Even with custom types, it can be handled flexibly. It should be able to handle cases where only specific types need to be encrypted.

For more details, please see here.

https://kuery-client.hsbrysk.dev/type-conversion.html

Detekt Custom Rule

Writing in the following incorrect way may cause SQL Injection or similar issues.

kueryClient.sql {
    // BAD !!
    val sql = "SELECT * FROM user WHERE id = $id"
    +sql
}
Enter fullscreen mode Exit fullscreen mode

Since the string interpolation is customized for specific methods of Kuery Client, such writing is not allowed.
To detect such incorrect writing, we provide a Detekt Custom Rule.

https://kuery-client.hsbrysk.dev/detekt.html

Example Code

Sample code combined with Spring Boot.

Conclusion

For more detailed information, please check the documentation site. (Although it's very simple at the moment...)
https://kuery-client.hsbrysk.dev/

I've already started using it for personal projects and find it quite convenient and enjoyable to use.

Looking ahead, I vaguely think it would be nice to integrate SQL-related linters since I'm writing SQL by hand.

Also, I want to implement something similar to the query type checks available in Scala's Doobie. (I haven't used it much, so I'm not very familiar yet)
https://tpolecat.github.io/doobie/docs/06-Checking.html

Although I wrote as I did in the aforementioned motivation, it is certainly better if things can be made robust.

On the other hand, DSLs have the advantage of being type-safe. They prevent mistakes such as inserting a string into an integer column.
However, personally, I write corresponding unit tests when I write SQL, so I am indifferent to this aspect.

Top comments (0)

Read next

amit_kharel_aae65abe2b111 profile image

Here's how you can build and train GPT-2 from scratch using PyTorch

Amit Kharel -

arindam_roy_382 profile image

I created a nosql DB using rust

Arindam Roy -

forhad96 profile image

Using Redux Toolkit Query to Create an Authentication API with ``injectEndpoints``

FORHAD -

princessonyi profile image

Bash Script Automation for User and Group Management in Linux

Princess Alieze -