Skip to content

SQLite チートシート

自己完結型で、サーバーレス、設定不要の SQL データベースエンジン。

01

はじめに

CLI の基本

単一のファイルへデータベース全体を保存するのが、SQLite です。必要に応じて .sqlite/.db のファイルを開くか作るのが、sqlite3 の CLI です。MySQL/PostgreSQL と違い、サーバーのプロセスがなく — アプリケーションが、SQLite のライブラリへ直接リンクします。バックアップや移行のために、テキストの SQL としてデータベース全体をエクスポートするのが、.dump です。

sqlite
# open/create a database file
sqlite3 mydb.sqlite

# at the sqlite> prompt
.help              # show help
.tables            # list tables
.schema users      # show CREATE statement for users
.databases         # list attached databases
.dump              # dump entire DB as SQL text
.quit              # exit

# execute SQL from a file
sqlite3 mydb.sqlite < script.sql

# execute a query from command line
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users;"

ドットコマンド

sqlite3 の CLI が解釈するものであり、SQL エンジンがではないのが、ドットコマンドです。セミコロンを必要とせず、プログラミング API 経由では利用できません。出力の整形を制御するのが、.mode です(読みやすい対話使用のためには、.header on 付きの「column」を試しましょう)。CREATE 文を示すのが、.schema で、インデックス、ビュー、トリガーを加えるのが、.fullschema です。発見には、.help を。

sqlite
-- dot commands are CLI-only, NOT SQL (no semicolon needed)
.help                 -- list all dot commands
.tables               -- list all tables
.schema               -- schema for all tables
.schema users         -- schema for the users table
.fullschema           -- schema incl. indexes, triggers, views
.indices users        -- list indexes on users
.header on            -- show column headers
.mode column          -- column-aligned output
.mode list            -- default, pipe-separated
.mode csv             -- CSV output
.mode json            -- JSON output
.show                 -- show current settings
.width 10 20 30       -- set column widths
.timer on             -- show query execution time

データベースファイルの管理

1 つの接続が、スキーマとして(main、temp、加えて attached)、最大 10 のデータベースファイルへ同時にクエリできるのが、ATTACH DATABASE です。閉じると消える、純粋なインメモリのデータベースを作るのが、:memory: の URI です — テストや一時的な計算に理想的です。読み取り専用、共有キャッシュ、その他のモードを可能にするのが、(file:... の)URI のファイル名です。独立したファイルが、ディスク上の各 attached された DB です。

sqlite
-- attach another database file to the current connection
ATTACH DATABASE 'archive.sqlite' AS archive;
DETACH DATABASE archive;

-- list attached databases (main + attached)
.databases

-- the in-memory database (never written to disk)
sqlite3 :memory:

-- temporary database (deleted when connection closes)
sqlite3 ""          -- empty arg => temp DB

-- open read-only
sqlite3 "file:mydb.sqlite?mode=ro" --readonly

-- query the underlying page size / file format
PRAGMA page_size;
PRAGMA journal_mode;

ヘッダーと出力の整形

CLI だけに影響し、決してデータ自体には影響しないのが、出力の整形です。人間にとって最も読みやすいのが、.headers on 付きの「column」モードです。シェルのパイプラインに最良なのが、「list」(デフォルト)です。手軽な変換器にするのが、「json」と「csv」です。結果をファイルへリダイレクトするのが、.output で、.output stdout で戻すのを覚えましょう。ASCII の罫線を描くのが、.mode box/table です。

sqlite
-- readable interactive mode
.headers on
.mode column
.width 15 20 10
SELECT id, name, email FROM users LIMIT 5;

-- pipe-separated (good for scripts)
.mode list
.separator "|"
SELECT id, name FROM users;

-- boxed table output (sqlite 3.36+)
.mode box
.mode table

-- JSON output for piping to other tools
.mode json
SELECT id, name FROM users;

-- write output to a file
.output users.txt
SELECT id, name FROM users;
.output stdout

スクリプトと SQL ファイルの実行

開いた CLI セッションの内側で SQL のファイルを実行するのが、.read で、< によるリダイレクトが、対話プロンプトの前に実行させます。最初のエラーでバッチを止めるのが、--bail です(さもなければ、SQLite が続け、失敗が覆い隠され得ます)。文字列の補間を避け、シェルから名前付きパラメータを bind するのが、.parameter(3.44+)です。開発では、.echo on 付きでスクリプトを必ず整合性確認しましょう。

sqlite
-- run a .sql file from the shell
sqlite3 mydb.sqlite ".read setup.sql"
-- or piped in
sqlite3 mydb.sqlite < setup.sql

-- inside the CLI
.read setup.sql

-- stop on first error (shell flag)
sqlite3 mydb.sqlite --bail < script.sql

-- echo commands as they run
.echo on
.read migrations.sql

-- show EXPLAIN before each statement (debug)
.explain on

-- parameterize from the shell (sqlite 3.44+)
sqlite3 mydb.sqlite \
  -cmd ".parameter set @min 18" \
  "SELECT name FROM users WHERE age >= @min"
02

テーブルとデータ型

テーブルの作成

rowid の別名であるのが、INTEGER PRIMARY KEY です。自動増分し、可能な限り最速のキーです。削除された ID が決して再利用されないよう、アルゴリズムを変えるのが、AUTOINCREMENT です(わずかに遅く、通常不要です)。datetime('now') のような任意の式を可能にするのが、DEFAULT (expr) です。クエリの結果を事前ロードしたテーブルを作るのが、CREATE TABLE AS SELECT ですが、制約やインデックスをコピーしません。

sqlite
CREATE TABLE users (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT    NOT NULL UNIQUE,
  email    TEXT    NOT NULL UNIQUE,
  age      INTEGER CHECK (age >= 0),
  role     TEXT    NOT NULL DEFAULT 'user',
  created  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS orders (
  id        INTEGER PRIMARY KEY,
  user_id   INTEGER NOT NULL,
  total     REAL    NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

-- quick table from a query
CREATE TABLE archive AS
  SELECT * FROM orders WHERE created < '2024-01-01';

ストレージクラスと型の親和性

動的型付けを使うのが、SQLite です。どの列も、5 つのストレージクラスのいずれかを保持できます。可能なとき値を変換しようとするのが(INTEGER 列の「42」が、整数 42 になるなど)、「type affinity」ですが、不一致で決してエラーを出しません。列ごとの従来の厳密な型チェックを復元するのが、STRICT のテーブル(3.37+)です。実際に保存されたクラスを報告するのが、typeof() です。強力だが規律を要求するのが、この柔軟性です。データの整合性が効く新しいスキーマには、STRICT を。

sqlite
-- SQLite has 5 storage classes (not strict types):
--   NULL, INTEGER, REAL, TEXT, BLOB
CREATE TYPE demo...  -- not supported

-- columns have 'type affinity', not enforced types
CREATE TABLE t (
  a INTEGER,   -- affinity INTEGER
  b TEXT,      -- affinity TEXT
  c REAL,      -- affinity REAL
  d BLOB,      -- affinity BLOB (no conversion)
  e            -- no affinity (accepts anything)
);

-- this WORKS despite 'INTEGER' column (dynamic typing)
INSERT INTO t (a) VALUES ('hello');
SELECT typeof(a) FROM t;   -- 'text'

-- STRICT tables (SQLite 3.37+) actually enforce types
CREATE TABLE strict_t (a INTEGER, b TEXT) STRICT;
INSERT INTO strict_t VALUES ('hi', 1);   -- error

よくあるデータ型

ネイティブの DATE/TIME や BOOLEAN の型が SQLite になく — ソート可能性のため、タイムスタンプは ISO-8601 の TEXT(「YYYY-MM-DD HH:MM:SS」)として保存し、操作するには日付/時刻の関数を使いましょう。整数の 0/1 であるのが、真偽値です。無視されるのが(互換性のためだけです)、VARCHAR(n) の長さの上限です。最初に INTEGER を試み、その後 REAL で、可能なとき正確な小数を保つのが、NUMERIC の親和性です。生のバイトをそのまま保存するのが、BLOB です。

sqlite
-- integers
CREATE TABLE numerics (
  small  INTEGER,        -- 1/2/4/8 bytes depending on value
  big    INTEGER,        -- up to 64-bit
  -- no separate BIGINT/SMALLINT, all map to INTEGER affinity
  flag   BOOLEAN         -- stored as 0 (false) or 1 (true)
);

-- text & blobs
CREATE TABLE blobs (
  name   TEXT,           -- variable-length UTF-8, no length limit
  data   BLOB,           -- raw bytes, no conversion
  note   VARCHAR(255)    -- length ignored, affinity TEXT
);

-- real numbers
CREATE TABLE floats (
  price  REAL,           -- 8-byte IEEE float
  precise NUMERIC(10,2)  -- affinity NUMERIC, decimal preserved if exact
);

-- dates: SQLite has no native DATE type — store as TEXT (ISO-8601)
CREATE TABLE events (
  ts TEXT  -- 'YYYY-MM-DD HH:MM:SS' recommended
);

テーブルの変更

サーバーレスのエンジンのために意図的に限定されているのが、SQLite の ALTER TABLE です。O(1) であるのが(テーブルの書き換えなし)、ADD COLUMN です。モダンな便利機能が、RENAME COLUMN(3.25+)と、DROP COLUMN(3.35+)です。型の変更や制約の追加のためには、12 ステップのテーブル再構築のパターンを使いましょう(作成、コピー、削除、改名、インデックスの再構築)。再構築の間、外部キーを一時的に無効化しなければならず — PRAGMA legacy_alter_table を参照。

sqlite
-- add a column (fast, appends to end)
ALTER TABLE users ADD COLUMN bio TEXT;

-- rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN name TO username;

-- rename a table
ALTER TABLE users RENAME TO accounts;

-- drop a column (SQLite 3.35+)
ALTER TABLE users DROP COLUMN deprecated_field;

-- SQLite CANNOT: change a column type, add constraints to
-- an existing column, or reorder columns. Workaround:
-- 1) create new table with desired schema
-- 2) INSERT INTO new SELECT * FROM old
-- 3) DROP old; ALTER TABLE new RENAME TO old
-- 4) recreate indexes/triggers

制約

エンジンレベルでデータの整合性を強制するのが、制約です。NOT NULL と UNIQUE を含意するのが、PRIMARY KEY です。行の中の任意の列を参照できるのが、CHECK です。ステートメントごとの競合解決を制御するのが、(OR IGNORE / OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK の)ON CONFLICT 句です。競合行を削除した後挿入するのが、rowid をリセットする、OR REPLACE です。UPSERT のセマンティクスには、ON CONFLICT ... DO UPDATE を推奨しましょう(CRUD のセクションを参照)。

sqlite
CREATE TABLE products (
  id         INTEGER PRIMARY KEY,
  sku        TEXT    NOT NULL UNIQUE,
  name       TEXT    NOT NULL,
  price      REAL    NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category   TEXT    CHECK (category IN ('a','b','c')),

  -- composite unique constraint
  UNIQUE (name, category),

  -- table-level check
  CHECK (price * stock < 1000000)
);

-- name a constraint for clearer error messages
CREATE TABLE t (
  x INTEGER CONSTRAINT x_positive CHECK (x > 0)
);

-- conflict handling
INSERT OR IGNORE INTO products (sku, name, price) VALUES (...);
INSERT OR REPLACE INTO products (...) VALUES (...);
UPDATE OR ABORT products SET stock = stock - 1 WHERE id = 1;
03

CRUD 操作

INSERT

ループ内の行ごとの挿入より劇的に速いのが、マルチ行の INSERT です。バッチ化しましょう。テーブル間でデータをコピーするのが、INSERT ... SELECT です。現在の接続の(トランザクションでない)、最も最近の INSERT の rowid を返すのが、last_insert_rowid() です。SQL インジェクションと引用の頭痛を避けるため、アプリには(Integration のセクションを参照)、パラメータ化された挿入を推奨しましょう。列のデフォルト値を明示的に取るのが、DEFAULT です。

sqlite
-- single row
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);

-- multiple rows in one statement (fast)
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

-- insert from a query
INSERT INTO archive_users
SELECT * FROM users WHERE active = 0;

-- explicit NULL / default
INSERT INTO users (username, email, age)
VALUES ('eve', '[email protected]', DEFAULT);

-- return the new rowid (handy in apps)
INSERT INTO users (username) VALUES ('frank');
SELECT last_insert_rowid();   -- returns the new id

UPSERT(ON CONFLICT)

「存在すれば挿入か更新」のための SQLite の慣用句が、ON CONFLICT(UPSERT)です。既存の行を削除する(DELETE トリガーを発火し rowid をリセットする)INSERT OR REPLACE より、はるかに安全です。挿入が提案された行を参照するのが、「excluded」です。静かにスキップするのが、DO NOTHING です。任意の UNIQUE か PRIMARY KEY の制約が、競合のターゲットにでき、そののがこれです。カウンタ、last-seen の更新、冪等なインポートのための正しいパターンです。

sqlite
-- insert, but update on conflict
INSERT INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]')
ON CONFLICT(id) DO UPDATE SET
  email  = excluded.email,
  username = excluded.username;

-- ignore on conflict
INSERT INTO users (id, username)
VALUES (1, 'alice')
ON CONFLICT(id) DO NOTHING;

-- conflict on a specific unique constraint
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]')
ON CONFLICT(email) DO UPDATE SET
  username = excluded.username;

UPDATE

本当にすべての行を更新する意図でない限り、WHERE を必ず含めましょう — ドライランモードが SQLite にありません。SQLite を、PostgreSQL のエルゴノミクスに近づけるのが、UPDATE ... FROM(3.33+)と、UPDATE ... RETURNING(3.35+)です。別の SELECT なしに、更新後の状態が必要なアプリにとって、RETURNING が非常に有用です。行ごとのロジック付きの、単一ステートメントでの一括更新を可能にするのが、CASE で、これは行ごとの更新よりはるかに速いです。

sqlite
-- basic update (ALWAYS use WHERE in production!)
UPDATE users SET age = 31 WHERE id = 1;

-- multiple columns
UPDATE users
SET age = age + 1, role = 'admin'
WHERE username = 'alice';

-- conditional update with CASE
UPDATE products
SET price = CASE
  WHEN category = 'a' THEN price * 0.9
  WHEN category = 'b' THEN price * 0.95
  ELSE price
END;

-- update from another table (subquery)
UPDATE orders
SET total = (SELECT SUM(qty * price)
             FROM items WHERE order_id = orders.id);

-- UPDATE ... RETURNING (SQLite 3.35+)
UPDATE users SET role = 'admin'
WHERE id IN (1,2,3)
RETURNING id, username, role;

DELETE

TRUNCATE が SQLite になく — すべての行を取り除くのが、DELETE FROM table ですが、行レベルのトリガーを発火し、(sqlite_sequence に保存された)AUTOINCREMENT のカウンタをリセットしません。取り除かれたものをログするのが、DELETE ... RETURNING(3.35+)です。監査データには、(deleted_at の列の)ソフト削除をハード削除より推奨しましょう。削除から解放されたスペースが、将来の挿入で再利用され、OS へ取り戻すのが、VACUUM です。

sqlite
-- basic delete
DELETE FROM users WHERE id = 1;

-- delete with a subquery condition
DELETE FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = 0);

-- DELETE ... RETURNING
DELETE FROM sessions
WHERE expires < datetime('now')
RETURNING id, user_id;

-- delete ALL rows (fast, but fires triggers)
DELETE FROM logs;

-- TRUNCATE equivalent: delete all + reset rowid
DELETE FROM logs;
DELETE FROM sqlite_sequence WHERE name = 'logs';

-- soft delete pattern
UPDATE users SET deleted_at = datetime('now') WHERE id = 1;

SELECT の基本

主力が、SELECT です。ページネーションを実装するのが、LIMIT count OFFSET skip です(大きなオフセットには、キーセットのページネーションがより速い — Querying のセクションを参照)。読みやすさを改善し、ビューでの計算された列に必要なのが、別名(省略可能な AS)です。同一の行の折りたたみが、DISTINCT で、より細かい制御には GROUP BY を考えましょう。結果セットへ if/else のロジックを加え、任意の句で使えるのが、CASE です。

sqlite
-- basic select
SELECT id, username, email FROM users;

-- limit and offset
SELECT id, username FROM users LIMIT 10 OFFSET 20;
SELECT id, username FROM users LIMIT 20, 10;  -- offset, count

-- aliasing columns and tables
SELECT u.id AS user_id, u.username AS name
FROM users AS u;

-- expressions
SELECT
  username,
  age,
  age * 365 AS days_alive,
  UPPER(username) AS upper_name
FROM users;

-- distinct rows
SELECT DISTINCT category FROM products;

-- conditional output
SELECT name,
  CASE WHEN age >= 18 THEN 'adult' ELSE 'minor' END AS status
FROM users;
04

クエリとフィルタリング

WHERE と演算子

SQL で特殊であるのが、NULL です。「未知」であり、値ではありません。常に NULL を返す(false として扱われる)のが、= NULL であるため、IS NULL / IS NOT NULL を使いましょう。三値論理に従うのが、AND/OR と組み合わされた NULL です。含むのが、BETWEEN です。OR 等価の省略形が、IN で、奇妙に振る舞うため、IN (NULL, ...) は避けましょう。複合条件を曖昧さなくするため、気前よく括弧を使いましょう。

sqlite
-- comparison operators
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM users WHERE age IN (18, 21, 25);
SELECT * FROM users WHERE age NOT IN (18, 21);

-- NULL handling (NEVER use = NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- logical operators
SELECT * FROM users
WHERE age >= 18 AND age <= 65 AND role = 'admin';

SELECT * FROM users
WHERE role = 'admin' OR role = 'editor';

-- range with multiple conditions
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
   OR (status = 'pending' AND total > 500);

ORDER BY と LIMIT

一意のタイブレーカーのない ORDER BY が、実行をまたいで非決定的な順序を与えるため — 最終のソートキーとして、(id のような)一意の列を必ず加えましょう。スキャンし破棄するため、O(n) であるのが、OFFSET のページネーションです。O(limit) であり、大きなテーブルのための正しい選択が、(WHERE id > last_seen_id の)キーセットのページネーションです。フルのソートを必要とするのが、RANDOM() であるため、小さい結果セットかサンプルの部分集合にだけ使いましょう。

sqlite
-- ascending (default) / descending
SELECT * FROM users ORDER BY username;
SELECT * FROM users ORDER BY created DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;

-- sort with NULLs first or last (SQLite 3.30+)
SELECT * FROM tasks ORDER BY due_date ASC NULLS FIRST;
SELECT * FROM tasks ORDER BY due_date DESC NULLS LAST;

-- pagination: keyset is faster than OFFSET
-- slow (offset scans & discards rows):
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;
-- fast (keyset):
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;

-- random sample
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;

パターンマッチング

ASCII のためにデフォルトで大文字小文字を区別しないのが(驚くべき落とし穴 — 大文字小文字の敏感さのためには、COLLATE BINARY を)、LIKE です。大文字小文字を区別し、[A-Z] のような文字クラスをサポートするのが、GLOB です。組み込みでないのが、REGEXP で、関数を登録するか拡張をロードしなければなりません。規模の大きい全文検索のためには、(Performance のセクションを参照)、インデックスを使えない LIKE '%term%' の代わりに、FTS5 を。

sqlite
-- LIKE: case-insensitive by default for ASCII,
--   wildcards % (any chars) and _ (single char)
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'AL%' COLLATE BINARY;

-- GLOB: Unix shell-style, case-SENSITIVE
--   wildcards * and ?, plus [abc] character classes
SELECT * FROM users WHERE username GLOB 'Al*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- REGEXP (requires extension or custom function)
SELECT * FROM users WHERE username REGEXP '^a.*e$';

-- substring match
SELECT * FROM users WHERE INSTR(username, 'lic') > 0;

CASE と条件ロジック

SQL の if/else であり、SELECT、WHERE、ORDER BY、GROUP BY、か HAVING で機能するのが、CASE です。値に一致するのが、「simple」の形式で、順に真偽条件を評価するのが、「searched」の形式です。クラシックなピボット/条件付きカウントの慣用句が、SUM(CASE WHEN ... THEN 1 ELSE 0 END) です。最初に一致する WHEN で短絡するため、順序が効くのが、CASE です。省略された場合のデフォルトが、NULL であるのが、ELSE です。

sqlite
-- simple CASE (value match)
SELECT name,
  CASE role
    WHEN 'admin' THEN 'Administrator'
    WHEN 'editor' THEN 'Editor'
    ELSE 'Regular User'
  END AS role_label
FROM users;

-- searched CASE (conditions)
SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;

-- CASE in aggregate (conditional count)
SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN active = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM users;

-- CASE in ORDER BY (custom sort order)
SELECT * FROM products
ORDER BY CASE category
  WHEN 'featured' THEN 0
  WHEN 'new' THEN 1
  ELSE 2
END;

DISTINCT と集合の操作

重複除去のソートを UNION が行うため、UNION ALL より遅いのが、UNION です。重複が不可能か許容できるときは、UNION ALL を推奨しましょう。互換的な列の数と型を要求するのが、すべての集合の操作です。UNION/UNION ALL より高い優先順位で INTERSECT/EXCEPT を適用するのが、SQLite です。順序の制御には、括弧を使いましょう。最後の SELECT でなく、複合クエリでの ORDER BY が、結果セット全体に適用されます。

sqlite
-- distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT city, country FROM users;

-- UNION (dedup) and UNION ALL (faster, keeps dups)
SELECT 'user' AS type, id FROM users WHERE active = 1
UNION ALL
SELECT 'admin', id FROM admins;

-- INTERSECT: rows in both
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;

-- EXCEPT: rows in first but not second
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;

-- combine with ORDER BY (applies to whole result)
SELECT name FROM users WHERE active = 1
UNION
SELECT name FROM archived_users
ORDER BY name;
05

JOIN

INNER JOIN

両方のテーブルに一致する行だけを返すのが、INNER JOIN です。どちらの側の一致しない行も落とされます。両方のテーブルが同名の列を持つときの省略形であり、単一のマージされた列を作るのが、USING (col) です。暗黙に、すべての同名の列を使うのが、NATURAL JOIN です。便利ですが脆い(スキーマ変更が、静かに結合の挙動を変え得ます)。明快さのためには、明示的な ON を。

sqlite
-- only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id;

-- join with additional filter
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.total > 100 AND u.active = 1;

-- USING shorthand (when join columns share a name)
SELECT u.username, o.total
FROM users u
JOIN orders o USING (user_id);

-- natural join (joins on all same-named columns — risky)
SELECT * FROM users NATURAL JOIN profiles;

LEFT JOIN

左テーブルからすべての行を保ち、欠けている右側の列が NULL になるのが、LEFT JOIN です。一致のない行を見つけるのが(「WHERE right.id IS NULL」のパターンである、アンチ結合)、NULL が絡むときには特に、NOT IN とサブクエリよりしばしばより明快で速いです。LEFT JOIN をまたいだ集約では、一致した行だけを数えるのが、(COUNT(*) でなく)COUNT(right.id) で、注文のないユーザーが、1 でなく 0 を出します。

sqlite
-- all users, with their orders (NULLs if no orders)
SELECT u.username, o.id AS order_id, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

-- find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- multiple left joins
SELECT u.username,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

CROSS JOIN と RIGHT/FULL

デカルト積を作るのが、CROSS JOIN です。組み合わせ(サイズ × 色)の生成に有用ですが、大きなテーブルでは危険です(n×m の行です)。RIGHT JOIN を SQLite がサポートします(3.39+)が、めったに使われません。明快さのため、LEFT JOIN として書き直しましょう。ネイティブの FULL OUTER JOIN はなく、LEFT JOIN ... UNION ... RIGHT JOIN で、重なる一致行の重複を除去しつつ、エミュレートしましょう。

sqlite
-- CROSS JOIN: cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

-- explicit CROSS JOIN (same as comma join)
SELECT a.name, b.name FROM teams a, teams b;

-- RIGHT JOIN (preserves right table) — supported but unusual
SELECT u.username, o.total
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- SQLite has no FULL OUTER JOIN; emulate with LEFT + UNION:
SELECT u.username, o.id AS order_id
FROM users u LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id
FROM users u RIGHT JOIN orders o ON o.user_id = u.id;

Self Join

別名を使い、各コピーにロールを与え、同じテーブルへ 2 回クエリするのが、self join です(employee 対 manager、user_a 対 user_b)。行が自分自身に一致し、鏡像の重複ペアが作られるのを避けるトリックが、「a.id < b.id」です。深い階層(任意の深さ)には、正しい道具が、再帰 CTE です(CTE のセクションを参照)— 固定の深さしか扱えないのが、self join です。

sqlite
-- employees and their managers (same table)
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  manager_id INTEGER REFERENCES employees(id)
);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;

-- recursive hierarchy (tree traversal) — see CTE section
-- multi-level category paths
SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

マルチテーブルの結合

左から右へチェーンされるのが、結合です。累積の結果を見るのが、各 JOIN です。LEFT と INNER の結合を混ぜるとき、LEFT JOIN の後の INNER JOIN が、NULL を再フィルタし LEFT の目的を無効にし得るため、順序は慎重に。1 対多の結合をまたぐ二重カウントを避けるのが、COUNT(DISTINCT col) です。結合の多重度に注意しましょう:2 つの 1 対多テーブルの結合が、行の爆発を作り得ます(親ごとに m × n 行)。

sqlite
-- chain joins across 3+ tables
SELECT u.username, o.id AS order_id, i.name AS item, i.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
WHERE u.active = 1
ORDER BY o.id, i.name;

-- mixing JOIN types
SELECT u.username, o.id AS order_id, p.name AS product
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN products p ON p.id = i.product_id;

-- join with aggregation
SELECT u.username,
  COUNT(DISTINCT o.id) AS orders,
  SUM(i.qty * i.price) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items i ON i.order_id = o.id
GROUP BY u.id, u.username;
06

集約と GROUP BY

GROUP BY の基本

グループ化された列を共有する行を、グループごとに 1 行へ折りたたむのが、GROUP BY です。SELECT の集約されていないすべての列が、GROUP BY に現れなければなりません(SQLite が寛容で任意の値を選びますが、頼ってはいけません)。時間バケットのレポートに素晴らしいのが、(日付上の strftime のような)式によるグループ化です。GROUP BY の後に適用されるのが、ORDER BY です — グループをソートするのに使いましょう。

sqlite
-- count users per role
SELECT role, COUNT(*) AS user_count
FROM users
GROUP BY role;

-- multiple aggregates per group
SELECT role,
  COUNT(*)            AS user_count,
  AVG(age)            AS avg_age,
  MIN(age)            AS min_age,
  MAX(age)            AS max_age,
  SUM(age)            AS total_age
FROM users
GROUP BY role;

-- group by multiple columns
SELECT role, active, COUNT(*) AS cnt
FROM users
GROUP BY role, active
ORDER BY role, active;

-- group by expression
SELECT strftime('%Y-%m', created) AS month,
  COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;

集約関数

行を数えるのが、COUNT(*) で、非 NULL の値を数えるのが、COUNT(col)、一意の非 NULL を数えるのが、COUNT(DISTINCT col) です。NULL を無視するのが、AVG/SUM です。文字列へ値を結合するのが(デフォルトのカンマの区切り)、GROUP_CONCAT です。常に REAL を返すのが(空の入力には 0.0)、TOTAL() であり、空の入力に対し NULL を返すのが、SUM() です。条件付きの集約には、(CASE よりきれいでより速くなり得る、3.30+ の)FILTER 句を。

sqlite
SELECT
  COUNT(*)             AS row_count,
  COUNT(email)         AS emails_non_null,
  COUNT(DISTINCT role) AS distinct_roles,
  AVG(age)             AS avg_age,
  SUM(total)           AS revenue,
  MIN(created)         AS first_signup,
  MAX(created)         AS last_signup,
  GROUP_CONCAT(name)   AS all_names,
  GROUP_CONCAT(name, ' | ') AS names_piped,
  TOTAL(price)         AS total_real  -- always returns REAL, 0.0 if empty
FROM orders;

-- aggregate with FILTER (SQLite 3.30+)
SELECT
  COUNT(*)                              AS all_orders,
  COUNT(*) FILTER (WHERE status='paid') AS paid,
  SUM(total) FILTER (WHERE status='paid') AS paid_revenue
FROM orders;

HAVING(グループのフィルタ)

グループ化の前に入力行をフィルタするのが、WHERE で、集約後にグループをフィルタするのが、HAVING です。この順序が効きます。集約上の WHERE が違法です。読みやすさのために、(か GROUP BY での)HAVING での別名の参照を SQLite が許しますが、非標準です。クラシックな重複発見のクエリが、「group by X having count(*) > 1」のパターンです。

sqlite
-- HAVING filters groups (after GROUP BY);
-- WHERE filters rows (before GROUP BY)
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
WHERE status = 'paid'         -- filter rows first
GROUP BY user_id
HAVING COUNT(*) >= 3 AND SUM(total) > 100   -- filter groups
ORDER BY spent DESC;

-- HAVING with aliases (SQLite allows this)
SELECT role, COUNT(*) AS n
FROM users
GROUP BY role
HAVING n > 1;

-- find duplicate usernames
SELECT username, COUNT(*) AS dupes
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

GROUP_CONCAT

SQLite の文字列集約の関数であるのが、GROUP_CONCAT です(他では STRING_AGG か LISTAGG と呼ばれます)。デフォルトの区切りが、カンマで、2 番目の引数として、カスタムのものを指定しましょう。連結の順序を制御するのが、集約内の ORDER BY(3.44+)です。結合前の重複除去が、DISTINCT です。1GB の上限が、結果にあり、巨大なグループのためには、行を取得しアプリで集約しましょう。

sqlite
-- concatenate values within a group
SELECT role, GROUP_CONCAT(username) AS members
FROM users
GROUP BY role;
-- members: 'alice,bob,carol'

-- custom separator
SELECT role, GROUP_CONCAT(username, ' | ') AS members
FROM users
GROUP BY role;

-- ordered concatenation (SQLite 3.44+)
SELECT role,
  GROUP_CONCAT(username ORDER BY username) AS sorted_members
FROM users
GROUP BY role;

-- distinct values only
SELECT role,
  GROUP_CONCAT(DISTINCT city) AS cities
FROM users
GROUP BY role;

データのピボット

ネイティブの PIVOT が SQLite になく、SUM(CASE WHEN ...) か、よりきれいな FILTER 句でエミュレートしましょう。グループ化属性の 1 つの値を、各ピボットされた列がテストします。このパターンが、長い(正規化された)データを、幅の(クロス集計の)レポートへ変えます。完全に動的なピボット(未知のカテゴリ)のためには、アプリコードで SQL 文字列を組み立てなければなりません — 実行時に、SQL が列を生成できません。

sqlite
-- manual pivot with SUM(CASE ...)
SELECT user_id,
  SUM(CASE WHEN strftime('%w', created) = '0' THEN 1 ELSE 0 END) AS sun,
  SUM(CASE WHEN strftime('%w', created) = '1' THEN 1 ELSE 0 END) AS mon,
  SUM(CASE WHEN strftime('%w', created) = '2' THEN 1 ELSE 0 END) AS tue,
  SUM(CASE WHEN strftime('%w', created) = '3' THEN 1 ELSE 0 END) AS wed,
  SUM(CASE WHEN strftime('%w', created) = '4' THEN 1 ELSE 0 END) AS thu,
  SUM(CASE WHEN strftime('%w', created) = '5' THEN 1 ELSE 0 END) AS fri,
  SUM(CASE WHEN strftime('%w', created) = '6' THEN 1 ELSE 0 END) AS sat
FROM orders
GROUP BY user_id;

-- pivot with FILTER (cleaner)
SELECT user_id,
  COUNT(*) FILTER (WHERE status='paid')    AS paid,
  COUNT(*) FILTER (WHERE status='pending') AS pending,
  COUNT(*) FILTER (WHERE status='refunded') AS refunded
FROM orders
GROUP BY user_id;
07

サブクエリ

スカラーのサブクエリ

正確に 1 行と 1 列を返し、値の式が有効などこでも現れ得るのが、スカラーのサブクエリです。外側のクエリを参照し行ごとに再実行されるのが、相関サブクエリです。強力だが、大きなテーブルでは遅くなり得ます(代わりに、JOIN か窓関数を考えましょう)。単純な相関サブクエリを結合へ最適化するのが SQLite は得意ですが、ホットな経路のためには、必ず EXPLAIN QUERY PLAN を確認しましょう。

sqlite
-- a subquery returning a single value
SELECT username, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- in SELECT list
SELECT username,
  age,
  age - (SELECT AVG(age) FROM users) AS age_diff
FROM users;

-- in HAVING
SELECT role, AVG(age) AS avg_age
FROM users
GROUP BY role
HAVING AVG(age) > (SELECT AVG(age) FROM users);

-- correlated scalar subquery (re-evaluated per row)
SELECT username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

IN と NOT IN のサブクエリ

直感的なのが、IN のサブクエリですが、NULL に注意しましょう:サブクエリの結果が NULL を含めば、(false として扱われる)NULL に評価されるのが、すべての行に対する NOT IN であり、空の結果を返します。nullable な列でのアンチ結合には、NOT EXISTS を推奨しましょう — NULL セーフであり、しばしばより速いです。複合キーの一致には簡潔なのが、マルチ列の IN です。

sqlite
-- users who have placed an order
SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders);

-- users who have NOT placed an order
SELECT username FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- DANGER: NOT IN with NULLs returns no rows!
-- if the subquery returns any NULL, NOT IN matches nothing.
-- Safe version using NOT EXISTS:
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- multi-column IN
SELECT * FROM products
WHERE (category, price) IN (
  SELECT category, MIN(price) FROM products GROUP BY category
);

EXISTS と NOT EXISTS

データを取得せず、行の存在をテストするのが、EXISTS です。最初の一致で止まるため、効率的です。非一致の行を見つける NULL セーフな方法が(NOT IN と違い)、NOT EXISTS です。最良のパフォーマンスのためには、相関サブクエリが結合する列に(例の orders.user_id)、インデックスが存在することを確保しましょう。慣例なのが、SELECT 1 で、EXISTS にとって、列のリストは無関係です。

sqlite
-- EXISTS: true if subquery returns any row
SELECT username FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 100
);

-- NOT EXISTS: classic anti-join (NULL-safe)
SELECT username FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- correlated EXISTS — usually efficient with an index on
-- the subquery's join column (orders.user_id here)
SELECT p.name FROM products p
WHERE EXISTS (
  SELECT 1 FROM order_items i
  WHERE i.product_id = p.id AND i.qty > 10
);

派生テーブル(FROM 内のサブクエリ)

中間結果をステージングし、事前集約するか、結合前にフィルタする強力な方法であるのが、(FROM 内の)サブクエリである、派生テーブルです。別名が必要なのが、派生テーブルです。ほとんどの場合、サブクエリをマテリアライズする(一時テーブルへ書く)のが、SQLite です。繰り返しの使用のためには、より明快で複数回参照できるのが、(WITH 句の)CTE です。FROM のサブクエリへの、相関の参照を SQLite が許しません。

sqlite
-- treat a query result as a table
SELECT t.role, t.cnt
FROM (
  SELECT role, COUNT(*) AS cnt
  FROM users
  GROUP BY role
) t
WHERE t.cnt > 1;

-- join a derived table
SELECT u.username, t.total_spent
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
) t ON t.user_id = u.id;

-- derived tables must be aliased
SELECT * FROM (SELECT 1 AS x) AS sub;

相関対非相関

1 回実行されキャッシュできるのが、非相関のサブクエリで、外側の行ごとに再実行されるのが(速く保つにはインデックスを)、相関サブクエリです。LATERAL/APPLY が SQLite になく、行ごとの集約を「結合」するには、派生テーブルで事前集約し LEFT JOIN しましょう。相関サブクエリが遅いとき、通常の修正が、GROUP BY 付きの JOIN か窓関数としての書き直しです。

sqlite
-- UNCORRELATED: runs once, result cached
SELECT username FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- CORRELATED: references outer row, re-runs per outer row
SELECT u.username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders
FROM users u;

-- lateral-style correlation in a JOIN subquery is NOT
-- supported directly; use a scalar subquery in SELECT or
-- rewrite as a join with GROUP BY:
SELECT u.username, COALESCE(t.n, 0) AS orders
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS n FROM orders GROUP BY user_id
) t ON t.user_id = u.id;
08

インデックス

インデックスの作成

読み取りを速くするが書き込みを遅くするのが(すべてのインデックスが、各 INSERT/UPDATE/DELETE で更新されます)、インデックスです。効くのが、列の順序です。(a, b) 上の複合インデックスが、WHERE a=? と WHERE a=? AND b=? を助けますが、WHERE b=? 単独では助けません(最も左の接頭辞のルールです)。制約を強制するのが、一意のインデックスです。B-tree としてインデックスを保存するのが、SQLite です。未使用のインデックスを落としましょう — スペースと書き込み性能を浪費します。

sqlite
-- single-column index
CREATE INDEX idx_users_email ON users(email);

-- unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- IF NOT EXISTS for idempotent scripts
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- drop an index
DROP INDEX IF EXISTS idx_users_email;

-- list indexes
.indices users
-- or
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

部分インデックス

一致する行だけをインデックスするため、より小さく保守が速いのが、部分インデックスです。クエリが、常に安定した述語でフィルタするとき(「active = 1」、「deleted_at IS NULL」)に理想的です。オプティマイザーが使うためには、クエリの WHERE が、インデックスの WHERE に(かより厳しく)一致しなければなりません。部分集合上でのみ一意性を強制するのが、一意の部分インデックスで — 「親ごとに 1 つのアクティブ行」の制約に完璧です。

sqlite
-- index only active users (smaller, faster)
CREATE INDEX idx_active_users ON users(username)
WHERE active = 1;

-- index only unpaid orders
CREATE INDEX idx_unpaid_orders ON orders(user_id)
WHERE status = 'unpaid';

-- unique partial index: one active session per user
CREATE UNIQUE INDEX idx_one_active_session
ON sessions(user_id) WHERE active = 1;

-- query must match the WHERE for the index to be used
SELECT * FROM users WHERE active = 1 AND username = 'alice';

式インデックス

関数か式の結果をインデックスし、変換された値での速いルックアップを可能にするのが、式インデックスです。しかし、クエリが正確に同じ式を使わなければなりません。よくある使い方:大文字小文字を区別しない検索(LOWER)、日付のバケット化(strftime)、JSON のフィールドの抽出(json_extract)です。決定的な関数だけが(RANDOM() か NOW() ではありません)。強力だが、スキーマ変更が厄介になるのが、式インデックスです。

sqlite
-- index a function result (must match the query expression exactly)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- query that can use it (must use the same expression)
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- index a date extraction for fast monthly queries
CREATE INDEX idx_orders_month ON orders(strftime('%Y-%m', created));
SELECT * FROM orders WHERE strftime('%Y-%m', created) = '2024-06';

-- index a JSON path
CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- indexed computed value (collation)
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);

カバリングと WITHOUT ROWID

クエリが読むすべての列を含み、インデックスだけで(テーブルのルックアップなし)SQLite がクエリを満たせるのが、カバリングインデックスです。PRIMARY KEY をクラスタ化されたインデックスにするのが、WITHOUT ROWID のテーブルです。キーでソートされて保存されるのが、行であり、キー/バリューのワークロードと、PK 上のレンジスキャンに理想的です。トレードオフ:AUTOINCREMENT なし、rowid の別名なし、わずかに複雑な更新。広く採用する前に、ベンチマークしましょう。

sqlite
-- covering index: includes all columns the query needs,
-- so SQLite never reads the table
CREATE INDEX idx_orders_cover ON orders(user_id, status, total);
SELECT user_id, status, total FROM orders WHERE user_id = 1;
-- ^ fully served by the index

-- WITHOUT ROWID tables: store rows IN the index (clustered)
CREATE TABLE kv (
  key   TEXT PRIMARY KEY,
  value TEXT
) WITHOUT ROWID;

-- the PK becomes the table itself; lookups by key are fast
SELECT value FROM kv WHERE key = 'config:theme';

-- good for key/value lookups and read-heavy tables
-- trade-off: no rowid, some features unsupported

インデックスの検査

最重要のツールが、EXPLAIN QUERY PLAN です。「USING INDEX」 = 良い。「SCAN」(フルテーブルスキャン)= 大きいテーブルでは通常悪いです。結合のためには、結合列がインデックスされていること、プランナーがより大きいテーブルをスキャンしていないことを確認しましょう。VDBE のバイトコードを示すのが(QUERY PLAN なしの)EXPLAIN で、めったに不要ですが、深いデバッグに有用です。プランナーがインデックスを選ぶのに使う、カーディナリティの統計を保持するのが、(ANALYZE により満たされた)sqlite_stat1 です。テーブルを再構築せず、肥大化したか壊れたインデックスを再構築するのが、REINDEX です。プランナーが良い計画を選ぶための、新鮮な統計を持つよう、大きなデータのロード後に ANALYZE を実行しましょう。

sqlite
-- see if an index is used
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';
-- look for 'SEARCH users USING INDEX idx_users_email'

-- list all indexes on a table with their definitions
SELECT name, sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

-- index stats (how often each index is used)
SELECT name, idx_scan, idx_read
FROM sqlite_stat1, sqlite_master
WHERE sqlite_stat1.tbl = 'users';

-- rebuild an index (after corruption or bloat)
REINDEX idx_users_email;
REINDEX;  -- rebuild all indexes

-- analyze tables to update query planner stats
ANALYZE;
09

ビュー

ビューの作成

保存されたクエリにより定義された仮想テーブルであるのが、ビューです。データを保存せず(temp ビューを除き)、基礎の SELECT を毎回再実行します。複雑なクエリの単純化、一貫したアクセスパターンの強制、アプリからのスキーマ変更の抽象化に使いましょう。デフォルトで読み取り専用であるのが(更新可能なビューには制約付き)、SQLite でのビューです。基礎のテーブルに影響するのは決してないのが、DROP VIEW です。

sqlite
-- a view is a saved SELECT, queried like a table
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE active = 1;

SELECT * FROM active_users WHERE age > 18;

-- view with calculated columns
CREATE VIEW user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- IF NOT EXISTS for idempotent scripts
CREATE VIEW IF NOT EXISTS active_users AS
SELECT id, username FROM users WHERE active = 1;

-- drop a view
DROP VIEW IF EXISTS active_users;

更新可能なビュー

単純なビュー(単一の基底テーブル、JOIN/集約/DISTINCT/GROUP BY なし)上の INSERT/UPDATE/DELETE を SQLite が許します。基礎のテーブルへ転送されるのが、操作です。WITH CHECK OPTION を SQLite はサポートし「ない」ため、ビューを通して挿入された行が、ビューの WHERE を満たさず、その後ビュー越しに見えないかもしれません。書き込みを必要とする複雑なビューには、INSTEAD OF トリガーを使いましょう。

sqlite
-- simple views (one base table, no aggregates/distinct/etc.)
-- are auto-updatable in SQLite
CREATE VIEW active_users AS
SELECT id, username, email, age FROM users WHERE active = 1;

-- INSERT through the view (active defaults/applies)
INSERT INTO active_users (id, username, email) VALUES (1, 'x', '[email protected]');

-- UPDATE through the view
UPDATE active_users SET age = 30 WHERE id = 1;

-- DELETE through the view
DELETE FROM active_users WHERE id = 1;

-- the WITH CHECK OPTION is NOT supported in SQLite, so
-- inserts/updates that don't match the view's WHERE still
-- land in the base table but vanish from the view.

ビュー上の INSTEAD OF トリガー

ビューへの書き込みを横取りし、カスタムロジックを実行し、任意のビューを更新可能にするのが、INSTEAD OF トリガーです。複雑な(マルチテーブル、集約された)ビューを書き込み可能にする標準的な方法が、これです。入ってくる行と既存の行を参照するのが、NEW と OLD です。ビュー上でのみ発火し、基底テーブル上では発火しないのが、INSTEAD OF トリガーです。カプセル化されたビジネスロジックの実装や、非正規化されたビューの背後に正規化されたスキーマを隠すのに、使いましょう。

sqlite
-- a view joining multiple tables is not auto-updatable,
-- but INSTEAD OF triggers let you define the write behavior
CREATE VIEW user_orders AS
SELECT u.id AS user_id, u.username, o.id AS order_id, o.total
FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- route INSERTs on the view to the orders table
CREATE TRIGGER trg_user_orders_insert
INSTEAD OF INSERT ON user_orders
FOR EACH ROW
BEGIN
  INSERT INTO orders (id, user_id, total)
  VALUES (NEW.order_id, NEW.user_id, NEW.total);
END;

-- now this works:
INSERT INTO user_orders (user_id, order_id, total)
VALUES (1, 100, 49.99);

一時ビュー

現在のデータベース接続のためだけに存在し、切断時に自動的に落とされるのが、一時ビューです。共有されたスキーマを散らかさずに、複雑なアドホッククエリをステージングするのに完璧です。別々の temp スキーマに保存されるため、(同じファイルへの接続さえ)他の接続に見えません。TEMP のテーブルを参照できるのが、TEMP のビューであり、セッションスコープの ETL パイプラインに有用です。

sqlite
-- TEMP view exists only for the current connection
CREATE TEMP VIEW recent_orders AS
SELECT * FROM orders WHERE created > datetime('now', '-7 days');

-- the view vanishes when the connection closes
-- (no other connection can see it)

-- also valid: CREATE TEMPORARY VIEW
CREATE TEMPORARY VIEW my_scratch AS
SELECT id, username FROM users WHERE role = 'admin';

-- use case: ad-hoc analysis without polluting the schema
SELECT * FROM recent_orders WHERE total > 100;

-- list views
SELECT name, sql FROM sqlite_master
WHERE type = 'view';
SELECT name, sql FROM temp.sqlite_master
WHERE type = 'view';  -- temp views

マテリアライズドビュー(エミュレート)

ネイティブのマテリアライズドビューが SQLite になく、スケジュールで更新される通常のテーブルが、同じことを達成します。アトミックな更新のためには、トランザクションで新しいテーブルを構築し、改名しましょう。コミットまで古いバージョンを、その後新しいバージョンを、読み手が見ます。再構築の後に、インデックスを加えましょう。読み取り速度のための書き込み/ストレージのコストのトレードオフが、マテリアライズドビューであり — 頻繁にクエリされる高コストな集約に理想的です。

sqlite
-- SQLite has no native materialized views; emulate with a
-- real table that you refresh periodically

-- 1) create the 'materialized' table
CREATE TABLE mv_user_summary AS
SELECT u.id, u.username,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 2) add an index for fast lookups
CREATE INDEX idx_mv_user_summary_id ON mv_user_summary(id);

-- 3) refresh (full rebuild)
DELETE FROM mv_user_summary;
INSERT INTO mv_user_summary
SELECT u.id, u.username, COUNT(o.id), COALESCE(SUM(o.total), 0)
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.username;

-- 4) refresh atomically with a name swap
BEGIN;
CREATE TABLE mv_user_summary_new AS SELECT ...;
DROP TABLE mv_user_summary;
ALTER TABLE mv_user_summary_new RENAME TO mv_user_summary;
COMMIT;
10

トリガー

BEFORE / AFTER トリガー

行が書かれる前に動くのが、BEFORE トリガーです。検証(RAISE ABORT)や NEW の値の変換に使いましょう。行がコミットされた後に動くのが、AFTER トリガーです。監査、カスケード効果、非正規化に使いましょう。唯一のオプションであるのが(ステートメントレベルのトリガーなし)、FOR EACH ROW です。入ってくる行を保持するのが(INSERT/UPDATE)、NEW で、前の行を保持するのが(UPDATE/DELETE)、OLD です。条件付きでトリガーを発火させるのが、WHEN 句です。

sqlite
-- AFTER INSERT: audit log
CREATE TRIGGER trg_users_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_audit (user_id, action, at)
  VALUES (NEW.id, 'insert', datetime('now'));
END;

-- BEFORE INSERT: validate / normalize data
CREATE TRIGGER trg_users_lower_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SELECT RAISE(ABORT, 'email cannot be empty')
  WHERE NEW.email = '';
END;

-- AFTER UPDATE: log changes
CREATE TRIGGER trg_users_update_log
AFTER UPDATE ON users
FOR EACH ROW
WHEN OLD.email IS NOT NEW.email
BEGIN
  INSERT INTO email_changes (user_id, old_email, new_email, at)
  VALUES (NEW.id, OLD.email, NEW.email, datetime('now'));
END;

カスケード効果のためのトリガー

非正規化されたカウンタ、サマリーテーブル、updated_at のタイムスタンプを、トリガーが自動的に維持できます。注意:可能であるのが(自身のテーブルを更新するトリガーの)、再帰トリガーのチェーンです。これを制御するのが(後方互換性のためのデフォルト OFF)、PRAGMA recursive_triggers です。同じイベント上の複数のトリガーの順序が、作成順に従い、同じタイミングのトリガーをまたぐ、BEFORE/AFTER の優先度はありません。

sqlite
-- maintain a denormalized counter
CREATE TRIGGER trg_orders_count_inc
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count + 1
  WHERE id = NEW.user_id;
END;

CREATE TRIGGER trg_orders_count_dec
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  UPDATE users
  SET order_count = order_count - 1
  WHERE id = OLD.user_id;
END;

-- keep an 'updated_at' column fresh
CREATE TRIGGER trg_users_touch
AFTER UPDATE ON users
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
  UPDATE users SET updated_at = datetime('now') WHERE id = NEW.id;
END;

INSTEAD OF トリガー(ビュー)

ビュー上の元の INSERT/UPDATE/DELETE の代わりに動き、1 つ以上の基底テーブルへの書き込みをルートできるのが、INSTEAD OF トリガーです。複雑なビューを書き込み可能にする正統な方法が、これです。ビューの行ごとに発火します。ビュー上に定義できるのが、INSTEAD OF トリガーだけです。BEFORE/AFTER トリガーには、基底テーブルが要ります。正規化されたスキーマの上の、API スタイルの抽象化レイヤーに有用です。

sqlite
-- make a multi-table view writable
CREATE VIEW order_detail AS
SELECT o.id AS order_id, o.total, u.username, i.product_name
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items i ON i.order_id = o.id;

CREATE TRIGGER trg_order_detail_update
INSTEAD OF UPDATE ON order_detail
FOR EACH ROW
BEGIN
  UPDATE orders SET total = NEW.total WHERE id = NEW.order_id;
  UPDATE order_items SET product_name = NEW.product_name
  WHERE order_id = NEW.order_id;
END;

-- now UPDATE on the view routes to both tables
UPDATE order_detail SET total = 99.99, product_name = 'Widget'
WHERE order_id = 5;

RAISE とエラー処理

トリガーの失敗の伝播を制御するのが、RAISE です。ステートメントをキャンセルし変更をロールバックするのが、ABORT(最もよくある)で、トランザクション全体をロールバックするのが、ROLLBACK、ステートメントを中止するがトランザクション内の以前の成功したステートメントを保つのが、FAIL、犯人の行だけをスキップするのが、IGNORE です。一括ロードの間、無効な行を静かに落とすきれいな方法が、RAISE(IGNORE) です。

sqlite
-- RAISE aborts the statement with a custom error
CREATE TRIGGER trg_check_age
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.age < 0
BEGIN
  SELECT RAISE(ABORT, 'age must be non-negative');
END;

-- ROLLBACK: undo the whole transaction, statement fails
CREATE TRIGGER trg_rollback_example
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN NEW.balance < 0
BEGIN
  SELECT RAISE(ROLLBACK, 'balance cannot go negative');
END;

-- FAIL: abort the current statement only (like ABORT)
-- IGNORE: skip this row, continue with the rest
CREATE TRIGGER trg_skip_invalid
BEFORE INSERT ON logs
FOR EACH ROW
WHEN NEW.level NOT IN ('info','warn','error')
BEGIN
  SELECT RAISE(IGNORE);
END;

トリガーの管理

sqlite_master に保存されるのが(TEMP トリガーには temp.sqlite_master)、トリガーです。取り除くのが、DROP TRIGGER で、ALTER TRIGGER はなく — 落として再作成しましょう。接続ローカルであり、切断時に消えるのが、TEMP のトリガーで、デバッグに有用です。ドロップすることを除き、トリガーを一時的に無効化する組み込みの方法がなく、条件付きの実行のためには、WHEN 句を使いましょう。タイミング内で、作成順に発火するのが、同じイベント上の複数のトリガーです。

sqlite
-- list all triggers
SELECT name, tbl_name, sql FROM sqlite_master
WHERE type = 'trigger';

-- list triggers on a specific table
SELECT name, sql FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'users';

-- drop a trigger
DROP TRIGGER IF EXISTS trg_users_audit;

-- triggers in a TEMP schema (connection-local)
CREATE TEMP TRIGGER trg_temp_log
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  SELECT 'inserted ' || NEW.id;
END;

-- trigger timing order: BEFORE triggers fire in
-- creation order, then AFTER triggers in creation order.
11

PRAGMA の設定

PRAGMA の基本

SQLite 固有の設定インターフェースであるのが、PRAGMA です。ジャーナリング、キャッシング、外部キー、整合性、クエリプランナーを設定が制御します。ドキュメントが別のことを言わない限り、セッションスコープであるのが(接続ごと)、設定です。トランザクション内での設定が無視され得ます。トランザクションの外か、テーブル作成の前でのみ効果を取るのが、一部の PRAGMA(journal_mode、page_size)です。ビルドが何をサポートするか見るには、PRAGMA compile_options を。

sqlite
-- PRAGMA is SQLite's configuration knob (CLI and APIs)
-- read a setting
PRAGMA journal_mode;
PRAGMA cache_size;
PRAGMA foreign_keys;

-- set a setting (session scope unless noted)
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -20000;   -- negative = KB, positive = pages
PRAGMA temp_store = MEMORY;

-- many PRAGMAs are no-ops below a certain SQLite version
-- check the compile-time options
PRAGMA compile_options;
-- e.g. ENABLE_FTS5, ENABLE_JSON1, MAX_ATTACHED, etc.

-- PRAGMAs are NOT part of SQL standard and are SQLite-only.

外部キー

デフォルトで OFF であるのが、SQLite での外部キーの強制です。他のデータベースから来たユーザーにとって悪名高い落とし穴です。PRAGMA foreign_keys = ON により、すべての接続で有効化しなければならず、トランザクション内では設定できません。この設定が存在するのは、(FK 対応以前の)早いスキーマがさもなければ壊れるためです。本番では必ず有効化しましょう。依存関係に違反する順序でのデータのインポートを助けるのが、defer_foreign_keys です。

sqlite
-- foreign keys are OFF by default in SQLite!
PRAGMA foreign_keys = ON;   -- enable per connection

-- check current state
PRAGMA foreign_keys;

-- inspect FK definitions on a table
PRAGMA foreign_key_list(orders);
-- returns: id, seq, table, from, to, on_update, on_delete, match

-- enable FKs in your app's connection setup, every time
-- (this is a frequent source of bugs when moving from MySQL/PG)

-- deferred FK checking (checked at COMMIT, not per statement)
PRAGMA defer_foreign_keys = ON;
-- or declare DEFERRABLE INITIALLY DEFERRED on the FK

ジャーナルモードと WAL

ほとんどのアプリのための推奨ジャーナルモードが、WAL(Write-Ahead Logging)です。読み手と書き手が互いをブロックせず、耐久性が良いです。-wal と -shm のサイドカーファイルを作るのが、メインの DB の隣にです。データベースファイルごとに永続的であるのが(1 回設定しましょう)、WAL です。WAL の内容をメインファイルへ折り返しスペースを取り戻すのが、wal_checkpoint(TRUNCATE) です。ネットワークのファイルシステムでの WAL は避けましょう — 共有メモリが要ります。

sqlite
-- WAL: write-ahead logging (best for most apps)
PRAGMA journal_mode = WAL;
-- benefits: readers never block writers, writers never block
--           readers, crash recovery is fast
-- trade-off: creates -wal and -shm sidecar files

-- other modes: DELETE (default), TRUNCATE, PERSIST, MEMORY, OFF
PRAGMA journal_mode = DELETE;

-- WAL checkpoint: merge -wal back into the main file
PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(TRUNCATE);  -- also shrinks the -wal file

-- auto-checkpoint threshold (pages of WAL before auto-checkpoint)
PRAGMA wal_autocheckpoint = 1000;

-- WAL is persistent: setting it once keeps it for that DB file.

同期と耐久性

SQLite がどれほど積極的に fsync を呼ぶかを制御するのが、synchronous です。最も安全なのが(すべてのコミットが fsync する)、FULL で、WAL を使うときの推奨値が、NORMAL です(チェックポイントで fsync し、コミットごとではなく、実際には無視できる破損のリスクです)。停電時のデータベースの破損のリスクがあるのが、OFF であり — 本当に使い捨てのデータか再構築可能なキャッシュのためだけに。スキーマ作成の前に page_size を設定すべきで、後の変更には VACUUM が要ります。

sqlite
-- how thoroughly SQLite flushes to disk on commit
PRAGMA synchronous;        -- read current value
PRAGMA synchronous = FULL;       -- safest, slowest (default for rollback journal)
PRAGMA synchronous = NORMAL;     -- safe with WAL, fast (recommended for WAL)
PRAGMA synchronous = OFF;        -- fastest; corruption risk on power loss
PRAGMA synchronous = EXTRA;      -- even more careful than FULL

-- trade-off: durability vs speed
-- FULL: fsync at every commit (survives OS crash)
-- NORMAL (WAL): fsync at checkpoint, not every commit
-- OFF: no fsync — fast but may corrupt on power loss

-- page size (set BEFORE creating tables, ideally)
PRAGMA page_size = 4096;
PRAGMA page_size;

キャッシュ、メモリ、temp ストア

メモリ内のページキャッシュであるのが、cache_size です。読み取りが重いワークロードには、一般に大きい方が良いです。KB であるのが、負の値で、ページが、正の値です。中間の結果(ソート、一時テーブル)を RAM へ動かし、大きな集約を速くするのが、temp_store = MEMORY です。読み取りのためのメモリマップド I/O を有効化するのが、mmap_size で、大きな読み取り専用データベースを速くし得ます。アプリのメモリ予算とアクセスパターンに基づき、これらを調整しましょう。

sqlite
-- page cache (negative = KB, positive = pages)
PRAGMA cache_size = -65536;   -- 64 MB cache
PRAGMA cache_size = 20000;    -- 20000 pages

-- where temp tables and intermediate results live
PRAGMA temp_store = DEFAULT;  -- compile-time default
PRAGMA temp_store = FILE;     -- temp files
PRAGMA temp_store = MEMORY;   -- all temp data in RAM

-- mmap memory-mapped I/O for reads
PRAGMA mmap_size = 268435456;  -- 256 MB

-- soft heap limit for the SQLite library
PRAGMA soft_heap_limit = 100000000;  -- 100 MB

-- store prepared statements in memory between calls
PRAGMA cache_spill;

整合性とスキーマの情報

データベース構造とコンテンツ全体を検証するのが(遅いが徹底的)、integrity_check で、コンテンツの検証をスキップし、はるかに速いのが、quick_check です。バックアップの間や、クラッシュの後に、integrity_check を実行しましょう。列のメタデータを報告するのが(FTS5 の rowid のような隠れた列のためには、table_xinfo)、table_info です。どのインデックスが存在し、その列が何かを明らかにするのが、index_list と index_info です。attached されたデータベースを示すのが、database_list です。

sqlite
-- full integrity check (slow on large DBs)
PRAGMA integrity_check;
-- returns 'ok' or a list of problems

-- quick check (faster, less thorough)
PRAGMA quick_check;

-- list tables / schema
SELECT name, sql FROM sqlite_master WHERE type = 'table';

-- table info: columns, types, notnull, default, pk
PRAGMA table_info(users);
-- returns: cid, name, type, notnull, dflt_value, pk

-- extended table info (includes hidden columns from FTS/etc.)
PRAGMA table_xinfo(users);

-- index list with origin and unique flag
PRAGMA index_list(users);
PRAGMA index_info(idx_users_email);

-- database file header info
PRAGMA database_list;
12

日付と時刻の関数

date / time / datetime

ネイティブの DATE 型が SQLite になく、ISO-8601(「YYYY-MM-DD HH:MM:SS」)の TEXT であるのが、日付です。多くの入力形式を受け付けるのが(ISO、スラッシュ、月名、「unixepoch」修飾子経由の Unix タイムスタンプ)、日付/時刻の関数です。ステートメントごとに 1 回評価され、行ごとではないのが、「now」です。マルチ行の UPDATE のすべての行が、同じタイムスタンプを得ます。これは通常、望むものです。UTC で保存し、表示用に localtime で整形しましょう。

sqlite
-- current date/time (UTC by default)
SELECT date('now');              -- '2024-06-15'
SELECT time('now');              -- '14:30:00'
SELECT datetime('now');          -- '2024-06-15 14:30:00'
SELECT julianday('now');         -- 2460476.1042 (days since 4713 BC)

-- parse various formats
SELECT date('2024-06-15');
SELECT date('2024/06/15');
SELECT date('June 15, 2024');
SELECT datetime('2024-06-15 14:30:00.123');
SELECT date('2024-06-15','unixepoch');  -- from Unix timestamp (seconds)

-- Unix timestamp to datetime
SELECT datetime(1718450400, 'unixepoch');
SELECT datetime(1718450400, 'unixepoch', 'localtime');

strftime の整形

普遍的な日付の整形器であるのが、strftime です。C の strftime を映し返すのが、形式コードです。グループ化のための(年、月、曜日の)日付のパートの抽出や、カスタムの表示形式の描画に、これを使いましょう。月ごとのグループ化のためには、「YYYY-MM」を出すのが、strftime('%Y-%m', created) で、これは自然にソート・グループ化されます。DAYNAME()/MONTHNAME() が SQLite になく、曜日名が必要なら、CASE と strftime('%w', ...) を組み合わせましょう。

sqlite
-- strftime is the most flexible formatter
--   strftime(format, timestring, modifier, modifier, ...)
SELECT strftime('%Y-%m-%d', 'now');          -- '2024-06-15'
SELECT strftime('%Y-%m-%d %H:%M', 'now');    -- '2024-06-15 14:30'
SELECT strftime('%H:%M:%S', 'now');          -- '14:30:00'

-- common format codes
--   %Y year (4-digit)    %m month (01-12)   %d day (01-31)
--   %H hour (00-23)      %M minute (00-59)  %S second (00-59)
--   %j day of year       %w day of week (0=Sun)
--   %W week of year      %p AM/PM           %I hour (01-12)

-- get the day name
SELECT strftime('%w', '2024-06-15');   -- '6' (Saturday)
SELECT strftime('%Y-%m', 'now');       -- month bucket for grouping

修飾子(算術)

日付を動かしたり整列させたりするため、左から右へチェーンされるのが、修飾子です。「+N units」 / 「-N units」であるのが、間隔です(単位:days、hours、minutes、seconds、months、years)。期間の境界へスナップするのが、「start of month/year/day」で — 月次レポートに完璧です。次の weekday N の日付を返すのが(今日が一致すれば、今日)、weekday N です。現在月の最終日を出すのが、「start of month」、「+1 month」、「-1 day」の組み合わせで — クラシックな慣用句です。

sqlite
-- shift a date by an interval
SELECT date('now', '+1 day');           -- tomorrow
SELECT date('now', '-1 month');         -- one month ago
SELECT date('now', '+1 year', '+1 day');-- next year + 1 day
SELECT datetime('now', '+2 hours', '+30 minutes');

-- start of period
SELECT date('now', 'start of month');       -- first day of month
SELECT date('now', 'start of year');        -- first day of year
SELECT date('now', 'start of day');         -- midnight today

-- weekday: next occurrence of a given weekday (0=Sun, 1=Mon, ... 6=Sat)
SELECT date('now', 'weekday 1');            -- next Monday
SELECT date('now', 'weekday 0', '+7 days'); -- Sunday after this one

-- combine modifiers
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- last day of month

Unix 時刻とユリウス日

エポックからの整数/秒であるのが、Unix のタイムスタンプで、直接整数を返すのが、unixepoch()(3.38+)です。連続した分数の日のカウントであるのが、ユリウス日です。2 つの julianday 値の引き算が、日数で経過時間を与えます(秒には 86400 を掛けましょう)。年齢計算のためには、365.25 による割り算が、閏年をおおよそ考慮します。法的な正確さのためには、year/month/day のコンポーネントを明示的に比較しましょう。

sqlite
-- Unix timestamp (seconds since 1970-01-01 UTC)
SELECT strftime('%s', 'now');                  -- current Unix time (text)
SELECT unixepoch('now');                       -- integer Unix time (SQLite 3.38+)
SELECT datetime(1718450400, 'unixepoch');      -- back to datetime

-- Julian day (fractional days since 4713-01-01 BC)
SELECT julianday('now');                       -- 2460476.6042
SELECT julianday('2024-06-15') - julianday('2024-06-10');  -- 5.0 (days between)

-- compute elapsed time in seconds
SELECT (julianday('now') - julianday('2024-01-01')) * 86400.0 AS seconds_elapsed;

-- human-readable age from a birthdate
SELECT CAST((julianday('now') - julianday('1990-05-20')) / 365.25 AS INT) AS age;

タイムゾーンと localtime

タイムゾーンのデータベースが SQLite になく、UTC であるのが、「now」で、ホスト OS のタイムゾーン設定に頼るのが、「localtime」/「utc」の修飾子です。推奨パターン:タイムスタンプは常に UTC で保存し(UTC であるのが、datetime('now'))、表示時に「localtime」の修飾子でローカル時刻へ変換しましょう。これが、異なるゾーンのサーバーをまたぐ曖昧さや、サマータイムの遷移を避けます。ローカル時刻を決して保存しないこと。

sqlite
-- 'now' is always UTC; convert to local time for display
SELECT datetime('now', 'localtime');          -- local wall-clock time
SELECT datetime('now');                       -- UTC

-- convert a stored UTC timestamp to local time
SELECT datetime(created, 'localtime') FROM users;

-- convert local time back to UTC for storage
SELECT datetime('2024-06-15 14:30:00', 'utc');

-- best practice: store UTC, format for display
CREATE TABLE events (ts TEXT DEFAULT (datetime('now')));  -- UTC
-- display:
SELECT datetime(ts, 'localtime') AS local_ts FROM events;

-- timezone is determined by the OS; SQLite has no TZ database.
13

文字列の関数

長さと部分文字列

TEXT のための文字数と、BLOB のためのバイト数を数えるのが、length() です。UTF-8 のバイト長のためには、先に BLOB へ cast しましょう。1 起点なのが、substr() で、末尾から数えるのが、負の開始です。ネイティブの LPAD/RPAD が SQLite になく、文字列の連結と substr でエミュレートしましょう。マルチバイトのテキストを切り詰めるときのバイト対文字の混乱に注意しましょう:文字上で動くのが substr なので安全ですが、バイトレベルのスライシングはそうではありません。

sqlite
SELECT length('hello');              -- 5
SELECT length('日本語');             -- 3 (characters, not bytes)
SELECT length(x'00ff');             -- 2 (blob length in bytes)

SELECT substr('hello world', 7);    -- 'world' (1-indexed)
SELECT substr('hello world', 1, 5); -- 'hello' (length 5)
SELECT substr('hello', -3);         -- 'llo' (negative = from end)
SELECT substr('hello', -3, 2);      -- 'll'

-- byte length (vs character length)
SELECT length('日本語');            -- 3 (characters)
SELECT length(CAST('日本語' AS BLOB)); -- 9 (UTF-8 bytes)

-- left/right pad (workaround; no native LPAD/RPAD)
SELECT substr('0000' || '42', -4, 4);  -- '0042'

大文字小文字とトリミング

デフォルトで ASCII の文字だけに影響するのが、upper()/lower() です。ICU がロードされない限り、非 ASCII の文字が変わらずに残ります。デフォルトで前後の空白を取り除くのが、trim() で、2 番目の引数として与えられた特定の文字もです。リテラルの部分文字列の置換を行うのが(正規表現やパターンなし)、replace() です。文字セットの置換のためのネイティブの TRANSLATE() がなく、replace() の呼び出しをチェーンするか、カスタム関数を使いましょう。

sqlite
SELECT upper('hello');       -- 'HELLO'
SELECT lower('HELLO');       -- 'hello'
SELECT upper('日本語');      -- '日本語' (no case to change)

SELECT trim('  hi  ');            -- 'hi'  (both sides)
SELECT ltrim('  hi  ');           -- 'hi   ' (left only)
SELECT rtrim('  hi  ');           -- '  hi' (right only)

-- trim specific characters
SELECT trim('xxhelloxx', 'x');    -- 'hello'
SELECT ltrim('aaabbb', 'a');      -- 'bbb'

-- replace characters
SELECT replace('a-b-c', '-', '/'); -- 'a/b/c'
SELECT replace('Hello World', 'o', '0'); -- 'Hell0 W0rld'

連結と printf

標準の SQL の連結子であるのが、|| の演算子ですが、NULL を伝播します(NULL || x = NULL)ので、NULL が可能なときは COALESCE で包みましょう。C スタイルの整形をもたらし、パディング、数値の整形、カンマの区切り付きの文字列を構築する最もきれいな方法が、printf()/format() です。SQL への埋め込みのため、安全に引用符で囲まれエスケープされた文字列を返すのが、quote() です。動的な SQL を構築するときに有用です。

sqlite
-- || operator concatenates
SELECT 'Hello' || ' ' || 'World';      -- 'Hello World'
SELECT username || ' <' || email || '>' FROM users;

-- NULL propagates through || (NULL || 'x' IS NULL)
SELECT 'a' || NULL || 'b';             -- NULL
-- use COALESCE to handle NULLs:
SELECT COALESCE(username, '') || ' <' || COALESCE(email, '') || '>';

-- printf-style formatting (also called format())
SELECT printf('%s has %d orders', 'alice', 5);  -- 'alice has 5 orders'
SELECT format('%,d', 1234567);                   -- '1,234,567' (3.38+)
SELECT printf('%5.2f', 3.14159);                 -- ' 3.14'
SELECT printf('%04d', 42);                       -- '0042'

-- quote() escapes a string for SQL
SELECT quote("It's alive");   -- '''It''s alive'''

分割と検索

部分文字列の最初の出現を見つけるのが(1 起点、見つからなければ 0)、instr() です。ネイティブの SPLIT_PART が SQLite になく、substr + instr か再帰 CTE でエミュレートしましょう。「出現のカウント」の慣用句が、区切りの除去の前後の長さの引き算です。符号点から文字列を構築するのが、char() で、最初の文字の符号点を返すのが、unicode() です。フルのテキストの分割のためには、json_each 経由で、JSON の配列をロードしましょう。

sqlite
-- find position of substring (1-indexed; 0 if not found)
SELECT instr('hello world', 'world');   -- 7
SELECT instr('hello', 'xyz');           -- 0

-- emulate split by getting the Nth field
-- (no native SPLIT_PART; use a CTE or substring + instr)
SELECT
  substr('a,b,c,d', 1, instr('a,b,c,d', ',') - 1) AS first;  -- 'a'

-- count occurrences of a substring
SELECT (length('a,b,c,d') - length(replace('a,b,c,d', ',', ''))) / length(',') AS commas;
-- 3

-- reverse a string
SELECT reverse('hello');   -- 'olleh'

-- character from integer code
SELECT char(65, 66, 67);   -- 'ABC'
SELECT unicode('A');       -- 65

LIKE / GLOB / 照合順序

ASCII のためにデフォルトで大文字小文字を区別しないのが(頻出の驚き)、LIKE です。大文字小文字の敏感さのためには、COLLATE BINARY を。常に大文字小文字を区別し、シェルスタイルの文字クラスをサポートするのが、GLOB です。大文字小文字を区別しない比較のための組み込みの照合順序が、NOCASE で、大文字小文字を区別しないクエリがインデックスを使うよう、列のインデックスへ適用しましょう。非 ASCII の大文字小文字の折り畳みのためには、ICU の拡張をロードするか、インデックス付きの小文字化されたコピーの列を保存しましょう。

sqlite
-- LIKE: case-insensitive (ASCII), wildcards % and _
SELECT * FROM users WHERE username LIKE 'al%';
SELECT * FROM users WHERE username LIKE '_lice';

-- case-sensitive LIKE
SELECT * FROM users WHERE username LIKE 'Al%' COLLATE BINARY;

-- make LIKE case-insensitive for non-ASCII too (needs ICU)
-- or store a lowercased copy and query that
SELECT * FROM users WHERE LOWER(username) = LOWER('Alice');

-- GLOB: case-sensitive, Unix-style (* ? [abc] [a-z])
SELECT * FROM users WHERE username GLOB 'A*';
SELECT * FROM users WHERE username GLOB '[A-D]*';

-- built-in collations: BINARY (default), NOCASE, RTRIM
CREATE INDEX idx_users_name_ci ON users(username COLLATE NOCASE);
SELECT * FROM users WHERE username = 'ALICE' COLLATE NOCASE;

-- COLLATE in ORDER BY
SELECT * FROM users ORDER BY username COLLATE NOCASE;
14

JSON の関数

値の抽出

JSON を読むための主力であるのが、json_extract(か、3.38+ の -> / ->> の演算子)です。ルートが、$ で、オブジェクトのプロパティへのアクセスが、$.key、配列要素へのアクセスが(0 起点で)、$.arr[i] です。スカラー(text/integer/real)を返すのが、->> で、JSON を返すのが(そのため、配列上の '$.b' が、JSON として配列を出します)、-> です。頻繁にクエリされる JSON のフィールドは通常の列として保存するか、パフォーマンスのためには、json_extract 上に式インデックスを加えましょう。

sqlite
-- json_extract: get a value by JSON path
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.a');       -- 1
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b[1]');    -- 20
SELECT json_extract('{"a": 1, "b": [10, 20, 30]}', '$.b');       -- '[10,20,30]'

-- -> returns JSON; ->> returns scalar (text/integer/etc.)
SELECT '{"a": 1, "b": "x"}' -> '$.a';   -- 1 (as JSON)
SELECT '{"a": 1, "b": "x"}' ->> '$.a';  -- 1 (as scalar)
SELECT '{"a": 1, "b": "x"}' ->> '$.b';  -- 'x' (as text)

-- nested paths
SELECT json_extract('{"u": {"name": "alice"}}', '$.u.name');   -- 'alice'

-- array length
SELECT json_array_length('[1, 2, 3]');  -- 3
SELECT json_array_length('{"a": [1,2]}', '$.a');  -- 2

-- get object keys
SELECT json_each.name FROM json_each('{"a":1,"b":2}');  -- 'a', 'b'

JSON の構築

SQL の値から JSON を構築するのが、json_object と json_array です(省略されず、NULL が JSON の null になります)。クエリの行から JSON を構築する集約関数が、json_group_array と json_group_object(3.38+)で、SQL から直接 JSON API を作るのに非常に有用です。複雑な文書を構築するには、コンストラクタをネストしましょう。JSON の文字列リテラルとして、文字列を安全に埋め込むのが、json_quote です。

sqlite
-- build a JSON object
SELECT json_object('id', 1, 'name', 'alice', 'active', 1);
-- {"id":1,"name":"alice","active":1}

-- build a JSON array
SELECT json_array(1, 2, 3, 'four', NULL);
-- [1,2,3,"four",null]

-- nest them
SELECT json_object(
  'user', json_object('id', 1, 'name', 'alice'),
  'roles', json_array('admin', 'editor')
);

-- aggregate rows into a JSON array
SELECT json_group_array(username) FROM users WHERE active = 1;
-- ["alice","bob","carol"]

-- aggregate into a JSON object (key:value)
SELECT json_group_object(username, email) FROM users WHERE active = 1;
-- {"alice":"[email protected]","bob":"[email protected]"}

-- quote a string as JSON
SELECT json_quote('hello');   -- '"hello"'

JSON の修正

挿入対更新の挙動を区別するのが、json_set/insert/replace で、パスを削除するのが、json_remove です。NEW の JSON 値を返すのが、これらです。不変であるのが、SQLite の JSON であるため、変更を永続化するには、結果で列を UPDATE します:UPDATE t SET data = json_set(data, '$.x', 1) WHERE id = 5。オブジェクトをマージするのが(RFC 7396)、json_patch で、パッチの null 値がキーを削除します。大きな文書のためには、ホットなフィールドを別々の列として保存することを検討しましょう。

sqlite
-- json_set: insert or update a path
SELECT json_set('{"a": 1}', '$.b', 2);
-- {"a":1,"b":2}
SELECT json_set('{"a": 1}', '$.a', 99);
-- {"a":99}

-- json_insert: insert only if path does NOT exist
SELECT json_insert('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":1,"b":2}  (a unchanged because it exists)

-- json_replace: update only if path EXISTS
SELECT json_replace('{"a": 1}', '$.a', 99, '$.b', 2);
-- {"a":99}  (b not added because it doesn't exist)

-- json_remove: delete a path
SELECT json_remove('{"a": 1, "b": 2}', '$.b');
-- {"a":1}

-- json_patch: merge per RFC 7396
SELECT json_patch('{"a": 1, "b": 2}', '{"b": 3, "c": 4}');
-- {"a":1,"b":3,"c":4}

JSON 配列のクエリ

JSON を行へ拡張する、仮想のテーブル値関数であるのが、json_each と json_tree です。単一の配列/オブジェクトを扱うのが、json_each で、fullkey のパスを公開しつつ、構造全体を再帰的にたどるのが、json_tree です。これで、JSON 配列上のセットベースの操作の扉が開きます。フィルタリング、結合、集約です。列の中のオブジェクトの配列をクエリするには、LATERAL 風の結合を使いましょう:FROM users u, json_each(u.tags) e。

sqlite
-- json_each: virtual table, one row per array element
SELECT value FROM json_each('[10, 20, 30]');
-- 10, 20, 30

-- with index
SELECT key, value FROM json_each('["a","b","c"]');
-- 0,'a'  1,'b'  2,'c'

-- query an array stored in a column
SELECT u.username, e.value AS tag
FROM users u, json_each(u.tags) e
WHERE e.value LIKE 'admin%';

-- aggregate back to JSON
SELECT json_group_array(value)
FROM json_each('[10, 20, 30]')
WHERE value > 15;
-- [20,30]

-- json_tree: recursively walk nested structures
SELECT fullkey, value
FROM json_tree('{"a": {"b": [1, 2]}}');
-- '$','{"a":{"b":[1,2]}}'
-- '$.a','{"b":[1,2]}'
-- '$.a.b[0]',1
-- '$.a.b[1]',2

JSON のインデックスと検証

速い JSON フィールドのルックアップのため、json_extract の式をインデックスしましょう。利得を得るためには、クエリが同一の式を使わなければなりません。1/0 を返すのが、json_valid() です。JSON 専用の列を強制するため、CHECK の制約で使いましょう。パスでの JSON のタイプを報告するのが(「object」、「array」、「integer」、「real」、「string」、「boolean」、「null」)、json_type() です。重い JSON のワークロードのためには、ホットなフィールドを通常のインデックス付きの列へ非正規化し、柔軟な末尾のためには JSON を保ちましょう。

sqlite
-- expression index on a JSON field for fast lookups
CREATE INDEX idx_events_type
ON events(json_extract(data, '$.type'));
SELECT * FROM events WHERE json_extract(data, '$.type') = 'login';

-- unique index on a JSON field
CREATE UNIQUE INDEX idx_users_email_json
ON users(json_extract(profile, '$.email'));

-- validate JSON
SELECT json_valid('{"a": 1}');     -- 1
SELECT json_valid('{a: 1}');       -- 0 (unquoted keys)

-- JSON type of a path
SELECT json_type('{"a": 1, "b": [1,2]}', '$.a');  -- 'integer'
SELECT json_type('{"a": 1, "b": [1,2]}', '$.b');  -- 'array'

-- check constraint for valid JSON columns
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  data TEXT CHECK (json_valid(data))
);
15

窓関数

OVER 句の基本

GROUP BY が折りたたむのではなく、現在の行に関連する行の集合をまたいで計算するのが、(SQLite 3.25+ の)窓関数です。「all rows」を意味するのが、OVER () で、ランキングのための順序を定義するのが、OVER (ORDER BY ...) です。一意なのが、ROW_NUMBER で、同点の後にギャップを残すのが、RANK、残さないのが、DENSE_RANK です。SELECT と ORDER BY に現れ、WHERE には現れないのが、窓関数です。外側のクエリか CTE でフィルタしましょう。

sqlite
-- window functions: aggregate OVER a 'window' of rows
-- without collapsing rows (unlike GROUP BY)
SELECT username, age,
  AVG(age) OVER () AS overall_avg
FROM users;

-- ROW_NUMBER: unique sequential number per row
SELECT username,
  ROW_NUMBER() OVER (ORDER BY created) AS row_num
FROM users;

-- RANK / DENSE_RANK
SELECT username, age,
  RANK()       OVER (ORDER BY age DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
FROM users;

PARTITION BY

窓関数のために行をグループへ分割するのが、PARTITION BY です。GROUP BY のようですが折りたたみません。グループごとの集約を得つつ、各行が身元を保ちます。「グループごとのトップ N」のパターン(ROW_NUMBER + 外側の WHERE rn <= N)が、正統な解決策です:「顧客ごとのトップ 3 注文」、「著者ごとの最新 5 投稿」など。相関サブクエリより、はるかに速いのが、典型的です。

sqlite
-- per-group ranking / aggregation
SELECT username, role, age,
  ROW_NUMBER() OVER (PARTITION BY role ORDER BY age DESC) AS rank_in_role,
  AVG(age)     OVER (PARTITION BY role)                   AS avg_role_age
FROM users;

-- top N per group (common pattern)
SELECT * FROM (
  SELECT user_id, total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders
) WHERE rn <= 3;

-- percent of group total
SELECT username, role,
  age * 1.0 / SUM(age) OVER (PARTITION BY role) AS pct_of_role_total
FROM users;

フレームの指定

現在の行に関連し、窓関数がどの行を見るかを定義するのが、フレームです。累計を与えるのが、UNBOUNDED PRECEDING..CURRENT ROW です。スライディングウィンドウ(移動平均)を与えるのが、N PRECEDING..N FOLLOWING です。peer の行(同じ ORDER BY 値)を含むのが、ORDER BY 付きのデフォルトのフレームが使う RANGE で、厳密に行を数えるのが、ROWS です。誤った分析のよくある源が、フレーム対 ORDER BY の誤解です。

sqlite
-- RUNNING TOTAL: cumulative sum over rows so far
SELECT username, total,
  SUM(total) OVER (ORDER BY created
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders;

-- MOVING AVERAGE: 3-row window centered on current row
SELECT username, total,
  AVG(total) OVER (ORDER BY created
                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM orders;

-- default frame for ORDER BY windows:
--   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- default frame (no ORDER BY):
--   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- ROWS vs RANGE: ROWS counts rows; RANGE includes peers.

LAG と LEAD

現在の行に対する他の行へのアクセスが、LAG と LEAD です。時系列の分析には(日ごとの差分、連続の検出)、不可欠です。範囲外の行のためのデフォルトが、省略可能な第 3 引数です。フレームの縁からの値を返すのが、FIRST_VALUE/LAST_VALUE で、注意:明示的な UNBOUNDED FOLLOWING のフレームを LAST_VALUE が必要とし、さもなければ(クラシックな落とし穴です)、現在の行の値を返します。

sqlite
-- compare current row to previous / next row
SELECT username, created,
  LAG(created, 1)  OVER (ORDER BY created) AS prev_created,
  LEAD(created, 1) OVER (ORDER BY created) AS next_created
FROM users;

-- difference from previous value (time-series analysis)
SELECT date, sales,
  sales - LAG(sales, 1) OVER (ORDER BY date) AS day_over_day
FROM daily_sales;

-- LAG with default value and offset
SELECT username, created,
  LAG(username, 3, 'N/A') OVER (ORDER BY created) AS three_back
FROM users;

-- FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT username, age,
  FIRST_VALUE(age) OVER (ORDER BY age) AS youngest,
  LAST_VALUE(age)  OVER (ORDER BY age
                         ROWS BETWEEN UNBOUNDED PRECEDING
                         AND UNBOUNDED FOLLOWING) AS oldest
FROM users;

NTILE とパーセンタイル

順序付けられた行をおおよそ等しい n バケットへ分割するのが、NTILE(n) です。四分位数には 4 を、パーセンタイルには 100 を、十分位数には 10 を。値が現在の行以下の行の分数であるのが、CUME_DIST で、0..1 の分数としての相対ランクが、PERCENT_RANK です。統計/分析のクエリのための積み木が、これらです。すべての窓関数のように、行を折りたたまないため、フィルタするには外側のクエリで包みましょう。

sqlite
-- divide rows into N equal buckets (quartiles, percentiles)
SELECT username, age,
  NTILE(4) OVER (ORDER BY age) AS quartile
FROM users;

-- CUME_DIST: cumulative distribution (0..1)
SELECT username, age,
  CUME_DIST() OVER (ORDER BY age) AS cume_dist
FROM users;

-- PERCENT_RANK: rank as a fraction (0..1)
SELECT username, age,
  PERCENT_RANK() OVER (ORDER BY age) AS pct_rank
FROM users;

-- NTH_VALUE: value at a specific row in the frame
SELECT username, age,
  NTH_VALUE(age, 2) OVER (ORDER BY age) AS second_age
FROM users;
16

CTE(WITH 句)

基本の CTE

WITH により定義された、名前付きでスコープされたサブクエリであるのが、CTE(共通テーブル式)です。読みやすさを改善し、同じステートメント内で複数回参照できます。通常、CTE をマテリアライズするのが(一時テーブルへ書く)、SQLite です。そのため、性能が派生テーブルと似ます。複雑なクエリをステージへ分解するか、事前集約するか、サブクエリの繰り返しを避けるのに、CTE を。標準の SQL であり、移植可能です。

sqlite
-- a CTE is a named temporary result set, scoped to one statement
WITH active_user_count AS (
  SELECT COUNT(*) AS n FROM users WHERE active = 1
)
SELECT username,
  (SELECT n FROM active_user_count) AS total_active
FROM users WHERE active = 1;

-- CTE in a join
WITH user_totals AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT u.username, COALESCE(t.spent, 0) AS spent
FROM users u
LEFT JOIN user_totals t ON t.user_id = u.id
ORDER BY spent DESC;

-- CTEs make complex queries readable; they are syntactic sugar
-- (SQLite often materializes them, like a temp table).

複数の CTE

カンマ区切りの複数の CTE が、それぞれ同じ WITH 内の以前の CTE を参照できます。単一のステートメントでの段階的なデータパイプラインに理想的です。論理的なステップであるのが、各 CTE です。上から下への読み取りが、データフローを映し返します。ネストしたサブクエリより、はるかに保守しやすいのが、これです。各 CTE をマテリアライズするのが、SQLite です。非常に大きな中間結果のためには、1 つの巨大な CTE のチェーンの代わりに、ステートメントをまたいで一時テーブルを考えましょう。

sqlite
-- chain multiple CTEs, each able to reference earlier ones
WITH
active_users AS (
  SELECT id, username FROM users WHERE active = 1
),
user_orders AS (
  SELECT user_id, COUNT(*) AS n_orders, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
),
labeled AS (
  SELECT au.username,
    COALESCE(uo.n_orders, 0) AS orders,
    COALESCE(uo.spent, 0)    AS spent
  FROM active_users au
  LEFT JOIN user_orders uo ON uo.user_id = au.id
)
SELECT * FROM labeled
WHERE orders > 0
ORDER BY spent DESC;

再帰 CTE

UNION(重複除去)か UNION ALL で結合される、アンカー(基底ケース)と再帰メンバーを持つのが、再帰 CTE です。ツリー(組織図、カテゴリツリー、スレッド化されたコメント)とグラフの走査の標準的な SQL の方法が、これです。再帰の深さに SQLite が上限を設けます(PRAGMA max_recursion_depth のデフォルト 1000 — 実際には、SQLITE_MAX_TRIGGER_DEPTH/limit です)。無価値でないのが、「generate series」のトリックです。SQLite に GENERATE_SERIES がないため(series の拡張が存在しますが)。

sqlite
-- a recursive CTE references itself; perfect for hierarchies
WITH RECURSIVE subordinates AS (
  -- anchor: top-level manager
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- recursive: their direct reports, one level deeper
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT depth, id, name FROM subordinates ORDER BY depth, name;

-- generate a series of numbers (no native GENERATE_SERIES)
WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt WHERE n < 10
)
SELECT n FROM cnt;

階層クエリ

自己参照テーブルで輝くのが、再帰 CTE です。ルートから各ノードへのスラッシュ区切りのパスを構築するのが、最初の例で、パンくずに有用です。ノードのすべての子孫(サブツリー)を見つけるのが、2 番目です。循環が可能なら、無限ループを防ぐため、UNION(UNION ALL ではなく)を使い、さもなければ、より速いのが、UNION ALL です。非常に深いツールのためには、パフォーマンスを監視しましょう — 結合列の適切なインデックスなしでは、再帰 CTE が遅くなり得ます。

sqlite
-- categories with parent/child self-reference
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES categories(id)
);

-- full path from root to each node
WITH RECURSIVE cat_path(id, name, path) AS (
  SELECT id, name, name
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, cp.path || ' > ' || c.name
  FROM categories c
  JOIN cat_path cp ON c.parent_id = cp.id
)
SELECT id, path FROM cat_path ORDER BY path;

-- find all descendants of a node
WITH RECURSIVE descendants(id) AS (
  SELECT id FROM categories WHERE id = 5   -- root of subtree
  UNION ALL
  SELECT c.id FROM categories c
  JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM categories WHERE id IN (SELECT id FROM descendants);

CTE の最適化

CTE の評価を制御する、MATERIALIZED / NOT MATERIALIZED のヒントをサポートするのが、SQLite 3.35+ です。一時テーブルへの書き込みを強制するのが、MATERIALIZED です(複数回参照される高コストな CTE に良い)。CTE をインライン化し、オプティimizer が述語を push down できるのが(安価な CTE に良い)、NOT MATERIALIZED です。自分でよく選ぶのが、通常プランナーです。EXPLAIN QUERY PLAN が悪い計画を示すときだけ、ヒントを使いましょう。常にマテリアライズされるのが、再帰 CTE です。

sqlite
-- MATERIALIZED: force SQLite to materialize (temp table)
WITH cte AS MATERIALIZED (
  SELECT user_id, SUM(total) AS spent FROM orders GROUP BY user_id
)
SELECT u.username, cte.spent
FROM users u JOIN cte ON cte.user_id = u.id;

-- AS NOT MATERIALIZED: inline the CTE (may run multiple times)
WITH cte AS NOT MATERIALIZED (
  SELECT COUNT(*) FROM users WHERE active = 1
)
SELECT * FROM products WHERE id < (SELECT * FROM cte);

-- choose MATERIALIZED when the CTE is expensive and used
-- multiple times; NOT MATERIALIZED when it's cheap or used
-- once and could benefit from predicate pushdown.

-- recursive CTEs are always materialized.
17

バックアップとリストア

.dump(テキストバックアップ)

プレーンな SQL の INSERT 文としてデータベースをエクスポートするのが、.dump です。人間可読で、diff 可能で、SQLite のバージョンとアーキテクチャをまたいで移植可能です。最も互換性のあるバックアップの形式が、これです。新鮮なデータベースへの SQL のリプレイが、リストアです。sqlite_sequence と他の内部テーブルをスキップするのが、--nosys です。大きなデータベースでは、遅く巨大なファイルを作るのが、.dump です。代わりに、.backup(バイナリコピー)か Online Backup API を。

sqlite
-- dump the entire database as SQL text (portable)
sqlite3 mydb.sqlite .dump > backup.sql

-- dump a single table
sqlite3 mydb.sqlite ".dump users" > users.sql

-- dump only data (no schema)
sqlite3 mydb.sqlite ".dump" | grep '^INSERT' > data.sql

-- restore from a dump
sqlite3 newdb.sqlite < backup.sql
-- or inside the CLI:
.read backup.sql

-- dump in a specific order to satisfy FK constraints
sqlite3 mydb.sqlite ".dump --nosys" > backup.sql

-- .dump works on attached databases too
ATTACH 'archive.sqlite' AS archive;
.dump archive

.backup(バイナリコピー)

SQLite Online Backup API を使い、安全なオンラインのバイナリコピーを行うのが、.backup です。書き手がアクティブでも機能し、トランザクション的に一貫したスナップショットを作ります。稼働中のデータベースをバックアップする推奨方法が、これです。有効な SQLite ファイルであるのが、出力なので、リストアはファイルのコピーにすぎません。.dump と違い、人間可読でないのが、バイナリ形式ですが、バージョンとアーキテクチャ互換です。

sqlite
-- binary copy to another file (online, safe with active writers)
sqlite3 mydb.sqlite ".backup 'backup.sqlite'"

-- inside the CLI
.backup backup.sqlite
.backup main backup.sqlite     -- source 'main', target file

-- backup to an attached database
ATTACH 'backup.sqlite' AS bk;
.backup main bk;
DETACH bk;

-- .backup uses the Online Backup API internally: it copies
-- page-by-page and handles concurrent writers safely, so it's
-- the right choice for live production backups.

-- restore = just copy the file back (or use it directly)
cp backup.sqlite mydb.sqlite

Online Backup API

ゴールドスタンダードのバックアップの方法であるのが、(sqlite3_backup_* の)Online Backup API です。ページごとにライブデータベースをコピーし、同時書き込みを正しく扱い、一貫したスナップショットを作ります。ラップするのが、(Python の)sqlite3.Connection.backup() と、(Node の better-sqlite3 の).backup() です。WAL モードで機能します。書き込みが進行中なら、壊れたバックアップを作り得るのが、生のファイルのコピーです — 停止したデータベース上でか、ファイルレベルのスナップショット付きでだけ、ファイルコピーを使いましょう。

sqlite
-- the sqlite3 CLI's .backup uses this C API; in Python:
import sqlite3
src = sqlite3.connect("mydb.sqlite")
dst = sqlite3.connect("backup.sqlite")
src.backup(dst)
dst.close()
src.close()

-- with progress callback
def progress(remaining, total):
    print(f"{total - remaining}/{total} pages copied")
src.backup(dst, pages=progress)

-- this is the safest backup method: it's online (doesn't block
-- writers), transactionally consistent, and works on WAL-mode
-- databases. Prefer it over raw file copy for production.

-- Node.js (better-sqlite3):
const backup = db.backup('backup.sqlite');
while (!backup.completed) backup.step(-1);
backup.delete();

データベースのアタッチ

名前付きのスキーマとして(main、temp、加えて attached)、最大 10 のデータベースファイルを 1 接続へ持ち込むのが、ATTACH DATABASE です。スキーマ修飾された名前で(main.users、archive.orders)、それらをまたいで、クエリ、結合、データのコピーができます。アーカイブ(古い行をアーカイブファイルへ動かす)、シャーディング、マージのワークフローの基礎が、これです。スキーマを閉じるがファイルを無傷に保つのが、DETACH です。WAL モードでアトミックなのが、クロスデータベースのトランザクションです。

sqlite
-- work with multiple DB files in one connection
ATTACH DATABASE 'archive.sqlite' AS archive;
ATTACH DATABASE 'lookup.sqlite' AS lookup;

-- query across databases
SELECT u.username, a.note
FROM main.users u
JOIN archive.old_users a ON u.id = a.id;

-- copy a table between databases
CREATE TABLE archive.users_copy AS
SELECT * FROM main.users;

-- move data: insert into one DB, delete from another
INSERT INTO archive.orders SELECT * FROM main.orders WHERE created < '2023-01-01';
DELETE FROM main.orders WHERE created < '2023-01-01';

-- list attached databases
.databases

-- detach (does NOT delete the file)
DETACH DATABASE archive;

-- limit: 10 attached databases (compile-time SQLITE_MAX_ATTACHED).

回復と破損

構造的な破損を検出するのが、integrity_check で、壊れたファイルからデータを救出する最良のツールであるのが(3.29+)、.recover です。読み取り可能な行を取り出し悪いページをスキップし、新鮮なデータベースへパイプできる SQL を作ります。.recover が失敗すれば、.dump へフォールバックし部分的な喪失を受け入れましょう。ほとんどの破損が、安全でないファイル操作から来ます(ライブ DB のコピー、ネットワークのファイルシステム、synchronous=OFF での停電)。回復の試みの前に、必ずバックアップしましょう。

sqlite
-- check integrity
PRAGMA integrity_check;
PRAGMA quick_check;

-- recover from a corrupt database (best effort)
sqlite3 corrupt.sqlite ".recover" > recovered.sql
sqlite3 clean.sqlite < recovered.sql

-- .recover extracts everything readable, skipping corrupt pages

-- if .recover fails, try dumping what you can
sqlite3 corrupt.sqlite ".dump" > partial.sql 2>errors.txt
sqlite3 clean.sqlite < partial.sql

-- point-in-time from a backup file
cp nightly_backup.sqlite mydb.sqlite

-- after a crash, WAL is replayed automatically on next open;
-- if stuck, checkpoint manually:
sqlite3 mydb.sqlite "PRAGMA wal_checkpoint(TRUNCATE);"

-- prevent corruption: never overwrite a live DB file,
-- never share a DB on a network filesystem without WAL off.
18

インポートとエクスポート

CSV のインポート

CSV をテーブルへ読み込むのが、.import です。テーブルが存在しなければ、SQLite が、すべて TEXT の列で作ります(しばしば望むものではありません — 適切な型付きで先にテーブルを作りましょう)。ヘッダーの行をスキップするのが、--skip N です。埋め込まれたカンマと改行付きの引用符付きフィールドを扱うよう、パーサーを設定するのが、.mode csv です。大きなインポートのためには、BEGIN/COMMIT で包み、(一時的に)PRAGMA synchronous=OFF とすれば、10 倍以上の高速化です。一括ロードの間インデックスを無効化し、後に再作成しましょう。

sqlite
-- import a CSV file into a table
.mode csv
.import users.csv users
-- ^ creates 'users' table with text columns named from the header row

-- import into an EXISTING table (column order must match)
.mode csv
.import --skip 1 users.csv users   -- --skip 1 to skip header

-- recommended: create the table first with proper types
CREATE TABLE users (id INTEGER, name TEXT, email TEXT);
.mode csv
.import --skip 1 users.csv users

-- from the shell, one-shot
sqlite3 mydb.sqlite ".mode csv" ".import users.csv users"

-- handle quotes/escapes
.mode csv
.separator ,
.import data.csv mytable

CSV のエクスポート

ヘッダー行付きの標準 CSV を作るのが、.headers on 付きの .mode csv で、.output でファイルへパイプしましょう。CLI に入らず 1 行でやるのが、シェルのフラグ -header -csv と、出力のリダイレクトです。TSV には、.separator "\t" 付きの .mode list を。大きなエクスポートには、ストリームベースでメモリ効率が良いのが、これです。複数のテーブルをエクスポートするには、クエリの間に .output を変えましょう。

sqlite
-- export a query to CSV
.mode csv
.headers on
.output users.csv
SELECT id, username, email FROM users;
.output stdout

-- from the shell, one-shot
sqlite3 mydb.sqlite -header -csv \
  "SELECT id, username, email FROM users" > users.csv

-- export all rows of a table
.mode csv
.headers on
.output products.csv
SELECT * FROM products;
.output stdout

-- custom separator
.mode list
.separator "|"
.output users.txt
SELECT id, username FROM users;
.output stdout

JSON のエクスポート

(ラップする配列なしで)行ごとに 1 つの JSON オブジェクトを出すのが、.mode json です。本物の JSON 配列のためには、json_group_array(json_object(...)) を使いましょう — これは、フィールド名とネストの完全な制御を与え、相関サブクエリ経由でネストした配列にも機能します。ORM なしで SQL から直接 JSON API を構築する強力な方法が、これです。大きな結果セットのためには、メモリに 1 つの巨大な配列を構築するのでなく、行ごとに .mode json をストリーミングしましょう。

sqlite
-- export a query as JSON (one JSON object per row)
.mode json
.output users.json
SELECT id, username, email FROM users;
.output stdout

-- build a single JSON array with json_group_array
SELECT json_group_array(json_object(
  'id', id, 'username', username, 'email', email
)) AS users_json
FROM users;
-- [{"id":1,"username":"alice","email":"[email protected]"},...]

-- nested JSON (orders with their items)
SELECT json_object(
  'order_id', o.id,
  'total', o.total,
  'items', (SELECT json_group_array(json_object('name', i.name, 'qty', i.qty))
            FROM order_items i WHERE i.order_id = o.id)
) AS order_json
FROM orders o;

JSON のインポート

JSON のための .import が SQLite になく、ファイルを(アプリコードか INSERT 付きの .read で)TEXT としてロードし、その後、json_each/json_extract でパースしましょう。セットベースの挿入のために、配列を行へ展開するのが、json_each(array) です。大きな JSON ファイルのためには、(ファイル全体をメモリへロードせず)アプリでストリームパースしましょう。ロード中の json_valid() による検証と、継続的な整合性のための JSON の列上の CHECK 制約を使いましょう。

sqlite
-- load a JSON file into a text column, then parse
CREATE TABLE raw (data TEXT);
-- (no built-in .import for JSON; load via CLI or app)

-- once loaded, parse with json_each (array) or json_extract (object)
SELECT json_extract(data, '$.name') AS name,
       json_extract(data, '$.age')  AS age
FROM raw;

-- expand a JSON array into rows
INSERT INTO users (username, email)
SELECT json_extract(value, '$.username'),
       json_extract(value, '$.email')
FROM raw, json_each(raw.data);

-- import via Python
import sqlite3, json
db = sqlite3.connect("mydb.sqlite")
with open("users.json") as f:
    for row in json.load(f):
        db.execute("INSERT INTO users(username,email) VALUES(?,?)",
                   (row["username"], row["email"]))
db.commit()

Excel とその他の形式

Excel のためには、CSV をエクスポートしましょう(Excel が直接開きます)。UTF-8 が正しく描画されるよう BOM を加えるのが、.mode excel(3.36+)です。ドキュメントのための貼り付け可能なテーブルのマークアップを作るのが、.mode markdown です。INSERT 文を生成するのが、.mode insert で — テストデータベースの播種や、スキーマ間のデータ移行に最適です。すべての値を引用するのが(カンマ/改行/引用符を含むデータが、他のツールを混乱させ得るときに有用)、.mode quote です。ターミナルのための ASCII テーブルを描くのが、.mode table/box です。

sqlite
-- Excel: export CSV (Excel opens it) or use .mode excel
.mode csv
.headers on
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode excel (3.36+): CSV with BOM, friendly to Excel
.mode excel
.output report.csv
SELECT * FROM monthly_report;
.output stdout

-- .mode markdown: GitHub-flavored markdown tables
.mode markdown
SELECT id, username, email FROM users LIMIT 5;

-- .mode insert: generate INSERT statements
.mode insert users
.output seed.sql
SELECT id, username, email FROM users;
.output stdout
-- produces: INSERT INTO "users" VALUES(1,'alice','[email protected]');

-- .mode quote: CSV with all values quoted
.mode quote
.output safe.csv
SELECT * FROM users;
.output stdout
19

アプリケーションの統合

Python(sqlite3)

標準ライブラリに組み込まれているのが、Python の sqlite3 です。SQL インジェクションを防ぎ引用を正しく扱うため、(f 文字列や % ではなく)? のプレースホルダー(か :name)を ALWAYS で使いましょう。一括挿入のためには、execute のループより、executemany がはるかに速いです。列名のアクセスのため、row_factory = sqlite3.Row を設定しましょう。自動のコミット/ロールバックのためには、(with conn: の)コンテキストマネージャーを使いましょう。同時実行のためには、スレッドごとに 1 接続を使いましょう(デフォルトで、スレッドをまたぐ共有を sqlite3 が禁じます)。

sqlite
import sqlite3

# connect (creates the file if missing)
conn = sqlite3.connect("mydb.sqlite")
conn.row_factory = sqlite3.Row  # dict-like rows
cur = conn.cursor()

# parameterized query (NEVER use string formatting!)
cur.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("alice", "[email protected]"),
)
conn.commit()

# query with parameters
cur.execute("SELECT id, username FROM users WHERE active = ?", (1,))
for row in cur:
    print(row["id"], row["username"])

# named parameters
cur.execute(
    "SELECT * FROM users WHERE age >= :min_age",
    {"min_age": 18},
)

# bulk insert (fast)
cur.executemany(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    [("bob", "[email protected]"), ("carol", "[email protected]")],
)
conn.commit()
conn.close()

Node.js(better-sqlite3)

同期であり(コールバック/Promise のボイラープレートなし)、最速の Node.js の SQLite ドライバーであるのが、better-sqlite3 です。再利用されるのが、準備されたステートメントです。一度 prepare し、何度も実行しましょう。関数を BEGIN/COMMIT(投げられたら ROLLBACK)で包むのが、.transaction(fn) で、ステートメントごとの自動コミットより劇的に速いです。SQL インジェクションを避けるには、? のプレースホルダーを。同時実行のためには、WAL を有効化しましょう。Promise ベースの API のためには、呼び出しを包むか、sqlite/sqlite3 を代わりに使いましょう。

sqlite
const Database = require("better-sqlite3");
const db = new Database("mydb.sqlite");
db.pragma("journal_mode = WAL");

// prepared statement (synchronous, fast)
const insert = db.prepare(
  "INSERT INTO users (username, email) VALUES (?, ?)"
);
const info = insert.run("alice", "[email protected]");
console.log(info.lastInsertRowid);

// query multiple rows
const select = db.prepare("SELECT id, username FROM users WHERE active = ?");
const rows = select.all(1);  // array of objects

// query one row
const one = select.get(1);

// transaction (atomic, fast)
const insertMany = db.transaction((users) => {
  for (const u of users) insert.run(u.username, u.email);
});
insertMany([{username: "bob"}, {username: "carol"}]);

db.close();

接続 URI とモード

読み取り専用、共有キャッシュ、インメモリモードの扉を開くのが、(file:... の)URI のファイル名です。Python では uri=True を、Node のドライバーには file: の文字列を渡しましょう。デフォルトで接続ごとであるのが、:memory: で、同じプロセス内の接続をまたいで共有される、名前付きのインメモリ DB を作るのが、file:name?mode=memory&cache=shared です(テストに最適です)。ロック上で短く待つ writer が、即座にエラーを出さないよう、busy_timeout を設定しましょう。接続ごとに、PRAGMA foreign_keys = ON を必ず設定しましょう。

sqlite
# open read-only
sqlite3.connect("file:mydb.sqlite?mode=ro", uri=True)

# open in-memory (vanishes on close)
sqlite3.connect(":memory:")
# or via URI:
sqlite3.connect("file::memory:", uri=True)

# shared in-memory database (visible across connections in same process)
sqlite3.connect("file:memdb1?mode=memory&cache=shared", uri=True)

# with a busy timeout (wait instead of erroring on locks)
conn = sqlite3.connect("mydb.sqlite", timeout=30)

# Node.js: same URI form
const db = new Database("file:mydb.sqlite?mode=ro", { readonly: true });

# Node.js in-memory
const mem = new Database(":memory:");

# Python: per-connection pragmas
conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA foreign_keys = ON")

トランザクションと同時実行

アトミックにステートメントをグループ化するのが(すべてか無し)、トランザクションです。一度に単一の writer を許すのが(データベースレベルの書き込みロック)、SQLite ですが、同時の読み手を WAL モードが許します。暗黙のトランザクションで DML を包むのが、(Python の)デフォルトの isolation_level です(コミットが要ります)。明示的な制御のためには、isolation_level = None を設定し、自分で BEGIN/COMMIT を発行しましょう。ロック上で短く待つ writer が、即座に SQLITE_BUSY を投げるのではなく待つよう、busy_timeout を(PRAGMA busy_timeout か接続のタイムアウト)使いましょう。

sqlite
# Python: explicit transaction
conn = sqlite3.connect("mydb.sqlite")
conn.isolation_level = None  # autocommit off -> manual BEGIN
cur = conn.cursor()
cur.execute("BEGIN")
try:
    cur.execute("UPDATE accounts SET bal = bal - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET bal = bal + 100 WHERE id = 2")
    cur.execute("COMMIT")
except Exception:
    cur.execute("ROLLBACK")
    raise

// Node.js (better-sqlite3) transaction
const transfer = db.transaction((from, to, amt) => {
  db.prepare("UPDATE accounts SET bal = bal - ? WHERE id = ?").run(amt, from);
  db.prepare("UPDATE accounts SET bal = bal + ? WHERE id = ?").run(amt, to);
});
transfer(1, 2, 100);

-- SQLite allows ONE writer at a time; many readers in WAL mode.
-- Use a busy_timeout to wait for locks instead of failing fast.

準備されたステートメントと安全

最重要の単一のセキュリティの実践であるのが、パラメータ化されたクエリ(? のプレースホルダー)です。SQL インジェクションを防ぎ、型の変換/エスケープを正しく扱います。SQL 文字列へユーザー入力を決して補間しないこと。値のためだけに機能するのが、プレースホルダーです。パラメータ化できないのが、識別子(テーブル/列の名前)であり — ホワイトリストに対して検証しましょう。LIKE のためには、ユーザーがワイルドカードを注入できないよう、ユーザー入力内の % と _ をエスケープしましょう。すべての言語とドライバーで適用されるのが、これです。

sqlite
-- SQL injection vulnerability (NEVER do this):
-- app: "SELECT * FROM users WHERE name = '" + user_input + "'"
-- user_input = "'; DROP TABLE users; --"
-- result:    SELECT * FROM users WHERE name = ''; DROP TABLE users; --'

-- SAFE: parameterized query (placeholders are NOT string substitution)
-- Python:
cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))
-- Node.js:
stmt.get(user_input);

-- identifiers (table/column names) CANNOT be parameterized;
-- validate against a whitelist:
allowed_tables = {"users", "orders", "products"}
if table not in allowed_tables:
    raise ValueError("bad table")
cur.execute(f"SELECT * FROM {table} WHERE id = ?", (id_,))

-- LIKE with user input: escape wildcards
cur.execute(
    "SELECT * FROM users WHERE name LIKE ? ESCAPE '\'",
    (user_input.replace("\", "\\").replace("%", "\%").replace("_", "\_") + "%",)
)
20

パフォーマンスの最適化

EXPLAIN QUERY PLAN

最重要のチューニングのツールであるのが、EXPLAIN QUERY PLAN です。「USING INDEX」 = 良い。「SCAN」(フルテーブルスキャン)= 大きいテーブルでは通常悪いです。結合のためには、結合列がインデックスされていること、プランナーがより大きいテーブルをスキャンしていないことを確認しましょう。VDBE のバイトコードを示すのが(QUERY PLAN なしの)EXPLAIN で、めったに不要ですが、深いデバッグに有用です。インデックスを提案し得るのが、実験的な EXPERT のコマンド(3.36+)で、か .expert の CLI ドットコマンドです。

sqlite
-- see how SQLite executes a query (essential for tuning)
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = '[email protected]';

-- good: uses an index
--   SEARCH users USING INDEX idx_users_email (email=?)

-- bad: full table scan
--   SCAN users

-- join plan
EXPLAIN QUERY PLAN
SELECT u.username, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = 1;

-- automated index recommendation (3.36+, experimental)
SELECT * FROM users WHERE email = 'x';
-- then:
EXPLAIN QUERY PLAN SELECT * FROM sqlite_stat1;

-- lower-level: EXPLAIN (bytecode)
EXPLAIN SELECT * FROM users WHERE id = 1;

VACUUM と ANALYZE

ファイルを再構築し、削除からスペースを取り戻しページをデフラグするのが、VACUUM です。大きな削除の後や、書き込みが重いデータベースのために定期的に実行しましょう。長くソースをロックせず、きれいなコピーを書くのが、VACUUM INTO(3.27+)です。プランナーがインデックスを選ぶのに使う、sqlite_stat1 の統計を更新するのが、ANALYZE です。一括ロードやスキーマ/データの変化の後に実行しましょう。フルの VACUUM なしで、増分的にスペースを取り戻すのが、auto_vacuum=INCREMENTAL です。

sqlite
-- VACUUM: rebuild the database file, reclaiming free space
VACUUM;
-- after large deletes, this shrinks the file and defrags pages

-- VACUUM INTO: write a defragmented copy to a new file (3.27+)
VACUUM INTO 'clean.sqlite';

-- incremental VACUUM (only with auto_vacuum enabled)
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- free up to 100 pages

-- ANALYZE: update planner statistics for better query plans
ANALYZE;                          -- all tables
ANALYZE users;                    -- one table
ANALYZE users(idx_users_email);   -- one index

-- run ANALYZE after bulk loads or major data changes

一括ローディング

一括ロードの方法:単一のトランザクションで包むこと(最大の単一の高速化です。行ごとの自動コミットが、破滅的に遅いです)、一時的に同期とジャーナリングを無効化、インデックスの削除と再作成、PRAGMA cache_size の増加の検討、です。INSERT 文より速いのが、.import のドットコマンドです。本物に巨大なロードのためには、事前構築されたデータベースの .backup を考えましょう。本番データのロード後は、安全な PRAGMA を必ず復元しましょう。

sqlite
-- fastest bulk load into a fresh table
PRAGMA synchronous = OFF;     -- risk: power-loss corruption (use only for one-off loads)
PRAGMA journal_mode = MEMORY;
PRAGMA temp_store = MEMORY;

BEGIN;
CREATE TABLE big (id INTEGER, data TEXT);
.import data.csv big
COMMIT;

-- then turn safety back on
PRAGMA synchronous = FULL;
PRAGMA journal_mode = WAL;

-- faster still: drop indexes, load, recreate
DROP INDEX idx_big_data;
-- ... load ...
CREATE INDEX idx_big_data ON big(data);

-- use a transaction (10-100x faster than autocommit per row)
BEGIN;
INSERT INTO big VALUES (1, 'a');
-- ... thousands of inserts ...
COMMIT;

FTS5(全文検索)

SQLite の全文検索エンジンであるのが、FTS5 です。速い MATCH クエリのためテキストをインデックスし、すべての行をスキャンする LIKE '%term%' よりはるかに速いです。boolean(AND/OR/NOT)、接頭辞(term*)、フレーズ("...")、関連度によるランキングをサポートします。データの複製を避けるのが、(content='posts' の)外部コンテンツのパターンです。トリガーで(基底テーブル上の INSERT/UPDATE/DELETE)、FTS のインデックスを同期に保ちましょう。入力しながらの検索、オートコンプリート、文書検索に理想的なのが、FTS5 です。

sqlite
-- create a full-text search virtual table
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title, body,
  content='posts', content_rowid='id'
);

-- populate it (keep in sync with triggers or rebuild)
INSERT INTO posts_fts(rowid, title, body)
  SELECT id, title, body FROM posts;

-- search with MATCH (much faster than LIKE '%term%')
SELECT p.* FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite python';

-- ranking by relevance
SELECT p.title, rank
FROM posts p
JOIN posts_fts f ON f.rowid = p.id
WHERE posts_fts MATCH 'sqlite OR python'
ORDER BY rank;

-- prefix and phrase queries
SELECT * FROM posts_fts WHERE posts_fts MATCH 'sql*';        -- prefix
SELECT * FROM posts_fts WHERE posts_fts MATCH '"full text"'; -- exact phrase

よくある落とし穴とチューニング

少数の原因から来るのが、ほとんどの SQLite のパフォーマンスの問題です:WHERE/JOIN/ORDER BY の列でのインデックスの欠落、(トランザクションの欠落。行ごとの自動コミットが、遅い一括挿入の第 1 の原因です)、静かにオフの外部キー、幅広のテーブル上の SELECT * です。すべての接続のセットアップで、WAL と外部キーを有効化しましょう。長い書き込みのロックと巨大な一時スペースを避けるため、大きな DELETE をバッチ化しましょう。プランナーが良い計画を選ぶよう、重大なデータ変化の後に ANALYZE を実行しましょう。

sqlite
-- 1. Enable foreign keys (OFF by default!)
PRAGMA foreign_keys = ON;

-- 2. Use WAL for concurrency
PRAGMA journal_mode = WAL;

-- 3. Index foreign keys AND join columns
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 4. Avoid SELECT * (more I/O, breaks on schema change)
SELECT id, username FROM users;

-- 5. Use transactions for multi-statement writes
BEGIN;
-- ... several inserts/updates ...
COMMIT;

-- 6. Don't index every column (slows writes)
-- index selectively on WHERE/JOIN/ORDER BY columns.

-- 7. Use LIMIT on exploratory queries
SELECT * FROM big_table LIMIT 10;

-- 8. Batch UPDATEs/DELETEs to avoid long locks
DELETE FROM logs WHERE id IN (
  SELECT id FROM logs WHERE ts < '2023-01-01' LIMIT 10000
);

-- 9. Prefer EXISTS over IN for large subquery results
-- 10. Run ANALYZE after big data changes.

Was this helpful?