🦥

[Kotlin] INSERT...RETURNINGをORMでサポートしてみた

2023/05/01に公開

Kotlin

はじめに

SQLでデータの追加と取得を同時に行う、いわゆるINSERT ... RETURNINGの構文ですが、PostgreSQLだけがサポートしているという認識だったんですが最近はそうでもないんですね。全く同じ構文は使えないにしても他のデータベースでも同様のことを実現できるとわかりました。

Komapperに実装する過程で調べたことをまとめておきます。KomapperはJDBCとR2DBCをサポートしたサーバサイドKotlin向けのORMです。したがって、この記事で示すSQLはJDBCやR2DBCのドライバを使って発行する前提となります。

データベースごとのSQL

PostgreSQL

PostgreSQLは、INSERT文の最後のRETURNING句で返したいカラムを指定します。

insert into address (address_id, street, version) values (?, ?, ?) returning address_id, street, version

ドキュメントはこの辺り。

MariaDB

MariaDBは、PostgreSQLと全く同じ構文が使えます。

insert into address (address_id, street, version) values (?, ?, ?) returning address_id, street, version

ドキュメントはこの辺り。

Oracle Database

Oracle Databaseは、PostgreSQLやMariaDBと似ているんのですが、最後にINTO句で出力用のバインド変数を指定します。

insert into address (address_id, street, version) values (?, ?, ?) returning address_id, street, version into ?, ?, ?

ドキュメントはこの辺り。「DML RETURNING」という名前がついています。

他のデータベースではSQLさえ記述できればあとはJDBCやR2DBCの標準APIの範囲で処理できるのですが、Oracle Databaseの場合はOracle JDBC driver固有の機能を使う必要があります。

今のところR2DBCドライバではサポートされていません。

SQL Server

SQL Serverは、OUTPUT句でinsertedというプレフィックスをつけて追加されたカラムを指定します。PostgreSQLなどのRETURNING句のようにINSERT文の最後に追加する形ではなく、INSERT INTO句とVALUES句の間に記載するというスタイルです。

insert into address (address_id, street, version) output inserted.address_id, inserted.street, inserted.version values (?, ?, ?)

ドキュメントはこの辺り。

H2 Database

H2 Databaseは、Data Change Delta Tableというものを使ってSELECT文として結果を返します。

select address_id, street, version from final table (insert into address (address_id, street, version) values (?, ?, ?))

ドキュメントはこの辺り。

MySQL

おそらくサポートされていません。別の構文でエミュレートするのもできないと思います（もちろん追加と取得で2回に分けてSQLを発行すれば可能ですが1つのSQLでは無理だと認識しています）。方法を知っている方がいたら教えていただけたらと思います。

Kotlinで記述すると

Komapperを使ったKotlinのコードは以下のようになります。returningという関数を呼んでいるのがポイントです。下記のコードは接続先のデータベースに応じて適切なSQLを出力します。

val a = Meta.address
val address: Address = db.runQuery { 
    QueryDsl.insert(a)
        .single(Address(16, "STREET 16", 0))
        .returning() 
}

上述の例では全てのカラムを返していますが、returning関数にカラムを指定すれば特定のカラムのみを返却できます。

val a = Meta.address
val street: String? = db.runQuery { 
    QueryDsl.insert(a)
        .single(Address(16, "STREET 16", 0))
        .returning(a.street) 
}

複数件を同時に追加して結果をリストで取得することもできます（ただし、Oracle Databaseではサポートされていません）。

val a = Meta.address
val addressList: List<Address> = db.runQuery { 
    QueryDsl.insert(a)
        .multiple(listOf(
            Address(16, "STREET 16", 0),
            Address(17, "STREET 17", 0),
            Address(18, "STREET 18", 0),
        ))
        .returning()
}

他にも、UPSERT（MERGE）文と組みわせて使ったりなども可能です。

実装時の考慮ポイント

KomapperにINSERT...RETURNINGを実装するにあたっては、ユーザー向けのAPIを簡潔にした上で下記のようなことに気を配りました。

データベースごとに異なるサポート状況に対応する
データベースごとに異なるSQLに対応する
必要に応じてデータベースのドライバ固有のAPIを呼び出す
JDBCとR2DBCのドライバのAPI呼び出し部分は完全に分離する

関数やinterfaceを使って条件分岐をいかにうまくマネジメントするのかみたいな作業になりました。

おわりに

Komapperは先日リリースしたv1.10.0でINSERT...RETURNINGとUPDATE...RETURNINGをサポートしました。また、次のバージョンではDELETE...RETURNINGをサポート予定です。

ぜひ、お試しください。

はじめに

データベースごとのSQL

PostgreSQL

MariaDB

Oracle Database

SQL Server

H2 Database

MySQL

Kotlinで記述すると

実装時の考慮ポイント

おわりに

Discussion