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は以下。

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で。

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

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

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 を参考に、ファイルをつくります。

まず、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: それぞれテーブル定義から対応する型をひっぱってくる

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インスタンスの生成時にログ関数を指定しておきます。

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>になっていますが、「マイグレーションのファイルは現在のソースコードに依存させないでね」と書いてありました。
テーブル型定義とかから自動生成でもできれば楽ですが、とりあえず手書きです。
型生成 のページによるとprisma-kyselyあたりが便利かもしれません。

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

マイグレーション実行用ファイルをつくります。
公式ドキュメント とほぼ同じです。(dbをimportしただけ)

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にコマンドを追加します。

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で書いてみます。
まず、テーブル名がインテリジェンスに出てくる！

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

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

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

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();
}

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();

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のエラーコードが入っているようなのでそれで判定できそうです。

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です。テーブル名のエイリアスがちゃんとインテリジェンス効くのがすごい。

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になっている)

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

別の記事

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