ハクソク

世界を動かす技術を、日本語で。

モダンSQLite: あなたが知らなかった機能

53日前原文(slicker.me)

概要

SQLiteは JSON拡張機能 や FTS5全文検索、 ウィンドウ関数 など、現代的なデータ分析・検索機能をサポート。 柔軟なスキーマ管理や STRICTテーブル による型安全性向上も特徴。 生成カラムや WALモード でパフォーマンスと一貫性を確保。 単一ファイルで多機能なデータベース運用が可能。 外部サービス不要でアプリケーションの開発効率向上。

SQLiteでのJSONデータ操作

  • JSON拡張機能 により、テーブル内にJSONドキュメントを直接保存・クエリ可能
  • スキーマの柔軟性を保ちつつ、 SQLによる構造化データ抽出 を実現
    • 例：JSONカラムから特定フィールド抽出 
      CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  payload TEXT NOT NULL -- JSON
);
SELECT
  json_extract(payload, '$.user.id') AS user_id,
  json_extract(payload, '$.action') AS action,
  json_extract(payload, '$.metadata') AS metadata
FROM events
WHERE json_extract(payload, '$.action') = 'login';
  • JSON式へのインデックス作成 により、半構造化データの高速クエリを実現

FTS5による全文検索

  • FTS5拡張 でSQLiteが強力な全文検索エンジンに変身
  • 外部検索サービス不要、 単一データベースファイル内で全文検索
    • 例：シンプルな検索インデックスの構築 
      CREATE VIRTUAL TABLE docs USING fts5(
  title, body, tokenize = "porter"
);
INSERT INTO docs (title, body) VALUES
  ('SQLite Guide', 'Learn how to use SQLite effectively.'),
  ('Local-first Apps', 'Why local storage and sync matter.');
SELECT rowid, title FROM docs WHERE docs MATCH 'local NEAR/5 storage';
  • ランキング・フレーズ検索・接頭辞検索 など多彩な検索機能を提供

ウィンドウ関数とCTEによる分析

  • CTE（共通テーブル式） と ウィンドウ関数 で高度な分析クエリが可能
    • 例：ウィンドウ関数による累積合計の計算 
      SELECT user_id, created_at, amount,
  SUM(amount) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM payments
ORDER BY user_id, created_at;
  • シングルファイルで リッチなレポート・ダッシュボード構築 を実現

STRICTテーブルと型安全性

  • 従来の柔軟な型付けに加え、 STRICTテーブル で型制約を強化
    • 例：STRICTテーブルの定義 
      CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  is_active INTEGER NOT NULL DEFAULT 1
) STRICT;
  • 不正な型データの挿入を拒否 し、スキーマの予測可能性とバグ低減に寄与

生成カラム（Generated Columns）の活用

  • 式をカラムとして保存 し、アプリ側での重複ロジックを排除
    • 例：正規化された検索用カラムの自動生成 
      CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  full_name TEXT GENERATED ALWAYS AS (
    trim(first_name || ' ' || last_name)
  ) STORED
);
CREATE INDEX idx_contacts_full_name ON contacts(full_name);
  • 自動同期・効率的なインデックス作成 によるクエリ高速化

WALモードによる同時実行性と性能向上

  • WAL（Write-Ahead Logging）モード で読み書きの同時実行性と性能向上
    • 例：WALモードの有効化 
      PRAGMA journal_mode = WAL;
  • デスクトップアプリやローカルファーストツール、小規模サービスで パフォーマンスと信頼性の両立

Hackerたちの意見

fuzzy検索を使える拡張機能のspellfix1もあるよ。それと、ON CONFLICTを使うと、重複排除が簡単かつ効率的にできるんだ。

└

自分が書いた小さなプログラムをPostgresからSQLiteに移行しようとしてたんだけど（主にインストールを楽にするため）、SQLiteが「on conflict」をサポートしてるのを見つけて嬉しかった。でも、どうやらCTEを使って外部キーを挿入するのを乱用してたみたいで、SQLiteはそれが嫌だったみたい。以下のように書いたんだけど、 thing_key as ( insert into item(key, description) values('thing', 'a thing') on conflict do nothing ) insert into user_note(uid, key, note) values (123, 'thing', 'I like this thing') on conflict (uid, thing) do update set note = 'I like this thing');

STRICTテーブルはすごくありがたいと思ってる。過去にそのおかげで問題が防げたかは思い出せないけど、あって損はないよね。SQLiteのJSON関数はあまり使ったことないけど、1回か2回、TEXTカラムに有効なJSONが含まれていることを強制する制約を使ったことがある。それ以外だとすごく面倒だったと思う。

└

過去にそのおかげで問題が防げたかは思い出せないけど、実際にはすごく助けられたよ。Pythonのパッケージを使ってて、内部でたくさんNumPyを使ってたんだけど、返り値がPythonの整数だったり、NumPyの整数だったりすることがあった。Pythonの整数はSQLiteの整数として書き込まれ、NumPyの整数はSQLiteのバイナリBLOBとして書き込まれる。これが原因で、単純な値の比較すらできなくなってしまう。STRICTに設定すると、コードがバイナリBLOBを整数カラムに挿入しようとするたびにエラーが出たから、どこでPythonの整数に明示的に変換する必要があるかがわかったんだ。

誰もTursoのことを言ってないのが意外！最近、Rustで書かれたSQLiteの再実装にマルチライターサポートが追加されたんだ。これが高い同時実行性のアプリケーションでSQLiteを使うときの最大の問題だった。PRAGMA journal_mode = 'mvcc'; https://docs.turso.tech/tursodb/concurrent-writes SQLiteがネイティブサポートを追加するかどうか、すごく楽しみ。競争が両方の改善を促すことを期待してる。

└

データベース会社がそのページを書いて、機能の隔離レベルを文書化していないなんて信じられない。

過去にはバックアップAPIを使って、SQLiteデータベースのコピーをメモリに読み込んで、別のライブデータベースを持ってた。特定のユーザーアクションの後にこれをやって、diffを取ることで何が変わったかがわかるんだ。…あまり良い方法ではないけど、PostgreSQLのイベントを実装するには。小さなDB（数メガバイト）だったし、変更を一つずつ集めるのは避けたかったから、最後の状態とのdiffが欲しかっただけなんだ。

興味がある人がいるかわからないけど、自分のプロジェクトでSQLiteをよく使うから、プロダクション用の軽量モニタリングと安全レイヤーを作ってる。アイデアはシンプルで、SQLiteは素晴らしいけど、プロダクションで動かすと観測性がほぼゼロになる。変なことが起こると（予期しない書き込みやスキーマ変更、バックグラウンドジョブがテーブルに触るなど）、事後にしかわからないんだ。それをアプリケーションコードに手を加えずに解決しようとしてる。SQLiteファイルの隣で動くRustエージェントで、すべてのログが記録されるサーバーに接続するんだ。今の課題は暗号化と信頼性かな。他にプロダクションでSQLiteを使ってる人がいて、こういうのに興味があるか知りたい。

└

https://newrelic.com/instant-observability/sqlite

SQLiteはFTS（ユーザーが自由なテキストを入力して、高精度・高再現率の結果を期待する）を構築するには非常に強力なようですね。でも、良い検索品質を得るのは簡単じゃない気がします。素朴なアプローチは、入力をトークン化して、プレフィックスマッチングのために"*"を追加することだと思います。あまり経験がないので、もっと改善できる部分があると思います。異なるトークナイザーやステミングなど、設定がたくさんありますし、重み付けや正確な一致のブーストなど、さらにその上に色々と構築できることもあります。これについて学んだりインスピレーションを得るための良いリソースを知っている人、いますか？

└

つまり、SQLiteをインデックスとして使って、その上にLuceneを再構築することができるってことですね。実際の検索ライブラリの上に検索品質を構築するのも簡単じゃないです。O'Reillyの「Relevant Search」はそこまで悪くはないけど、自分で少しポーティングやコーディングする必要があります。

└

これについて学んだりインスピレーションを得るための良いリソースを知っている人、いますか？ もっとカスタムビルドされたもの、例えばParadeDB Communityエディションがあなたのニーズを満たさない理由はありますか？ あなたがSQLiteについて話しているのは理解していますが、ParadeDBはPostgreSQLですし、良い検索品質を得るのは簡単じゃないので、あなたの状況やニーズを理解しようとしています。

SQLiteとそのほとんどの機能が大好きです。STRICTモードについて、他のところで質問したことがあるんですが、答えがもらえなかったんです。SQLiteの非厳密で、行ごとに異なる型が許可されることが大きな利点になった緩い型付けの例アプリケーションを持っている人、いますか？ SQLiteの少ないカラムタイプのシンプルさが好きなんですが、どこでも任意の型が許可されるデザインはちょっと変に思えました。

└

アプリケーションの設計が変わると、少し異なるタイプのデータを保存する必要が出てくることがあります。リレーショナルデータベースは通常、これに対して明示的なスキーマ変更を必要としますが、NoSQLデータベースはより柔軟でスキーマレスなデータを許可します。SQLiteはその中間に位置しています。リレーショナルデータベースでありながら、動的型付けにより、異なる型の値をカラムに保存できるので、すぐにデータを新しいテーブルに移行する必要がありません。この柔軟性は、1つのアプリケーションだけがテーブルを読み書きする場合には便利です。しかし、複数のアプリケーションが同じテーブルにアクセスする場合、厳密に施行されたスキーマがないことが問題になります。SQLiteテーブル内のデータを処理するための一般的なツールを使用する場合も同様で、そういったツールはどの型のデータを期待すればいいのかわからないからです。カラムの型はXでも、実際のデータは型Yかもしれません。

└

どこでも任意の型が許可されるデザインはちょっと変に思えました。SQLiteはTCLから来ていて、すべてが文字列なんですよ。 https://www.tcl-lang.org/ これが利点になる例としては、異なるフォーマットで日付や時間を保存する場合（アプリが進化するにつれて変わること）があります。

└

フレキシブルな型付けはJSONと相性が良いよね。JSONも柔軟に型付けされてるし。JSONオブジェクトや配列から値を取り出す->>演算子って知ってる？もしjjjがJSONオブジェクトを持つカラムだったら、jjj->>'xyz'はそのオブジェクトの"xyz"フィールドの値になるんだ。->>演算子のアイデアはPostgreSQLからパクったんだけど、PostgreSQLでは->>演算子は常にJSONからの値をテキスト形式で返すんだよね。たとえその値が整数や浮動小数点数でも。PGは厳密に型付けされてるから、それが限界なんだ。でもSQLiteは柔軟に型付けされてるから、->>演算子はテキスト、整数、浮動小数点、NULLなど、JSON内で見つけたどんな値でも返せるんだ。

└

必ずしもそうではないけど、STRICTテーブルで許可されている型を超えて型を指定できるのは便利だよね。理想を言えば、保存する型を指定できるようにしたいし（少なくとも数値の親和性を回避したい）、型に名前を付けたい（自己検査やドキュメント用に）。カラムがDATETIME、JSON、DECIMALだと指定するのは有用だと思う。でも残念ながら、STRICTテーブルも非STRICTテーブルもこれを許可していないんだよね。

└

SQLiteが何も強制しないから、booleanやdatetimeみたいな偽のカラム型を使うORMを覚えてる。そのおかげでORMはデータをどのようにデシリアライズするかを知ってるんだ。厳密モードではSQLiteが認識するカラム型しか受け付けないから、これが禁止される。俺はSQLiteが一般的なデータ型を実際にサポートしてくれることを望んでるけど、そうじゃない限り、データベースに保存しているデータをスキーマで指定するのは魅力的だと思う。

SQLiteはめちゃくちゃ堅牢です。数十万のユーザーが毎日訪れるウェブサイトを開発したことがありますが、そのストレージ層は完全にSQLiteで処理しています。私が作った抽象化レイヤーを通じて、便利なキー・バリューインターフェースを提供しているので、データの保存や取得が必要なときにクエリを作る必要がありません。: https://github.com/aaviator42/StorX

JSONに対するテーブル値関数もありますね、[1]で言及されている通りです。 https://sqlite.org/json1.html#table_valued_functions_for_par... [1] https://news.ycombinator.com/item?id=47618597

SQLiteでは矢印表記を使ってJSONクエリを短縮できるよ。例えば、SELECT settings -> '$.languages' languages FROM user_settings WHERE settings ->> '$.languages' LIKE '%"en"%'; 俺はjekyll-sqliteプロジェクトでこれを多用してる。例としては、https://github.com/blr-today/website/blob/main/_config.yml#L...を見てみて。