Automating SQL Review with sql-review-helper: Capturing, Analyzing, and Reviewing SQL in CI

Low‑efficiency SQL often leads to rollbacks and hot‑fixes, so ensuring every new statement is reviewed is critical. The traditional manual review process—developers submitting SQL to DBAs who run EXPLAIN and evaluate indexes—can be error‑prone and easily missed.

The proposed solution is a lightweight tool called sql-review-helper that automates the entire workflow. After continuous integration (CI) finishes, the tool captures every SQL executed, de‑duplicates them, runs EXPLAIN automatically, and writes the results to a repository file that can be reviewed via the normal code‑review process.

Key prerequisites include: all database operations must run inside CI, every piece of code has unit tests, CI runs automatically, and all changes go through code review before merging to master.

To capture all SQL statements in MySQL, the tool enables the General Query Log with:

SET global general_log = 'ON';
SET global log_output = 'table';

In CI environments (e.g., a Dockerized MySQL started via GitLab service), the log is stored in a table for easy extraction.

Each captured SQL is processed by sql-chief to compute a fingerprint, identify similar statements, and produce an EXPLAIN plan. The output file contains entries such as:

---
# WARNING:
#   This file is generated by sql-review-helper,
#   including all SQL statements executed grouped by sql-fingerprint
#   Manual modification of this file is prohibited

- explain_result:
    Extra: null
    filtered: 100.0
    id: 1
    key: null
    key_len: null
    partitions: null
    possible_keys: null
    ref: null
    rows: 1
    select_type: SIMPLE
    table: general_log
    type: ALL
  fingerprint: sql-73b5bc996402ca9f3b95f482a42d0b62
  parameterized_sql: 'SELECT * FROM mysql.general_log LIMIT ?'
- explain_result:
    Extra: Using index condition; Using where; Using temporary; Using filesort
    filtered: 11.11
    id: 1
    key: sqlambda_index
    key_len: '48'
    possible_keys: sqlambda_index
    ref: null
    rows: 2
    select_type: SIMPLE
    table: test_sqls
    type: range
  fingerprint: sql-a06cd31ea45e4746219d23357d000109
  parameterized_sql: |
    SELECT sqlambda_id, uuid, username, substr(sql_params, ?, ?) AS sql_params,
           input_sql, create_time, finish_time, update_time, status
    FROM test_sqls
    WHERE sqlambda_id LIKE ?
    ORDER BY create_time DESC
    LIMIT ?, ?

Usage steps are straightforward: install sql-review-helper in the CI image, run sql-review-helper prepare before the CI job to enable capture, execute the CI pipeline, and finally generate the review file with a command such as

sql-review-helper generate --passwd $MYSQL_ROOT_PASSWORD --output-file sql-to-review.yml --db your_database

. The resulting sql-to-review.yml can be added to version control and reviewed like any other code change.

Beyond CI, the same fingerprint data can be cross‑referenced with production slow‑query logs stored in ELK, allowing engineers to locate problematic SQL in the review file and even trace the author via git blame.

In summary, the sql-review-helper automates SQL capture, analysis, and review, reducing the risk of missed or inefficient statements while integrating seamlessly into existing CI and code‑review workflows.