TypescriptのSQLクエリビルダーのkyselyが快適

## はじめに

Node.jsのサーバーからデータベースにアクセスする(SQLを投げる)ことって、`Nuxt.js`とか使ってたらあるあるですよね。みなさんどうしているのでしょうか。
世の中には`ORM`という、SQL文を直接書かなくてもなんかいいかんじにSQLを発行してくれるものがあるようですが(有名どころは`Prisma`や`TypeORM`)、SQLに慣れ親しんだ側からすると逆に学習コストがあるなぁと感じていました。
あと、せっかくだからいいかんじにTypescriptの型も効いてくれるとうれしい！
`Knex`でもなんとなくはできますがもう一歩感。
ということで`kysely`を使ってみました。

参考までに、2023/08/13現在のバージョンとスター数。ORMの方が強い。

|リポジトリ|バージョン(最新のタグ)|スター数|
|---|---|---|
|prisma/prisma|5.1.1|33.2k|
|typeorm/typeorm|0.3.17|31.9k|
|knex/knex|2.5.1|17.9k|
|kysely-org/kysely|0.26.1|7k|

## 使ってみる

まず、環境。

|ツール|バージョン|
|---|---|
|Node.js|v18.17.0|
|npm|9.6.7|
|PostgreSQL|15.3|

さくっと準備します。`npm init`してちょっぴり編集した`package.json`は以下。

```json:package.json
{
  "name": "kysely-test",
  "version": "0.1.0",
  "private": true,
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC"
}
```

必要なパッケージを入れます。
まえ触ったとき`ts-node`だとうまく動かなかったので`esbuild-register`で。

```bash
npm install kysely esbuild-register pg
npm install --save-dev @types/pg
```

入ったバージョン(package.jsonの差分)は以下。

```diff:package.json
-   "license": "ISC"
+   "license": "ISC",
+   "dependencies": {
+     "esbuild-register": "^3.4.2",
+     "kysely": "^0.26.1",
+     "pg": "^8.11.2"
+   },
+   "devDependencies": {
+     "@types/pg": "^8.10.2"
+   }
```

[公式のGetting started](https://kysely.dev/docs/getting-started?dialect=postgresql) を参考に、ファイルをつくります。

まず、DBスキーマ定義から。
簡単なkyselyのジェネリクス型の説明
- ColumnType\<T, U = T, V = T\>: selectの型がT、insertの型がU、updateの型がV
- Generated\<T\>: insertでoptionalになる(Generated\<T\> = ColumnType\<T, T | undefined, T\>)
- Insertable, Selectable, Updatable: それぞれテーブル定義から対応する型をひっぱってくる

```ts:src/schema/type.ts
import { ColumnType, Generated, Insertable, Selectable, Updateable } from 'kysely';

/** データベース全体 */
export interface Database {
  /** ユーザーテーブル */
  user: UserTable;
  /** 投稿テーブル */
  post: PostTable;
};

/** ユーザーテーブル */
export interface UserTable {
  /** ユーザーID */
  id: Generated<number>;
  /** ユーザー氏名 */
  name: string;
  /** 性別 */
  gender: 'man' | 'woman' | 'other';
  /** 自己紹介 */
  introduction: string | null;
};
export type User = Selectable<UserTable>;
export type NewUser = Insertable<UserTable>;
export type UserUpdate = Updateable<UserTable>;

/** 投稿テーブル */
export interface PostTable {
  /** 投稿ID */
  id: Generated<number>;
  /** タイトル */
  title: string;
  /** 内容 */
  content: string;
  /** 作成日時 */
  created_at: ColumnType<Date, never, never>;
  /** 作成者ID */
  created_by: number;
  /** 更新日時 */
  updated_at: ColumnType<Date, never, Date>;
  /** 更新者ID */
  updated_by: number;
};
export type Post = Selectable<PostTable>;
export type NewPost = Insertable<PostTable>;
export type PostUpdate = Updateable<PostTable>;
```

次にDB接続。
SQLログがないとデバッグしにくいので、`Kysely`インスタンスの生成時にログ関数を指定しておきます。

```ts:src/core/db.ts
import { Database } from '../schema/type';
import { Pool } from 'pg';
import { Kysely, PostgresDialect } from 'kysely';

const dialect = new PostgresDialect({
  pool: new Pool({
    database: 'kysely-test',
    host: 'localhost',
    user: 'postgres',
    max: 10,
  }),
});

const db = new Kysely<Database>({
  dialect,
  log: (event) => {
    if (event.level == 'query') {
      const q = event.query;
      const time = Math.round(event.queryDurationMillis * 100) / 100;
      console.log(`\u001b[34mkysely:sql\u001b[0m [${q.sql}] parameters: [${q.parameters}] time: ${time}`);
    }
  },
});
export default db;
```

マイグレーション用のファイルもつくります。
`Kysely<any>`になっていますが、「マイグレーションのファイルは現在のソースコードに依存させないでね」と書いてありました。
テーブル型定義とかから自動生成でもできれば楽ですが、とりあえず手書きです。
[型生成](https://kysely.dev/docs/generating-types) のページによると`prisma-kysely`あたりが便利かもしれません。

```ts:src/migrations/001_init.ts
import { Kysely, sql } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('user')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('name', 'varchar(100)', (col) => col.notNull())
    .addColumn('gender', 'varchar(20)', (col) => col.notNull())
    .addColumn('introduction', 'text')
    .execute();
  await db.schema
    .createTable('post')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('title', 'varchar(100)', (col) => col.notNull())
    .addColumn('content', 'text', (col) => col.notNull())
    .addColumn('created_at', 'timestamptz', (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn('created_by', 'integer', (col) => col.references('user.id').onDelete('cascade').notNull())
    .addColumn('updated_at', 'timestamptz', (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn('updated_by', 'integer', (col) => col.references('user.id').onDelete('cascade').notNull())
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('post').execute();
  await db.schema.dropTable('user').execute();
}
```

kyselyでデータベースまではつくれなさそうなので、コマンドでつくります。

```
$ psql -U postgres
psql (15.3)

postgres=# create database "kysely-test" owner postgres;
CREATE DATABASE
```

マイグレーション実行用ファイルをつくります。
[公式ドキュメント](https://kysely.dev/docs/migrations) とほぼ同じです。(`db`をimportしただけ)

```ts:src/core/migrate.ts
import * as path from 'path';
import { promises as fs } from 'fs';
import { Migrator, FileMigrationProvider } from 'kysely';
import db from './core/db';

async function migrateToLatest() {
  const migrator = new Migrator({
    db,
    provider: new FileMigrationProvider({
      fs,
      path,
      migrationFolder: path.join(__dirname, '../migrations'),
    }),
  });

  const { error, results } = await migrator.migrateToLatest();

  results?.forEach((it) => {
    if (it.status === 'Success') {
      console.log(`migration "${it.migrationName}" was executed successfully.`);
    } else if (it.status === 'Error') {
      console.error(`failed to execute migration "${it.migrationName}".`);
    }
  })

  if (error) {
    console.error('failed to migrate');
    console.error(error);
    process.exit(1);
  }

  await db.destroy();
}

migrateToLatest();
```

package.jsonにコマンドを追加します。

```diff:package.json
    "scripts": {
+    "migrate": "node -r esbuild-register src/migrate.ts",
      "test": "echo \"Error: no test specified\" && exit 1"
    },
```

`npm run migrate`でテーブルを作成できました。マイグレーション管理用のテーブルもできています。ここは`knex`と一緒ですね。

```
postgres=# \c kysely-test
データベース"kysely-test"にユーザー"postgres"として接続しました。
kysely-test=# \d
                     リレーション一覧
 スキーマ |         名前          |   タイプ   |  所有者
----------+-----------------------+------------+----------
 public   | kysely_migration      | テーブル   | postgres
 public   | kysely_migration_lock | テーブル   | postgres
 public   | post                  | テーブル   | postgres
 public   | post_id_seq           | シーケンス | postgres
 public   | user                  | テーブル   | postgres
 public   | user_id_seq           | シーケンス | postgres
(6 行)


kysely-test=# \d post
                                        テーブル"public.post"
     列     |          タイプ          | 照合順序 | Null 値を許容 |            デフォルト
------------+--------------------------+----------+---------------+----------------------------------
 id         | integer                  |          | not null      | nextval('post_id_seq'::regclass)
 title      | character varying(100)   |          | not null      |
 content    | text                     |          | not null      |
 created_at | timestamp with time zone |          | not null      | now()
 created_by | integer                  |          | not null      |
 updated_at | timestamp with time zone |          | not null      | now()
 updated_by | integer                  |          | not null      |
インデックス:
    "post_pkey" PRIMARY KEY, btree (id)
外部キー制約:
    "post_created_by_fkey" FOREIGN KEY (created_by) REFERENCES "user"(id) ON DELETE CASCADE
    "post_updated_by_fkey" FOREIGN KEY (updated_by) REFERENCES "user"(id) ON DELETE CASCADE


kysely-test=# \d user
                                        テーブル"public.user"
      列      |         タイプ         | 照合順序 | Null 値を許容 |            デフォルト
--------------+------------------------+----------+---------------+----------------------------------
 id           | integer                |          | not null      | nextval('user_id_seq'::regclass)
 name         | character varying(100) |          | not null      |
 gender       | character varying(20)  |          | not null      |
 introduction | text                   |          |               |
インデックス:
    "user_pkey" PRIMARY KEY, btree (id)
参照元:
    TABLE "post" CONSTRAINT "post_created_by_fkey" FOREIGN KEY (created_by) REFERENCES "user"(id) ON DELETE CASCADE
    TABLE "post" CONSTRAINT "post_updated_by_fkey" FOREIGN KEY (updated_by) REFERENCES "user"(id) ON DELETE CASCADE
```

データ作成をkyselyで書いてみます。
まず、テーブル名がインテリジェンスに出てくる！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/642815/5b821a5f-6016-dcf7-7b70-af3dd5b690d7.png)

次に、Not NULLのカラム未指定だとエラーになる！

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/642815/09be7a0f-35d5-a2b3-ef9a-ed8f545ec0f2.png)

ここまで来ると当然のように、テーブルの型定義でした通り`gender`も3つの選択肢が出てきます。

データ作成のコードを作成。
`await db.destroy();`がないと、終わるまで時間がかかりました。マイグレーションのコードにあったので書いておくのがよさそうです。
`npm run start -- create`で実行できるようにします。

```ts:src/create.ts
import db from "./core/db";

export default async function create() {
  await db.insertInto('user').values({ 'name': 'ユーザー1', gender: 'man' }).execute();
  await db.insertInto('user').values({ 'name': 'ユーザー2', gender: 'woman', introduction: 'じこしょーかい' }).execute();
  await db.insertInto('post').values({ title: 'タイトル', content: '内容'.repeat(30), created_by: 1, updated_by: 1 }).execute();
  await db.destroy();
}
```

```ts:index.ts
import create from "./src/create";

const commands = ['create', 'update', 'get'];

const command = process.argv[2];
if (!command || !commands.includes(command)) {
  console.log(`usage: npm run start -- [create/update/get]`);
  process.exit(0);
}

async function main() {
  if (command == 'create') await create();
}
main();
```

```diff:package.json
    "scripts": {
+     "start": "node -r esbuild-register index.ts",
      "migrate": "node -r esbuild-register src/core/migrate.ts",
      "test": "echo \"Error: no test specified\" && exit 1"
  },
```

次、失敗するupdateです。呼び出し元の`index.ts`にも追記します。
`user.name`は100文字までですが、型は`string`で上限がないため実行時にエラーになります。
エラー時は例外になりました。`err.code`にPostgreSQLのエラーコードが入っているようなのでそれで判定できそうです。

```ts:update.ts
import db from "./core/db";

export default async function update() {
  try {
    const res = await db.updateTable('user')
      .set({ name: 'ユーザー1'.repeat(50) }).where('id', '=', 1).execute();
    console.log(res);
  } catch(err) {
    console.log(err);
  }
  await db.destroy();
}
```

最後、JOINつきselectです。テーブル名のエイリアスがちゃんとインテリジェンス効くのがすごい。

```ts:get.ts
import db from "./core/db";

export default async function get() {
  const res = await db
    .selectFrom('post as p')
    .leftJoin('user as u1', 'p.created_by', 'u1.id')
    .leftJoin('user as u2', 'p.updated_by', 'u2.id')
    .selectAll('p')
    .select(['u1.name as created_by_name', 'u2.name as updated_by_name'])
    .where('p.id', '=', 1)
   .executeTakeFirst();
  console.log(res);
  await db.destroy();
}
```

実行結果。ちゃんと想定通りのSQLになってる。

```
kysely:sql [select "p".*, "u1"."name" as "created_by_name", "u2"."name" as "updated_by_name" from "post" as "p"
 left join "user" as "u1" on "p"."created_by" = "u1"."id" left join "user" as "u2" on "p"."updated_by" = "u2"."id" where "p"."id" = $1]
 parameters: [1] time: 3.78

{
  id: 1,
  title: 'タイトル',
  content: '内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容内容 内容内容',
  created_at: 2023-08-13T09:39:29.587Z,
  created_by: 1,
  updated_at: 2023-08-13T09:39:29.587Z,
  updated_by: 1,
  created_by_name: 'ユーザー1',
  updated_by_name: 'ユーザー1'
}
```

結果にもちゃんとインテリジェンスが効く。(LEFT JOINなので`string | null`になっている)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/642815/42cbe98c-4fd1-f722-8664-6cb04e45a122.png)

課題がないわけではないですが、だいぶいいかんじです。

## 別の記事

課題になっていた点をいくつか解消した、npmパッケージをつくりました。  
紹介記事は以下

https://qiita.com/mont_blanc/items/7982bceddf58a0e9ab61