sql-database-assistant

SQL データベースアシスタント - POWERFUL Tier スキル

概要

データベース設計を運用面から支援します。database-designer がスキーマアーキテクチャに重点を置き、database-schema-designer が ERD モデリングを担当するのに対し、このスキルは日々の作業をカバーします。クエリの作成、パフォーマンスの最適化、マイグレーションの生成、アプリケーションコードとデータベースエンジンの橋渡しを行います。

主な機能

• 自然言語から SQL への変換 — 要件を正確で高性能なクエリに変換します
• スキーマの調査 — PostgreSQL、MySQL、SQLite、SQL Server の稼働中のデータベースをイントロスペクションします
• クエリの最適化 — EXPLAIN 分析、インデックスの推奨、N+1 の検出、書き換えパターン
• マイグレーションの生成 — up/down スクリプト、ダウンタイムゼロの戦略、ロールバック計画
• ORM 連携 — Prisma、Drizzle、TypeORM、SQLAlchemy のパターンと代替手段
• マルチデータベース対応 — 互換性に関するガイダンスを備えた、方言を考慮した SQL

ツール

スクリプト	目的
scripts/query_optimizer.py	パフォーマンス上の問題を検出する SQL クエリの静的解析
scripts/migration_generator.py	変更内容の説明からマイグレーションファイルのテンプレートを生成
scripts/schema_explorer.py	イントロスペクションクエリからスキーマドキュメントを生成

---

自然言語から SQL への変換

変換パターン

要件を SQL に変換する際は、次の手順に従います。

1. エンティティを特定する — 名詞をテーブルに対応付けます
2. リレーションシップを特定する — 動詞を JOIN またはサブクエリに対応付けます
3. フィルターを特定する — 形容詞や条件を WHERE 句に対応付けます
4. 集計を特定する — 「合計」「平均」「件数」を GROUP BY に対応付けます
5. 並べ替えを特定する — 「上位」「最新」「最大」を ORDER BY + LIMIT に対応付けます

よく使われるクエリテンプレート

グループごとの上位 N 件（ウィンドウ関数）

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
) ranked WHERE rn <= 3;

累計

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM transactions;

欠落の検出

SELECT curr.id, curr.seq_num, prev.seq_num AS prev_seq
FROM records curr
LEFT JOIN records prev ON prev.seq_num = curr.seq_num - 1
WHERE prev.id IS NULL AND curr.seq_num > 1;

UPSERT（PostgreSQL）

INSERT INTO settings (key, value, updated_at)
VALUES ('theme', 'dark', NOW())
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at;

UPSERT（MySQL）

INSERT INTO settings (key_name, value, updated_at)
VALUES ('theme', 'dark', NOW())
ON DUPLICATE KEY UPDATE value = VALUES(value), updated_at = VALUES(updated_at);

JOIN、CTE、ウィンドウ関数、JSON 操作などについては references/query_patterns.md を参照してください。

---

スキーマの調査

イントロスペクションクエリ

PostgreSQL — テーブルと列の一覧

SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

PostgreSQL — 外部キー

SELECT tc.table_name, kcu.column_name,
  ccu.table_name AS foreign_table, ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

MySQL — テーブルサイズ

SELECT table_name, table_rows,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length DESC;

SQLite — スキーマダンプ

SELECT name, sql FROM sqlite_master WHERE type = 'table' ORDER BY name;

SQL Server — 型付きの列

SELECT t.name AS table_name, c.name AS column_name,
  ty.name AS data_type, c.max_length, c.is_nullable
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
ORDER BY t.name, c.column_id;

スキーマからのドキュメント生成

scripts/schema_explorer.py を使って、Markdown または JSON 形式のドキュメントを生成します。

python scripts/schema_explorer.py --dialect postgres --tables all --format md
python scripts/schema_explorer.py --dialect mysql --tables users,orders --format json --json

---

クエリの最適化

EXPLAIN 分析のワークフロー

1. EXPLAIN ANALYZE（PostgreSQL）または EXPLAIN FORMAT=JSON（MySQL）を実行します
2. 最もコストの高いノードを特定します — 大規模テーブルでの Seq Scan、行数の推定値が大きい Nested Loop など
3. 不足しているインデックスを確認します — フィルター対象の列に対するシーケンシャルスキャン
4. 推定誤差を確認します — 計画時と実際の行数の乖離は、統計情報が古いことを示します
5. JOIN の順序を評価します — 最小の結果セットが JOIN を駆動するようにします

インデックス推奨チェックリスト

• 選択性の高い WHERE 句内の列
• JOIN 条件内の列（外部キー）
• LIMIT と組み合わせる ORDER BY 内の列
• 複数列の WHERE 条件に一致する複合インデックス（選択性の高い列を先頭にする）
• 定数によるフィルターを含むクエリ向けの部分インデックス（例：WHERE status = 'active'）
• 読み取りの多いクエリでテーブル検索を避けるためのカバリングインデックス

クエリの書き換えパターン

アンチパターン	書き換え
SELECT * FROM orders	SELECT id, status, total FROM orders（列を明示）
WHERE YEAR(created_at) = 2025	WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'（SARGable）
SELECT 内の相関サブクエリ	集計を伴う LEFT JOIN
NULL を含む NOT IN (SELECT ...)	NOT EXISTS (SELECT 1 ...)
不要な重複排除を行う UNION	UNION ALL
LIKE '%search%'	全文検索インデックス（GIN/FULLTEXT）
ORDER BY RAND()	アプリケーション側でのランダムサンプリング、または TABLESAMPLE

N+1 検出

症状：

• 親行ごとに 1 件のクエリを実行するアプリケーションループ
• ループ内で ORM が関連エンティティを遅延読み込みする
• クエリログに、ID だけが異なる同一パターンの SELECT が数百件表示される

修正方法：

• イーガーローディングを使用する（Prisma の include、SQLAlchemy の joinedload）
• WHERE id IN (...) でクエリをバッチ処理する
• GraphQL リゾルバーには DataLoader パターンを使用する

静的解析ツール

python scripts/query_optimizer.py --query "SELECT * FROM orders WHERE status = 'pending'" --dialect postgres
python scripts/query_optimizer.py --query queries.sql --dialect mysql --json

EXPLAIN プランの読み方、インデックスの種類、コネクションプーリングについては、references/optimization_guide.md を参照してください。

---

マイグレーションの生成

ダウンタイムゼロのマイグレーションパターン

カラムの追加（安全）

-- Up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Down
ALTER TABLE users DROP COLUMN phone;

カラム名の変更（拡張・縮小）

-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- Step 2: Backfill
UPDATE users SET full_name = name;
-- Step 3: Deploy app reading both columns
-- Step 4: Deploy app writing only new column
-- Step 5: Drop old column
ALTER TABLE users DROP COLUMN name;

NOT NULL カラムの追加（安全な手順）

-- Step 1: Add nullable
ALTER TABLE orders ADD COLUMN region VARCHAR(50);
-- Step 2: Backfill with default
UPDATE orders SET region = 'unknown' WHERE region IS NULL;
-- Step 3: Add constraint
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders ALTER COLUMN region SET DEFAULT 'unknown';

インデックスの作成（ブロックなし、PostgreSQL）

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

データのバックフィル戦略

• バッチ更新 — ロック競合を避けるため、1000〜10000 行ずつ処理します
• バックグラウンドジョブ — 進捗を追跡しながら、バックフィルを非同期で実行します
• デュアルライト — 移行期間中、旧カラムと新カラムの両方に書き込みます
• 検証クエリ — 各バッチの後に、行数とデータの整合性を検証します

ロールバック戦略

すべてのマイグレーションには、元に戻せる down スクリプトが必要です。元に戻せない変更の場合：

1. 実行前にバックアップする — 影響を受けるテーブルを pg_dump でダンプします
2. フィーチャーフラグ — アプリケーションで旧スキーマと新スキーマの読み取りを切り替えられるようにします
3. シャドーテーブル — マイグレーション期間中、元のテーブルのコピーを保持します

マイグレーション生成ツール

python scripts/migration_generator.py --change "add email_verified boolean to users" --dialect postgres --format sql
python scripts/migration_generator.py --change "rename column name to full_name in customers" --dialect mysql --format alembic --json

---

複数データベースのサポート

方言の違い

機能	PostgreSQL	MySQL	SQLite	SQL Server
UPSERT	ON CONFLICT DO UPDATE	ON DUPLICATE KEY UPDATE	ON CONFLICT DO UPDATE	MERGE
ブール値	ネイティブの BOOLEAN	TINYINT(1)	INTEGER	BIT
自動インクリメント	SERIAL / GENERATED	AUTO_INCREMENT	INTEGER PRIMARY KEY	IDENTITY
JSON	JSONB（インデックス付き）	JSON	テキスト（拡張機能）	NVARCHAR(MAX)
配列	ネイティブの ARRAY	未サポート	未サポート	未サポート
CTE（再帰）	完全サポート	8.0+	3.8.3+	完全サポート
ウィンドウ関数	完全サポート	8.0+	3.25.0+	完全サポート
全文検索	tsvector + GIN	FULLTEXT インデックス	FTS5 拡張機能	全文検索カタログ
LIMIT/OFFSET	LIMIT n OFFSET m	LIMIT n OFFSET m	LIMIT n OFFSET m	OFFSET m ROWS FETCH NEXT n ROWS ONLY

互換性に関するヒント

• 常にパラメーター化クエリを使用する — すべての方言で SQL インジェクションを防止できます
• 共有コードでは方言固有の関数を避ける — アダプターレイヤーでラップします
• 対象エンジンでマイグレーションをテストする — information_schema はエンジンによって異なります
• ISO 日付形式を使用する — 'YYYY-MM-DD' はどこでも機能します
• 識別子を引用符で囲む — ダブルクォート（SQL 標準）またはバッククォート（MySQL）を使用します

---

ORM パターン

Prisma

スキーマ定義

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int
}

マイグレーション：npx prisma migrate dev --name add_user_email クエリ API：prisma.user.findMany({ where: { email: { contains: '@' } }, include: { posts: true } }) 生 SQL のエスケープハッチ：prisma.$queryRaw\SELECT * FROM users WHERE id = ${userId}``

Drizzle

スキーマファーストの定義

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow(),
});

クエリビルダー：db.select().from(users).where(eq(users.email, email)) マイグレーション：npx drizzle-kit generate:pg を実行し、続けて npx drizzle-kit push:pg を実行します

TypeORM

エンティティデコレーター

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  email: string;

  @OneToMany(() => Post, post => post.author)
  posts: Post[];
}

リポジトリパターン：userRepo.find({ where: { email }, relations: ['posts'] }) マイグレーション：npx typeorm migration:generate -n AddUserEmail

SQLAlchemy

宣言的モデル

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False)
    name = Column(String(255))
    posts = relationship('Post', back_populates='author')

セッション管理：常に with Session() as session: コンテキストマネージャーを使用します Alembic マイグレーション：alembic revision --autogenerate -m "add user email"

各 ORM の比較とマイグレーションのワークフローについては、references/orm_patterns.md を参照してください。

---

データ整合性

制約の設計方針

• 主キー — すべてのテーブルに必要です。サロゲートキー（serial／UUID）を推奨します
• 外部キー — 参照整合性を確保し、ON DELETE の動作を明示的に定義します
• UNIQUE 制約 — ビジネス上の一意性（メールアドレス、slug、API キー）に使用します
• CHECK 制約 — 範囲、列挙値、ビジネスルールをデータベースレベルで検証します
• NOT NULL — デフォルトで NOT NULL にし、本当に任意の場合にのみ NULL を許可します

トランザクション分離レベル

レベル	ダーティリード	ノンリピータブルリード	ファントムリード	用途
READ UNCOMMITTED	あり	あり	あり	推奨されません
READ COMMITTED	なし	あり	あり	PostgreSQL のデフォルト、一般的な OLTP
REPEATABLE READ	なし	なし	あり（InnoDB：なし）	財務計算
SERIALIZABLE	なし	なし	なし	重要な整合性（請求、在庫管理）

デッドロックの防止

1. 一貫したロック順序 — 常に同じテーブル／行の順序でロックを取得します
2. 短いトランザクション — 最初のロック取得からコミットまでの時間を最小限に抑えます
3. アドバイザリーロック — アプリケーションレベルの調整に pg_advisory_lock() を使用します
4. リトライロジック — デッドロックエラーを捕捉し、指数バックオフで再試行します

---

バックアップと復元

PostgreSQL

# Full backup
pg_dump -Fc --no-owner dbname > backup.dump
# Restore
pg_restore -d dbname --clean --no-owner backup.dump
# Point-in-time recovery: configure WAL archiving + restore_command

MySQL

# Full backup
mysqldump --single-transaction --routines --triggers dbname > backup.sql
# Restore
mysql dbname < backup.sql
# Binary log for PITR: mysqlbinlog --start-datetime="2025-01-01 00:00:00" binlog.000001

SQLite

# Backup (safe with concurrent reads)
sqlite3 dbname ".backup backup.db"

バックアップのベストプラクティス

• 自動化 — cron または systemd timer を使用し、手動のみの運用は避けます
• 復元をテスト — テストされていないバックアップはバックアップではありません
• オフサイトコピー — S3、GCS、または別のリージョンに保存します
• 保持ポリシー — 日次は7日間、週次は4週間、月次は12か月間保持します
• バックアップのサイズと所要時間を監視 — 急激な変化は問題の兆候です

---

アンチパターン

アンチパターン	問題	対策
SELECT *	不要なデータを転送し、スキーマ変更時に問題が発生します	列を明示的に指定します
外部キー列にインデックスがない	JOIN と連鎖削除が遅くなります	すべての外部キーにインデックスを追加します
N+1 クエリ	データベースとの往復が 1 + N 回発生します	Eager loading またはバッチクエリを使用します
暗黙的な型変換	WHERE id = '123' によりインデックスが使用されません	条件式で型を一致させます
コネクションプーリングがない	負荷時にコネクションを使い果たします	PgBouncer、ProxySQL、または ORM のプールを使用します
上限のないクエリ	LIMIT がないと、数百万行を返すおそれがあります	常にページネーションを使用します
金額を FLOAT で保存する	丸め誤差が発生します	DECIMAL(19,4) またはセント単位の整数を使用します
巨大なテーブル	1つのテーブルに50列以上あります	正規化するか、垂直分割を使用します
あらゆる場所での論理削除	すべてのクエリに WHERE deleted_at IS NULL が必要になり、複雑になります	アーカイブテーブルまたはイベントソーシングを使用します
文字列の直接連結	SQL インジェクションにつながります	常にパラメーター化クエリを使用します

---

関連項目

スキル	関連性
database-designer	スキーマアーキテクチャ、正規化分析、ERD の生成
database-schema-designer	ビジュアル ERD モデリング、リレーションシップのマッピング
migration-architect	複雑な複数ステップのマイグレーションのオーケストレーション
api-design-reviewer	API エンドポイントとクエリパターンの整合性の確保
observability-platform	クエリパフォーマンスの監視、スロークエリアラート