本記事で行うこと

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

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

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

前回の記事

テーブルを作る

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

PostgreSQL用

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用

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サーバーを作ってみる

参考サイト

プロジェクト作成

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

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

npm create hono@latest exercise_kysely_2

パッケージインストール

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

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のデータファイルを指定します。

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

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

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

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

begin

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

good：

commit

bad：

rollback

親テーブルへのUPSERT

INSERT ON CONFLICTを使用しました。

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

        // 親テーブルに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」という値が入ります。

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文が成功するように記述します。

        // 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とパラメータを、整形して見やすくしました。

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の時は失敗するように記述します。

        // 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

    // トランザクションが成功したら、親テーブルと子テーブルを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を、整形して見やすくしました。

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のスナップショット：

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のスナップショット：

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のスナップショット：

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のスナップショット：

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ともに、期待通りに動きました。