TypeScript用クエリービルダー「Kysely」をDBファーストで使う試み（SQLite編）

# 本記事で行うこと

以前、Go言語用のORM「[Bun](https://bun.uptrace.dev/)」やTypesctipr用のORM「[Drizzle ORM](https://orm.drizzle.team/)」を、データベースファーストで使えるか試しました。
それと同様に、TypeScript用のクエリービルダー「[Kysely](https://kysely.dev/)」をデータベースファーストで使えるか試します。
Kyselyは「ORMじゃなくてクエリービルダー」だそうですので、データベースファーストで使えると思いますが、使用感はどうでしょうか。

Webフレームワークは[Hono](https://hono.dev/)を使用します。

## 関連記事
- [チーム開発参加の記録【2023-10～2024-03】(1) Go言語用ORM「Bun」をDBファーストで使う試み（SQLite使用）](https://qiita.com/kanedaq/items/97ca682602b626d5d717)」
- [TypeScript用ORM「Drizzle ORM」をDBファーストで使う試み（SQLite編）](https://qiita.com/kanedaq/items/2e5b8e2911ea8211761b)

## DBファーストで開発したい理由

理由は[以前書いた記事](https://qiita.com/kanedaq/items/97ca682602b626d5d717#db%E3%83%95%E3%82%A1%E3%83%BC%E3%82%B9%E3%83%88%E3%81%A7%E9%96%8B%E7%99%BA%E3%81%97%E3%81%9F%E3%81%84)と全く同じです。

# テーブルを作る

では作業に移ります。
DBファーストですので、まずDDLを書きます。

```sql
CREATE TABLE foo (
    col_text           TEXT    NOT NULL,
    col_text_null      TEXT,
    col_int            INTEGER NOT NULL,
    col_int_null       INTEGER,
    col_real           REAL    NOT NULL,
    col_real_null      REAL,
    col_timestamp      REAL    NOT NULL,
    col_timestamp_null REAL
) STRICT;
```

## 上記DDLの意図

SQLite3のSTRICTテーブルで使える型は以下に限られています。

- INT
- INTEGER
- REAL
- TEXT
- BLOB
- ANY

このうち、私のプロジェクトで使う可能性が高い以下の型を試します。

- TEXT
- INTEGER
- REAL

加えて、日付・時刻も試します。
日付・時刻は、以下の型で保存できます。

- TEXT：ISO-8601形式
- REAL：ユリウス通日
- INTEGER：1970年からの秒数

本記事では日付・時刻をREAL型で保存することにしました。理由は、

- INTEGER型で持つと精度が秒になるが、REAL型やTEXT型は秒の小数の精度まで持てる
- TEXT型は保存フォーマットに気を遣う必要があって面倒だし、バイト数もREAL型より大きい

以上が上記DDLの意図です。

# テスト用データをINSERTする

作成したfooテーブルに、以下のSQLでテスト用データを2件INSERTしました。

```sql
INSERT INTO foo
(
    col_text,
    col_text_null,
    col_int,
    col_int_null,
    col_real,
    col_real_null,
    col_timestamp,
    col_timestamp_null
)
VALUES
(
    'text_data',
    NULL,
    1234567890123456789,
    NULL,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    NULL,
    JULIANDAY('now'),
    NULL
),
(
    'text_data',
    'text_data',
    1234567890123456789,
    1234567890123456789,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    JULIANDAY('now'),
    JULIANDAY('now')
);
```

INSERTしたら、fooテーブルにSELECT文を発行してみます。

```sql
SELECT
    col_text,
    col_text_null,
    col_int,
    col_int_null,
    col_real,
    col_real_null,
    STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp) AS col_timestamp,
    STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp_null) AS col_timestamp_null
FROM
    foo;
```

SELECT文の結果です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/86d1bc87-c57a-5a8f-7232-978c33d04ded.png)

# HonoとKyselyを使って、fooテーブルにCRUD操作するWeb APIサーバーを作ってみる

それでは、TypeScriptからKyselyを使っていきます。
参考サイト： https://kysely.dev/docs/getting-started?dialect=sqlite&package-manager=npm

## プロジェクト作成

プロジェクト名は「exercise_kysely_sqlite」にしました。
どのテンプレートを使うか聞かれたので、最初は「bun」を選択しましたが、動かせませんでしたので「nodejs」で作り直しました。
参考ページ： https://hono.dev/getting-started/basic

```sh
npm create hono@latest exercise_kysely_sqlite
```

### 本記事執筆時点で、bunで動かせなかった理由

[公式ページ](https://kysely.dev/docs/getting-started?dialect=sqlite&package-manager=bun)に以下の記述がありました。

```
Driver unsupported

Kysely's built-in SQLite dialect does not work in Bun because the driver library it uses, "better-sqlite3", doesn't. You have to use a community SQLite dialect that works in Bun, or implement your own.
```

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

プロジェクトディレクトリ直下で、以下を実行しました。

参考ページ
- https://kysely.dev/docs/getting-started?package-manager=npm
- https://github.com/RobinBlomberg/kysely-codegen

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

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

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

```sh
DATABASE_URL=/home/user/DataGripProjects/sqlite/exercise.sqlite3 kysely-codegen
```

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

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

## src/index.ts

メインのコードです。

```src/index.ts
import {serve} from '@hono/node-server'
import {Hono} from 'hono'
import {Kysely, SqliteDialect, sql} from "kysely"
import {DB} from "kysely-codegen"
const SQLite = require("better-sqlite3");

const dialect = new SqliteDialect({
    database: new SQLite("/home/user/DataGripProjects/sqlite/exercise.sqlite3"),
})

export const db = new Kysely<DB>({
    dialect,
    log: (event) => {
        if (event.level === "query") {
            console.log(event.query.sql)
            console.log(event.query.parameters)
        }
    }
})

const app = new Hono()
app.notFound((c) => c.json({message: "Not Found", ok: false}, 404))

app.get("/api/selectFoo", async (c) => {
    const resultSet = await db
        .selectFrom("foo")
        .select([
            sql`col_text`.as("colText"),
            sql`col_text_null`.as("colTextNull"),
            sql`CAST(col_int AS TEXT)`.as("colInt"),
            sql`CAST(col_int_null AS TEXT)`.as("colIntNull"),
            sql`col_real`.as("colReal"),
            sql`col_real_null`.as("colRealNull"),
            sql`STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp)`.as("colTimestamp"),
            sql`STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp_null)`.as("colTimestampNull"),
        ])
        .execute()
    return c.json(resultSet)
})

app.put("/api/updateFoo", async (c) => {
    await db.updateTable("foo")
        .set({
            col_text_null: "ABC",
        })
        .where("col_text_null", "is", null)
        .execute()

    return c.json({message: "ok", ok: true})
})

app.post("/api/insertFoo", async (c) => {
    await db.insertInto("foo")
        .values({
            col_text: "XYZ",
            col_text_null: null,
            col_int: 123,
            col_int_null: null,
            col_real: 3.14,
            col_real_null: null,
            col_timestamp: sql`JULIANDAY('now')`,
            col_timestamp_null: null
        })
        .execute()

    return c.json({message: "ok", ok: true})
})

app.delete("/api/deleteFoo", async (c) => {
    await db.deleteFrom("foo")
        .where("col_text", "=", "XYZ")
        .execute()

    return c.json({message: "ok", ok: true})
})

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"
  },
  "devDependencies": {
    "@types/better-sqlite3": "^7.6.8",
    "kysely-codegen": "^0.11.0",
    "tsx": "^3.12.2"
  }
}
```

## tsconfig.json

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

# CRUD操作

それでは、CRUD操作について一つ一つ見ていきます。

## SELECT文

まずはSELECT文です。

### SELECT用ハンドラの中身

ハンドラの中身を見ていきます。
クエリービルダーを謳うだけあって、SQLが透けて見えますね。
私みたいにSQLを書きたい人には、嬉しいライブラリだと思いました。

「sql``」で囲った部分は、KyselyがSQLを生成するときにそのまま使われるので、DB側の構文やファンクションを使用できます。
DBでINT型で定義された列は、TypeScriptのnumber型で受け取ると、桁数が多い場合に丸め誤差が生じるので、TEXT型にキャストしました。

```typescript
    const resultSet = await db
        .selectFrom("foo")
        .select([
            sql`col_text`.as("colText"),
            sql`col_text_null`.as("colTextNull"),
            sql`CAST(col_int AS TEXT)`.as("colInt"),
            sql`CAST(col_int_null AS TEXT)`.as("colIntNull"),
            sql`col_real`.as("colReal"),
            sql`col_real_null`.as("colRealNull"),
            sql`STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp)`.as("colTimestamp"),
            sql`STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp_null)`.as("colTimestampNull"),
        ])
        .execute()
    return c.json(resultSet)
```

### Kyselyが生成したSQLとパラメータ

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

```sql
select
    col_text as "colText",
    col_text_null as "colTextNull",
    CAST(col_int AS TEXT) as "colInt",
    CAST(col_int_null AS TEXT) as "colIntNull",
    col_real as "colReal",
    col_real_null as "colRealNull",
    STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp) as "colTimestamp",
    STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp_null) as "colTimestampNull"
from
    "foo"
```

```
[]
```

### selectFoo APIの実行結果

selectFoo APIを呼び出した結果です。
レスポンスは、ハンドラの最後の一行で、結果表をそのままjsonにして返しているだけですが、きれいなレスポンスが生成できていますね。
シンプルなコードできれいなレスポンスを返せているのはポイントが高いです。

```json
[
    {
        "colText": "text_data",
        "colTextNull": null,
        "colInt": "1234567890123456789",
        "colIntNull": null,
        "colReal": 3.141592653589793,
        "colRealNull": null,
        "colTimestamp": "2024-01-21T21:40:48.313Z",
        "colTimestampNull": null
    },
    {
        "colText": "text_data",
        "colTextNull": "text_data",
        "colInt": "1234567890123456789",
        "colIntNull": "1234567890123456789",
        "colReal": 3.141592653589793,
        "colRealNull": 3.141592653589793,
        "colTimestamp": "2024-01-21T21:40:48.313Z",
        "colTimestampNull": "2024-01-21T21:40:48.313Z"
    }
]
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/3a0a4919-a648-7c7d-c62c-4a5640576266.png)

## UPDATE文

次はUPDATE文を見ていきます。

### UPDATE用ハンドラの中身

ハンドラのクエリー部分を見ます。
col_text_null列がNULLの行について、同じ列を"ABC"に更新する内容です。

```typescript
    await db.updateTable("foo")
        .set({
            col_text_null: "ABC",
        })
        .where("col_text_null", "is", null)
        .execute()
```

### Kyselyが生成したSQLとパラメータ

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

```sql
update
    "foo"
set
    "col_text_null" = ?
where
    "col_text_null" is null
```

```
[ 'ABC' ]
```

### updateFoo APIの実行結果

updateFoo APIを呼び出した結果です。
col_text_nullがNULLだった行の意図した列が、意図した値に更新されました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/09d81fa4-f711-b9b6-2d13-e36f4d91737c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/022ad607-56fa-e374-9693-68f80aa654ef.png)

## INSERT文

次はINSERT文を見ていきます。
col_text列が"XYZ"の行を挿入する内容です。

### INSERT用ハンドラの中身

ハンドラのクエリー部分を見ます。

```typescript
    await db.insertInto("foo")
        .values({
            col_text: "XYZ",
            col_text_null: null,
            col_int: 123,
            col_int_null: null,
            col_real: 3.14,
            col_real_null: null,
            col_timestamp: sql`JULIANDAY('now')`,
            col_timestamp_null: null
        })
        .execute()
```

### Kyselyが生成したSQLとパラメータ

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

```sql
insert into "foo"(
    "col_text",
    "col_text_null",
    "col_int",
    "col_int_null",
    "col_real",
    "col_real_null",
    "col_timestamp",
    "col_timestamp_null"
)
values (
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    JULIANDAY('now'),
    ?
)
```

```
[
    'XYZ',
    null,
    123,
    null,
    3.14,
    null,
    null
]
```
### insertFoo APIの実行結果

insertFoo APIを呼び出した結果です。
3行目に、意図した行がINSERTされたことを確認できました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/17402ffa-9f68-70b4-a40b-34e010ec5aeb.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/7bc37cf6-f5b4-a8b2-d9c4-14ba1cf6af0b.png)

## DELETE文

次はDELETE文を見ていきます。

### DELETE用ハンドラの中身

ハンドラのクエリー部分を見ます。
col_text列が"XYZ"の行を削除する内容です。

```typescript
    await db.deleteFrom("foo")
        .where("col_text", "=", "XYZ")
        .execute()
```

### Kyselyが生成したSQLとパラメータ

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

```sql
delete from
    "foo"
where
    "col_text" = ?
```

```
[ 'XYZ' ]
```

### deleteFoo APIの実行結果

deleteFoo APIを呼び出した結果です。
意図した行（3行目）が正常に削除されました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/f005da5f-63e3-33e5-0762-3a4b08070079.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/8fb88b94-4740-844c-0da3-56a840022fff.png)

# まとめ

KyselyをDBファーストで使ってみたところ、問題なく動いてくれました。
SQLが透けて見える書き方ができて、SQLを書きたい私には嬉しいライブラリでした。
以前の記事で試したGo言語用ORMのBunと、割と似た感覚で使えそうです。