TypeScript用ORM「Drizzle ORM」をDBファーストで使う試み（SQLite編）

# 本記事で行うこと

以前、Go言語用のORM「[Bun](https://bun.uptrace.dev/)」をデータベースファーストで使えるか試しました。
それと同様に、TypeScript用のORM「[Drizzle ORM](https://orm.drizzle.team/)」をデータベースファーストで使えるか試します。
Webフレームワークは[Hono](https://hono.dev/)を使用します。

- 以前書いた記事：[チーム開発参加の記録【2023-10～2024-03】(1) Go言語用ORM「Bun」をDBファーストで使う試み（SQLite使用）](https://qiita.com/kanedaq/items/97ca682602b626d5d717)」

## 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 TABLE foo (
    col_text           TEXT    NOT NULL,
    col_text_null      TEXT,
    col_int            INTEGER NOT NULL,
    col_int_null       INTEGER,
    col_real           REAL    NOT NULL,
    col_real_null      REAL,
    col_timestamp      REAL    NOT NULL,
    col_timestamp_null REAL
) STRICT;
```

## 上記DDLの意図

SQLite3のSTRICTテーブルで使える型は以下に限られています。

- INT
- INTEGER
- REAL
- TEXT
- BLOB
- ANY

このうち、私のプロジェクトで使う可能性が高い以下の型を試します。

- TEXT
- INTEGER
- REAL

加えて、日付・時刻も試します。
日付・時刻は、以下の型で保存できます。

- TEXT：ISO-8601形式
- REAL：ユリウス通日
- INTEGER：1970年からの秒数

本記事では日付・時刻をREAL型で保存することにしました。理由は、

- INTEGER型で持つと精度が秒になるが、REAL型やTEXT型は秒の小数の精度まで持てる
- TEXT型は保存フォーマットに気を遣う必要があって面倒だし、バイト数もREAL型より大きい

以上が上記DDLの意図です。

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

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

```sql
INSERT INTO foo
(
    col_text,
    col_text_null,
    col_int,
    col_int_null,
    col_real,
    col_real_null,
    col_timestamp,
    col_timestamp_null
)
VALUES
(
    'text_data',
    NULL,
    1234567890123456789,
    NULL,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    NULL,
    JULIANDAY('now'),
    NULL
),
(
    'text_data',
    'text_data',
    1234567890123456789,
    1234567890123456789,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    3.141592653589793238462643383279502884197169399375105820974944592307816406286208998628034825342117,
    JULIANDAY('now'),
    JULIANDAY('now')
);
```

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

```sql
SELECT
    col_text,
    col_text_null,
    col_int,
    col_int_null,
    col_real,
    col_real_null,
    STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp) AS col_timestamp,
    STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp_null) AS col_timestamp_null
FROM
    foo;
```

SELECT文の結果です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/d467c2b0-8dd5-6dc9-c707-246dd9538ab9.png)

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

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

## プロジェクト作成

プロジェクト名は「exercise_drizzle_sqlite」にしました。
どのテンプレートを使うか聞かれたので、「bun」を選択しました。
参考ページ： https://hono.dev/getting-started/basic

```sh
bunx create-hono exercise_drizzle_sqlite
```

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

プロジェクトディレクトリ直下で、以下を実行しました。
参考ページ： https://orm.drizzle.team/docs/get-started-sqlite#better-sqlite3

```sh
bun add drizzle-orm better-sqlite3
bun add -D drizzle-kit
```

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

DBファーストなので、コードからDBスキーマを生成するのではなく、DBスキーマからコードを生成する順になりました。
プロジェクトディレクトリ直下で、以下のdrizzle-kitコマンドを実行しました。
urlオプションには、SQLite3のデータファイルを指定します。

```sh
drizzle-kit introspect:sqlite --driver=better-sqlite --url=/home/user/DataGripProjects/sqlite/exercise.sqlite3
```

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

drizzle/ディレクトリ配下のファイルは、drizzle-kitコマンドを実行したときに自動生成されたものです。

```
Project Root
  ├── drizzle/
  │     ├── meta/
  │     │     ├── 0000_snapshot.json
  │     │     └── journal.json
  │     ├── 0000_violet_mentallo.sql
  │     └── schema.ts
  ├── src/
  │     └── index.ts
  ├── package.json
  ├── README.md
  └── tsconfig.json
```

## drizzle/schema.ts

自動生成されたコードです。
src/index.tsからimportして使用します。

```drizzle/schema.ts
import { sqliteTable, AnySQLiteColumn, text, integer, real } from "drizzle-orm/sqlite-core"
  import { sql } from "drizzle-orm"

export const foo = sqliteTable("foo", {
	colText: text("col_text").notNull(),
	colTextNull: text("col_text_null"),
	colInt: integer("col_int").notNull(),
	colIntNull: integer("col_int_null"),
	colReal: real("col_real").notNull(),
	colRealNull: real("col_real_null"),
	colTimestamp: real("col_timestamp").notNull(),
	colTimestampNull: real("col_timestamp_null"),
});
```

## src/index.ts

メインのコードです。

```src/index.ts
import { Hono } from "hono";
import { Database } from "bun:sqlite";
import { drizzle } from "drizzle-orm/bun-sqlite";
import { sql, eq } from "drizzle-orm";
import { foo } from "../drizzle/schema"

const sqlite = new Database("/home/user/DataGripProjects/sqlite/exercise.sqlite3");
const db = drizzle(sqlite, { logger: true });

const app = new Hono()
app.notFound((c) => c.json({ message: 'Not Found', ok: false }, 404))

app.get("/api/selectFoo", (c) => {
    const resultSet = db.select(
        {
            colText: foo.colText,
            colTextNull: foo.colTextNull,
            colInt: sql`CAST(col_int AS TEXT)`,
            colIntNull: sql`CAST(col_int_null AS TEXT)`,
            colReal: foo.colReal,
            colRealNull: foo.colRealNull,
            colTimestamp: sql`STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp)`,
            colTimestampNull: sql`STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp_null)`,
        }
        ).from(foo)
        .all()

    return c.json(resultSet)
})

app.put("/api/updateFoo", async (c) => {
    await db.update(foo)
        .set({
            colTextNull: "ABC",
        })
        .where(sql`${foo.colTextNull} IS NULL`)

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

app.post("/api/insertFoo", async (c) => {
    await db.insert(foo)
        .values({
            colText: "XYZ",
            colTextNull: null,
            colInt: 123,
            colIntNull: null,
            colReal: 3.14,
            colRealNull: null,
            colTimestamp: sql`JULIANDAY('now')`,
            colTimestampNull: null
        })

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

app.delete("/api/deleteFoo", async (c) => {
    await db.delete(foo)
        .where(eq(foo.colText, "XYZ"))

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

export default app
```

## package.json

```package.json
{
  "scripts": {
    "dev": "bun run --hot src/index.ts"
  },
  "dependencies": {
    "better-sqlite3": "^9.3.0",
    "drizzle-orm": "^0.29.3",
    "hono": "^3.12.6"
  },
  "devDependencies": {
    "@types/bun": "^1.0.0",
    "drizzle-kit": "^0.20.13"
  }
}
```

## tsconfig.json

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

# CRUD操作

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

## SELECT文

まずはSELECT文です。

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

ハンドラの中身を見ていきます。
SQLが透けて見えますね。
私みたいにSQLを書きたい人には、嬉しいORMだと思いました。

「sql``」で囲った部分は、Drizzle ORMがSQLを生成するときにそのまま使われるので、DB側の構文やファンクションを使用できます。
DBでINT型で定義された列は、TypeScriptのnumber型で受け取ると、桁数が多い場合に丸め誤差が生じるので、TEXT型にキャストしました。

```typescript
    const resultSet = db.select(
        {
            colText: foo.colText,
            colTextNull: foo.colTextNull,
            colInt: sql`CAST(col_int AS TEXT)`,
            colIntNull: sql`CAST(col_int_null AS TEXT)`,
            colReal: foo.colReal,
            colRealNull: foo.colRealNull,
            colTimestamp: sql`STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp)`,
            colTimestampNull: sql`STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp_null)`,
        }
        ).from(foo)
        .all()

    return c.json(resultSet)
```

### Drizzle ORMが生成したSQL

上記コードからDrizzle ORMが生成したSQLを、整形して見やすくしました。

```sql
select
    "col_text",
    "col_text_null",
    CAST(col_int AS TEXT),
    CAST(col_int_null AS TEXT),
    "col_real",
    "col_real_null",
    STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp),
    STRFTIME('%Y-%m-%dT%H:%M:%fZ', col_timestamp_null)
from
    "foo"
```

### selectFoo APIの実行結果

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

```json
[
    {
        "colText": "text_data",
        "colTextNull": null,
        "colInt": "1234567890123456789",
        "colIntNull": null,
        "colReal": 3.141592653589793,
        "colRealNull": null,
        "colTimestamp": "2024-01-21T01:47:36.868Z",
        "colTimestampNull": null
    },
    {
        "colText": "text_data",
        "colTextNull": "text_data",
        "colInt": "1234567890123456789",
        "colIntNull": "1234567890123456789",
        "colReal": 3.141592653589793,
        "colRealNull": 3.141592653589793,
        "colTimestamp": "2024-01-21T01:47:36.868Z",
        "colTimestampNull": "2024-01-21T01:47:36.868Z"
    }
]
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/5658ccbb-359f-b51c-a3f6-ef4f8e319df5.png)

## UPDATE文

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

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

ハンドラのクエリー部分を見ます。
col_text_null列がNULLの行について、同じ列を"ABC"に更新する内容です。

```typescript
    await db.update(foo)
        .set({
            colTextNull: "ABC",
        })
        .where(sql`${foo.colTextNull} IS NULL`)
```

### Drizzle ORMが生成したSQL

上記コードからDrizzle ORMが生成したSQLを、整形して見やすくしました。

```sql
update
    "foo"
set
    "col_text_null" = ?
where
    "foo"."col_text_null" IS NULL

-- params: ["ABC"]
```

### updateFoo APIの実行結果

updateFoo APIを呼び出した結果です。
col_text_nullがNULLだった行の意図した列が、意図した値に更新されました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/de6fab79-9098-4969-deec-a619ea9326cf.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/d8c7c4ef-249d-78c6-0102-a6b16f1ea200.png)

## INSERT文

次はINSERT文を見ていきます。
col_text列が"XYZ"の行を挿入する内容です。

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

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

```typescript
    await db.insert(foo)
        .values({
            colText: "XYZ",
            colTextNull: null,
            colInt: 123,
            colIntNull: null,
            colReal: 3.14,
            colRealNull: null,
            colTimestamp: sql`JULIANDAY('now')`,
            colTimestampNull: null
        })
```

### Drizzle ORMが生成したSQL

上記コードからDrizzle ORMが生成したSQLを、整形して見やすくしました。

```sql
insert into "foo" (
    "col_text",
    "col_text_null",
    "col_int",
    "col_int_null",
    "col_real",
    "col_real_null",
    "col_timestamp",
    "col_timestamp_null"
)
values (
    ?,
    ?,
    ?,
    ?,
    ?,
    ?,
    JULIANDAY('now'),
    ?
)


-- params: [
    "XYZ",
    null,
    123,
    null,
    3.14,
    null,
    null
]
```

### insertFoo APIの実行結果

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/7eb6ec98-3f87-8649-825b-245e4e7b878a.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/140f3e76-caa6-4b64-869a-621ca2da93f6.png)

## DELETE文

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

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

ハンドラのクエリー部分を見ます。
col_text列が"XYZ"の行を削除する内容です。

```typescript
    await db.delete(foo)
        .where(eq(foo.colText, "XYZ"))
```

### Drizzle ORMが生成したSQL

上記コードからDrizzle ORMが生成したSQLを、整形して見やすくしました。

```sql
delete from
    "foo"
where
    "foo"."col_text" = ?

-- params: ["XYZ"]
```

### deleteFoo APIの実行結果

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/279c08fd-b641-9616-c3f8-86581d7e8d42.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/11bde2cf-a1a8-36da-14ea-8d14d0ec0619.png)

# まとめ

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