運用と注意点

SQL Injection 対策、可読性、テスト、書き方の流儀、計測、典型アンチパターン。

SQL Injection 対策

絶対 NG

// ユーザ入力を文字列連結
const sql = `SELECT * FROM users WHERE email = '${email}'`

OK: プレースホルダ

// pg
client.query("SELECT * FROM users WHERE email = $1", [email])

// postgres.js（テンプレートリテラル）
sql`SELECT * FROM users WHERE email = ${email}`

// SQLite (better-sqlite3)
db.prepare("SELECT * FROM users WHERE email = ?").get(email)

// Prisma など ORM 経由は基本安全
prisma.user.findUnique({ where: { email } })

動的な識別子（テーブル名 / 列名）

プレースホルダは値専用。識別子を動的にしたい場合は厳密にホワイトリストで:

const allowed = ["created_at", "name", "email"]
if (!allowed.includes(orderBy)) throw new Error("invalid")
sql.unsafe(`SELECT * FROM users ORDER BY ${orderBy}`)

クエリの書き方（流儀）

• キーワード（SELECT / FROM）は大文字
• テーブル名は複数形 snake_case
• 長い句は改行して見やすく
• サブクエリ / CTE で段階分解
• 「すべてを 1 行」は禁物

SELECT
  p.id,
  p.title,
  u.email AS author
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.status = 'published'
  AND p.created_at >= NOW() - INTERVAL '7 days'
ORDER BY p.created_at DESC
LIMIT 20;

命名のルール

• テーブル: users / blog_posts（複数形 snake_case）
• 列: created_at / user_id
• 制約: users_email_uniq / posts_user_id_fkey
• インデックス: posts_user_idx
• 真偽: is_active / has_avatar

SQL のテスト

• テスト用 DB を別途用意（開発と分離）
• 各テスト前に TRUNCATE + seed
• pgTap: SQL レベルの単体テスト
• SQLancer: ファジングで挙動検証
• 本物の DB に対するテストの方が信頼性高い

pgTap の例

BEGIN;
SELECT plan(2);

INSERT INTO users (email) VALUES ('a@ex.com');
SELECT is(count(*)::int, 1, 'one user inserted') FROM users;

INSERT INTO users (email) VALUES ('a@ex.com');  -- 重複
-- ↑ ROLLBACK されるべき → throws_ok

SELECT * FROM finish();
ROLLBACK;

計測の習慣

• EXPLAIN ANALYZE を読めるように
• pg_stat_statements でスロークエリ抽出
• 本番に近いデータ量で計測（10 行のテストでは何も分からない）
• クエリログ + APM（Datadog / Sentry）

典型アンチパターン

1. SELECT *

必要な列だけ。列追加で挙動が変わる、転送量増、Index Only Scan の機会喪失。

2. ループの中でクエリ（N+1）

JOIN / IN にまとめる。

3. WHERE で関数呼び出し

インデックスが効かなくなる。関数式インデックスにする。

4. NOT IN + NULL

NULL が混じると常に空。NOT EXISTS に書き換え。

5. OFFSET 100000

後ろのページほど遅い。カーソル式に。

6. 巨大 IN

IN (1000 個) は遅い。= ANY(VALUES (...)) や JOIN に。

7. 再帰なしのツリー走査

アプリで何度もクエリ → 再帰 CTE で 1 回。

8. 集計しないで count(*) を毎回

頻出ならカウンタ列 / マテビュー / キャッシュ。

SQL のスタイルガイド

• カンマは行頭 or 行末を統一（チームで揃える）
• JOIN ごとに 1 行
• WHERE の AND は新しい行
• サブクエリは内部もインデント
• linter: SQLFluff / EditorConfig

SQL のフォーマッタ

• SQLFluff: 静的解析 + フォーマット
• pg_format: pgFormatter
• VS Code: Prisma / SQL extension

典型レシピ集

「Top N per group」

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY views DESC) AS rn
  FROM posts
) t WHERE rn <= 3;

「Distinct on (Postgres)」

SELECT DISTINCT ON (user_id) *
FROM posts
ORDER BY user_id, created_at DESC;

「カーソル式ページング」

SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)   -- 前回の最終値
ORDER BY created_at DESC, id DESC
LIMIT 20;

「期間をまたぐ集計」

SELECT
  date_trunc('day', generated.day) AS day,
  COALESCE(stats.count, 0) AS posts
FROM generate_series(
  CURRENT_DATE - INTERVAL '30 days',
  CURRENT_DATE,
  '1 day'
) AS generated(day)
LEFT JOIN (
  SELECT date_trunc('day', created_at) AS day, count(*) FROM posts GROUP BY 1
) stats ON stats.day = generated.day
ORDER BY day;

SQL を学び続けるリソース

• SQLBolt — 入門
• PostgreSQL Exercises
• Use The Index, Luke! — インデックス
• 「SQL 達人への道」「SQL アンチパターン」（書籍）
• Postgres 公式 Docs

本セクションの総まとめ

1. SELECT / WHERE / ORDER / LIMIT を確実に
2. JOIN を図で理解する
3. GROUP BY と集計関数
4. Window 関数で世界が広がる
5. CTE で段階分解
6. JSON / 配列 / 全文検索などの応用
7. EXPLAIN を読めるように
8. SQL Injection / NULL の罠を回避