Zenn
R&DテックブログPublicationへの投稿
🐝

最強のクエリビルダ「kysely」の紹介 ~薄いORMを探して~

Daiki-Hatada
2024/08/25に公開
PostgreSQL
TypeScript
Prisma
ORM
Kysely
tech

はじめに

こんにちは、畑田です。
最近、RDBを使用しているプロダクトをリプレイスする機会がありました。

このプロジェクトの目標は今現在の使用や実装方針をできるだけそのままに、インフラと技術だけをリプレイスするというものでした。
もちろん、インフラや技術を刷新するということはその上に乗るアプリケーションの実装にも必ず影響を及ぼします。その中で出来るだけ現状の仕様に寄せる試みを行いました。

元のプロジェクトはPHPで書かれており、DBのへの通信においても、文字列とsprintfなどを使用し、生のSQLを書いてpg_queryで実行していました。
私はこれを再現する最も安全な方法を探しました。

Prismaなどを用いたactive record形式のORMを使用するより、SQLに近い書き味のクエリビルダーを求めてkyselyに辿り着きました。

あまり情報が第三者からの情報が出てきていないように思ったので、ハンズオンを含めて備忘録を記載しておこうと思います。

前提

プロジェクトの前提として、以下を挙げておきます。

  • プロジェクトには既存のDBが存在し、その内容を正として実装する。
  • 既存のDBは、当該アプリケーション以外の仕組みから更新のみ受け付けるbatch処理を持つ。
  • 既存のDBはPostgreSQLである。
  • リプレイス後のプロジェクトはTypeScriptで実装する。
  • インフラはAWSに統一されている。

技術選定

kyselyを選定するにあたって列挙した他の候補について雑多に記載しておきます。

  • Prisma
  • TypeORM
  • knex
  • Drizzle

上記の前提に加えて個人的にPrismaのようなeasyなORMは却って扱いにくいと感じておりましたので、SQL likeなライブラリを嗜好する傾向がありました。深く学習すれば違うのでしょうが、DjangoやPrismaのようなactive recorde形式のORMでは実際にいつどんなクエリが発行されるのか、見通しが悪く、デバッグのためのコードを書く必要性が増えるなどのペインがあります。

DrizzleはSQL-likeな書き方とactive record likeな書き方の両方のインターフェイスを提供していて非常に使いやすそうでした。懸念としてはモデルを定義するDSLの存在と、今回リプレイスする既存のDBのテーブル定義との相性の悪さがありました。

kyselyはTypeScriptで実装されており、Prismaと橋渡しの3rdパーティライブラリなどもあり、静的型検査の観点でも他との互換性の観点でも選びやすかったです。

やっていき

とりあえず

とりあえず脳を止めてプロジェクトを初期化します。あとTypeScript前提なので、srcディレクトリも作っておきます。

yarn init -y
mkdir src

公式ドキュメント通りに

まだ脳は止めています。公式ドキュメントを上から読んで言われた通りにコマンドを叩いていきます。皆さんもご一緒にまずはインストールをします。

yarn add kysely

型定義

次の項は型定義です。よしよしコピペするかと思いましたが、ここで正気に戻ります。

今回は、既存プロダクトにDBが存在し、そちらを同期する形で型情報を生成する必要があります。
コードファーストかDBファーストかの議論でいうDBファーストに当たります。
(型情報を手で書くのは保守性の観点から選択したくないオプションですし、既存DBのテーブル数、カラム数がある程度大きかったので、素早くアプリをリプレイスするという点からも避けることになりました。)

さて、DBに繋いで自動で型定義してくれる仕組みはkyselyにはありません。しかし、公式ドキュメントを参照すると、kysely-codegenを使用することで型情報が生成されるとのことで安心です。(実は上項の技術選定の時点で流石にここまでは調べていました。)

サクッとインストールします。

yarn add -D kysely-codegen

そしてREADMEの型生成コマンドの項によれば、DATABASE_URLという環境変数を読んでくれそうです。というより、.envに置いておけば読んでくれそうですね。
ということで、以下のコマンドでいけるはず。

DATABASE_URL=postgres://daiki:password@localhost:5432/example?schema=connect yarn kysely-codegen

いいえ。エラーします。

Error: Cannot find module 'pg'

気を取り直して行ってみましょう。

yarn add pg
yarn add -D @types/pg
DATABASE_URL=postgres://daiki:password@localhost:5432/example?schema=connect yarn kysely-codegen

Excellent.

✓ Introspected 122 tables and generated ./node_modules/kysely-codegen/dist/db.d.ts in 1080ms.

型定義ファイルがnode_modulesディレクトリの中に生成されていますね。
この場所にあるということは、以下の想定でしょう。

  • このファイルはバージョン管理から外す。
  • 環境ごとに読み込んでそれぞれの環境で新鮮な型定義ファイルを生成して参照する。
  • package.jsonのpostinstallなどで上記コマンドを呼び出してプロジェクト初期化時に常に型定義ファイルが生成される。

良い感じに見えますが、環境によっては難しいケースもありそうです。例えば、Cloud FunctionsのCloud Buildなどのビルドタイミングでも無闇に呼び出され、エラーの可能性を孕むなどは簡単に思い浮かびます。もちろんそれぞれの問題に対処すれば良いだけですが、一旦型定義ファイルをバージョン管理下に入れ、DBの様子をソースで見れるようにしておきたいです。

yarn kysely-codegen -h

上記コマンドで提供される情報によれば、--out-fileがあり、これは非常にそれっぽいですね😁

DATABASE_URL=postgres://daiki:password@localhost:5432/example?schema=connect yarn kysely-codegen --out-file src/types.ts

Tres bien.

✓ Introspected 122 tables and generated ./src/types.ts in 1311ms.

中身を見ていきましょう。

上の方にユーティリティタイプが定義されています。このColumnTypeの実装が非常に整理されていて好きです。

import type { ColumnType } from "kysely";

export type Generated<T> = T extends ColumnType<infer S, infer I, infer U>
  ? ColumnType<S, I | undefined, U>
  : ColumnType<T, T | undefined, T>;

export type Int8 = ColumnType<string, bigint | number | string, bigint | number | string>;

export type Numeric = ColumnType<string, number | string, number | string>;

export type Timestamp = ColumnType<Date, Date | string, Date | string>;

その下の方にそれぞれのテーブルのスキーマに対応する型が定義されています。

export interface ConnectRecommendationHistories {
  createddate: Generated<Timestamp>;
  id: Generated<string>;
  isdeleted: Generated<boolean>;
  number_of_results: number;
  systemmodstamp: Generated<Timestamp>;
  user_id: string;
}

export interface ConnectRecommendationHistoryOpportunities {
  id: Generated<number>;
  isdeleted: Generated<boolean | null>;
  opportunity_id: string;
  recommendation_history_id: string;
  score: number;
}

最後に全てのテーブルの定義をまとめたものが定義されています。

export interface DB {
  // skip details
  "connect.recommendation_histories": ConnectRecommendationHistories;
  "connect.recommendation_history_opportunities": ConnectRecommendationHistoryOpportunities;
}

型定義は一件落着です。

DB接続

話を公式ドキュメントのハンズオンに戻しましょう。
今回はPostgreSQLに接続する前提で進めているため、driverにはpgが必要なようですが、先ほどkysely-codegenを実行するためにインストールしたので、スキップします。

以下で接続します。ほぼ公式ドキュメント通りですが、DATABAES_URL環境変数で接続先を指定したいので、少し変えております。

import { DB } from './types'
import { Pool } from 'pg'
import { Kysely, PostgresDialect } from 'kysely'
import { config } from 'dotenv'

config()

const dialect = new PostgresDialect({
  pool: new Pool({
    ssl: { rejectUnauthorized: false },  // TODO: 証明書対応
    connectionString: process.env.DATABASE_URL,
  }),
})

export const db = new Kysely<DB>({
  dialect,
})

多分行けているはずです。実際にクエリして試してみましょう。そろそろ脳は止めても良さそうです。

クエリ実行とテスト

こちらも公式ドキュメント丸写しです。
以下のファイルに書き込んでいきます。

touch src/RecommendationHistoryRepository.ts

簡単にいくつか参照系を書いてみます。repositoryというファイルの中に通信系を書いていくサンプルがkyselyのORMではなくクエリビルダーとしての立ち位置を表していますね。

import { Selectable } from 'kysely'
import { db } from './database'
import { ConnectRecommendationHistories } from './types'

export const findOneById = (id: string) =>
  db.selectFrom('connect.recommendation_histories').where('id', '=', id).selectAll().executeTakeFirst()

export const search = (
  criteria: Partial<Pick<Selectable<ConnectRecommendationHistories>, 'id' | 'number_of_results' | 'user_id'>>,
) => {
  let query = db.selectFrom('connect.recommendation_histories')
  if (criteria.id) {
    query = query.where('id', '=', criteria.id)
  }
  if (criteria.number_of_results) {
    query = query.where('number_of_results', '=', criteria.number_of_results)
  }
  if (criteria.user_id) {
    query = query.where('user_id', '=', criteria.user_id)
  }
  return query.selectAll().execute()
}

テストしてみないといけないですね。
適当にvitestを入れてみます。

yarn add -D vitest

そしてテストファイルを作りますが、こちらも公式ドキュメントの丸写しです。

以下のファイルを作ります。

touch src/RecommendationHistoryRepository.spec.ts

あとはそれぞれの関数をテスト書きます。

import * as RecommendationHistoryRepository from './RecommendationHistoryRepository'
import { it, describe } from 'vitest'

describe('RecommendationHistoryRepository', () => {
  it('findOneById', async () => {
    await RecommendationHistoryRepository.findOneById('bf7a5d37-77bf-4c81-bbff-5e5c6a9dbc35')
  })
  it('search', async () => {
    await RecommendationHistoryRepository.search({ user_id: 'a2KHz000000bqdMMAQ' })
  })
})

そして実行するだけです。

yarn vitest

結果は完璧です。

 ✓ src/RecommendationHistoryRepository.spec.ts (2) 2120ms
   ✓ RecommendationHistoryRepository (2) 2120ms
     ✓ findOneById 1790ms
     ✓ search 328ms

 Test Files  1 passed (1)
      Tests  2 passed (2)
   Start at  16:11:44
   Duration  2.81s (transform 103ms, setup 0ms, collect 264ms, tests 2.12s, environment 0ms, prepare 156ms)


 PASS  Waiting for file changes...
       press h to show help, press q to quit
✨  Done in 5.62s.

ここまで来れば、更新系や複雑なクエリの参照系は書いていくだけという感じですね。

import { Insertable, Selectable, Updateable } from 'kysely'
import { db } from './database'
import { ConnectRecommendationHistories } from './types'

export const findOneById = (id: string) =>
  db.selectFrom('connect.recommendation_histories').where('id', '=', id).selectAll().executeTakeFirst()

export const search = (
  criteria: Partial<Pick<Selectable<ConnectRecommendationHistories>, 'id' | 'number_of_results' | 'user_id'>>,
) => {
  let query = db.selectFrom('connect.recommendation_histories')
  if (criteria.id) {
    query = query.where('id', '=', criteria.id)
  }
  if (criteria.number_of_results) {
    query = query.where('number_of_results', '=', criteria.number_of_results)
  }
  if (criteria.user_id) {
    query = query.where('user_id', '=', criteria.user_id)
  }
  return query.selectAll().execute()
}

+ export const searchWithScore = async (criteria: { minAvgScore?: number } = {}) => {
+   const { minAvgScore = 0 } = criteria
+   return db
+     .selectFrom('connect.recommendation_histories')
+     .leftJoin('connect.recommendation_history_opportunities', (join) =>
+       join.onRef(
+         'connect.recommendation_histories.id',
+         '=',
+         'connect.recommendation_history_opportunities.recommendation_history_id',
+       ),
+     )
+     .groupBy('connect.recommendation_histories.id')
+     .having((eb) => eb(eb.fn.avg('connect.recommendation_history_opportunities.score'), '>=', minAvgScore))
+     .select((eb) => [
+       eb.fn.avg('connect.recommendation_history_opportunities.score').as('avg_score'),
+       eb.fn.min('connect.recommendation_history_opportunities.score').as('min_score'),
+       eb.fn.max('connect.recommendation_history_opportunities.score').as('max_score'),
+     ])
+     .execute()
+ }
+ 
+ export const update = (id: string, data: Partial<Updateable<ConnectRecommendationHistories>>) =>
+   db.updateTable('connect.recommendation_histories').set(data).where('id', '=', id).execute()
+ 
+ export const create = (data: Insertable<ConnectRecommendationHistories>) =>
+   db.insertInto('connect.recommendation_histories').values(data).returningAll().executeTakeFirstOrThrow()
+ 
+ export const remove = (id: string) =>
+   db.deleteFrom('connect.recommendation_histories').where('id', '=', id).returningAll().executeTakeFirstOrThrow()

テストも追加しておきます。

import * as RecommendationHistoryRepository from './RecommendationHistoryRepository'
import { it, describe } from 'vitest'

describe('RecommendationHistoryRepository', () => {
  it('findOneById', async () => {
    await RecommendationHistoryRepository.findOneById('bf7a5d37-77bf-4c81-bbff-5e5c6a9dbc35')
  })
  it('search', async () => {
    await RecommendationHistoryRepository.search({ user_id: 'a2KHz000000bqdMMAQ' })
  })
+  it('searchWithScore', async () => {
+    await RecommendationHistoryRepository.searchWithScore({ minAvgScore: 60 })
+  })
+  it('update', async () => {
+    await RecommendationHistoryRepository.update('bf7a5d37-77bf-4c81-bbff-5e5c6a9dbc35', { isdeleted: false })
+  })
+  it('create and remove', async () => {
+    const { id } = await RecommendationHistoryRepository.create({
+      user_id: 'a2KHz000000bqdMMAQ',
+      number_of_results: 0,
+    })
+    await RecommendationHistoryRepository.remove(id)
+  })
})

少し解説

上に記載したコードに関して注釈程度の解説をします。

kyselyのSQL記法について

最後に追加したsearchWithScoreを見て解説していきます。もちろんkyselyの公式ドキュメントやAPIリファレンスが詳しいですし、第一に参考にすべきものですが、簡単に説明を書いていきます。

この関数では1:optional manyなリレーションである、connect.recommendation_historiesとconnect.recommendation_history_opportunitiesを扱っています。
これらのテーブルはある推薦アルゴリズムに従って案件 (opportunity)をユーザーに推薦するロジックの履歴を表現しています。推薦アルゴリズムの一回の試行がconnect.recommendation_historiesであり、その試行で推薦された案件 (opportunity)とその際のスコアがconnect.recommendation_history_opportunitiesです。

まずはconnect.recommendation_historiesとconnect.recommendation_history_opportunitiesを左側結合しています。その際の条件として、connect.recommendation_histories.id = connect.recommendation_history_opportunities.recommendation_history_idを設定しています。
さらにそこから、GROUP BYとavg()を用いて計算したスコアの平均値に対してフィルタを掛けたあと、スコアの平均値、最小値、最大値を呼び出しています。これらの情報によってconnect.recommendation_historiesを評価しようという意図が汲み取れるクエリです。

kyselyでlogを設定して実行してみても以下のようなSQLが発行されていることがわかります。

# 実際の出力
select avg("connect"."recommendation_history_opportunities"."score") as "avg_score", min("connect"."recommendation_history_opportunities"."score") as "min_score", max("connect"."recommendation_history_opportunities"."score") as "max_score" from "connect"."recommendation_histories" left join "connect"."recommendation_history_opportunities" on "connect"."recommendation_histories"."id" = "connect"."recommendation_history_opportunities"."recommendation_history_id" group by "connect"."recommendation_histories"."id" having avg("connect"."recommendation_history_opportunities"."score") >= $1
# 整形した出力
SELECT avg("connect"."recommendation_history_opportunities"."score") AS "avg_score",
       min("connect"."recommendation_history_opportunities"."score") AS "min_score",
       max("connect"."recommendation_history_opportunities"."score") AS "max_score"
FROM "connect"."recommendation_histories"
LEFT JOIN "connect"."recommendation_history_opportunities" ON "connect"."recommendation_histories"."id" = "connect"."recommendation_history_opportunities"."recommendation_history_id"
GROUP BY "connect"."recommendation_histories"."id"
HAVING avg("connect"."recommendation_history_opportunities"."score") >= 1;

工夫していく

kyselyをcamelCaseで使う

実務レベルという話になると、命名規則などの細かい話から目を背けるわけにはいきません。

実際、バックエンドでsnake_caseを、フロントエンドでcamelCaseを使用する場合、JSONのキー名における議論は頻発します。変換層を設けて翻訳するか、どちらかが妥協してその形式で書き続けることが多いでしょう。
我々は変換処理を行うレイヤーを設ける (zodのtransformでvalidationと同時に行ってみたり？)ことが多いです。
同様にして、DBのtable名、column名の命名規則では (PostgreSQLやMySQLなどでは)snake_caseが一般的なのに対して、JavaScriptのそれではcamelCaseが一般的であるため、乖離が生じてしまいます。
例えば、JavaScript側でESLintを用いて命名規則を束縛していれば、kyselyを使用したファイルが真っ赤にlintされてしまいます。だからといってDBの方をいじるのは具合が悪く、少なくともPostgreSQLでcase-sensitiveな命名をすると投げるクエリに工夫をする必要が出てきます。(尤も、単純にdouble quoteで包むだけですが。)

kyselyは上記の問題を解決するレイヤーを包摂してくれるようです。その仕組みがpluginです。詳しい説明では公式ドキュメントに譲りますが、今回はkyselyをcamelCaseで使う方法を記載していきます。

まずは、kyselyに入力したテーブル名とカラム名などを全てcamelCaseからsnake_caseに変換してくれ、かつ、出力される内容を全てsnake_caseからcamelCaseに変換してくれるpluginを入れます。

import { DB } from './types'
import { Pool } from 'pg'
- import { Kysely, PostgresDialect } from 'kysely'
+ import { CamelCasePlugin, Kysely, PostgresDialect } from 'kysely'
import { config } from 'dotenv'

config()

const dialect = new PostgresDialect({
  pool: new Pool({
    ssl: { rejectUnauthorized: false },
    connectionString: process.env.DATABASE_URL,
  }),
})

export const db = new Kysely<DB>({
  dialect,
+   plugins: [new CamelCasePlugin()],
})

こちらを導入して、早速camelCaseでクエリを投げたいところですが、TypeErrorで怒られます。これはkysely-codegenで生成した型定義がsnake_caseで書かれているからです。
そこでこちらもcamelCaseで生成します。
--camel-case optionという神機能により一瞬で実現できます。オプション自身はkebab-caseなんかい。

DATABASE_URL=postgres://daiki:password@localhost:5432/example?schema=connect yarn kysely-codegen --camel-case --out-file src/types.ts

こちらで書き直していくとcamelCaseで入出力が整理されたDBとの通信が実現されます。

import { Insertable, Selectable, Updateable } from 'kysely'
import { db } from './database'
import { ConnectRecommendationHistories } from './types'

export const findOneById = (id: string) =>
  db.selectFrom('connect.recommendationHistories').where('id', '=', id).selectAll().executeTakeFirst()

export const search = (
  criteria: Partial<Pick<Selectable<ConnectRecommendationHistories>, 'id' | 'numberOfResults' | 'userId'>>,
) => {
  let query = db.selectFrom('connect.recommendationHistories')
  if (criteria.id) {
    query = query.where('id', '=', criteria.id)
  }
  if (criteria.numberOfResults) {
    query = query.where('numberOfResults', '=', criteria.numberOfResults)
  }
  if (criteria.userId) {
    query = query.where('userId', '=', criteria.userId)
  }
  return query.selectAll().execute()
}

export const searchWithScore = async (criteria: { minAvgScore?: number } = {}) => {
  const { minAvgScore = 0 } = criteria
  return db
    .selectFrom('connect.recommendationHistories')
    .leftJoin('connect.recommendationHistoryOpportunities', (join) =>
      join.onRef(
        'connect.recommendationHistories.id',
        '=',
        'connect.recommendationHistoryOpportunities.recommendationHistoryId',
      ),
    )
    .groupBy('connect.recommendationHistories.id')
    .having((eb) => eb(eb.fn.avg('connect.recommendationHistoryOpportunities.score'), '>=', minAvgScore))
    .select((eb) => [
      eb.fn.avg('connect.recommendationHistoryOpportunities.score').as('avg_score'),
      eb.fn.min('connect.recommendationHistoryOpportunities.score').as('min_score'),
      eb.fn.max('connect.recommendationHistoryOpportunities.score').as('max_score'),
    ])
    .execute()
}

export const update = (id: string, data: Partial<Updateable<ConnectRecommendationHistories>>) =>
  db.updateTable('connect.recommendationHistories').set(data).where('id', '=', id).execute()

export const create = (data: Insertable<ConnectRecommendationHistories>) =>
  db.insertInto('connect.recommendationHistories').values(data).returningAll().executeTakeFirstOrThrow()

export const remove = (id: string) =>
  db.deleteFrom('connect.recommendationHistories').where('id', '=', id).returningAll().executeTakeFirstOrThrow()

repository patternを変更

公式ドキュメントの丸写しによるrepository patternを上記でも実装しましたが、少しrepository pattern自体を書き直してみましょう。
以下のようにDBの実態をrepositoryの実装が直接参照しないようにすることで、そのインターフェイスを持つobjectであれば、repositoryの永続層として機能することができるようになります。これはテスタビリティや将来的な機能拡張に余裕を持たせるのに役立つでしょう。

import { Insertable, Kysely, Selectable, Updateable } from 'kysely'
import { ConnectRecommendationHistories, DB } from './types'

const recommendationHistoryRepositoryFactory = (db: Kysely<DB>) => ({
  findOneById: (id: string) =>
    db.selectFrom('connect.recommendationHistories').where('id', '=', id).selectAll().executeTakeFirst(),

  search: (
    criteria: Partial<Pick<Selectable<ConnectRecommendationHistories>, 'id' | 'numberOfResults' | 'userId'>>,
  ) => {
    let query = db.selectFrom('connect.recommendationHistories')
    if (criteria.id) {
      query = query.where('id', '=', criteria.id)
    }
    if (criteria.numberOfResults) {
      query = query.where('numberOfResults', '=', criteria.numberOfResults)
    }
    if (criteria.userId) {
      query = query.where('userId', '=', criteria.userId)
    }
    return query.selectAll().execute()
  },

  searchWithScore: async (criteria: { minAvgScore?: number } = {}) => {
    const { minAvgScore = 0 } = criteria
    return db
      .selectFrom('connect.recommendationHistories')
      .leftJoin('connect.recommendationHistoryOpportunities', (join) =>
        join.onRef(
          'connect.recommendationHistories.id',
          '=',
          'connect.recommendationHistoryOpportunities.recommendationHistoryId',
        ),
      )
      .groupBy('connect.recommendationHistories.id')
      .having((eb) => eb(eb.fn.avg('connect.recommendationHistoryOpportunities.score'), '>=', minAvgScore))
      .select((eb) => [
        eb.fn.avg('connect.recommendationHistoryOpportunities.score').as('avg_score'),
        eb.fn.min('connect.recommendationHistoryOpportunities.score').as('min_score'),
        eb.fn.max('connect.recommendationHistoryOpportunities.score').as('max_score'),
      ])
      .execute()
  },

  update: (id: string, data: Partial<Updateable<ConnectRecommendationHistories>>) =>
    db.updateTable('connect.recommendationHistories').set(data).where('id', '=', id).execute(),

  create: (data: Insertable<ConnectRecommendationHistories>) =>
    db.insertInto('connect.recommendationHistories').values(data).returningAll().executeTakeFirstOrThrow(),

  remove: (id: string) =>
    db.deleteFrom('connect.recommendationHistories').where('id', '=', id).returningAll().executeTakeFirstOrThrow(),
})

// 使用場面
const recommendationHistoryRepository = recommendationHistoryRepositoryFactory(db)

まとめ

今回はプロジェクトの性質に合わせて選定した技術に関して、ハンズオンを書いてみました。

DBとの通信やSQLの発行を抽象化する試みはあらゆるアプリケーションで必要ですし、それがeasyであるべきか、simpleであるべきかによって選定する結果が変化しうるでしょう。もしsimpleなクエリビルダーが欲しくなったら、kyselyが目の前にあります。
今後皆さんが似た状況になった際に、この記事がkyselyを活用する助けになれば、大変嬉しいです。

今後、アプリの環境ごと再現性を高めながらデプロイメントの簡単化及び高頻度化を図ることで、質の高いアプリを量産していくため、積極的に静的型付けによるアプリケーション開発を行ってまいります。

R&Dテックブログ
R&DテックブログPublication

MVP開発サービスを提供しております。 日々の開発での気付きを発信していきます。

Discussion