SQLite STRICT tableで型崩れを防ぐ設計｜型変換・ANY・CHECK・Python

API、CSV、表計算、CADからSQLiteへデータを取り込むとき、列をINTEGERとして定義しただけで数値以外を完全に拒否できるとは限りません。SQLiteの通常tableは柔軟な型付けを採用し、値を変換できない場合でも別のstorage classのまま保存できるためです。

SQLiteのSTRICT tableを使うと、列へ保存できる型を限定できます。ただしSTRICTだけで業務データの正しさが保証されるわけではありません。この記事では、storage classとtype affinity、lossless変換、ANY、CHECK、Python側のvalidationを組み合わせ、型崩れを防ぐ境界設計を整理します。

通常tableで起きる型崩れ

SQLiteでは、値そのものにNULL、INTEGER、REAL、TEXT、BLOBというstorage classがあります。通常tableの列に指定する型は、保存形式を絶対に固定する規則ではなく、値を望ましい型へ変換するtype affinityとして働きます。

たとえば通常tableのINTEGER列へ文字列'123'を入れると整数へ変換されます。一方、整数へ変換できない'unknown'を入れても、通常tableではTEXTとして残る場合があります。同じ列に整数と文字列が混在すると、比較、集計、API出力時の型が不安定になります。

SQLiteのdatatype仕様でも、SQLiteが動的型付けを採用し、型はcontainerではなくvalueに関連付くと説明されています。この柔軟性は一時dataや互換性重視の用途に便利ですが、data contractを強制したいtableでは追加設計が必要です。

STRICT tableで変わること

SQLite 3.37.0以降では、CREATE TABLEの閉じ括弧の後へSTRICTを付けられます。

CREATE TABLE measurements (
    measurement_id       TEXT PRIMARY KEY NOT NULL,
    source_system        TEXT NOT NULL,
    source_record_id     TEXT NOT NULL,
    measured_at_us       INTEGER NOT NULL CHECK (measured_at_us >= 0),
    pressure_pa          INTEGER NOT NULL CHECK (pressure_pa >= 0),
    valid                INTEGER NOT NULL CHECK (valid IN (0, 1)),
    note                 TEXT,
    payload_json         TEXT NOT NULL CHECK (json_valid(payload_json)),
    schema_version       INTEGER NOT NULL CHECK (schema_version >= 1),
    UNIQUE (source_system, source_record_id)
) STRICT;

STRICT tableで列に指定できるdatatype名は、現在の公式仕様ではINT、INTEGER、REAL、TEXT、BLOB、ANYです。BOOLEAN、DATE、DATETIME、DECIMAL、JSONなどをそのままdatatype名にはできません。

そのため、意味と保存形式を次のように分けます。

STRICTは保存型を強くしますが、圧力の単位、IDの一意性、日時の基準、JSON内の必須fieldまでは判断しません。NOT NULL、UNIQUE、CHECK、外部キー、application validationを重ねます。json_valid()を使う場合は、運用するSQLite buildでJSON関数が利用できることも起動時に確認します。

STRICTでも文字列の数値は変換される

STRICT tableは、入力値が列の型と違えば必ず拒否する仕組みではありません。SQLiteは通常のaffinity規則で値を変換し、指定型へ情報を失わず変換できる場合は保存します。

CREATE TABLE samples (
    value INTEGER NOT NULL
) STRICT;

INSERT INTO samples VALUES ('123');

この'123'はINTEGERの123として保存できます。一方、'12MPa'や'unknown'はINTEGERへlosslessに変換できないため、datatype constraint errorになります。

API契約が「JSON numberだけを許可し、文字列"123"は拒否する」であれば、STRICTだけでは足りません。JSONを解析した直後にPython側で型を確認してからDBへ渡します。受信時の型と保存時の型は、別のvalidation段階です。

ANYは型を決められない列ではない

ANYは、整数、実数、文字列、BLOBなどを変換せず保持するdatatypeです。STRICT tableのANYへ文字列'000123'を保存すると、先頭ゼロを含むTEXTのまま残ります。

CREATE TABLE raw_values (
    record_id TEXT PRIMARY KEY NOT NULL,
    raw_value ANY NOT NULL
) STRICT;

INSERT INTO raw_values VALUES ('r-001', '000123');

SELECT typeof(raw_value), quote(raw_value)
FROM raw_values
WHERE record_id = 'r-001';

結果はtextと'000123'です。通常tableのANY列では数値に見える文字列がINTEGERへ変換される場合がありますが、STRICT tableのANYは入力値とdatatypeを保持します。

ただし、業務IDや数量の型を決めたくないという理由でANYを使うと、比較規則が不明確になります。元fileの値を監査用に保存する、複数形式のraw valueを変換前に保持するなど、意図的に型を保存したい列へ限定します。正規化後の列はTEXTやINTEGERへ分けます。

入力から保存までの責務を分ける

安全な取り込みでは、すべてをDB制約だけに任せず、次の順序で処理します。

JSON・CSV・CAD属性を受信
  ↓
文字コードと構文を確認
  ↓
fieldの存在・入力型・表記を検証
  ↓
UTC・Pa・scale付き整数へ正規化
  ↓
parameter bindingでINSERT
  ↓
STRICT・CHECK・UNIQUEで最終防御
  ↓
canonicalなrecordを返す

Pythonではboolがintのsubclassなので、単にisinstance(value, int)とするとTrueも整数として通ります。整数だけを許可するならtype(value) is intのように契約を明確にします。

Pythonで入力型とDB制約を検証する

次の例では、API相当のdictを検証し、日時をUTCのmicrosecond整数へ変換して保存します。

import calendarimport jsonimport sqlite3from datetime import datetime, timezoneSCHEMA_SQL = """CREATE TABLE measurements (    measurement_id       TEXT PRIMARY KEY NOT NULL,    source_system        TEXT NOT NULL,    source_record_id     TEXT NOT NULL,    measured_at_us       INTEGER NOT NULL CHECK (measured_at_us >= 0),    pressure_pa          INTEGER NOT NULL CHECK (pressure_pa >= 0),    valid                INTEGER NOT NULL CHECK (valid IN (0, 1)),    note                 TEXT,    payload_json         TEXT NOT NULL CHECK (json_valid(payload_json)),    schema_version       INTEGER NOT NULL CHECK (schema_version >= 1),    UNIQUE (source_system, source_record_id)) STRICT;"""def parse_utc_microseconds(value: str) -> int:    if not isinstance(value, str) or not value.endswith("Z"):        raise ValueError("measured_atはZ付きUTC文字列が必要です")    try:        dt = datetime.fromisoformat(value.replace("Z", "+00:00"))    except ValueError as exc:        raise ValueError("measured_atの形式が不正です") from exc    if dt.tzinfo is None or dt.utcoffset() != timezone.utc.utcoffset(dt):        raise ValueError("measured_atはUTCで指定してください")    return calendar.timegm(dt.utctimetuple()) * 1_000_000 + dt.microsecond

Pythonのsqlite3公式ドキュメントが案内するparameter substitutionを使い、入力値をSQL文字列へ直接連結しません。with conn:内で制約違反が起きればtransactionはrollbackされます。

正常系と異常系をテストする

最低限、次の組み合わせを自動テストします。

PRAGMA integrity_checkとPRAGMA quick_checkは、STRICT tableについて列の型も検査します。ただし、検査結果がokでも圧力の単位やIDの意味が正しいとは限りません。構造上の整合性検査と業務validationを混同しないことが重要です。

冪等性と再実行を設計する

STRICTは同じrecordの二重登録を防ぐ機能ではありません。再送される連携では、source_systemとsource_record_idの組み合わせへUNIQUE制約を付けます。

同じIDの再送を無視するのか、内容が同じ場合だけ成功扱いにするのか、更新するのかを先に決めます。UPSERTを使う場合も、入力型と業務validationを通した後で実行し、受信時刻やversionを無条件に増やして冪等性を壊さないようにします。

schema変更では、古いSQLite libraryがSTRICTを解釈できるか確認します。STRICT keywordはSQLite 3.37.0以降の機能なので、Pythonではsqlite3.sqlite_versionを記録し、実行環境とmigration testへ含めます。

セキュリティと機密情報の分離

STRICT tableはSQL injectionや情報漏えいを防ぐ機能ではありません。

• 値はparameter bindingで渡す
• table名や列名を外部入力から組み立てない
• API key、token、passwordをpayload JSONへ保存しない
• raw payloadの保存期間と閲覧権限を決める
• 制約errorを外部へそのまま返さず、公開用error codeへ変換する
• DB fileとbackupへ適切なアクセス制御を設定する

元payloadが監査に必要でも、秘密情報を除去した公開用recordと、制限された原本を分けます。

API・CSV・CADへ再利用する

STRICT tableの列定義を共通data contractへすると、API、CSV、CAD属性で同じ規則を再利用できます。

field: pressure_pa
storage_type: INTEGER
input_type: integer
nullable: false
minimum: 0
unit: Pa
boolean_allowed: false
database:
  table_mode: STRICT
  check: pressure_pa >= 0

この定義からJSON Schema、Python validator、CSV列仕様、DBのCHECK、test caseを派生できます。CAD側でMPaを使う場合は抽出直後にPaへ変換し、元値・元単位・変換versionも残します。

まとめ

SQLite STRICT tableは、同じ列へ無関係な型が混在する事故を防ぐための強い最終防御です。ただし、入力値の意味まで自動で保証するものではありません。

• 通常tableのtype affinityとstorage classを区別する
• STRICTで使えるdatatype名を確認する
• losslessに変換できる文字列は受理され得ると理解する
• APIの入力型はPythonなどで変換前に検証する
• 真偽値、日時、Decimal、JSONは保存型と意味を分ける
• ANYはraw valueを型ごと保持する用途へ限定する
• CHECK、NOT NULL、UNIQUEを組み合わせる
• eventやrequestの再送には安定した外部IDを使う
• PRAGMA integrity_checkと業務validationを分ける

入力境界、application、STRICT tableの三層で同じ契約を表現すれば、API、CSV、Python、CADから届いたdataを、型と意味を保ったまま再利用できます。

参考情報

• SQLite: STRICT Tables
• SQLite: Datatypes In SQLite
• SQLite: CREATE TABLE
• SQLite: JSON Functions And Operators
• Python sqlite3 documentation