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

Last updated at 2024-01-21Posted at 2024-01-21

本記事で行うこと

以前、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

{
  "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

{
  "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と、割と似た感覚で使えそうです。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

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

本記事で行うこと

関連記事

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

テーブルを作る

上記DDLの意図

テスト用データをINSERTする

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

プロジェクト作成

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

パッケージインストール

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

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

src/index.ts

package.json

tsconfig.json

CRUD操作

SELECT文

SELECT用ハンドラの中身

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

selectFoo APIの実行結果

UPDATE文

UPDATE用ハンドラの中身

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

updateFoo APIの実行結果

INSERT文

INSERT用ハンドラの中身

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

insertFoo APIの実行結果

DELETE文

DELETE用ハンドラの中身

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

deleteFoo APIの実行結果

まとめ