本記事で行うこと
前回の記事で、TypeScript用クエリービルダー「Kysely」を使って基本的なCRUD操作を試しました。
次はもう少し複雑な処理を書いてみよう、ということで、本記事で以下の3つを試します。
- トランザクション
- UPSERT
- テーブルのJOIN
データベースはPostgreSQLと、SQLiteの2つを使用します。
前回の記事
- TypeScript用クエリービルダー「Kysely」をDBファーストで使う試み（SQLite編）
- TypeScript用クエリービルダー「Kysely」をDBファーストで使う試み（PostgreSQL編）
テーブルを作る
それでは作業に移ります。
DBファーストで行きますので、まずDDLを書きます。
このページのCREATE TABLE文を、PostgreSQLとSQLiteの両方で動くように書き直しました（私は本番プロジェクトではこんなことはせず、各DBに最適なDDLを書きます）。
ただし、SQLite用にはSTRICTオプションを付け加えます。
PostgreSQL用
CREATE TABLE cities (
name TEXT PRIMARY KEY,
location TEXT
);
CREATE TABLE weather (
city TEXT REFERENCES CITIES(name),
temp_lo INT, -- 最低気温
temp_hi INT, -- 最高気温
prcp REAL, -- 降水量
date TEXT
);
SQLite用
CREATE TABLE cities (
name TEXT PRIMARY KEY,
location TEXT
) STRICT;
CREATE TABLE weather (
city TEXT REFERENCES CITIES(name),
temp_lo INT, -- 最低気温
temp_hi INT, -- 最高気温
prcp REAL, -- 降水量
date TEXT
) STRICT;
HonoとKyselyを使って、トランザクション操作するWeb APIサーバーを作ってみる
参考サイト
- PostgreSQL
- SQLite
- 共通
プロジェクト作成
プロジェクト名は「exercise_kysely_2」にしました。
どのテンプレートを使うか聞かれたので、「npm」を選択しました。
参考ページ： https://hono.dev/getting-started/basic
npm create hono@latest exercise_kysely_2
パッケージインストール
PostgreSQL関連とSQLite関連のパッケージを両方インストールします。
プロジェクトディレクトリ直下で、以下を実行しました。
npm install kysely pg better-sqlite3
npm install --save-dev @types/pg @types/better-sqlite3 kysely-codegen
DBからTypeScriptのスキーマを自動生成
DBファーストなので、コードからDBスキーマを生成するのではなく、DBスキーマからコードを生成します。
プロジェクトディレクトリ直下で、kysely-codegenコマンドを2回実行しました。
環境変数のDATABASE_URLにはそれぞれ、PostgreSQLのURLと、SQLiteのデータファイルを指定します。
DATABASE_URL=postgres://postgres:secret@localhost:5432/postgres kysely-codegen --out-file ./src/postgres.d.ts
DATABASE_URL=/home/user/DataGripProjects/sqlite/exercise.sqlite3 kysely-codegen --out-file ./src/sqlite.d.ts
ディレクトリ構造とファイル
Project Root
├── src/
│ ├── index.ts
│ ├── postgres.d.ts
│ └── sqlite.d.ts
├── package.json
├── README.md
└── tsconfig.json
src/postgres.d.ts
kysely-codegenコマンドによって自動生成されたコードです。
src/postgres.d.ts
export interface Cities {
location: string | null;
name: string;
}
export interface Weather {
city: string | null;
date: string | null;
prcp: number | null;
temp_hi: number | null;
temp_lo: number | null;
}
export interface DB {
cities: Cities;
weather: Weather;
}
src/sqlite.d.ts
kysely-codegenコマンドによって自動生成されたコードです。
postgres.d.tsとの違いとして、temp_hi列とtemp_lo列がstring型にマッピングされました。
SQLiteのINT型をTypeScriptのnumber型にマッピングすると、桁数が大きい時に丸め誤差が生じることがあるので、string型にマッピングされたと思われます。
src/sqlite.d.ts
export interface Cities {
location: string | null;
name: string;
}
export interface Weather {
city: string | null;
date: string | null;
prcp: number | null;
temp_hi: string | null;
temp_lo: string | null;
}
export interface DB {
cities: Cities;
weather: Weather;
}
src/index.ts
メインのコードです。
postgresTransaction()とsqliteTransaction()は、それぞれPostgreSQLとSQLiteに対して、ほぼ同じ処理を行います。
src/index.ts
import {serve} from '@hono/node-server'
import {Hono} from 'hono'
import {Kysely, PostgresDialect, SqliteDialect, sql} from "kysely"
import {DB as POSTGRES_DB} from "./postgres"
import {DB as SQLITE_DB} from "./sqlite"
import {Pool} from "pg"
const SQLite = require("better-sqlite3");
// PostgreSQL初期設定
const postgresDialect = new PostgresDialect({
pool: new Pool({
host: "localhost",
port: 5432,
user: "postgres",
password: "secret",
database: "postgres",
})
})
export const postgresDb = new Kysely<POSTGRES_DB>({
dialect: postgresDialect,
log: (event) => {
if (event.level === "query") {
console.log(event.query.sql)
console.log(event.query.parameters)
}
}
})
// SQLite初期設定
const sqliteDialect = new SqliteDialect({
database: new SQLite("/home/user/DataGripProjects/sqlite/exercise.sqlite3"),
})
export const sqliteDb = new Kysely<SQLITE_DB>({
dialect: sqliteDialect,
log: (event) => {
if (event.level === "query") {
console.log(event.query.sql)
console.log(event.query.parameters)
}
}
})
// PostgreSQLクエリー
const postgresTransaction = async (isGood: boolean) => {
// トランザクション
await postgresDb.transaction().execute(async (tx) => {
// 親テーブルに1件UPSERT（INSERT ... ON CONFLICT）
await tx.insertInto("cities")
.values({
name: "San Francisco",
location: "(-194.0, 53.0)",
})
.onConflict((ocb) => ocb
.column("name")
.doUpdateSet({
location: (eb) => eb.ref("excluded.location")
})
)
.execute()
// 子テーブルに3件INSERT
// 1件目
await tx.insertInto("weather")
.values({
city: "San Francisco",
temp_lo: 43,
temp_hi: 57,
prcp: 0.0,
date: "2023-01-01",
})
.execute()
// 2件目
await tx.insertInto("weather")
.values({
city: "San Francisco",
temp_lo: 43,
temp_hi: 57,
prcp: 0.0,
date: "2023-01-02",
})
.execute()
// 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
let city
if (isGood) {
city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
} else {
city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
}
await tx.insertInto("weather")
.values({
city: city,
temp_lo: 43,
temp_hi: 57,
prcp: 0.0,
date: "2023-01-03",
})
.execute()
})
// トランザクションが成功したら、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返す
return postgresDb
.selectFrom("cities")
.leftJoin("weather", "cities.name", "weather.city")
.select([
"cities.name",
sql`weather.temp_lo`.as("tempLo"),
sql`weather.temp_hi`.as("tempHi"),
"weather.prcp",
"weather.date",
])
.orderBy("weather.date")
.execute()
}
// SQLiteクエリー
const sqliteTransaction = async (isGood: boolean) => {
// トランザクション
await sqliteDb.transaction().execute(async (tx) => {
// 親テーブルに1件UPSERT（INSERT ... ON CONFLICT）
await tx.insertInto("cities")
.values({
name: "San Francisco",
location: "(-194.0, 53.0)",
})
.onConflict((ocb) => ocb
.column("name")
.doUpdateSet({
location: (eb) => eb.ref("excluded.location")
})
)
.execute()
// 子テーブルに3件INSERT
// 1件目
await tx.insertInto("weather")
.values({
city: "San Francisco",
temp_lo: "43",
temp_hi: "57",
prcp: 0.0,
date: "2023-01-01",
})
.execute()
// 2件目
await tx.insertInto("weather")
.values({
city: "San Francisco",
temp_lo: "43",
temp_hi: "57",
prcp: 0.0,
date: "2023-01-02",
})
.execute()
// 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
let city
if (isGood) {
city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
} else {
city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
}
await tx.insertInto("weather")
.values({
city: city,
temp_lo: "43",
temp_hi: "57",
prcp: 0.0,
date: "2023-01-03",
})
.execute()
})
// トランザクションが成功したら、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返す
return sqliteDb
.selectFrom("cities")
.leftJoin("weather", "cities.name", "weather.city")
.select([
"cities.name",
sql`CAST(weather.temp_lo AS TEXT)`.as("tempLo"),
sql`CAST(weather.temp_hi AS TEXT)`.as("tempHi"),
"weather.prcp",
"weather.date",
])
.orderBy("weather.date")
.execute()
}
// HonoでAPIサーバー
const app = new Hono()
app.notFound((c) => c.json({message: "Not Found", ok: false}, 404))
// Routing
// PostgreSQL：成功するトランザクション
app.post("/postgres/good", async (c) => {
try {
const resultSet = await postgresTransaction(true)
return c.json(resultSet)
} catch (e) {
return c.json({message: e.toString(), ok: false}, 500)
}
})
// PostgreSQL：失敗するトランザクション
app.post("/postgres/bad", async (c) => {
try {
return c.json(await postgresTransaction(false))
} catch (e) {
return c.json({message: e.toString(), ok: false}, 500)
}
})
// SQLite：成功するトランザクション
app.post("/sqlite/good", async (c) => {
try {
return c.json(await sqliteTransaction(true))
} catch (e) {
return c.json({message: e.toString(), ok: false}, 500)
}
})
// SQLite：失敗するトランザクション
app.post("/sqlite/bad", async (c) => {
try {
return c.json(await sqliteTransaction(false))
} catch (e) {
return c.json({message: e.toString(), ok: false}, 500)
}
})
const port = 3000
console.log(`Server is running on port ${port}`)
serve({
fetch: app.fetch,
port
})
package.json
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",
"pg": "^8.11.3"
},
"devDependencies": {
"@types/better-sqlite3": "^7.6.8",
"@types/pg": "^8.10.9",
"kysely-codegen": "^0.11.0",
"tsx": "^3.12.2"
}
}
tsconfig.json
tsconfig.json
{
"compilerOptions": {
"strict": true,
"jsx": "react-jsx",
"jsxImportSource": "hono/jsx",
}
}
トランザクション処理
それでは、トランザクション処理を行う関数の中身について、一つ一つ見ていきます。
postgresTransaction()とsqliteTransaction()でほぼ同じ処理を行いますので、postgresTransaction()だけ見ることにします。
トランザクション開始・終了
参考ページ： https://kysely.dev/docs/examples/transactions/simple-transaction
// トランザクション
await postgresDb.transaction().execute(async (tx) => {
// 一連のクエリーを書く
}
上記コードからKyselyが生成したSQLは以下の通りです。
トランザクション開始時：
begin
トランザクション終了時：
good：
commit
bad：
rollback
親テーブルへのUPSERT
INSERT ON CONFLICTを使用しました。
参考ページ： https://kysely-org.github.io/kysely-apidoc/classes/InsertQueryBuilder.html#onConflict
// 親テーブルに1件UPSERT（INSERT ... ON CONFLICT）
await tx.insertInto("cities")
.values({
name: "San Francisco",
location: "(-194.0, 53.0)",
})
.onConflict((ocb) => ocb
.column("name")
.doUpdateSet({
location: (eb) => eb.ref("excluded.location")
})
)
.execute()
上記コードからKyselyが生成したSQLとパラメータを、整形して見やすくしました。
プライマリーキーのname列には「San Francisco」という値が入ります。
insert into "cities" (
"name",
"location"
)
values (
$1,
$2
)
on conflict (
"name"
)
do update set
"location" = "excluded"."location"
パラメータ
[
'San Francisco',
'(-194.0, 53.0)'
]
子テーブルへのINSERT
3件INSERTします。
1件目・2件目
INSERT文が成功するように記述します。
// 1件目
await tx.insertInto("weather")
.values({
city: "San Francisco",
temp_lo: 43,
temp_hi: 57,
prcp: 0.0,
date: "2023-01-01",
})
.execute()
// 2件目
await tx.insertInto("weather")
.values({
city: "San Francisco",
temp_lo: 43,
temp_hi: 57,
prcp: 0.0,
date: "2023-01-02",
})
.execute()
上記のコードからKyselyが生成したSQLとパラメータを、整形して見やすくしました。
insert into "weather" (
"city",
"temp_lo",
"temp_hi",
"prcp",
"date"
)
values (
$1,
$2,
$3,
$4,
$5
)
1件目のパラメータ
[
'San Francisco',
43,
57,
0,
'2023-01-01'
]
2件目のパラメータ
[
'San Francisco',
43,
57,
0,
'2023-01-02'
]
3件目
引数isGoodがtrueの時は成功するように、falseの時は失敗するように記述します。
// 3件目のINSERTは、isGoodのときは成功させて、!isGoodのときは失敗させる
let city
if (isGood) {
city = "San Francisco" // 親テーブルに存在する値を外部キーに設定
} else {
city = "Hayward" // 親テーブルに存在しない値を外部キーに設定
}
await tx.insertInto("weather")
.values({
city: city,
temp_lo: 43,
temp_hi: 57,
prcp: 0.0,
date: "2023-01-03",
})
.execute()
失敗させたい時は、外部キーのcity列に、親テーブルにない値を設定しました。
パラメータ
[
'Hayward',
43,
57,
0,
'2023-01-03'
]
親テーブルと子テーブルをJOIN
トランザクション後に処理が成功したか見たいので、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返すことにしました。
参考サイト： https://kysely.dev/docs/category/join
// トランザクションが成功したら、親テーブルと子テーブルをjoinして、結果表をレスポンスにして返す
return postgresDb
.selectFrom("cities")
.leftJoin("weather", "cities.name", "weather.city")
.select([
"cities.name",
sql`weather.temp_lo`.as("tempLo"),
sql`weather.temp_hi`.as("tempHi"),
"weather.prcp",
"weather.date",
])
.orderBy("weather.date")
.execute()
上記コードからKyselyが生成したSQLを、整形して見やすくしました。
select
"cities"."name",
weather.temp_lo as "tempLo",
weather.temp_hi as "tempHi",
"weather"."prcp",
"weather"."date"
from
"cities"
left join
"weather"
on
"cities"."name" = "weather"."city"
order by
"weather"."date"
動かしてみる
それでは、テーブルが空の状態で動かしてみます。
API一覧
APIのメソッドは、すべてPOSTです。
| PATH | DB | トランザクションの成否 |
|---|---|---|
| /postgres/good | PostgreSQL用 | 成功する |
| /SQLite/good | SQLite用 | 成功する |
| /postgres/bad | PostgreSQL用 | 失敗する |
| /SQLite/bad | SQLite用 | 失敗する |
失敗パターン
失敗パターンから動かしてみます。
処理後のログを見ると、トランザクションがコミットされずにロールバックされています。
また、処理後のテーブルはすべて空のままでした。
PostgreSQLの失敗パターン
Postmanのスナップショット：
Kyselyが出力したログ：
begin
[]
insert into "cities" ("name", "location") values ($1, $2) on conflict ("name") do update set "location" = "excluded"."location"
[ 'San Francisco', '(-194.0, 53.0)' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-01' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-02' ]
rollback
[]
SQLiteの失敗パターン
Postmanのスナップショット：
Kyselyが出力したログ：
begin
[]
insert into "cities" ("name", "location") values (?, ?) on conflict ("name") do update set "location" = "excluded"."location"
[ 'San Francisco', '(-194.0, 53.0)' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-01' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-02' ]
rollback
[]
成功パターン
次は成功パターンです。
処理後のログを見ると、トランザクションがコミットまで進んでいます。
また、APIのレスポンスを見て、データがテーブルに挿入されていることが確認できました。
なお、親テーブルへのUPSERT処理について、本記事ではINSERT部分しか動作確認しませんでしたが、UPDATE部分も成功することを確認しました。
PostgreSQLの成功パターン
Postmanのスナップショット：
Kyselyが出力したログ：
begin
[]
insert into "cities" ("name", "location") values ($1, $2) on conflict ("name") do update set "location" = "excluded"."location"
[ 'San Francisco', '(-194.0, 53.0)' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-01' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-02' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values ($1, $2, $3, $4, $5)
[ 'San Francisco', 43, 57, 0, '2023-01-03' ]
commit
[]
select "cities"."name", weather.temp_lo as "tempLo", weather.temp_hi as "tempHi", "weather"."prcp", "weather"."date" from "cities" left join "weather" on "cities"."name" = "weather"."city" order by "weather"."date"
[]
SQLiteの成功パターン
Postmanのスナップショット：
Kyselyが出力したログ：
begin
[]
insert into "cities" ("name", "location") values (?, ?) on conflict ("name") do update set "location" = "excluded"."location"
[ 'San Francisco', '(-194.0, 53.0)' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-01' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-02' ]
insert into "weather" ("city", "temp_lo", "temp_hi", "prcp", "date") values (?, ?, ?, ?, ?)
[ 'San Francisco', '43', '57', 0, '2023-01-03' ]
commit
[]
select "cities"."name", CAST(weather.temp_lo AS TEXT) as "tempLo", CAST(weather.temp_hi AS TEXT) as "tempHi", "weather"."prcp", "weather"."date" from "cities" left join "weather" on "cities"."name" = "weather"."city" order by "weather"."date"
[]
まとめ
Kyselyでトランザクション（コミットとロールバック）、UPSERT、テーブルのJOINを試しました。
PostgreSQL、SQLiteともに、期待通りに動きました。