Zenn
🦢

[Kotlin] KomapperのUPSERT

2022/07/24に公開
Kotlin
SQL
ORM
Komapper
tech

はじめに

「レコードがあれば更新するが、なければ追加する」という処理をUPSERTと呼ぶことがあります。本記事では、KomapperのUPSERTサポートを紹介します。

UPSERTのSQL

UPSERTのSQLはデータベースごとに差が大きい部分です。例えば、PostgreSQLでは次のようなSQLでUPSERTを実現できます。

insert into department as t0_ (
    department_id, 
    department_no, 
    department_name, 
    location, 
    version
) values (5, 50, 'PLANNING', 'TOKYO', 0) on conflict (department_id) 
do update set 
    department_no = excluded.department_no, 
    department_name = excluded.department_name, 
    location = excluded.location, 
    version = excluded.version

他のデータベースの例も載せておきます。興味があればご覧ください。

H2 Database Engineの場合
merge into department as t using (
    select 
        department_id, 
        department_no, 
        department_name, 
        location, 
        version 
    from values (5, 50, 'PLANNING', 'TOKYO', 0) as x (
        department_id, 
        department_no, 
        department_name, 
        location, 
        version
    )
) as excluded on t.department_id = excluded.department_id 
    when not matched then 
        insert (
            department_id, 
            department_no, 
            department_name, 
            location, 
            version
        ) values (
            excluded.department_id, 
            excluded.department_no, 
            excluded.department_name, 
            excluded.location, 
            excluded.version
        ) 
    when matched then 
        update set 
            t.department_no = excluded.department_no, 
            t.department_name = excluded.department_name, 
            t.location = excluded.location, 
            t.version = excluded.version
MariaDBの場合
insert into department (
    department_id, 
    department_no, 
    department_name, 
    location, 
    version
) values (5, 50, 'PLANNING', 'TOKYO', 0) on duplicate key 
update 
    department_no = values(department_no), 
    department_name = values(department_name), 
    location = values(location), 
    version = values(version)
MySqlの場合
insert into department (
    department_id, 
    department_no, 
    department_name, 
    location, 
    version
) values (5, 50, 'PLANNING', 'TOKYO', 0) as excluded on duplicate key 
update 
    department_no = excluded.department_no, 
    department_name = excluded.department_name, 
    location = excluded.location, 
    version = excluded.version
Oracle Databaseの場合
merge into department t using (
    select 
        5 as department_id, 
        50 as department_no, 
        'PLANNING' as department_name, 
        'TOKYO' as location, 
        0 as version 
    from dual
) excluded on (t.department_id = excluded.department_id) 
when matched then 
    update set 
        t.department_no = excluded.department_no, 
        t.department_name = excluded.department_name, 
        t.location = excluded.location, 
        t.version = excluded.version 
when not matched then 
    insert (
        department_id, 
        department_no, 
        department_name, 
        location, 
        version
    ) values (
        excluded.department_id, 
        excluded.department_no, 
        excluded.department_name, 
        excluded.location, 
        excluded.version
    )
SQL Serverの場合
merge into department as t using (
    values (5, 50, 'PLANNING', 'TOKYO', 0)
) as excluded (
    department_id, 
    department_no, 
    department_name, 
    location, 
    version
) on t.department_id = excluded.department_id 
when matched then 
    update set 
        t.department_no = excluded.department_no, 
        t.department_name = excluded.department_name, 
        t.location = excluded.location, 
        t.version = excluded.version 
when not matched then 
    insert (
        department_id, 
        department_no, 
        department_name, 
        location, 
        version) 
    values (
        department_id, 
        department_no, 
        department_name, 
        location, 
        version
    );

Komapperの戦略

Komapperは、データベースの差を吸収する機能（Dialect）を使ってデータベースごとに異なるSQLを構築します。アプリケーションではデータベースの違いを意識する必要がないため、上述のUPSERTに相当するクエリはどのデータベースに対しても次のように記述できます。

val d = Meta.department
val department = Department(5, 50, "PLANNING", "TOKYO", 0)
val query = QueryDsl.insert(d).onDuplicateKeyUpdate().single(department)

なお、Departmentのエンティティ定義は次のとおりです。

@KomapperEntity
data class Department(
    @KomapperId @KomapperColumn("department_id") val departmentId: Int,
    @KomapperColumn("department_no") val departmentNo: Int,
    @KomapperColumn("department_name") val departmentName: String,
    val location: String,
    @KomapperVersion val version: Int,
)

UPSERTのバリエーション

上で示したのは最も単純なUPSERTです。Komapperはもう少し複雑なユースケースにも対応しています。

主キー以外の列を指定したい場合

上述の例では重複対象チェックの列に主キーであるdepartment_idを使っていますが、onDuplicateKeyUpdate関数に明示的に他の列（ユニークキー）を指定することもできます。

val d = Meta.department
val department = Department(5, 50, "PLANNING", "TOKYO", 0)
val query = QueryDsl.insert(d).onDuplicateKeyUpdate(d.departmentNo).single(department)

追加しようとしているデータに基づく更新をしたい場合

キーに重複がありUPDATE処理が行われる際、2種類のデータが存在しています。

  1. 更新対象のレコード
  2. 追加しようとしているレコード

このとき、以下のようにset関数のラムダ式の中でこの2種類のデータを参照しつつ更新処理を記述できます。

val d = Meta.department
val department = Department(1, 50, "PLANNING", "TOKYO", 10)
val query = QueryDsl.insert(d).onDuplicateKeyUpdate().set { excluded ->
    d.location eq concat(d.location, concat(",", excluded.location))
}.single(department)

上述のコード内では、更新対象のレコードはdで、追加しようとしているレコードはexcludedで表現されています。PostgreSQLに対して上述のクエリを実行すると次のようなSQLが発行されます。

insert into department as t0_ (
    department_id, 
    department_no, 
    department_name, 
    location, version
) values (1, 50, 'PLANNING', 'TOKYO', 10) on conflict (department_id) 
do update set 
    location = (concat(t0_.location, (concat(',', excluded.location))))

おまけ - UPSERT文の組み立て実装に関する雑感

正直な話、データベースごとにSQLに差がありすぎて実装するのが辛かった...。

各データベースのドキュメントやJOOQのドキュメントを参考にさせてもらったり実際に動かして試したりとなんとか複数データベースで同じ挙動をするように調整しました。特に追加しようとしているデータに基づく更新を実現するためにはデータベースによっては工夫が必要で難儀しました。

そんなときに役立ったのがTestcontainersだったりGradleのJVM Test Suiteプラグインでした。これらを利用することで実際にデータベースにSQLを発行して挙動を確認したり、テストに組み込んで安定的に動作することをビルド時に確認できました。Testcontainersについてはとても感謝していてスポンサーになったほどです。どちらもとても便利なツールなのでこの辺りのノウハウはいずれどこかで共有できたらと思っています。

おわりに

KomapperのUPSERTサポートを紹介しました。おそらく、Kotlinで利用可能なORMでここまでUPSERTのサポートがあるものは少ないのではないかと思います。ぜひお試しください。もちろんフィードバックもお待ちしています。

参考情報

Komapperのドキュメント
https://www.komapper.org/ja/docs/reference/query/querydsl/insert

Discussion