TypeScript用クエリービルダー「Kysely」でトランザクション、UPSERT、JOINを使ってみた

# 本記事で行うこと

前回の記事で、TypeScript用クエリービルダー「Kysely」を使って基本的なCRUD操作を試しました。
次はもう少し複雑な処理を書いてみよう、ということで、本記事で以下の3つを試します。

- トランザクション
- UPSERT
- テーブルのJOIN

データベースはPostgreSQLと、SQLiteの2つを使用します。

## 前回の記事

- [TypeScript用クエリービルダー「Kysely」をDBファーストで使う試み（SQLite編）](https://qiita.com/kanedaq/items/d3f29991dbee4cefa26d)
- [TypeScript用クエリービルダー「Kysely」をDBファーストで使う試み（PostgreSQL編）](https://qiita.com/kanedaq/items/3cfda9916c3615949202)

# テーブルを作る

それでは作業に移ります。
DBファーストで行きますので、まずDDLを書きます。
[このページ](https://www.postgresql.jp/document/15/html/tutorial-fk.html)のCREATE TABLE文を、PostgreSQLとSQLiteの両方で動くように書き直しました（私は本番プロジェクトではこんなことはせず、各DBに最適なDDLを書きます）。
ただし、SQLite用にはSTRICTオプションを付け加えます。

PostgreSQL用

```sql
CREATE TABLE cities (
        name     TEXT  PRIMARY KEY,
        location TEXT
);

CREATE TABLE weather (
        city      TEXT REFERENCES CITIES(name),
        temp_lo   INT,           -- 最低気温
        temp_hi   INT,           -- 最高気温
        prcp      REAL,          -- 降水量
        date      TEXT
);
```

SQLite用

```sql
CREATE TABLE cities (
        name     TEXT  PRIMARY KEY,
        location TEXT
) STRICT;

CREATE TABLE weather (
        city      TEXT REFERENCES CITIES(name),
        temp_lo   INT,           -- 最低気温
        temp_hi   INT,           -- 最高気温
        prcp      REAL,          -- 降水量
        date      TEXT
) STRICT;
```

# HonoとKyselyを使って、トランザクション操作するWeb APIサーバーを作ってみる

参考サイト

- PostgreSQL
  - https://kysely.dev/docs/getting-started?dialect=postgresql&package-manager=npm
  - https://kysely-org.github.io/kysely-apidoc/classes/PostgresDialect.html
- SQLite
  - https://kysely.dev/docs/getting-started?dialect=sqlite&package-manager=npm
  - https://kysely-org.github.io/kysely-apidoc/classes/SqliteDialect.html
- 共通
  - https://github.com/RobinBlomberg/kysely-codegen

## プロジェクト作成

プロジェクト名は「exercise_kysely_2」にしました。
どのテンプレートを使うか聞かれたので、「npm」を選択しました。

参考ページ： https://hono.dev/getting-started/basic

```sh
npm create hono@latest exercise_kysely_2
```

## パッケージインストール

PostgreSQL関連とSQLite関連のパッケージを両方インストールします。
プロジェクトディレクトリ直下で、以下を実行しました。

```sh
npm install kysely pg better-sqlite3
npm install --save-dev @types/pg @types/better-sqlite3 kysely-codegen
```

## DBからTypeScriptのスキーマを自動生成

DBファーストなので、コードからDBスキーマを生成するのではなく、DBスキーマからコードを生成します。
プロジェクトディレクトリ直下で、kysely-codegenコマンドを2回実行しました。
環境変数のDATABASE_URLにはそれぞれ、PostgreSQLのURLと、SQLiteのデータファイルを指定します。

```sh
DATABASE_URL=postgres://postgres:secret@localhost:5432/postgres kysely-codegen --out-file ./src/postgres.d.ts
DATABASE_URL=/home/user/DataGripProjects/sqlite/exercise.sqlite3 kysely-codegen --out-file ./src/sqlite.d.ts
```

## ディレクトリ構造とファイル

```
Project Root
  ├── src/
  │     ├── index.ts
  │     ├── postgres.d.ts
  │     └── sqlite.d.ts
  ├── package.json
  ├── README.md
  └── tsconfig.json
```

## src/postgres.d.ts

kysely-codegenコマンドによって自動生成されたコードです。

```src/postgres.d.ts
export interface Cities {
  location: string | null;
  name: string;
}

export interface Weather {
  city: string | null;
  date: string | null;
  prcp: number | null;
  temp_hi: number | null;
  temp_lo: number | null;
}

export interface DB {
  cities: Cities;
  weather: Weather;
}
```

## src/sqlite.d.ts

kysely-codegenコマンドによって自動生成されたコードです。

postgres.d.tsとの違いとして、temp_hi列とtemp_lo列がstring型にマッピングされました。
SQLiteのINT型をTypeScriptのnumber型にマッピングすると、桁数が大きい時に丸め誤差が生じることがあるので、string型にマッピングされたと思われます。

```src/sqlite.d.ts
export interface Cities {
  location: string | null;
  name: string;
}

export interface Weather {
  city: string | null;
  date: string | null;
  prcp: number | null;
  temp_hi: string | null;
  temp_lo: string | null;
}

export interface DB {
  cities: Cities;
  weather: Weather;
}
```

## src/index.ts

メインのコードです。
postgresTransaction()とsqliteTransaction()は、それぞれPostgreSQLとSQLiteに対して、ほぼ同じ処理を行います。

```src/index.ts
import {serve} from '@hono/node-server'
import {Hono} from 'hono'
import {Kysely, PostgresDialect, SqliteDialect, sql} from "kysely"
import {DB as POSTGRES_DB} from "./postgres"
import {DB as SQLITE_DB} from "./sqlite"
import {Pool} from "pg"

const SQLite = require("better-sqlite3");

// PostgreSQL初期設定
const postgresDialect = new PostgresDialect({
    pool: new Pool({
        host: "localhost",
        port: 5432,
        user: "postgres",
        password: "secret",
        database: "postgres",
    })
})
export const postgresDb = new Kysely<POSTGRES_DB>({
    dialect: postgresDialect,
    log: (event) => {
        if (event.level === "query") {
            console.log(event.query.sql)
            console.log(event.query.parameters)
        }
    }
})

// SQLite初期設定
const sqliteDialect = new SqliteDialect({
    database: new SQLite("/home/user/DataGripProjects/sqlite/exercise.sqlite3"),
})
export const sqliteDb = new Kysely<SQLITE_DB>({
    dialect: sqliteDialect,
    log: (event) => {
        if (event.level === "query") {
            console.log(event.query.sql)
            console.log(event.query.parameters)
        }
    }
})

// PostgreSQLクエリー
const postgresTransaction = async (isGood: boolean) => {
    // トランザクション
    await postgresDb.transaction().execute(async (tx) => {
        // 親テーブルに1件UPSERT（INSERT ... ON CONFLICT）
        await tx.insertInto("cities")
            .values({
                name: "San Francisco",
                location: "(-194.0, 53.0)",
            })
            .onConflict((ocb) => ocb
                .column("name")
                .doUpdateSet({
                    location: (eb) => eb.ref("excluded.location")
                })
            )
            .execute()

        // 子テーブルに3件INSERT

        // 1件目
        await tx.insertInto("weather")
            .values({
                city: "San Francisco",
                temp_lo: 43,
                temp_hi: 57,
                prcp: 0.0,
                date: "2023-01-01",
            })
            .execute()

        // 2件目
        await tx.insertInto("weather")
            .values({
                city: "San Francisco",
                temp_lo: 43,
                temp_hi: 57,
                prcp: 0.0,
                date: "2023-01-02",
            })
            .execute()

        // 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
        let city
        if (isGood) {
            city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
        } else {
            city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
        }
        await tx.insertInto("weather")
            .values({
                city: city,
                temp_lo: 43,
                temp_hi: 57,
                prcp: 0.0,
                date: "2023-01-03",
            })
            .execute()
    })

    // トランザクションが成功したら、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返す
    return postgresDb
        .selectFrom("cities")
        .leftJoin("weather", "cities.name", "weather.city")
        .select([
            "cities.name",
            sql`weather.temp_lo`.as("tempLo"),
            sql`weather.temp_hi`.as("tempHi"),
            "weather.prcp",
            "weather.date",
        ])
        .orderBy("weather.date")
        .execute()
}

// SQLiteクエリー
const sqliteTransaction = async (isGood: boolean) => {
    // トランザクション
    await sqliteDb.transaction().execute(async (tx) => {
        // 親テーブルに1件UPSERT（INSERT ... ON CONFLICT）
        await tx.insertInto("cities")
            .values({
                name: "San Francisco",
                location: "(-194.0, 53.0)",
            })
            .onConflict((ocb) => ocb
                .column("name")
                .doUpdateSet({
                    location: (eb) => eb.ref("excluded.location")
                })
            )
            .execute()

        // 子テーブルに3件INSERT

        // 1件目
        await tx.insertInto("weather")
            .values({
                city: "San Francisco",
                temp_lo: "43",
                temp_hi: "57",
                prcp: 0.0,
                date: "2023-01-01",
            })
            .execute()

        // 2件目
        await tx.insertInto("weather")
            .values({
                city: "San Francisco",
                temp_lo: "43",
                temp_hi: "57",
                prcp: 0.0,
                date: "2023-01-02",
            })
            .execute()

        // 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
        let city
        if (isGood) {
            city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
        } else {
            city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
        }
        await tx.insertInto("weather")
            .values({
                city: city,
                temp_lo: "43",
                temp_hi: "57",
                prcp: 0.0,
                date: "2023-01-03",
            })
            .execute()
    })

    // トランザクションが成功したら、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返す
    return sqliteDb
        .selectFrom("cities")
        .leftJoin("weather", "cities.name", "weather.city")
        .select([
            "cities.name",
            sql`CAST(weather.temp_lo AS TEXT)`.as("tempLo"),
            sql`CAST(weather.temp_hi AS TEXT)`.as("tempHi"),
            "weather.prcp",
            "weather.date",
        ])
        .orderBy("weather.date")
        .execute()
}

// HonoでAPIサーバー
const app = new Hono()
app.notFound((c) => c.json({message: "Not Found", ok: false}, 404))

// Routing

// PostgreSQL：成功するトランザクション
app.post("/postgres/good", async (c) => {
    try {
        const resultSet = await postgresTransaction(true)
        return c.json(resultSet)
    } catch (e) {
        return c.json({message: e.toString(), ok: false}, 500)
    }
})

// PostgreSQL：失敗するトランザクション
app.post("/postgres/bad", async (c) => {
    try {
        return c.json(await postgresTransaction(false))
    } catch (e) {
        return c.json({message: e.toString(), ok: false}, 500)
    }
})

// SQLite：成功するトランザクション
app.post("/sqlite/good", async (c) => {
    try {
        return c.json(await sqliteTransaction(true))
    } catch (e) {
        return c.json({message: e.toString(), ok: false}, 500)
    }
})

// SQLite：失敗するトランザクション
app.post("/sqlite/bad", async (c) => {
    try {
        return c.json(await sqliteTransaction(false))
    } catch (e) {
        return c.json({message: e.toString(), ok: false}, 500)
    }
})

const port = 3000
console.log(`Server is running on port ${port}`)

serve({
    fetch: app.fetch,
    port
})
```

## package.json

```package.json
{
  "scripts": {
    "dev": "tsx watch src/index.ts"
  },
  "dependencies": {
    "@hono/node-server": "^1.4.1",
    "better-sqlite3": "^9.3.0",
    "hono": "^3.12.6",
    "kysely": "^0.27.2",
    "pg": "^8.11.3"
  },
  "devDependencies": {
    "@types/better-sqlite3": "^7.6.8",
    "@types/pg": "^8.10.9",
    "kysely-codegen": "^0.11.0",
    "tsx": "^3.12.2"
  }
}
```

## tsconfig.json

```tsconfig.json
{
  "compilerOptions": {
    "strict": true,
    "jsx": "react-jsx",
    "jsxImportSource": "hono/jsx",
  }
}
```

# トランザクション処理

それでは、トランザクション処理を行う関数の中身について、一つ一つ見ていきます。
postgresTransaction()とsqliteTransaction()でほぼ同じ処理を行いますので、postgresTransaction()だけ見ることにします。

## トランザクション開始・終了

参考ページ： https://kysely.dev/docs/examples/transactions/simple-transaction

```typescript
    // トランザクション
    await postgresDb.transaction().execute(async (tx) => {
        // 一連のクエリーを書く
    }
```

上記コードからKyselyが生成したSQLは以下の通りです。

### トランザクション開始時：

```sql
begin
```

### トランザクション終了時：

good：

```sql
commit
```

bad：

```sql
rollback
```

## 親テーブルへのUPSERT

INSERT ON CONFLICTを使用しました。

参考ページ： https://kysely-org.github.io/kysely-apidoc/classes/InsertQueryBuilder.html#onConflict

```typescript
        // 親テーブルに1件UPSERT（INSERT ... ON CONFLICT）
        await tx.insertInto("cities")
            .values({
                name: "San Francisco",
                location: "(-194.0, 53.0)",
            })
            .onConflict((ocb) => ocb
                .column("name")
                .doUpdateSet({
                    location: (eb) => eb.ref("excluded.location")
                })
            )
            .execute()
```

上記コードからKyselyが生成したSQLとパラメータを、整形して見やすくしました。
プライマリーキーのname列には「San Francisco」という値が入ります。

```sql
insert into "cities" (
    "name",
    "location"
)
values (
    $1,
    $2
)
on conflict (
    "name"
)
do update set
    "location" = "excluded"."location"
```

パラメータ

```
[
    'San Francisco',
    '(-194.0, 53.0)'
]
```

## 子テーブルへのINSERT

3件INSERTします。

### 1件目・2件目

INSERT文が成功するように記述します。

```typescript
        // 1件目
        await tx.insertInto("weather")
            .values({
                city: "San Francisco",
                temp_lo: 43,
                temp_hi: 57,
                prcp: 0.0,
                date: "2023-01-01",
            })
            .execute()

        // 2件目
        await tx.insertInto("weather")
            .values({
                city: "San Francisco",
                temp_lo: 43,
                temp_hi: 57,
                prcp: 0.0,
                date: "2023-01-02",
            })
            .execute()
```

上記のコードからKyselyが生成したSQLとパラメータを、整形して見やすくしました。

```sql
insert into "weather" (
    "city",
    "temp_lo",
    "temp_hi",
    "prcp",
    "date"
)
values (
    $1,
    $2,
    $3,
    $4,
    $5
)
```

1件目のパラメータ

```
[
    'San Francisco',
    43,
    57,
    0,
    '2023-01-01'
]
```

2件目のパラメータ

```
[
    'San Francisco',
    43,
    57,
    0,
    '2023-01-02'
]
```

### 3件目

引数isGoodがtrueの時は成功するように、falseの時は失敗するように記述します。

```typescript
        // 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
        let city
        if (isGood) {
            city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
        } else {
            city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
        }
        await tx.insertInto("weather")
            .values({
                city: city,
                temp_lo: 43,
                temp_hi: 57,
                prcp: 0.0,
                date: "2023-01-03",
            })
            .execute()
```

失敗させたい時は、外部キーのcity列に、親テーブルにない値を設定しました。

パラメータ

```
[
    'Hayward',
    43,
    57,
    0,
    '2023-01-03'
]
```

## 親テーブルと子テーブルをJOIN

トランザクション後に処理が成功したか見たいので、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返すことにしました。

参考サイト： https://kysely.dev/docs/category/join

```typescript
    // トランザクションが成功したら、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返す
    return postgresDb
        .selectFrom("cities")
        .leftJoin("weather", "cities.name", "weather.city")
        .select([
            "cities.name",
            sql`weather.temp_lo`.as("tempLo"),
            sql`weather.temp_hi`.as("tempHi"),
            "weather.prcp",
            "weather.date",
        ])
        .orderBy("weather.date")
        .execute()
```

上記コードからKyselyが生成したSQLを、整形して見やすくしました。

```sql
select
    "cities"."name",
    weather.temp_lo as "tempLo",
    weather.temp_hi as "tempHi",
    "weather"."prcp",
    "weather"."date"
from
    "cities"
left join
    "weather"
on
    "cities"."name" = "weather"."city"
order by
    "weather"."date"
```

# 動かしてみる

それでは、テーブルが空の状態で動かしてみます。

## API一覧

APIのメソッドは、すべてPOSTです。

|PATH|DB|トランザクションの成否|
|-|-|-|
|/postgres/good|PostgreSQL用|成功する|
|/SQLite/good|SQLite用|成功する|
|/postgres/bad|PostgreSQL用|失敗する|
|/SQLite/bad|SQLite用|失敗する|

## 失敗パターン

失敗パターンから動かしてみます。
処理後のログを見ると、トランザクションがコミットされずにロールバックされています。
また、処理後のテーブルはすべて空のままでした。

### PostgreSQLの失敗パターン

Postmanのスナップショット：

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/02f8441e-4b8e-8160-4a1c-adb9400a3442.png)

Kyselyが出力したログ：

```
begin
[]
insert into "cities" ("name", "location") values ($1, $2) on conflict ("name") do update set "location" = "excluded"."location"
[ 'San Francisco', '(-194.0, 53.0)' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-01' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-02' ]
rollback
[]
```

### SQLiteの失敗パターン

Postmanのスナップショット：

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/b5e03c34-54cd-cf56-494f-62a32cca83e0.png)

Kyselyが出力したログ：

```
begin
[]
insert into "cities" ("name", "location") values (?, ?) on conflict ("name") do update set "location" = "excluded"."location"
[ 'San Francisco', '(-194.0, 53.0)' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-01' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-02' ]
rollback
[]
```

## 成功パターン

次は成功パターンです。
処理後のログを見ると、トランザクションがコミットまで進んでいます。
また、APIのレスポンスを見て、データがテーブルに挿入されていることが確認できました。

なお、親テーブルへのUPSERT処理について、本記事ではINSERT部分しか動作確認しませんでしたが、UPDATE部分も成功することを確認しました。

### PostgreSQLの成功パターン

Postmanのスナップショット：

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/0dec19f6-662a-c3fc-0a15-a1efc00e25ca.png)

Kyselyが出力したログ：

```
begin
[]
insert into "cities" ("name", "location") values ($1, $2) on conflict ("name") do update set "location" = "excluded"."location"
[ 'San Francisco', '(-194.0, 53.0)' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-01' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-02' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-03' ]
commit
[]
select "cities"."name", weather.temp_lo as "tempLo", weather.temp_hi as "tempHi", "weather"."prcp", "weather"."date" from "cities" left join "weather" on "cities"."name" = "weather"."city" order by "weather"."date"
[]
```

### SQLiteの成功パターン

Postmanのスナップショット：

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/a2e13a33-c1b0-5512-bbe7-6a56f0e2e908.png)

Kyselyが出力したログ：

```
begin
[]
insert into "cities" ("name", "location") values (?, ?) on conflict ("name") do update set "location" = "excluded"."location"
[ 'San Francisco', '(-194.0, 53.0)' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-01' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-02' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-03' ]
commit
[]
select "cities"."name", CAST(weather.temp_lo AS TEXT) as "tempLo", CAST(weather.temp_hi AS TEXT) as "tempHi", "weather"."prcp", "weather"."date" from "cities" left join "weather" on "cities"."name" = "weather"."city" order by "weather"."date"
[]
```

# まとめ

Kyselyでトランザクション（コミットとロールバック）、UPSERT、テーブルのJOINを試しました。
PostgreSQL、SQLiteともに、期待通りに動きました。