はじめに 👋

みなさん、運用などで使用するSQLの保守って大変じゃないですか？

プロダクトコードであれば、ユニットテストなどを作って保守するところですが、手で叩くSQLのテストを作る優先度は落ちがちで叩く際に初めてSQLが壊れていることに気付きがちじゃないでしょうか。非エンジニアであれば余計に気付きづらいです。
そこで今回は、GitHub ActionsとGoogle Gemini AIを組み合わせて、SQLレビューを自動化するプロトタイプを試作してみました！

実現したいこと 🎯

PRでSQLファイルが変更されたら自動でレビュー
文法エラーや参照整合性の問題を検出
パフォーマンス上の懸念点を指摘
スキーマ変更時は既存SQLへの影響も確認

これらを実現することで、SQLレビューの効率化と品質向上を目指します。

前提

/db/checker.sql というテーブルスキーマを記述しているSQLがコミットされている
/sql/*.sql にSQLファイルをコミットしていく運用

システム概要 🔍

PRでSQLファイルが変更される
GitHub Actionsが起動
Gemini AIがSQLをレビュー
結果をPRにコメント

この流れで、SQLの変更が自動的にレビューされ、即座にフィードバックが得られます。

実装のポイント 💡

1. レビュー結果を3段階（OK/Warning/Critical）で判定

レビュー結果は以下の3段階で評価します。

OK: 問題なし
Warning: パフォーマンスや保守性に改善の余地あり
Critical: 文法エラーや実行不可能な重大な問題あり

2. スキーマ変更の検出

スキーマ定義ファイルが変更されたかを検出し、必要に応じて既存のSQLにも影響がないかを確認します。

3. 使用するLLM

弊社ではChatGPTのTeamプランに加入しているので、GPT4oのAPIでもよいのですが、gemini-2.0-flash-expが2024年12月現在無料とのことなので、そちらを使います...。この性能が無料って一体全体どうなっているんでしょうか。ただ無料枠は学習に使用されるそうなので、ご注意ください。従量課金の場合はされないそうです。

3. プロンプト

以下のプロンプトをリクエストでGeminiのAPIに送ります。


# MySQL SQL Review Prompt

## レビュー概要
MySQLの専門家として、以下のSQLをレビューし、問題の重要度を判定してください。
- サーバーバージョン: ${MYSQL_VERSION}
- レビュー対象: SQLファイルの変更
- スキーマ変更: ${SCHEMA_CHANGED}
- 目的: SQL品質の確保
  
## 対象

### スキーマ定義
${CHECKER_SQL_CONTENT}

### 変更・追加SQL
${NEW_SQL_CONTENT}

### 既に存在するSQL(スキーマ変更時のみチェック)
${EXISTING_SQL_CONTENT}

## レビュー方針
1. 問題の重要度を3段階で判定
   - 🔴 Critical: 以下のいずれかに該当する場合。
     - SQL文法エラーにより実行不可能
     - 存在しないテーブルやカラムの参照
     - スキーマ変更時に既存SQLが動作しなくなる破壊的変更
   - 🟡 Warning: 以下のような改善推奨事項
     - パフォーマンス上の問題（インデックス未使用など）
     - N+1クエリの可能性
     - より良い代替手段が存在
   - 🟢 OK: 以下の全てを満たす場合
     - SQL文が文法的に正しい
     - 全てのテーブル・カラム参照が有効
     - 重大なパフォーマンス問題がない

2. 変数とプレースホルダーの扱い
   - @や:で始まる変数は定義済みとして扱う
   - プレースホルダーの値は考慮せず、構文のみ確認

3. 重要度Criticalに該当するかを最重要でチェックしてください。

4. パフォーマンスに影響があっても実行自体が可能な場合はCriticalではありません。Warningで報告してください。軽微だと考えられる場合はOKとしてください。

5. 以下のいずれかの形式で報告:

# 🟢 Check Result: OK

確認済み:
- 基本的な文法エラーなし
- テーブル・カラムの参照整合性を確認
- 重大なパフォーマンス問題なし

または

# 🟡 Check Result: Warning

## ⚠️ パフォーマンス警告

<details>
<summary>📄 sql/example.sql</summary>

### 📝 内容
パフォーマンス改善の提案内容

### 🛠️ 推奨対応
```sql
-- 推奨されるコード例
```

</details>

または

# 🔴 Check Result: Critical

## 検出された重大な問題:

<details>
<summary>📄 sql/example.sql</summary>

### ❗ 問題の概要
文法エラーやテーブル参照の誤りなど

### 📍 場所
[ファイルパス]:行番号

### 📝 詳細
具体的な問題の説明

### 💥 影響
想定される影響

### 🛠️ 修正案
```sql
-- 修正後のコード例
```

</details>

GitHub Actions ワークフローの設定

以下は、GitHub ActionsでSQLレビューを自動化するためのワークフロー設定です。
詳細の説明は長くなりそうなので割愛させていただきます

name: SQL Review

# 必要な権限を明示的に指定
permissions:
  issues: write
  contents: read
  pull-requests: write

# PRの作成・更新時のみ実行
on:
  pull_request_target:
    types: [opened, synchronize, reopened]
    paths:
      - 'sql/**.sql'    # SQLディレクトリ配下の全SQLファイル
      - 'db/checker.sql' # スキーマ定義ファイル

jobs:
  review:
    name: Review SQL Changes
    runs-on: ubuntu-latest
    timeout-minutes: 10

    env:
      MYSQL_VERSION: '8.0'
      MODEL_NAME: 'gemini-2.0-flash-exp'

    steps:
      - name: Checkout PR Head
        uses: actions/checkout@v4
        with:
          ref: ${{ github.event.pull_request.head.sha }}
          fetch-depth: 0
          persist-credentials: false

      - name: Analyze SQL Changes
        id: analyze
        run: >-
          set -euo pipefail;
          
          BASE_SHA="${{ github.event.pull_request.base.sha }}";
          HEAD_SHA="${{ github.event.pull_request.head.sha }}";
          
          # スキーマファイルの変更有無をチェック
          SCHEMA_CHANGED=$(git diff --name-only "$BASE_SHA" "$HEAD_SHA" -- 'db/checker.sql' | grep -q . && echo 'true' || echo 'false');
          echo "schema_changed=${SCHEMA_CHANGED}" >> "$GITHUB_OUTPUT";
          
          # スキーマ変更があれば既存SQLすべてをチェック、なければ差分のみ
          if [ "$SCHEMA_CHANGED" = "true" ]; then
            find sql -type f -name "*.sql" -print0 | xargs -0 realpath --relative-to=. > sql_files.txt;
          else
            git diff --name-only "$BASE_SHA" "$HEAD_SHA" -- 'sql/*.sql' > sql_files.txt;
          fi;
          
          # 対象ファイルがない場合はスキップ
          if [ ! -s sql_files.txt ]; then
            echo "skip=true" >> "$GITHUB_OUTPUT";
            echo "No SQL files to review.";
            exit 0;
          fi

      - name: Prepare Review Content
        if: steps.analyze.outputs.skip != 'true'
        id: prepare
        run: >-
          set -euo pipefail;
          
          # スキーマ変更フラグを設定
          echo "SCHEMA_CHANGED=${{ steps.analyze.outputs.schema_changed }}" >> "$GITHUB_ENV";
          
          # スキーマファイル内容を環境変数へ
          if [ -f "db/checker.sql" ]; then
            {
              echo "CHECKER_SQL_CONTENT<<EOF";
              cat "db/checker.sql" | sed 's/`/\\`/g';
              echo "EOF";
            } >> "$GITHUB_ENV";
          fi;
          
          # 新規・変更SQLファイルの内容を環境変数へ
          {
            echo "NEW_SQL_CONTENT<<EOF";
            while IFS= read -r file; do
              [ -f "$file" ] || continue;
              echo "-- File: $file";
              cat "$file" | sed 's/`/\\`/g';
              echo;
            done < sql_files.txt;
            echo "EOF";
          } >> "$GITHUB_ENV";
          
          # スキーマ変更時には、既存SQL（変更前の内容）も環境変数へ
          if [ "${{ steps.analyze.outputs.schema_changed }}" = "true" ]; then
            {
              echo "EXISTING_SQL_CONTENT<<EOF";
              while IFS= read -r file; do
                echo "-- File: $file";
                git show "${{ github.event.pull_request.base.sha }}:$file" 2>/dev/null | sed 's/`/\\`/g' || true;
                echo;
              done < sql_files.txt;
              echo "EOF";
            } >> "$GITHUB_ENV";
          fi

      - name: Review SQL with Gemini
        if: steps.analyze.outputs.skip != 'true'
        id: review
        env:
          GEMINI_API_KEY: ${{ secrets.GEMINI_API_KEY }}
        run: >-
          set -euo pipefail;

          # Gemini AI へのプロンプトを組み立て
          PROMPT=$(cat .github/workflows/prompts/sql_review.md | envsubst);

          echo "=== Debug: Generated Prompt ===";
          echo "$PROMPT";
          echo "=== End Debug ===";

          RESPONSE=$(curl -sS -w "\n%{http_code}" \
            "https://generativelanguage.googleapis.com/v1beta/models/${MODEL_NAME}:generateContent?key=${GEMINI_API_KEY}" \
            -H "Content-Type: application/json" \
            -H "User-Agent: GitHub-Action-SQL-Review" \
            -d '{
              "contents": [{
                "parts": [{
                  "text": "'"$PROMPT"'"
                }]
              }]
            }');

          HTTP_STATUS=$(echo "$RESPONSE" | tail -n1);
          RESPONSE_BODY=$(echo "$RESPONSE" | sed '$ d');

          if [ "$HTTP_STATUS" != "200" ]; then
            echo "::error::API request failed with status ${HTTP_STATUS}";
            echo "Response: ${RESPONSE_BODY}";
            exit 1;
          fi;

          COMMENT=$(echo "$RESPONSE_BODY" | jq -r '.candidates[0].content.parts[0].text // empty');
          if [ -z "$COMMENT" ]; then
            echo "::error::Failed to extract comment from response";
            exit 1;
          fi;

          {
            echo "comment<<EOF";
            echo "$COMMENT";
            echo "EOF";
          } >> "$GITHUB_ENV"

      - name: Post Review Comment
        if: steps.analyze.outputs.skip != 'true'
        uses: actions/github-script@v7
        with:
          script: |
            const comment = process.env.comment;
            if (!comment) {
              core.setFailed('No comment to post');
              return;
            }
            
            await github.rest.issues.createComment({
              owner: context.repo.owner,
              repo: context.repo.repo,
              issue_number: context.issue.number,
              body: `${comment}`
            });

      - name: Check Review Result
        if: steps.analyze.outputs.skip != 'true'
        run: |
          # Geminiの出力から"Check Result"行を抽出し、OK/Warning/Criticalを判定
          CHECK_RESULT=$(echo "${{ env.comment }}" | grep -E "^# (🟢|🟡|🔴) Check Result:" | awk '{print $NF}')
          
          case "$CHECK_RESULT" in
            "OK")
              echo "SQLレビューが正常に完了しました"
              exit 0
              ;;
            "Warning")
              echo "::warning::SQLレビューで警告が検出されました"
              exit 0
              ;;
            "Critical")
              echo "::error::SQLレビューで重大な問題が検出されました"
              exit 1
              ;;
            *)
              echo "::error::不明なレビュー結果です: $CHECK_RESULT"
              exit 1
              ;;
          esac

実際のレビュー例 📝

実際にPRを出して確かめてみました。
ただの誤字の検出なのですが、まあまあいい感じに検出してくれています。

今後 🚀

ファイル数が多くなった時に期待通りに動くかを検証していこうと思っています。
Gemini1.5 Falshで100万トークンだったのでGemini 2.0ではさらに最大が上がっているのが期待できるのと、400テーブルほどで30万くらいのトークン数でしたので、動作自体は大丈夫なのかなと想定しています。厳しければSQLの変更箇所から該当テーブル定義のgrepなどしてAPIに渡すトークン数の削減が必要そうですね。

まとめ 📌

今回は、GitHub ActionsとGoogle Gemini AIを組み合わせて、SQLレビューを自動化する試作を行いました。今後、AIで自動化・補助できそうな開発サイクルにはどんどん適用していきたいですね。