SQLiteをドキュメントデータベースとして使う (2020年)

SQLiteは以前からJSONをサポートしていましたが、最近になって強力な機能が追加されました。それが生成列（generated columns）です（これは2020年1月22日にリリースされた3.31.0で追加されました）。これにより、JSONを直接SQLiteに挿入し、そのデータを抽出してインデックス化できるようになります。つまり、SQLiteをドキュメントデータベースとして扱うことが可能になるのです。これはPostgreSQLでも可能でしたし、Elasticのようなものが提供しているのは明らかですが、組み込みデータベースで利用できるのは軽量な用途にとって非常に素晴らしいことです。 早速始めましょう。 ``` sqlite3 SQLite version 3.31.1 2020-01-27 19:55:54 Connected to a transient in-memory database. sqlite> CREATE TABLE t ( body TEXT, d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL); sqlite> insert into t values(json('{"d":"42"}')); sqlite> select * from t WHERE d = 42; {"d":"42"}|42 ``` 非常にシンプルです。d列は提供されたJSONから抽出されます。 （余談ですが、執筆時点ではmacOSのHomebrewには含まれていますが、それ以外では新しいバージョンのSQLiteを入手するのが難しいかもしれません。nixpkgs-unstableのような不安定なソースを使う必要があるかもしれません。） これにはいくつかの良い特性があります。通常、JSONを挿入する際には（json()関数を介して）最小化および検証することが推奨されます。なぜなら、SQLiteにはJSON型がないため、何でも許可してしまうからです。しかし、それを強制するものは何もありません。制約を追加することもできますが、おそらく忘れてしまうでしょう。 json_extractを使用したGENERATED ALWAYSは、無効なJSONが挿入時にエラー（Error: malformed JSON at INSERT time）を発生させることを意味します。これをさらに進めることができます。 ``` sqlite> CREATE TABLE x ( body TEXT, id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL); sqlite> insert into x values(''); Error: malformed JSON sqlite> insert into x values('{}'); Error: NOT NULL constraint failed: x.id ``` 挿入されたJSONにアイテムが存在することを強制できます。ここではNOT NULLを追加しましたが、制約やその他のSQLite機能を使用することもできます！ これらの例では、生成列にVIRTUALを使用していることに気づくでしょう。値をキャッシュするSTOREDを使用するオプションもありますが、ALTER TABLEでこれらの列を追加できないという欠点があります。しかし、列にインデックスを追加することは常に可能です。たとえそれが仮想列として定義されていてもです。 ``` CREATE INDEX xid on x(id); ``` そして、それが期待通りに機能するかどうかを確認します。 ``` EXPLAIN QUERY PLAN SELECT * FROM x WHERE id='foo'; QUERY PLAN `--SEARCH TABLE x USING INDEX xid (id=?) ``` ALTER TABLEと組み合わせることで、新しい列を追加し、それをインデックス化できます。 ``` ALTER TABLE x ADD COLUMN text TEXT GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL; INSERT INTO x VALUES(json('{"id":43, "text":"test"}')); CREATE INDEX xtext ON x(text); ``` ここでの利点は、単一のJSON列のみを持つテーブルから始め、JSON内で有用なデータを見つけるにつれて列やインデックスを追加できることです。例えば、これはWebhooksに非常にうまく機能する可能性があります。受信したすべてのデータをテーブルに直接挿入し、後で有用なものだけを抽出できます。 楽しんでください。 17th June 2020 in code