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

# 本記事で行うこと

以前、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】(4) Go言語用ORM「Bun」をDBファーストで使う試み（PostgreSQL使用）](https://qiita.com/kanedaq/items/ec9bd42fefc755559df6)」
- [TypeScript用ORM「Drizzle ORM」をDBファーストで使う試み（PostgreSQL編）](https://qiita.com/kanedaq/items/9305951c92100b5e1fe9)

## 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 TYPE type_composite AS (
    i INTEGER,
    j TEXT
);

CREATE TABLE foo
(
    col_serial                 SERIAL                   NOT NULL,
    col_bigserial              BIGSERIAL                NOT NULL,
    col_boolean                BOOLEAN                  NOT NULL,
    col_boolean_null           BOOLEAN,
    col_smallint               SMALLINT                 NOT NULL,
    col_smallint_null          SMALLINT,
    col_int                    INTEGER                  NOT NULL,
    col_int_null               INTEGER,
    col_bigint                 BIGINT                   NOT NULL,
    col_bigint_null            BIGINT,
    col_numeric                NUMERIC                  NOT NULL,
    col_numeric_null           NUMERIC,
    col_real                   REAL                     NOT NULL,
    col_real_null              REAL,
    col_double                 DOUBLE PRECISION         NOT NULL,
    col_double_null            DOUBLE PRECISION,
    col_text                   TEXT                     NOT NULL,
    col_text_null              TEXT,
    col_char                   CHAR(1)                  NOT NULL,
    col_char_null              CHAR(1),
    col_date                   DATE                     NOT NULL,
    col_date_null              DATE,
    col_time_with              TIME WITH TIME ZONE      NOT NULL,
    col_time_with_null         TIME WITH TIME ZONE,
    col_time                   TIME                     NOT NULL,
    col_time_null              TIME,
    col_timestamp_with         TIMESTAMP WITH TIME ZONE NOT NULL,
    col_timestamp_with_null    TIMESTAMP WITH TIME ZONE,
    col_timestamp              TIMESTAMP                NOT NULL,
    col_timestamp_null         TIMESTAMP,
    col_uuid                   UUID                     NOT NULL,
    col_uuid_null              UUID,
    col_json                   JSONB                    NOT NULL,
    col_json_null              JSONB,
    col_array                  INTEGER[]                NOT NULL,
    col_array_null             INTEGER[],
    col_composite              type_composite           NOT NULL,
    col_composite_null         type_composite,
    col_composite_array        type_composite[]         NOT NULL,
    col_composite_array_null   type_composite[]
);
```

## 上記DDLの意図

列について、私のプロジェクトで使うかもしれない型を広めに試します。
JSON型、配列型、複合型（composite type）、複合型の配列も試します。

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

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

```sql
INSERT INTO foo
(
    col_boolean,
    col_boolean_null,
    col_smallint,
    col_smallint_null,
    col_int,
    col_int_null,
    col_bigint,
    col_bigint_null,
    col_numeric,
    col_numeric_null,
    col_real,
    col_real_null,
    col_double,
    col_double_null,
    col_text,
    col_text_null,
    col_char,
    col_char_null,
    col_date,
    col_date_null,
    col_time_with,
    col_time_with_null,
    col_time,
    col_time_null,
    col_timestamp_with,
    col_timestamp_with_null,
    col_timestamp,
    col_timestamp_null,
    col_uuid,
    col_uuid_null,
    col_json,
    col_json_null,
    col_array,
    col_array_null,
    col_composite,
    col_composite_null,
    col_composite_array,
    col_composite_array_null
)
VALUES
(
    TRUE,
    NULL,
    12345,
    NULL,
    123456789,
    NULL,
    1234567890123456789,
    NULL,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    NULL,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    NULL,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    NULL,
    'text_data',
    NULL,
    'c',
    NULL,
    '2023-01-01',
    NULL,
    '12:00:00',
    NULL,
    '12:00:00',
    NULL,
    '2023-12-31 23:59:59',
    NULL,
    '2023-12-31 23:59:59',
    NULL,
    GEN_RANDOM_UUID(),
    NULL,
    '{"a": 10, "b": "Json"}'::JSONB,
    NULL,
    ARRAY[10, NULL, 30]::INTEGER[],
    NULL,
    ROW(10, 'Composite')::type_composite,
    NULL,
    ARRAY[ROW(10, NULL), NULL, ROW(NULL, 'CompositeArray')]::type_composite[],
    NULL
),
(
    FALSE,
    FALSE,
    32767,
    32767,
    987654321,
    987654321,
    876543210987654321,
    876543210987654321,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    'text_data',
    'text_data',
    'c',
    'c',
    '2023-01-01',
    '2023-01-01',
    '12:00:00',
    '12:00:00',
    '12:00:00',
    '12:00:00',
    '2023-12-31 23:59:59',
    '2023-12-31 23:59:59',
    '2023-12-31 23:59:59',
    '2023-12-31 23:59:59',
    GEN_RANDOM_UUID(),
    GEN_RANDOM_UUID(),
    '{"a": 20, "b": null}'::JSONB,
    '{"a": null, "b": "Json"}'::JSONB,
    ARRAY[10, NULL, 30]::INTEGER[],
    ARRAY[10, NULL, 30]::INTEGER[],
    ROW(20, NULL)::type_composite,
    ROW(NULL, 'Composite')::type_composite,
    ARRAY[ROW(20, 'CompositeArray'), NULL]::type_composite[],
    ARRAY[NULL, ROW(30, 'CompositeArray')]::type_composite[]
);
```

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

```sql
SELECT
    *
FROM
    foo;
```

SELECT文の結果です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/ab983215-e29a-6b78-97b7-234b1f734bb6.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/1ab1c86d-84c2-3b29-c7c6-dace03225398.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/03ff1b84-dfbe-d306-56dd-f2779ac1f7e9.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/8e3ea93c-07e6-f094-bc8f-8faae59692fd.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/1ef306d1-b453-ba40-6d77-a2633d124b8f.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/7429e9f4-098c-2f35-ae84-75690411a606.png)

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

それでは、TypeScriptからKyselyを使っていきます。

参考サイト

- https://kysely.dev/docs/getting-started?dialect=postgresql&package-manager=bun
- https://kysely-org.github.io/kysely-apidoc/classes/PostgresDialect.html

## プロジェクト作成

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

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

```sh
bunx create-hono exercise_kysely_postgres
```

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

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

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

```sh
bun install kysely pg
bun add --dev @types/pg kysely-codegen
```

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

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

```sh
DATABASE_URL=postgres://postgres:secret@localhost:5432/postgres kysely-codegen
```

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

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

## src/index.ts

メインのコードです。

```src/index.ts
import {Hono} from "hono"
import {Kysely, PostgresDialect, sql} from "kysely"
import {DB} from "kysely-codegen"
import {Pool} from "pg"

const dialect = new PostgresDialect({
    pool: new Pool({
        host: "localhost",
        port: 5432,
        user: "postgres",
        password: "secret",
        database: "postgres",
    })
})

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_boolean`.as("colBoolean"),
            sql`col_boolean_null`.as("colBooleanNull"),
            sql`col_smallint`.as("colSmallint"),
            sql`col_smallint_null`.as("colSmallintNull"),
            sql`col_int`.as("colInt"),
            sql`col_int_null`.as("colIntNull"),
            sql`col_bigint`.as("colBigint"),
            sql`col_bigint_null`.as("colBigintNull"),
            sql`col_numeric`.as("colNumeric"),
            sql`col_numeric_null`.as("colNumericNull"),
            sql`col_real`.as("colReal"),
            sql`col_real_null`.as("colRealNull"),
            sql`col_double`.as("colDouble"),
            sql`col_double_null`.as("colDoubleNull"),
            sql`col_text`.as("colText"),
            sql`col_text_null`.as("colTextNull"),
            sql`col_char`.as("colChar"),
            sql`col_char_null`.as("colCharNull"),
            sql`CAST(col_date AS TEXT)`.as("colDate"),
            sql`CAST(col_date_null AS TEXT)`.as("colDateNull"),
            sql`col_time_with`.as("colTimeWith"),
            sql`col_time_with_null`.as("colTimeWithNull"),
            sql`col_time`.as("colTime"),
            sql`col_time_null`.as("colTimeNull"),
            sql`col_timestamp_with`.as("colTimestampWith"),
            sql`col_timestamp_with_null`.as("colTimestampWithNull"),
            sql`CAST(col_timestamp AS TEXT)`.as("colTimestamp"),
            sql`CAST(col_timestamp_null AS TEXT)`.as("colTimestampNull"),
            sql`col_uuid`.as("colUuid"),
            sql`col_uuid_null`.as("colUuidNull"),
            sql`col_json`.as("colJson"),
            sql`col_json_null`.as("colJsonNull"),
            sql`col_array`.as("colArray"),
            sql`col_array_null`.as("colArrayNull"),
            sql`TO_JSONB(col_composite)`.as("colComposite"),
            sql`TO_JSONB(col_composite_null)`.as("colCompositeNull"),
            sql`TO_JSONB(col_composite_array)`.as("colCompositeArray"),
            sql`TO_JSONB(col_composite_array_null)`.as("colCompositeArrayNull"),
        ])
        .execute()
    return c.json(resultSet)
})

app.put("/api/updateFoo", async (c) => {
    await db.updateTable("foo")
        .set({
            col_json: sql`${{a: 777, b: null, c: "Hello"}}::JSONB`,
            col_json_null: sql`'{}'::JSONB`,
            col_array: sql`ARRAY[${111}, ${null}, ${333}]::INTEGER[]`,
            col_array_null: [444, 555, 666],
            col_composite: sql`ROW(${222}, ${null})::type_composite`,
            col_composite_array: sql`ARRAY[${null}, ROW(${111}, ${"Hello"})]::type_composite[]`,
        })
        .where("col_serial", "=", 1)
        .execute()

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

app.post("/api/insertFoo", async (c) => {
    await db.insertInto("foo")
        .values({
            col_boolean: false,
            col_boolean_null: null,
            col_smallint: 9999,
            col_smallint_null: null,
            col_int: 999999999,
            col_int_null: null,
            col_bigint: 999999999999999999n,
            col_bigint_null: null,
            col_numeric: "2.718281828459045235360287471352662497757247093699959574966967627724076630353547594571382178",
            col_numeric_null: null,
            col_real: 2.71828182845904523536028747135266,
            col_real_null: null,
            col_double: 2.71828182845904523536028747135266,
            col_double_null: null,
            col_text: "Hello",
            col_text_null: null,
            col_char: "z",
            col_char_null: null,
            col_date: "2024-01-21",
            col_date_null: null,
            col_time_with: "13:00",
            col_time_with_null: null,
            col_time: "13:00",
            col_time_null: null,
            col_timestamp_with: "2024-01-21 13:00:00.000000",
            col_timestamp_with_null: null,
            col_timestamp: "2024-01-21 13:00:00.000000",
            col_timestamp_null: null,
            col_uuid: sql`GEN_RANDOM_UUID()`,
            col_uuid_null: null,
            col_json: sql`${{a: 777, b: null, c: "Hello"}}::JSONB`,
            col_json_null: sql`${{}}::JSONB`,
            col_array: sql`ARRAY[${111}, ${null}, ${333}]::INTEGER[]`,
            col_array_null: [444, 555, 666],
            col_composite: sql`ROW(${222}, ${null})::type_composite`,
            col_composite_null: null,
            col_composite_array: sql`ARRAY[${null}, ROW(${111}, ${"Hello"})]::type_composite[]`,
            col_composite_array_null: null,
        })
        .execute()

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

app.delete("/api/deleteFoo", async (c) => {
    const subQuery = db
        .selectFrom("foo")
        .select(({fn}) =>
            [
                fn.max("col_serial")
            ]
        )
    await db.deleteFrom("foo")
        .where("col_serial", "=", subQuery)
        .execute()

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

export default app
```

## package.json

```package.json
{
  "scripts": {
    "dev": "bun run --hot src/index.ts"
  },
  "dependencies": {
    "hono": "^3.12.6",
    "kysely": "^0.27.2",
    "pg": "^8.11.3"
  },
  "devDependencies": {
    "@types/bun": "^1.0.0",
    "@types/pg": "^8.10.9",
    "kysely-codegen": "^0.11.0"
  }
}
```

## tsconfig.json

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

# CRUD操作

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

## SELECT文

まずはSELECT文です。

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

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

「sql``」で囲った部分は、KyselyがSQLを生成するときにそのまま使われるので、DB側の構文やファンクションを使用できます。

```typescript
    const resultSet = await db
        .selectFrom("foo")
        .select([
            sql`col_boolean`.as("colBoolean"),
            sql`col_boolean_null`.as("colBooleanNull"),
            sql`col_smallint`.as("colSmallint"),
            sql`col_smallint_null`.as("colSmallintNull"),
            sql`col_int`.as("colInt"),
            sql`col_int_null`.as("colIntNull"),
            sql`col_bigint`.as("colBigint"),
            sql`col_bigint_null`.as("colBigintNull"),
            sql`col_numeric`.as("colNumeric"),
            sql`col_numeric_null`.as("colNumericNull"),
            sql`col_real`.as("colReal"),
            sql`col_real_null`.as("colRealNull"),
            sql`col_double`.as("colDouble"),
            sql`col_double_null`.as("colDoubleNull"),
            sql`col_text`.as("colText"),
            sql`col_text_null`.as("colTextNull"),
            sql`col_char`.as("colChar"),
            sql`col_char_null`.as("colCharNull"),
            sql`CAST(col_date AS TEXT)`.as("colDate"),
            sql`CAST(col_date_null AS TEXT)`.as("colDateNull"),
            sql`col_time_with`.as("colTimeWith"),
            sql`col_time_with_null`.as("colTimeWithNull"),
            sql`col_time`.as("colTime"),
            sql`col_time_null`.as("colTimeNull"),
            sql`col_timestamp_with`.as("colTimestampWith"),
            sql`col_timestamp_with_null`.as("colTimestampWithNull"),
            sql`CAST(col_timestamp AS TEXT)`.as("colTimestamp"),
            sql`CAST(col_timestamp_null AS TEXT)`.as("colTimestampNull"),
            sql`col_uuid`.as("colUuid"),
            sql`col_uuid_null`.as("colUuidNull"),
            sql`col_json`.as("colJson"),
            sql`col_json_null`.as("colJsonNull"),
            sql`col_array`.as("colArray"),
            sql`col_array_null`.as("colArrayNull"),
            sql`TO_JSONB(col_composite)`.as("colComposite"),
            sql`TO_JSONB(col_composite_null)`.as("colCompositeNull"),
            sql`TO_JSONB(col_composite_array)`.as("colCompositeArray"),
            sql`TO_JSONB(col_composite_array_null)`.as("colCompositeArrayNull"),
        ])
        .execute()
    return c.json(resultSet)
```

- DBでBIGSERIAL型、BIGINT型、NUMERIC型で定義された列は、TypeScriptのnumber型で受け取ると誤差が生じることがあるので、TEXT型にキャストしました。
- DBでタイムゾーンなしの日付・時間型で定義された列は、TypeScriptで受け取ったらタイムゾーン付きになる現象が起きたので、TEXT型にキャストしてそれを防ぎました。
- 複合型や複合型の配列は、TO_JSONB()を使ってJSON型を経由すれば、TypeScript側で良い感じに取り出せることがわかりました。

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

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

```sql
select
    col_boolean as "colBoolean",
    col_boolean_null as "colBooleanNull",
    col_smallint as "colSmallint",
    col_smallint_null as "colSmallintNull",
    col_int as "colInt",
    col_int_null as "colIntNull",
    col_bigint as "colBigint",
    col_bigint_null as "colBigintNull",
    col_numeric as "colNumeric",
    col_numeric_null as "colNumericNull",
    col_real as "colReal",
    col_real_null as "colRealNull",
    col_double as "colDouble",
    col_double_null as "colDoubleNull",
    col_text as "colText",
    col_text_null as "colTextNull",
    col_char as "colChar",
    col_char_null as "colCharNull",
    CAST(col_date AS TEXT) as "colDate",
    CAST(col_date_null AS TEXT) as "colDateNull",
    col_time_with as "colTimeWith",
    col_time_with_null as "colTimeWithNull",
    col_time as "colTime",
    col_time_null as "colTimeNull",
    col_timestamp_with as "colTimestampWith",
    col_timestamp_with_null as "colTimestampWithNull",
    CAST(col_timestamp AS TEXT) as "colTimestamp",
    CAST(col_timestamp_null AS TEXT) as "colTimestampNull",
    col_uuid as "colUuid",
    col_uuid_null as "colUuidNull",
    col_json as "colJson",
    col_json_null as "colJsonNull",
    col_array as "colArray",
    col_array_null as "colArrayNull",
    TO_JSONB(col_composite) as "colComposite",
    TO_JSONB(col_composite_null) as "colCompositeNull",
    TO_JSONB(col_composite_array) as "colCompositeArray",
    TO_JSONB(col_composite_array_null) as "colCompositeArrayNull"
from
    "foo"
```

パラメータ

```
[]
```

### selectFoo APIの実行結果

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

```json
[
    {
        "colBoolean": true,
        "colBooleanNull": null,
        "colSmallint": 12345,
        "colSmallintNull": null,
        "colInt": 123456789,
        "colIntNull": null,
        "colBigint": "1234567890123456789",
        "colBigintNull": null,
        "colNumeric": "3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117",
        "colNumericNull": null,
        "colReal": 3.1415927,
        "colRealNull": null,
        "colDouble": 3.141592653589793,
        "colDoubleNull": null,
        "colText": "text_data",
        "colTextNull": null,
        "colChar": "c",
        "colCharNull": null,
        "colDate": "2023-01-01",
        "colDateNull": null,
        "colTimeWith": "12:00:00+00",
        "colTimeWithNull": null,
        "colTime": "12:00:00",
        "colTimeNull": null,
        "colTimestampWith": "2023-12-31T23:59:59.000Z",
        "colTimestampWithNull": null,
        "colTimestamp": "2023-12-31 23:59:59",
        "colTimestampNull": null,
        "colUuid": "6c95c95f-d9b5-4657-a9ec-f92b61ece513",
        "colUuidNull": null,
        "colJson": {
            "a": 10,
            "b": "Json"
        },
        "colJsonNull": null,
        "colArray": [
            10,
            null,
            30
        ],
        "colArrayNull": null,
        "colComposite": {
            "i": 10,
            "j": "Composite"
        },
        "colCompositeNull": null,
        "colCompositeArray": [
            {
                "i": 10,
                "j": null
            },
            null,
            {
                "i": null,
                "j": "CompositeArray"
            }
        ],
        "colCompositeArrayNull": null
    },
    {
        "colBoolean": false,
        "colBooleanNull": false,
        "colSmallint": 32767,
        "colSmallintNull": 32767,
        "colInt": 987654321,
        "colIntNull": 987654321,
        "colBigint": "876543210987654321",
        "colBigintNull": "876543210987654321",
        "colNumeric": "3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117",
        "colNumericNull": "3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117",
        "colReal": 3.1415927,
        "colRealNull": 3.1415927,
        "colDouble": 3.141592653589793,
        "colDoubleNull": 3.141592653589793,
        "colText": "text_data",
        "colTextNull": "text_data",
        "colChar": "c",
        "colCharNull": "c",
        "colDate": "2023-01-01",
        "colDateNull": "2023-01-01",
        "colTimeWith": "12:00:00+00",
        "colTimeWithNull": "12:00:00+00",
        "colTime": "12:00:00",
        "colTimeNull": "12:00:00",
        "colTimestampWith": "2023-12-31T23:59:59.000Z",
        "colTimestampWithNull": "2023-12-31T23:59:59.000Z",
        "colTimestamp": "2023-12-31 23:59:59",
        "colTimestampNull": "2023-12-31 23:59:59",
        "colUuid": "929a4811-68d9-4853-9d65-7880c0789cd1",
        "colUuidNull": "ca5f7ecf-fc46-438b-aa3e-3a2464c067f9",
        "colJson": {
            "a": 20,
            "b": null
        },
        "colJsonNull": {
            "a": null,
            "b": "Json"
        },
        "colArray": [
            10,
            null,
            30
        ],
        "colArrayNull": [
            10,
            null,
            30
        ],
        "colComposite": {
            "i": 20,
            "j": null
        },
        "colCompositeNull": {
            "i": null,
            "j": "Composite"
        },
        "colCompositeArray": [
            {
                "i": 20,
                "j": "CompositeArray"
            },
            null
        ],
        "colCompositeArrayNull": [
            null,
            {
                "i": 30,
                "j": "CompositeArray"
            }
        ]
    }
]
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/bf202efa-75e4-4486-466e-a6dd662e17db.png)

## UPDATE文

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

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

ハンドラのクエリー部分を見ます。
col_serial列が1の行について、JSON型、配列型、複合型、複合型の配列の列をそれぞれ更新する内容です。

```typescript
    await db.updateTable("foo")
        .set({
            col_json: sql`${{a: 777, b: null, c: "Hello"}}::JSONB`,
            col_json_null: sql`'{}'::JSONB`,
            col_array: sql`ARRAY[${111}, ${null}, ${333}]::INTEGER[]`,
            col_array_null: [444, 555, 666],
            col_composite: sql`ROW(${222}, ${null})::type_composite`,
            col_composite_array: sql`ARRAY[${null}, ROW(${111}, ${"Hello"})]::type_composite[]`,
        })
        .where("col_serial", "=", 1)
        .execute()
```

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

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

```sql
update
    "foo"
set
    "col_json" = $1::JSONB,
    "col_json_null" = '{}'::JSONB,
    "col_array" = ARRAY[$2, $3, $4]::INTEGER[],
    "col_array_null" = $5,
    "col_composite" = ROW($6, $7)::type_composite,
    "col_composite_array" = ARRAY[$8, ROW($9, $10)]::type_composite[]
where
    "col_serial" = $11
```

パラメータ

```
[
    { a: 777, b: null, c: "Hello", },
    111,
    null,
    333,
    [ 444, 555, 666 ],
    222,
    null,
    null,
    111,
    "Hello",
    1
]
```

### updateFoo APIの実行結果

updateFoo APIを呼び出した結果を見てみます。

```sql
SELECT
    col_json,
    col_json_null,
    col_array,
    col_array_null,
    col_composite,
    col_composite_array
FROM
    foo
WHERE
    col_serial = 1;
```

6つの列が意図した値に更新されました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/39cb975f-310c-2743-2776-c84d734b8193.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/fcc446cc-c793-e7bc-d02e-64459631ccdf.png)

## INSERT文

次はINSERT文を見ていきます。
1行挿入する内容です。

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

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

```typescript
    await db.insertInto("foo")
        .values({
            col_boolean: false,
            col_boolean_null: null,
            col_smallint: 9999,
            col_smallint_null: null,
            col_int: 999999999,
            col_int_null: null,
            col_bigint: 999999999999999999n,
            col_bigint_null: null,
            col_numeric: "2.718281828459045235360287471352662497757247093699959574966967627724076630353547594571382178",
            col_numeric_null: null,
            col_real: 2.71828182845904523536028747135266,
            col_real_null: null,
            col_double: 2.71828182845904523536028747135266,
            col_double_null: null,
            col_text: "Hello",
            col_text_null: null,
            col_char: "z",
            col_char_null: null,
            col_date: "2024-01-21",
            col_date_null: null,
            col_time_with: "13:00",
            col_time_with_null: null,
            col_time: "13:00",
            col_time_null: null,
            col_timestamp_with: "2024-01-21 13:00:00.000000",
            col_timestamp_with_null: null,
            col_timestamp: "2024-01-21 13:00:00.000000",
            col_timestamp_null: null,
            col_uuid: sql`GEN_RANDOM_UUID()`,
            col_uuid_null: null,
            col_json: sql`${{a: 777, b: null, c: "Hello"}}::JSONB`,
            col_json_null: sql`${{}}::JSONB`,
            col_array: sql`ARRAY[${111}, ${null}, ${333}]::INTEGER[]`,
            col_array_null: [444, 555, 666],
            col_composite: sql`ROW(${222}, ${null})::type_composite`,
            col_composite_null: null,
            col_composite_array: sql`ARRAY[${null}, ROW(${111}, ${"Hello"})]::type_composite[]`,
            col_composite_array_null: null,
        })
        .execute()
```

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

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

```sql
insert into "foo" (
    "col_boolean",
    "col_boolean_null",
    "col_smallint",
    "col_smallint_null",
    "col_int",
    "col_int_null",
    "col_bigint",
    "col_bigint_null",
    "col_numeric",
    "col_numeric_null",
    "col_real",
    "col_real_null",
    "col_double",
    "col_double_null",
    "col_text",
    "col_text_null",
    "col_char",
    "col_char_null",
    "col_date",
    "col_date_null",
    "col_time_with",
    "col_time_with_null",
    "col_time",
    "col_time_null",
    "col_timestamp_with",
    "col_timestamp_with_null",
    "col_timestamp",
    "col_timestamp_null",
    "col_uuid",
    "col_uuid_null",
    "col_json",
    "col_json_null",
    "col_array",
    "col_array_null",
    "col_composite",
    "col_composite_null",
    "col_composite_array",
    "col_composite_array_null"
)
values (
    $1,
    $2,
    $3,
    $4,
    $5,
    $6,
    $7,
    $8,
    $9,
    $10,
    $11,
    $12,
    $13,
    $14,
    $15,
    $16,
    $17,
    $18,
    $19,
    $20,
    $21,
    $22,
    $23,
    $24,
    $25,
    $26,
    $27,
    $28,
    GEN_RANDOM_UUID(),
    $29,
    $30::JSONB,
    $31::JSONB,
    ARRAY[$32, $33, $34]::INTEGER[],
    $35,
    ROW($36, $37)::type_composite,
    $38,
    ARRAY[$39, ROW($40, $41)]::type_composite[],
    $42
)
```

パラメータ

```
[
    false,
    null,
    9999,
    null,
    999999999,
    null,
    999999999999999999n,
    null,
    "2.718281828459045235360287471352662497757247093699959574966967627724076630353547594571382178",
    null,
    2.718281828459045,
    null,
    2.718281828459045,
    null,
    "Hello",
    null,
    "z",
    null,
    "2024-01-21",
    null,
    "13:00",
    null,
    "13:00",
    null,
    "2024-01-21 13:00:00.000000",
    null,
    "2024-01-21 13:00:00.000000",
    null,
    null,
    { a: 777, b: null, c: "Hello", },
    {},
    111,
    null,
    333,
    [ 444, 555, 666 ],
    222,
    null,
    null,
    null,
    111,
    "Hello",
    null
]
```

### insertFoo APIの実行結果

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/9402d9e0-1b86-798c-2602-4a255c00467c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/a1a621b9-2888-b79d-23c2-ce8738f7e3c7.png)

## DELETE文

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

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

ハンドラのクエリー部分を見ます。
col_serial列==最大値の行を削除する内容です。

```typescript
    const subQuery = db
        .selectFrom("foo")
        .select(({fn}) =>
            [
                fn.max("col_serial")
            ]
        )
    await db.deleteFrom("foo")
        .where("col_serial", "=", subQuery)
        .execute()
```

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

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

```sql
delete from
    "foo"
where
    "col_serial" = (
        select
            max("col_serial")
        from
            "foo"
    )
```

パラメータ

```
[]
```

### deleteFoo APIの実行結果

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/58dd0b59-bb45-5430-1747-6b9bbd74a152.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/659f8831-3efe-9bfd-b62b-96aab0b6bfde.png)

# まとめ

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

私が嬉しかったのは、PostgreSQLの複合型（composite type）の挿入・更新ができたことです。
Drizzle ORMを試したときは、Raw SQLを使う以外の方法が見つからなかったので、私の心はKyselyに傾いています。