SQLiteをドキュメントデータベースとして活用する（2020年版）

SQLiteは単なるリレーショナルデータベースではありません。JSONをサポートすることで、柔軟なスキーマを持つドキュメントデータベースとしても強力な武器になります。従来のRDBの堅牢性と、NoSQLライクな柔軟性を両立させる、SQLite活用のための知見をまとめました。

もう何年もサイドプロジェクトでSQLiteをドキュメントDBとして使ってるよ。独自のレポジトリ基底クラスを作って、BLOBを別のカラムに保存できるようにしてるから、この手のデータはJSONドキュメントの一部にはなってない。最近はjsonb [1] もあるし、記憶してる限りではJSONでもjsonbでも関数は同じように動くはず。

あと、そのレポジトリクラスで書き込みと削除のタイムスタンプを別のカラムに保存してるからCDCができるんだ。CDCはキャッシュされたビューモデルの構築に使われてて、バックアップ用に5分おきにNDJSONとしてオブジェクトストレージにプッシュしてる。ホームサーバーの別のプロセスで数分おきにDBをリストアしてて、二重のバックアップと、いざという時にすぐ使えるDBの役割も果たしてる。

Litestreamみたいなツールがあるのは知ってるけど、インプロセスで動いて、バックアップ失敗時にアラートを飛ばせるようなものが欲しかったんだ。

[1] https://sqlite.org/jsonb.html

なんでMongoDB使わないの？
MongoDBはWebスケールだしね。

https://youtu.be/b2F-DItXtZs?is=HlayyJ_DPb8NzbS4

（笑！言わずにはいられなかった）

なんでみんな本当は単なるJSONデータベースのことなのに、わざわざドキュメントデータベースって呼ぶの？

明らかに例が不自然だけど、JSONをそれ自体のカラムに保存してないのが不思議だよね。単に一つのキーを抽出して生成カラムに保存してるだけだし。もしJSONの残りを捨てるつもりなら、なぜアプリのコード側でそれをやらないの？

昨日MariaDBで見かけた例を挙げるけど、あそこも次期リリースでJSONサポートを強化してるんだよね。

https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/virtual-column-support-in-the-optimizer#example

CREATE TABLE t1 (json_data JSON);
INSERT INTO t1 VALUES('{"column1": 1234}'); 
INSERT INTO t1 ...

JSONデータに対して効率的にクエリを投げるために、仮想カラムを追加してそのカラムにインデックスを貼るという手がある。

ALTER TABLE t1
  ADD COLUMN vcol1 INT AS (cast(json_value(json_data, '$.column1') AS INTEGER)),
  ADD INDEX(vcol1);

自分は「ドキュメント」とそれ以外のドキュメント（メールやOffice文書などのJSONやgzip圧縮されたBLOB）の両方を扱ってる。プレーンテキストは抽出してFTS5に流し込んでるんだ。こんなことできて、さらにベクターインデックスも扱えるデータベースなんて他には思いつかないな。

JSONの拡張性と仮想カラムは、可変メタデータを扱う時にめちゃくちゃ役に立つよ。

killer feature: generated columns（キラー機能：生成カラム）が追加された

SwiftDataが@Modelオブジェクトの計算プロパティを#Expressionマクロ経由でこれらの生成カラムに変換してくれたら最高にクールなんだけどな！

まさにそのために kindstore を作ったよ（今はBunしかサポートしてないけど）。このコメントまでするまでプロジェクトの宣伝なんてしたことなかったな。興味ある人いる？

https://github.com/alloc/kindstore

SQLite使いがこのスレにいそうだから聞くけど、ゲノムデータをSQLite tarとして保存して、必要なメタデータをすべてテーブルに入れておくのが「ダメなアイデア（Bad Idea）」とされるのはなぜ？fastqやbamファイルに、下流の全データ、助成金情報、実験パラメータ、検体情報なんかをまとめて単一ファイルに入れておけば簡単にパースできるじゃん。

2009年にバックエンドにzope、フロントエンドにextjs（今のSencha）とSQLiteを使ったアプリを開発したよ。フォームとデータはデータベースに文字列として保存されて、ネット接続時にバックエンドへ同期される仕組み。オフラインになることが多い第三世界の辺境の医師たちがターゲットだった。当時の数人の医師に、紙のフォームで慣れているから入力フォームと同じフォーマットで医療データを保存したいって言われたんだ。その後、医療ERPが普及してリレーショナルデータベースがドキュメントデータベースを駆逐しちゃったけどね。今なら（収集した時と同じフォーマットで保存・表示される）そんなアプリを構築するのはずっと簡単だろうけど、一体どんな市場が求めてるのか気になる。

もしNOT NULL制約を適用するなら、いっそのことJSONから抽出して別のカラムに保存すればよくない？JSONに入れたままだと何かいいことあるの？

もし結果で必要になっても、取り出す時に元の形に戻せばいいだけだし。