背景

• Cloudflare D1（SQLite3互換）を最近使っていて、DATE型が存在しないことに気づいた。SQLiteの型システムは他のRDBMSと考え方が異なるので、ここで整理した
• D1はSQLite互換のため、同じ型システムが適用される。アプリ側で日付を扱いたい場合は、TEXT（ISO8601）かINTEGER（UNIX時間）のどちらかに統一して保存するのが実用的だった

データ型

ストレージクラスは5種類

• SQLiteが値を格納する際の実体はこの5種類のみ
• DATEやBOOLEANのようなストレージクラスは存在しない

型アフィニティという仕組み

列定義にはDATEやVARCHAR(255)のような任意の型名を書けるが、これは「型アフィニティ」というヒントとして扱われるだけで、宣言した型名の文字列に応じて次のルールでいずれかのアフィニティに変換される。

• DATEはどのルールにも一致しないため、NUMERICアフィニティになる
• NUMERICアフィニティの列は、可能ならINTEGERまたはREALに変換され、不可能ならTEXTのまま格納される

日付・真偽値はどう表現するか

• BOOLEANはINTEGERの0/1として格納
• DATE/DATETIMEは次のいずれかで表現するのが一般的
  • TEXT: ISO8601形式の文字列（YYYY-MM-DD HH:MM:SS.SSS）
  • REAL: ユリウス日
  • INTEGER: UNIXタイムスタンプ（エポック秒）
• 組み込みの日付関数（date()、datetime()、strftime()など）はこれらの表現を前提に動作する

制約

制約とキー

型システムに加えて、CHECK制約・FK制約・PK・UUIDの扱いもSQLite特有のクセがあるので合わせて整理する。

CHECK制約

• 列レベル・テーブルレベルのどちらでもCHECK (条件)として定義できる
• デフォルトで常に有効、無効化する仕組みはない
• D1でも同様にデフォルトで有効

FK（外部キー）制約

• D1はクエリ単位でFK制約を無効化できない
• トランザクション内で一時的にチェックを遅延させたい場合はPRAGMA defer_foreign_keys = onを使う（RESTRICT指定の関係は即時エラーになり対象外）

PRIMARY KEY（PK）

• INTEGER PRIMARY KEYは特別扱いで、内部的なrowidのエイリアスになる（自動採番）
• AUTOINCREMENTを付けると、削除された最大IDの再利用を防げる（代わりにオーバーヘッドが増える）
• PKの型がINTEGER以外（TEXTなど）の場合はrowidのエイリアスにならず、通常の列として暗黙のUNIQUEインデックスで一意性が保たれる
• PRIMARY KEY (col1, col2)のようにテーブルレベルで複合PKも定義できる
• WITHOUT ROWIDを指定すると、PKそのものをテーブルの実体キーとして扱い、rowidを持たないテーブルにできる

UUIDの扱い

• SQLiteにネイティブなUUID型はなく、TEXT（36文字の文字列）かBLOB（16バイト）で格納するのが一般的
• D1（Cloudflare Workers）では、アプリ側でcrypto.randomUUID()を使ってUUIDv4を生成し、TEXT列に挿入する構成がよく使われる
• UUIDv4はランダムな値のため、INTEGER PRIMARY KEYのような連番PKと比べて挿入順序が不規則になり、B-treeのページ分割が増えやすい
• 挿入局所性を保ちたい場合は、時刻順にソート可能なUUIDv7のような方式を検討するとよい

まとめ

SQLiteのストレージクラスはNULL/INTEGER/REAL/TEXT/BLOBの5つのみで、DATE型は存在しない。列にDATEと宣言してもNUMERICアフィニティとして扱われるだけで、実体はINTEGER・REAL・TEXTのいずれかに格納される。D1でも同様の仕組みなので、日付はTEXT（ISO8601）かINTEGER（UNIX時間）で統一して保存するのが実用的。

制約面では、CHECKはSQLite・D1共にデフォルト有効だが、FKはSQLiteがデフォルト無効・D1がデフォルト有効という違いがある。PKはINTEGER PRIMARY KEYだとrowidのエイリアスになる点に注意が必要で、UUIDを使う場合はTEXT/BLOBで保存しつつ挿入局所性にも気を配るとよい。