データベース設計・SQL→Drizzle ORM対応

SQLは既に知っている前提で話を進める。このドキュメントの目的は「書いてきたSQL文をTypeScript（Drizzle ORM）でどう表現するか」の対応関係を整理することである。

この章で作るもの（全体像）

b13 で in-memory だった Todo を、Docker で起動した PostgreSQL に保存する。Hono のルートハンドラは Drizzle ORM 経由で型安全に SQL を発行する。

Oracle SQL → PostgreSQL（Drizzle ORM）対応表

スキーマ定義（TypeScriptで書く）

Oracle SQLのDDLと並べて確認する：

-- Oracle SQL での定義
CREATE TABLE clinical_trials (
  trial_id    VARCHAR2(36) DEFAULT SYS_GUID() PRIMARY KEY,
  drug_code   VARCHAR2(20) NOT NULL,
  phase       NUMBER(1) NOT NULL,
  start_date  DATE NOT NULL,
  status      VARCHAR2(20) DEFAULT 'ACTIVE',
  created_at  TIMESTAMP DEFAULT SYSDATE
);

// Drizzle ORM での同等定義
import { pgTable, text, integer, date, timestamp } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const clinicalTrials = pgTable("clinical_trials", {
  trialId: text("trial_id").primaryKey().default(sql`gen_random_uuid()`),
  drugCode: text("drug_code").notNull(),
  phase: integer("phase").notNull(),
  startDate: date("start_date").notNull(),
  status: text("status").default("ACTIVE"),
  createdAt: timestamp("created_at").defaultNow(),
});

カラム名はTypeScriptではキャメルケース（drugCode）、DBにはスネークケース（drug_code）として保存される。Drizzleが自動的にマッピングする。

よく使うクエリのDrizzle変換

import { eq, gt, desc, and, avg, count } from "drizzle-orm";

// SELECT ... WHERE
const active = await db
  .select()
  .from(clinicalTrials)
  .where(eq(clinicalTrials.status, "ACTIVE"));

// JOIN
const results = await db
  .select({
    trialId: clinicalTrials.trialId,
    drugCode: clinicalTrials.drugCode,
    resultValue: trialResults.value,
  })
  .from(clinicalTrials)
  .innerJoin(trialResults, eq(clinicalTrials.trialId, trialResults.trialId))
  .where(eq(clinicalTrials.phase, 3))
  .orderBy(desc(clinicalTrials.startDate));

// GROUP BY + HAVING（集計）
const summary = await db
  .select({
    drugCode: clinicalTrials.drugCode,
    avgValue: avg(trialResults.value),
    sampleCount: count(),
  })
  .from(clinicalTrials)
  .innerJoin(trialResults, eq(clinicalTrials.trialId, trialResults.trialId))
  .groupBy(clinicalTrials.drugCode)
  .having(gt(count(), 10));

SQLそのままでは書けないが、関数チェーンの構造は SELECT句 → FROM → JOIN → WHERE → GROUP BY → ORDER BY の順序と対応している。

テーブル設計のポイント（医療・製薬データ）

医療・製薬データを扱う際に設計段階で考慮すべき点：

テーブル設計例（医療・製薬コンテキスト）

// 患者テーブル（匿名化IDを主キーに使う設計）
export const patients = pgTable("patients", {
  id: text("id").primaryKey().default(sql`gen_random_uuid()`),
  // 氏名はアプリ側で暗号化してから格納（生の個人情報をDBに直書きしない）
  nameEncrypted: text("name_encrypted").notNull(),
  birthDate: date("birth_date").notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});

// 検査結果テーブル（患者と1対多のリレーション）
export const labResults = pgTable("lab_results", {
  id: text("id").primaryKey().default(sql`gen_random_uuid()`),
  patientId: text("patient_id").notNull().references(() => patients.id),
  testDate: date("test_date").notNull(),
  glucose: decimal("glucose", { precision: 5, scale: 2 }),
  hba1c: decimal("hba1c", { precision: 4, scale: 2 }),
  // AI解析結果を格納する場合、承認フラグを持たせる
  aiSummary: text("ai_summary"),
  approvedBy: text("approved_by"),   // 承認者のID
  approvedAt: timestamp("approved_at"), // null = 未承認
});

approvedAt が null のレコードは「AIが処理済みだが人間がまだ確認していない状態」を意味する。このパターンはHuman-in-the-loopワークフローの基本設計（b20参照）。

リレーションの種類

1対多（1:N）: 最も一般的。1人の患者が複数の検査結果を持つ。

patients (1) ←── lab_results (N)

多対多（N:M）: 中間テーブルで表現する。患者と担当医師が相互に複数持てる場合：

patients ←── patient_doctors ──→ doctors

1対1（1:1）: 特定の拡張情報を別テーブルに分離するとき（個人情報テーブルの分離など）。

Drizzle ORM（TypeScriptからDBを操作する）

直接SQLを書く代わりに、TypeScriptのコードとしてDB操作を記述できる：

// スキーマ定義（TypeScriptで書く）
import { pgTable, text, decimal, date, timestamp } from "drizzle-orm/pg-core";

export const patients = pgTable("patients", {
  id: text("id").primaryKey().default(sql`gen_random_uuid()`),
  name: text("name").notNull(),
  birthDate: date("birth_date").notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});

export const labResults = pgTable("lab_results", {
  id: text("id").primaryKey().default(sql`gen_random_uuid()`),
  patientId: text("patient_id").notNull().references(() => patients.id),
  glucose: decimal("glucose", { precision: 5, scale: 2 }),
  testDate: date("test_date").notNull(),
});

// クエリ（型安全）
const results = await db
  .select()
  .from(labResults)
  .where(eq(labResults.patientId, patientId))
  .orderBy(desc(labResults.testDate));

TypeScriptの型が自動的に生成されるため、コンパイル時に列名の誤りを検出できる。

インデックス

検索が遅い場合はインデックスを追加する。よく検索条件に使うカラムに設定：

-- patient_idでの検索が多い場合
CREATE INDEX idx_lab_results_patient_id ON lab_results(patient_id);
CREATE INDEX idx_lab_results_test_date ON lab_results(test_date DESC);

手を動かす

b13 で in-memory だった Todo API を、PostgreSQL + Drizzle ORM で永続化する。API が再起動してもデータが残るようになる。

やること

1. リポジトリルートに docker-compose.yml を置き、Postgres をコンテナで起動
2. api/ に Drizzle を導入し、todos テーブルを TypeScript で定義
3. api/src/index.ts の in-memory 配列を Drizzle クエリに置き換え

services:
  postgres:
    image: postgres:16-alpine
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: todo_dev
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

cd api
pnpm add drizzle-orm pg
pnpm add -D drizzle-kit @types/pg

import { pgTable, serial, text, boolean, timestamp } from 'drizzle-orm/pg-core';

export const todos = pgTable('todos', {
  id: serial('id').primaryKey(),
  text: text('text').notNull(),
  done: boolean('done').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export type Todo = typeof todos.$inferSelect;
export type NewTodo = typeof todos.$inferInsert;

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL ?? 'postgres://app:app@localhost:5432/todo_dev',
});

export const db = drizzle(pool);

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL ?? 'postgres://app:app@localhost:5432/todo_dev',
  },
});

# Postgres 起動
docker compose up -d postgres

# スキーマを DB に反映（本章では db:push で即時反映。b16 で migration 方式を学ぶ）
cd api
pnpm drizzle-kit push

import { Hono } from 'hono';
import { cors } from 'hono/cors';
import { serve } from '@hono/node-server';
import { eq } from 'drizzle-orm';
import { db } from './db/client';
import { todos } from './db/schema';

const app = new Hono();
app.use('/api/*', cors());

app.get('/api/todos', async (c) => {
  const rows = await db.select().from(todos);
  return c.json(rows);
});

app.get('/api/todos/:id', async (c) => {
  const id = Number(c.req.param('id'));
  const [todo] = await db.select().from(todos).where(eq(todos.id, id));
  if (!todo) return c.json({ error: 'not found' }, 404);
  return c.json(todo);
});

app.post('/api/todos', async (c) => {
  const { text } = await c.req.json<{ text: string }>();
  if (!text?.trim()) return c.json({ error: 'text is required' }, 400);
  const [todo] = await db.insert(todos).values({ text }).returning();
  return c.json(todo, 201);
});

app.patch('/api/todos/:id', async (c) => {
  const id = Number(c.req.param('id'));
  const [current] = await db.select().from(todos).where(eq(todos.id, id));
  if (!current) return c.json({ error: 'not found' }, 404);
  const [updated] = await db.update(todos).set({ done: !current.done }).where(eq(todos.id, id)).returning();
  return c.json(updated);
});

serve({ fetch: app.fetch, port: 3333 });

成功条件

• docker compose ps で postgres コンテナが running になっていること
• pnpm drizzle-kit push の出力に Everything is in sync が含まれていること
• psql postgres://app:app@localhost:5432/todo_dev -c '\d todos' で id / text / done / created_at の 4 列が表示されていること
• ブラウザで Todo を追加した後、api を再起動しても画面上に同じ Todo が残っていること
• docker compose down -v でボリュームごと消した後に起動し直すと、一覧が空になっていること（DB に入っている証拠）

次の章（b16）で カラム追加をマイグレーションとして管理する 方法を扱う。

参考リソース

• Drizzle ORM（https://orm.drizzle.team/）— TypeScript向けORMの公式ドキュメント
• PostgreSQL公式ドキュメント（https://www.postgresql.org/docs/）
• 『データベース設計のベストプラクティス』— 正規化とインデックス設計の入門
• HIPAA Technical Safeguards（医療データの技術的保護要件）— 医療アプリ設計の参考

確認クイズ

発展：より実務的な設計パターン

複合主キー・複合インデックス

「多対多テーブル」（フォロー・リアクション等）は自然な主キーを持ちません。Drizzle では primaryKey() に複数カラムを渡します：

import { pgTable, text, timestamp, primaryKey, index } from "drizzle-orm/pg-core";

export const reactions = pgTable(
  "reactions",
  {
    userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
    postId: text("post_id").notNull().references(() => posts.id, { onDelete: "cascade" }),
    emoji: text("emoji").notNull(),
    createdAt: timestamp("created_at").defaultNow(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.postId, t.emoji] }),
    postIdx: index("reactions_post_idx").on(t.postId),
  }),
);

複合主キーの順序 = 暗黙の複合インデックス。(userId, postId, emoji) の順なら WHERE user_id = ? は速いが、WHERE post_id = ? 単独は遅い。だから post_id 単独の検索用に 2 つ目のインデックスを張ります。

N+1 問題と JOIN 最適化

悪い例: 各投稿ごとに authorを個別取得 → N+1 クエリ

const posts = await db.select().from(postsTable);
for (const post of posts) {
  const [author] = await db.select().from(users).where(eq(users.id, post.authorId));
  // 1 投稿ごとに 1 クエリ = N+1
}

良い例: Drizzle の with で 1 クエリで取得

const postsWithAuthor = await db.query.posts.findMany({
  with: {
    author: { columns: { id: true, name: true } },  // 機密カラムは除外
  },
  orderBy: (p, { desc }) => desc(p.createdAt),
  limit: 20,
});

これには relations() の定義が必要：

import { relations } from "drizzle-orm";

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  reactions: many(reactions),
}));

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

COUNT 集計の JOIN

「各投稿のいいね数」を 1 クエリで：

import { sql, eq } from "drizzle-orm";

const postsWithLikeCount = await db
  .select({
    id: posts.id,
    content: posts.content,
    likeCount: sql<number>`count(${reactions.userId})`.as("like_count"),
  })
  .from(posts)
  .leftJoin(reactions, eq(reactions.postId, posts.id))
  .groupBy(posts.id);

leftJoin なので、リアクション 0 個の投稿も拾えます。

アプリレベルの暗号化（個人情報列）

DB 側の暗号化（TDE）に加え、アプリ側で機密カラムだけ暗号化する選択肢：

// api/src/lib/crypto.ts
import { createCipheriv, createDecipheriv, randomBytes } from "node:crypto";

const KEY = Buffer.from(process.env.FIELD_ENCRYPTION_KEY!, "hex"); // 32 bytes hex
const ALGO = "aes-256-gcm";

export function encryptField(plain: string): string {
  const iv = randomBytes(12);
  const cipher = createCipheriv(ALGO, KEY, iv);
  const enc = Buffer.concat([cipher.update(plain, "utf8"), cipher.final()]);
  const tag = cipher.getAuthTag();
  // iv + tag + ciphertext を Base64 で保存
  return Buffer.concat([iv, tag, enc]).toString("base64");
}

export function decryptField(stored: string): string {
  const buf = Buffer.from(stored, "base64");
  const iv = buf.subarray(0, 12);
  const tag = buf.subarray(12, 28);
  const enc = buf.subarray(28);
  const decipher = createDecipheriv(ALGO, KEY, iv);
  decipher.setAuthTag(tag);
  return Buffer.concat([decipher.update(enc), decipher.final()]).toString("utf8");
}

使用例（氏名だけ暗号化）：

const nameEncrypted = encryptField(plainName);
await db.insert(patients).values({ nameEncrypted, birthDate });

// 取り出すとき
const [p] = await db.select().from(patients).where(eq(patients.id, id));
const plainName = decryptField(p.nameEncrypted);

注意: 暗号化したカラムは 検索できない（WHERE name = ? が使えない）。検索が必要な場合はハッシュ化した別カラムも持つ等の設計が必要です。

ソフトデリート vs ハードデリート

削除の表現方法：

export const posts = pgTable("posts", {
  id: text("id").primaryKey(),
  content: text("content").notNull(),
  deletedAt: timestamp("deleted_at"),  // null = 生存
});

GDPR（本カリキュラムでは気にしなくていいが将来に備えて）は「完全削除が必要な場合」を規定しているので、ソフトデリート一択ではありません。

参考リソース

• Drizzle ORM（https://orm.drizzle.team/）— TypeScript向けORMの公式ドキュメント
• Drizzle Relations: https://orm.drizzle.team/docs/rqb#declaring-relations
• PostgreSQL公式ドキュメント（https://www.postgresql.org/docs/）
• PostgreSQL インデックス種別: https://www.postgresql.org/docs/current/indexes-types.html
• Node.js crypto: https://nodejs.org/api/crypto.html
• 『データベース設計のベストプラクティス』— 正規化とインデックス設計の入門
• HIPAA Technical Safeguards（医療データの技術的保護要件）— 医療アプリ設計の参考
• 前提: b07-er-diagram（エンティティ設計）
• 続き: b16-migration（スキーマ変更の運用）

生きているコード

本ドキュメントで扱ったパターンの完全な動作コードは、メンター側リポジトリの参照ブランチで確認できます。

• 対応 Week: W3 / W15
• 参照ブランチ:
• W3: reference/week-3
• W15: reference/week-15
• 対応 checklist 項目: M1-M8

ブランチの作り方・見方は b00-curriculum-map を参照してください。