SQLite には (Rust スタイルの) エディションが必要
SQLite should have (Rust-style) editions (mort.coffee)
要約
この記事では、SQLite のデフォルト設定における2つの大きな問題点を指摘しています。一つは、外部キー制約がデフォルトで無効になっていることで、データの一貫性が損なわれる可能性があることです。もう一つは、列の型定義が緩いため、意図しないデータ型が格納されてしまう可能性があることです。これらの問題に対する解決策として、`PRAGMA foreign_keys = ON;` や `strict` テーブルの使用が提案されていますが、グローバルな設定がないことが課題として挙げられています。
全文翻訳
SQLiteは(Rustスタイルの)エディションを持つべき
日付: 2026-07-15
Git: https://gitlab.com/mort96/blog/blob/published/content/00000-home/00017-sqlite-editions.md
SQLiteは驚くべきデータベースエンジンです。私は多くの組み込みプロジェクトでデータベースとして使用しており、ローカルデータストレージの業界標準と呼んでも誇張ではないと思います。一部のサーバーソフトウェアでさえSQLiteを使用しています。例えば、lobste.rsは現在SQLiteで動作しています。
従来のRDBMS(リレーショナルデータベース管理システム)とは異なり、SQLiteは独立したプロセスではありません。ライブラリとしてのRDBMSであり、ソフトウェアは自己完結型です。従来のファイル形式とは異なり、カスタムシリアライザーやパーサーを記述する必要はありません。ある意味では、両方の世界の最良の部分を兼ね備えています。
しかし、ただ一つ、非常に大きな問題があります。それは、デフォルト設定がすべて間違っていることです。
間違ったデフォルト #1: 外部キー制約がデフォルトで無視される ¶
その通りです。外部キー制約は、データベースの一貫性を保ち、参照の切れ(dangling references)を防ぐための、私たちが持つ主要なツールと言えるでしょう。
簡単な説明として、SQLの外部キー制約は以下のようになります。
CREATE TABLE users (
id INTEGER PRIMARY KEY,
display_name TEXT
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
content TEXT NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id)
);
他のすべてのRDBMSにおける典型的な動作は、投稿のuser_idカラムが常に有効なユーザーIDを参照しなければならないということです。有効なユーザーIDなしで新しい投稿を作成することはできませんし、投稿を削除せずにユーザーを削除しようとすると、外部キー制約違反のエラーが発生します。
SQLiteだけが、デフォルトでこれを強制しないRDBMSです。
SQLiteのROWIDの再利用の傾向により、この問題はさらに悪化します。この例では、INTEGER PRIMARY KEYの行は、テーブルのROWIDのエイリアスになります。ROWIDはSQLiteのテーブルの各行に割り当てられる一意の整数IDです。ROWIDを割り当てるアルゴリズムは少し複雑ですが(詳細はSQLiteのドキュメントを参照)、場合によってはIDの再利用が発生します。これは、参照の切れが簡単に間違った行への参照につながることを意味し、参照の切れよりもさらに悪い状況です。なぜなら、すべてが正常に見えるからです。検索時にもエラーは発生しません。
このおもちゃのデータベーススキーマでの仮説的な操作シーケンスを見てみましょう。
-- ボブがユーザーアカウントを作成します
INSERT INTO users (display_name) VALUES ('Bob');
SELECT * FROM users;
-- id | display_name
-- 1 | Bob
-- ボブが紹介投稿をします
INSERT INTO posts (user_id, content) VALUES (1, 'Hello, I am Bob');
SELECT u.display_name, p.content FROM users as u, posts as p WHERE u.id = p.user_id;
-- display_name | content
-- Bob | Hello, I am Bob
-- ボブがアカウントを削除しますが、
-- 投稿を削除し忘れます。
-- SQLiteは外部キーを無視するため、エラーを生成しません。
DELETE FROM users WHERE id = 1;
-- アリスがアカウントを作成します。
-- アリスはROWIDアルゴリズムにより、ボブが持っていたのと同じIDを取得します。
INSERT INTO users (display_name) VALUES ('Alice');
SELECT * FROM users;
-- id | display_name
-- 1 | Alice
-- アリスはボブの古い投稿を継承してしまいました!
SELECT u.display_name, p.content FROM users as u, posts as p WHERE u.id = p.user_id;
-- display_name | content
-- Alice | Hello, I am Bob
修正方法は、プラグマでforeign_keysを有効にすることです。
PRAGMA foreign_keys = ON;
もし最初からこれを実行していれば、バグのあるDELETEはエラーを生成したでしょう。
DELETE FROM users WHERE id = 1;
-- Runtime error: FOREIGN KEY constraint failed (19)
間違ったデフォルト #2: カラムが間違ったデータ型を格納できる ¶
SQLiteはシンプルな型システムを持っています。値はNULL、INTEGER、REAL(倍精度浮動小数点数)、TEXT、またはBLOB(バイナリデータ)のいずれかです。したがって、カラムはこれらのいずれかの型の値を保持するように定義できます。
しかし、INTEGERカラムとして定義されたカラムは、整数のみに制限されません。代わりに、SQLiteはそれを「INTEGERアフィニティを使用する」と見なします。これは実質的に次のような意味です。
TEXT値を挿入しようとしたときに、それが整数の有効な文字列表現であれば、整数に変換されて格納されます。TEXT値を挿入しようとしたときに、それが浮動小数点数の有効な文字列表現であれば、実数(倍精度浮動小数点数)に変換されて格納されます。- それ以外の場合、値はそのまま格納されます。
他のアフィニティには、異なるがより単純なルールがあります。
BLOBアフィニティを持つカラムは値をそのまま格納します。TEXTアフィニティを持つカラムは、BLOB、TEXT、NULL値をそのまま格納しますが、数値はTEXTに変換します。REALアフィニティを持つカラムは、INTEGERアフィニティを持つカラムのように動作しますが、整数値はREALに変換されます。
これが実際にはどうなるかを見てみましょう。
CREATE TABLE music (
id INTEGER PRIMARY KEY,
name TEXT,
duration_sec INTEGER
);
INSERT INTO music (name, duration_sec) VALUES ('Lost In Hollywood', 321);
INSERT INTO music (name, duration_sec) VALUES ('Comfortably Numb', 382);
INSERT INTO music (name, duration_sec) VALUES ('The Way of All Flesh', 'Way too long, I mean come on');
SELECT * FROM music;
-- id | name | duration_sec
-- 1 | Lost In Hollywood | 321
-- 2 | Comfortably Numb | 382
-- 3 | The Way of All Flesh | Way too long, I mean come on
データベースがデータ検証に対してこれほど無頓着であるのは、悪い考えだということを説明する必要はないと思います。SQLiteが明示的に動的型付けのドキュメントデータベースであれば、それは一つのことですが、そうではありません。SQLiteは構文ルールを通じて、「このカラムにどのような型を入れたいですか?」と尋ねてきます。
かつて、あるコードが意図せずブーリアン(1と0)を格納するカラムに文字列'1'と'0'を書き込んでいたプロジェクトをクリーンアップしなければなりませんでした。それは楽しいデバッグの話ではありませんでした。
幸いなことに、SQLiteには厳格なテーブル(strict tables)の概念があり、これによりSQLiteはカラムに間違った型が挿入された場合に型エラーを生成します。
CREATE TABLE music (
id INTEGER PRIMARY KEY,
name TEXT,
duration_sec INTEGER
) strict;
INSERT INTO music (name, duration_sec) VALUES ('The Way of All Flesh', 'Way too long, I mean come on');
-- Runtime error: cannot store TEXT value in INTEGER column music.duration_sec (19)
残念ながら、すべてのテーブルをグローバルに厳格にするためのプラグマはありません。そのため、すべてのテーブルにstrictタグを手動で追加することを覚えておく必要があります。
厳格なテーブルに対する議論がいくつかありますが、ここで取り上げたいと思います。
SQLiteの作者は、「柔軟な型付け」について書いています。個人的には、これは非常に奇妙な文章だと思います。INTEGERカラムにBLOBを挿入することがいつ有用になり得るかの例は提供されていません。提供されているのは、あらゆる型の値を格納できるカラムを持つことが役立つ場合があることを示す例だけです。厳格なテーブルにはそれに対する解決策があります。それはANYデータ型と呼ばれます。あらゆる値を受け入れるカラムを作成することは依然として可能ですが、それについては明示的である必要があります。
はるかに良い議論は、lobste.rsのユーザー「zie」によって提供されています。厳格なテーブルは型を強制するだけではありません。型指定子の解析方法も変更されます。
非厳格なSQLiteテーブルは、カラムの型を決定するために次のルールを使用します(SQLiteのドキュメントより)。
- 宣言された型に "INT" という文字列が含まれている場合、INTEGERアフィニティが割り当てられます。
- カラムの宣言された型に "CHAR"、"CLOB"、または "TEXT" のいずれかの文字列が含まれている場合、そのカラムは TEXT アフィニティを持ちます。VARCHAR 型には "CHAR" という文字列が含まれており、したがって TEXT アフィニティが割り当てられることに注意してください。
- カラムの宣言された型に "BLOB" という文字列が含まれている場合、または型が指定されていない場合、カラムは BLOB アフィニティを持ちます。
- カラムの宣言された型に "REAL"、"FLOA"、または "DOUB" のいずれかの文字列が含まれている場合、カラムは REAL アフィニティを持ちます。
- それ以外の場合、アフィニティは NUMERIC です。
このルールとSQLiteの緩い型付けの組み合わせの結果、DATETIMEやKEY_VALUE_SET、COLORのような型名を与えることができ、データベースコネクタ/ラッパーがカスタム型のカラムのシリアライズとデシリアライズを自動的に認識できるようになります。そうでなくても、これらのカスタム型名は有用なドキュメントとして機能します。
この余談の余談ですが、カスタム型にCHECK制約を関連付けることができれば素晴らしいでしょう。
更新: 'masklinn' がlobste.rsで指摘していますが、SQL 99標準はすでに型エイリアス、すなわちCREATE DOMAINを規定しています。これはすでに制約もサポートしています。したがって、SQLiteは標準の
CREATE DOMAINステートメントをサポートするだけでよいのです。
間違ったデフォルト #3: 同時書き込み時のSQLITE_BUSYエラー ¶
SQLiteは複数の同時読み取りを許可しますが、書き込みは一度に1つしか許可しません。デフォルトでは、2つのプロセスが同時に書き込みロックを取得しようとすると、一方のプロセスはすぐにSQLITE_BUSYエラーを受け取ります。
これは私が期待する動作ではありません。ロックが解除されるまで、タイムアウトまで待つことを期待します。ディスクI/Oを行っているのですから、書き込みが遅くなる可能性があるという前提でコードを構造化しています。
デフォルトの動作により、実際にはバグが発生し、システムがクラッシュすることがありました。それを修正するために手動でリトライループを記述しました。
修正方法は、プラグマでbusy_timeoutを設定することです。
PRAGMA busy_timeout = 5000;
これにより、SQLiteはSQLITE_BUSYエラーで失敗する前に、最大5秒間ロックの取得を試みます。
この設定については最近まで知りませんでした。これは非常に明白なデフォルト設定のように思えますが、そうでないことに驚いています。
更新: 同時書き込みサポートが望ましい理由について、ここに注釈を追加すべきです。
通常の操作中、すべての書き込みを単一のプロセス、理想的には単一のスレッドで行うようにソフトウェアを構造化するのが最善です。同時書き込みは決して速くはなりません。しかし、非典型的な状況もあります。コマンドラインで
sqlite3ツールを使用してデータベースを対話的に手動でクリーンアップする必要があるかもしれません。フロントエンドを作成する必要がなかった、まれな管理タスクのためのスクリプトがあるかもしれません。これらは完全に正当であり、おそらくかなり一般的なユースケースだと信じています。SQLiteのデフォルトでは、このような使用方法が予期しないSQLITE_BUSYエラーを発生させることで、ソフトウェアをクラッシュさせる可能性があるのは悪いことだと思います。
間違ったデフォルト #4: パフォーマンス ¶
SQLiteのパフォーマンスチューニングについては、語るべきことはたくさんあります。正しく設定されていれば、PostgreSQLやMySQLのような大規模サーバーに通常割り当てられる役割を担うことができる、真に高速なRDBMSになり得ます。
しかし、デフォルトではパフォーマンスはそれほど良くありません。私よりも賢い人々がこのトピックについてさらに多くを書いており、興味のある方はSylvain KerkourのOptimizing SQLite for serversをお勧めします。
しかし、最も重要な間違ったデフォルトは、SQLiteのライトアヘッドログ(WAL)がデフォルトで無効になっていることです。これは以下で有効にできます。
PRAGMA journal_mode = WAL;
WALは、ほとんどの状況で書き込み速度を劇的に向上させます。さらに、別の設定を変更することで、データ破損のリスクなしにディスク同期の量を大幅に削減できます。
PRAGMA synchronous = NORMAL;
synchronousが具体的に何をするかについては、SQLiteのドキュメントを参照してください。
解決策: エディション? ¶
これらのデフォルトがデフォルトであり続ける、しばしば引用される理由は、もちろん後方互換性です。今デフォルトを変更すると、多くの古いソフトウェアが壊れ、人々が将来SQLiteをアップグレードするのを恐れるようになるでしょう。なぜなら、Pythonをアップグレードする際に私が使用している多くのソフトウェアが壊れてしまうように、すべてが壊れてしまう可能性があるからです。依存関係を壊さないようにすることは、称賛に値するまれな目標です。
しかし、解決策はシンプルだと思います。すべての間違ったデフォルトを変更する1つの「スーパープラグマ」を追加することです。私は以下を提案します。
PRAGMA edition = 2026;
これは、少なくとも以下のプラグマのセットのエイリアスであるべきです。
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
そして、テーブルのデフォルトをstrictモードにすることもです。
これは、後方互換性を壊すことを避けつつ、データベースエンジンが前進し、その歴史に引きずられることのない、良い中間地点となるはずです。
editionというアイデアは、Rustのエディションからそのまま拝借しました。年ベースのエディションの利点は、JavaScriptの"use strict";のようなものよりも、年が経つにつれて、適切なデフォルトが変わる可能性があることです。例えば、HctreeのWAL2が、例えば2034年にメインブランチに組み込まれるかもしれません。そうすれば、PRAGMA edition = 2034は、いつかPRAGMA journal_mode = WAL2を設定するかもしれません。
とにかく、それがすべてです。SQLiteには、更新されたデフォルト設定を持つエディションシステムが必要だと思います。これが悪いアイデアになるような、私が漏らしたことはありますか?それとも、私の想像上の「2026エディション」に追加すべき他のプラグマはありますか?