SQLDelightを使ってKotlinで型安全にSQLを扱う

klibsを眺めていたところ面白そうなものを見つけたので試してみた。

sqldelight.github.io

これが結構面白い。リポジトリのAboutに「Generates typesafe Kotlin APIs from SQL」とあって「ORM？」と思ったけど、どうもちょっと違う気がする*1。ORMはオブジェクトを定義して、オブジェクトを操作することでSQLを扱う。が、SQLDelightは逆で、SQLを元に型安全なコードを自動生成するというアプローチを取っている。正しく表現できてるかアレだけど、簡単に違いを表にしてみる。

特徴	SQLDelight	典型的なORM
SQL操作	SQLを直接記述し、それを元に型安全なコードを生成	SQLを抽象化してオブジェクトとして操作
型安全性	SQLを元にオブジェクトを定義する。オブジェクトの作成はSQLDelightが自動で定義してくれる。	SQLを元にオブジェクトを定義する。*2。オブジェクトの作成は実装者に委ねられる。
自由度	SQL文を自由に書ける。複雑なクエリやパフォーマンスチューニングが容易。	ORMの抽象化が強い場合、複雑なクエリや最適化が困難なことがある
学習コスト	SQLの知識のみ	SQLの知識+ORM独自のAPIの学習が必要

こんな感じでORM独自のAPIを覚える必要がなく、生クエリベースでガリガリ書きつつ型安全にしたいという人に向いていそうだ。

今回は比較も兼ねて、Kotlin製ORMのExposedと共存させながらSQLDelightを使ってみた。Exposedとの比較とかはコードの転記がめんどくさいのでこの記事には直接書かない。けどこの後に書くリポジトリを見たら比較できるようにしておく。なので、書きっぷりの違いが気になる人はそっちを見てほしい。*3

環境・リポジトリ

このsqldelightブランチが今回の環境。DockerやSQLファイルも置いてあるのですぐに試せると思う。DBは今回はMySQLを使う。*4

github.com

元々はExposedだけを使い倒した本を書いたときの成果物だったりする。興味があればExposedの本も見てくれると嬉しい。

techbookfest.org

準備

SQLDelightはIntelliJ プラグインも提供しているので、あらかじめこれを入れておくと便利。便利になる場面は後ほど触れる。

sqldelight.github.io

Gradleプラグインの適用

まずはSQLから型定義ファイルを作るために必要なプラグインの設定をしていく。

build.gradle.ktsを以下のように修正する。

val ktor_version: String by project
val kotlin_version: String by project
val logback_version: String by project

plugins {
    kotlin("jvm") version "1.9.0"
    id("io.ktor.plugin") version "2.3.3"
+    id("app.cash.sqldelight") version "2.0.2"
}

group = "example.koin"
version = "0.0.1"

application {
    mainClass.set("io.ktor.server.tomcat.EngineMain")

    val isDevelopment: Boolean = project.ext.has("development")
    applicationDefaultJvmArgs = listOf("-Dio.ktor.development=$isDevelopment")
}

repositories {
+    google()
    mavenCentral()
}

+sqldelight {
+    databases {
+        create("Database") {
+            packageName.set("com.example")
+            dialect("app.cash.sqldelight:mysql-dialect:2.0.2")
+        }
+    }
+}

`sq`ファイルの作成

今回はFresh Schemaの方式で型定義をSQLDelightにやってもらう。

まずはsrc/mainの下にsqldelightディレクトリを作る。この下にできた.sq`ファイルをSQLDelightは認識してKotlinのソースに変換してくれる。

この時IntelliJのプラグインが有効になっているとファイルを編集したタイミングでKotlinのソースを作成・修正してくれる。

プラグインがない場合はgenerateMainDatabaseInterfaceタスクを実行する必要がある。

試しにsrc/main/sqldelight/employee/data/Employee.sqを作成し以下の内容で保存すると…

CREATE TABLE EMPLOYEE (
    EMPLOYEE_ID INT,
    DEPARTMENT_ID INT,
    ENROLLMENT_STATUS INT, -- 在籍状況 0:在籍中 1:休職中 2:退職済
    FIRST_NAME VARCHAR(128),
    LAST_NAME VARCHAR(128),
    CREATED_AT DATETIME,
    UPDATED_AT DATETIME
);

selectAll:
SELECT *
FROM EMPLOYEE;

selectById:
SELECT *
FROM EMPLOYEE
WHERE EMPLOYEE_ID = ?;

buildディレクトリ以下にファイルができている。

ファイルを見るとスキーマとSQLを実行する関数ができている。

package employee.`data`

import java.time.LocalDateTime
import kotlin.Int
import kotlin.String

public data class EMPLOYEE(
  public val EMPLOYEE_ID: Int?,
  public val DEPARTMENT_ID: Int?,
  public val ENROLLMENT_STATUS: Int?,
  public val FIRST_NAME: String?,
  public val LAST_NAME: String?,
  public val CREATED_AT: LocalDateTime?,
  public val UPDATED_AT: LocalDateTime?,
)

package employee.`data`

import app.cash.sqldelight.Query
import app.cash.sqldelight.TransacterImpl
import app.cash.sqldelight.db.QueryResult
import app.cash.sqldelight.db.SqlCursor
import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.jdbc.JdbcCursor
import app.cash.sqldelight.driver.jdbc.JdbcPreparedStatement
import java.time.LocalDateTime
import kotlin.Any
import kotlin.Int
import kotlin.String

public class EmployeeQueries(
  driver: SqlDriver,
) : TransacterImpl(driver) {
  public fun <T : Any> selectAll(mapper: (
    EMPLOYEE_ID: Int?,
    DEPARTMENT_ID: Int?,
    ENROLLMENT_STATUS: Int?,
    FIRST_NAME: String?,
    LAST_NAME: String?,
    CREATED_AT: LocalDateTime?,
    UPDATED_AT: LocalDateTime?,
  ) -> T): Query<T> = Query(-1_033_296_007, arrayOf("EMPLOYEE"), driver, "Employee.sq", "selectAll",
      """
  |SELECT EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.DEPARTMENT_ID, EMPLOYEE.ENROLLMENT_STATUS, EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME, EMPLOYEE.CREATED_AT, EMPLOYEE.UPDATED_AT
  |FROM EMPLOYEE
  """.trimMargin()) { cursor ->
    check(cursor is JdbcCursor)
    mapper(
      cursor.getLong(0)?.let { it.toInt() },
      cursor.getLong(1)?.let { it.toInt() },
      cursor.getLong(2)?.let { it.toInt() },
      cursor.getString(3),
      cursor.getString(4),
      cursor.getObject<LocalDateTime>(5),
      cursor.getObject<LocalDateTime>(6)
    )
  }

  public fun selectAll(): Query<EMPLOYEE> = selectAll { EMPLOYEE_ID, DEPARTMENT_ID,
      ENROLLMENT_STATUS, FIRST_NAME, LAST_NAME, CREATED_AT, UPDATED_AT ->
    EMPLOYEE(
      EMPLOYEE_ID,
      DEPARTMENT_ID,
      ENROLLMENT_STATUS,
      FIRST_NAME,
      LAST_NAME,
      CREATED_AT,
      UPDATED_AT
    )
  }

  public fun <T : Any> selectById(EMPLOYEE_ID: Int?, mapper: (
    EMPLOYEE_ID: Int?,
    DEPARTMENT_ID: Int?,
    ENROLLMENT_STATUS: Int?,
    FIRST_NAME: String?,
    LAST_NAME: String?,
    CREATED_AT: LocalDateTime?,
    UPDATED_AT: LocalDateTime?,
  ) -> T): Query<T> = SelectByIdQuery(EMPLOYEE_ID) { cursor ->
    check(cursor is JdbcCursor)
    mapper(
      cursor.getLong(0)?.let { it.toInt() },
      cursor.getLong(1)?.let { it.toInt() },
      cursor.getLong(2)?.let { it.toInt() },
      cursor.getString(3),
      cursor.getString(4),
      cursor.getObject<LocalDateTime>(5),
      cursor.getObject<LocalDateTime>(6)
    )
  }

  public fun selectById(EMPLOYEE_ID: Int?): Query<EMPLOYEE> = selectById(EMPLOYEE_ID) {
      EMPLOYEE_ID_, DEPARTMENT_ID, ENROLLMENT_STATUS, FIRST_NAME, LAST_NAME, CREATED_AT,
      UPDATED_AT ->
    EMPLOYEE(
      EMPLOYEE_ID_,
      DEPARTMENT_ID,
      ENROLLMENT_STATUS,
      FIRST_NAME,
      LAST_NAME,
      CREATED_AT,
      UPDATED_AT
    )
  }

  private inner class SelectByIdQuery<out T : Any>(
    public val EMPLOYEE_ID: Int?,
    mapper: (SqlCursor) -> T,
  ) : Query<T>(mapper) {
    override fun addListener(listener: Query.Listener) {
      driver.addListener("EMPLOYEE", listener = listener)
    }

    override fun removeListener(listener: Query.Listener) {
      driver.removeListener("EMPLOYEE", listener = listener)
    }

    override fun <R> execute(mapper: (SqlCursor) -> QueryResult<R>): QueryResult<R> =
        driver.executeQuery(null, """
    |SELECT EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.DEPARTMENT_ID, EMPLOYEE.ENROLLMENT_STATUS, EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME, EMPLOYEE.CREATED_AT, EMPLOYEE.UPDATED_AT
    |FROM EMPLOYEE
    |WHERE EMPLOYEE_ID ${ if (EMPLOYEE_ID == null) "IS" else "=" } ?
    """.trimMargin(), mapper, 1) {
      check(this is JdbcPreparedStatement)
      bindLong(0, EMPLOYEE_ID?.let { it.toLong() })
    }

    override fun toString(): String = "Employee.sq:selectById"
  }
}

KotlinでSQLを発行する

準備

SQLを実行する用にライブラリが必要なので、build.gradle.ktsのdependenciesに以下を追加する。

implementation("app.cash.sqldelight:jdbc-driver:2.0.2")

あとはSQLDelightがSQL喋るために必要なdatabaseなるものを用意してあげる。今回はHikariCPを使っている。この辺の詳しい実装はリポジトリを見てほしい。*5

import app.cash.sqldelight.db.SqlDriver
import app.cash.sqldelight.driver.jdbc.asJdbcDriver
import com.example.Database
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

open class DatabaseService {
    fun getHikariDataSource(): HikariDataSource{
        val config = HikariConfig().apply {
            password = "secret"
            jdbcUrl = "jdbc:mysql://127.0.0.1:13308/exposed_local"
            driverClassName = "com.mysql.cj.jdbc.Driver"
            username = "exposer"
            password = "secret"
            maximumPoolSize = 5
        }
        return HikariDataSource(config)
    }

    fun getDatabase(): Database{
        val driver: SqlDriver = getHikariDataSource().asJdbcDriver()
        val database = Database(driver)
        return database;
    }
}

selectを試してみる

以下のように書くことでDBからデータを取ってこれる。

package example.koin.service

import employee.data.EmployeeQueries

class SqlDelightService() : DatabaseService() {
    fun getAllEmployees(): String {
        val employeeQueries: EmployeeQueries = getDatabase().employeeQueries
        return employeeQueries.selectAll().executeAsList().joinToString {
            "${it.LAST_NAME} ${it.FIRST_NAME}"
        }
    }

    fun getEmployeeById(id: Int): String {
        val employeeQueries: EmployeeQueries = getDatabase().employeeQueries
        return employeeQueries.selectById(id).executeAsOne().let{
            "従業員の名前は「${it.LAST_NAME} ${it.FIRST_NAME}」です！"
        }
    }

}

insertなどの他のDMLに関してもSQLDelightが勝手に関数などを作成してくれるっぽい。Transactionも張れそう。

sqldelight.github.io

joinするとどうなるのか？

気になったのでやってみた。Employee.sqファイルに以下を追加する。

joinDepartment:
SELECT *
FROM EMPLOYEE
JOIN DEPARTMENT ON EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID;

すると、JoinDepartmentなるものができあがった。

package employee.`data`

import java.time.LocalDateTime
import kotlin.Int
import kotlin.String

public data class JoinDepartment(
  public val EMPLOYEE_ID: Int?,
  public val DEPARTMENT_ID: Int?,
  public val ENROLLMENT_STATUS: Int?,
  public val FIRST_NAME: String?,
  public val LAST_NAME: String?,
  public val CREATED_AT: LocalDateTime?,
  public val UPDATED_AT: LocalDateTime?,
  public val DEPARTMENT_ID_: Int?,
  public val DEPARTMENT_NAME: String?,
  public val CREATED_AT_: LocalDateTime?,
  public val UPDATED_AT_: LocalDateTime?,
)

つまりsqファイルのラベル(というかクエリ)の結果ごとにDTOを定義してくれるらしい。EmployeeQueriesの方も見てみると、joinDepartmentができている。

  public fun <T : Any> joinDepartment(mapper: (
    EMPLOYEE_ID: Int?,
    DEPARTMENT_ID: Int?,
    ENROLLMENT_STATUS: Int?,
    FIRST_NAME: String?,
    LAST_NAME: String?,
    CREATED_AT: LocalDateTime?,
    UPDATED_AT: LocalDateTime?,
    DEPARTMENT_ID_: Int?,
    DEPARTMENT_NAME: String?,
    CREATED_AT_: LocalDateTime?,
    UPDATED_AT_: LocalDateTime?,
  ) -> T): Query<T> = Query(-799_577_048, arrayOf("EMPLOYEE", "DEPARTMENT"), driver, "Employee.sq",
      "joinDepartment", """
  |SELECT EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.DEPARTMENT_ID, EMPLOYEE.ENROLLMENT_STATUS, EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME, EMPLOYEE.CREATED_AT, EMPLOYEE.UPDATED_AT, DEPARTMENT.DEPARTMENT_ID, DEPARTMENT.DEPARTMENT_NAME, DEPARTMENT.CREATED_AT, DEPARTMENT.UPDATED_AT
  |FROM EMPLOYEE
  |JOIN DEPARTMENT ON EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID
  """.trimMargin()) { cursor ->
    check(cursor is JdbcCursor)
    mapper(
      cursor.getLong(0)?.let { it.toInt() },
      cursor.getLong(1)?.let { it.toInt() },
      cursor.getLong(2)?.let { it.toInt() },
      cursor.getString(3),
      cursor.getString(4),
      cursor.getObject<LocalDateTime>(5),
      cursor.getObject<LocalDateTime>(6),
      cursor.getLong(7)?.let { it.toInt() },
      cursor.getString(8),
      cursor.getObject<LocalDateTime>(9),
      cursor.getObject<LocalDateTime>(10)
    )
  }

インスペクションしてみると、いい感じにデータが取れている。

感想

SQLクエリファーストで考えていて、後から型をつけてくれるのが新鮮だった。ネイティブクエリを書きながらも勝手に型をつけてくれるのが嬉しいっていう開発者は結構多そう。

それに学習コストがかなり低いのもいい。試してみてExposedと共存させられることもわかった。ネイティブクエリを扱いたいときだけSQLDelightからクエリを発行するのもありだと思う。Exposedを軸に考えるなら、DDLを一緒に管理しておけばよいだけなので。DTOはその分増えてしまうが、そっちはSQLDelightが勝手に管理してくれるのであまり二重管理の負担にはならないんじゃないだろうか。

あと気になるとしたら、ストアドを動かせるのかどうかが気になる。これは誰か試した人がいたら是非教えてほしい。

総じていうとGitHub Starが6.3kもついてる*6のも納得だった。今度Kotlinで何か作るときはExposedもいいけどこっちを使ってみたい。

*1:この記事のタイトル見た方もそう思われたのでは

*2:ここは表現が難しい。型安全に書くことには違いないがORMによっても違いがあるかも。例えばEloquentだとPHPの世界でオブジェクトを作り、それを元にMigrationされるのでSQLDelightとは順番が違う。Exposedの場合はそれができない。

*3:この辺の実装を比較してみて！

https://github.com/ysknsid25/hands-on-exposed/blob/1b65aa2794290fd1b4b8badf12a4af7490762d4c/src/main/kotlin/example/koin/service/SelectService.kt#L21-L40

*4:Exposedの本を書いた時に使ったのがMySQLだった都合。SQLDelight的にはPostgreSQLはじめ他のDBにも対応している。

*5:ローカルに作ったコンテナ&&ただの個人の検証環境なのでSQL接続情報はハードコードしている。言うまでもないけど業務で使う時なんかは開発環境でもちゃんと隠してね。こんなの書くまでもなくて当たり前のことすぎるんだけど一応脚注つけとかないと「機密情報ハードコードするな警察」が出てきてうざいので。

*6:2025年1月24日現在

空の箱

からのはこ

SQLDelightを使ってKotlinで型安全にSQLを扱う

環境・リポジトリ

準備

Gradleプラグインの適用

`sq`ファイルの作成

KotlinでSQLを発行する

準備

selectを試してみる

joinするとどうなるのか？

感想

環境・リポジトリ

準備

Gradleプラグインの適用

sqファイルの作成

KotlinでSQLを発行する

準備

selectを試してみる

joinするとどうなるのか？

感想

`sq`ファイルの作成