SQLite/Cloudflare D1 で boolean カラムを設計するときの定石

 ## 概要 SQLite には `BOOLEAN` 型がありません。「ON/OFF トグル」を素直に書きたいだけなのに、何で表現すべきか毎回悩んでしまう方向けの定石まとめです。Cloudflare D1（中身は SQLite）でも同じパターンが通用します。 結論を先に書くと: ```sql ALTER TABLE settings ADD COLUMN show_dashboard_notices INTEGER NOT NULL DEFAULT 1 CHECK (show_dashboard_notices IN (0, 1)); ``` この4要素（`INTEGER` / `NOT NULL` / `DEFAULT` / `CHECK IN (0, 1)`）が定石です。それぞれ理由があります。 ## 4つの要素を理由付きで ### 1. `INTEGER` を使う SQLite の型は柔らかい（dynamic typing）ですが、整数 0/1 が boolean の慣例です。`TEXT` で `'true'/'false'` も技術的には可能ですが： - ストレージサイズが大きい - 比較演算が遅い - アプリ側の boolean 化コストがかかる 迷ったら `INTEGER` にしましょう。 ### 2. `NOT NULL` を必ず付ける NULL を許すと「OFF と未設定」の区別が暗黙化して、後の集計や条件分岐でバグの温床になります。明示的に「未設定」を表現したいなら別カラム（`is_configured`）を持つ方がきれいです。 ### 3. `DEFAULT 1` または `DEFAULT 0` を付ける `ALTER TABLE ... ADD COLUMN` のときは特に重要です。既存行は新カラムの値を持たないので、`DEFAULT` が無いと NOT NULL 制約違反でマイグレーションが落ちます。 「機能をオンで提供する」なら `DEFAULT 1`、「明示的にオプトインさせる」なら `DEFAULT 0` にします。プロダクトの方針として既存ユーザへの後方互換を考えて選びましょう。 ### 4. `CHECK (col IN (0, 1))` で値域を制約 `INTEGER` は -2^63 〜 2^63-1 を受け付けるので、`CHECK` 無しだと `2` や `-1` が入って後で原因不明のバグになります。CHECK で 0/1 のみに絞ると、データの誤りはマイグレーション/INSERT の時点で弾けます。 ## マイグレーション例（既存テーブルへ追加） ```sql PRAGMA foreign_keys = ON; ALTER TABLE notification_preferences ADD COLUMN allow_email INTEGER NOT NULL DEFAULT 1 CHECK (allow_email IN (0, 1)); ``` `PRAGMA foreign_keys = ON` は安全側で先頭に付ける癖をつけておくと事故が減ります（D1 では既定 ON ですが、プレーン SQLite の手元検証で食い違わないように）。 ## アプリ側の取り扱い TypeScript からは boolean に正規化して扱うのがおすすめです。リポジトリ層で 0/1 ↔ boolean を変換します。 ```ts // repositories/settings.ts export async function getNotificationPref(db: D1Database, userId: number) { const row = await db .prepare('SELECT allow_email FROM notification_preferences WHERE user_id = ?') .bind(userId) .first<{ allow_email: number }>() if (!row) return null return { allowEmail: row.allow_email === 1 } } export async function setNotificationPref(db: D1Database, userId: number, allowEmail: boolean) { await db .prepare('UPDATE notification_preferences SET allow_email = ? WHERE user_id = ?') .bind(allowEmail ? 1 : 0, userId) .run() } ``` API レイヤーやドメイン層では `boolean` を使い、DB 境界（リポジトリ）でだけ 0/1 を意識します。これによりアプリ全体で型安全と表現力を両立できます。 ## アンチパターン - **CHECK 制約なしの INTEGER**: 値域が無制限になり、`2` 等が入る事故が起きます - **TEXT で `'true'/'false'`**: ストレージ・比較コスト・boolean 化のコストが増します - **`NULL` を許して三状態**: 「未設定」表現が必要なら別カラムにします - **アプリ層のみのバリデーション**: マイグレーション時の異常値や直接 SQL 実行で漏れます - **`ALTER TABLE` 後に `UPDATE` で値を埋める**: `DEFAULT` を付けて 1 回の `ALTER` で済ませる方がトランザクションが軽くなります ## まとめ SQLite/D1 で boolean を表現するときは **`INTEGER NOT NULL DEFAULT 0|1 CHECK (col IN (0, 1))`** をテンプレ化しましょう。 - 型: `INTEGER` - NULL 禁止 - DEFAULT 必須（特に ALTER 時） - CHECK で値域固定 - アプリ側はリポジトリ境界で 0/1 ↔ boolean 変換 これを徹底すると、後の機能追加で「あれ、このフラグの値って何が正しいんだっけ」になりにくくなります。 ## 補遺: 関連する設計判断 ### 閾値（threshold）はマイグレーション不要のことが多い 「2件以上で OK → 3件以上で OK に厳格化」のようなしきい値の変更は、定数として TypeScript 側に置いてあれば migration は不要です。ただし「サービス基準が変わった」事実は ADR/CHANGELOG/PR に残しましょう。 ### 監査ログは別テーブル このシリーズのトグルは監査ログ（誰がいつ ON/OFF したか）と組み合わさることが多いです。トグル本体テーブルに `updated_at` だけ持たせるのではなく、`*_audit_log` を別テーブルで持つ方が後々の事故対応で役立ちます。

ALTER TABLE settings
  ADD COLUMN show_dashboard_notices INTEGER NOT NULL
    DEFAULT 1
    CHECK (show_dashboard_notices IN (0, 1));

PRAGMA foreign_keys = ON;

ALTER TABLE notification_preferences
  ADD COLUMN allow_email INTEGER NOT NULL
    DEFAULT 1 CHECK (allow_email IN (0, 1));

// repositories/settings.ts
export async function getNotificationPref(db: D1Database, userId: number) {
  const row = await db
    .prepare('SELECT allow_email FROM notification_preferences WHERE user_id = ?')
    .bind(userId)
    .first<{ allow_email: number }>()
  if (!row) return null
  return { allowEmail: row.allow_email === 1 }
}

export async function setNotificationPref(db: D1Database, userId: number, allowEmail: boolean) {
  await db
    .prepare('UPDATE notification_preferences SET allow_email = ? WHERE user_id = ?')
    .bind(allowEmail ? 1 : 0, userId)
    .run()
}