本記事で行うこと

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

WebフレームワークはHonoを使用します。

関連記事

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

理由は以前書いた記事と全く同じです。

テーブルを作る

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

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しました。

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文を発行してみます。

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文の結果です。

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

npm create hono@latest exercise_kysely_sqlite

本記事執筆時点で、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.

パッケージインストール

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

参考ページ

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

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

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

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型にキャストしました。

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

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にして返しているだけですが、きれいなレスポンスが生成できていますね。
シンプルなコードできれいなレスポンスを返せているのはポイントが高いです。

[
    {
        "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"
    }
]

UPDATE文

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

UPDATE用ハンドラの中身

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

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

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

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

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

[ 'ABC' ]

updateFoo APIの実行結果

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

INSERT文

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

INSERT用ハンドラの中身

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

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

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されたことを確認できました。

DELETE文

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

DELETE用ハンドラの中身

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

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

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

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

delete from
    "foo"
where
    "col_text" = ?

[ 'XYZ' ]

deleteFoo APIの実行結果

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

まとめ

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