Developing a Custom Review Rule for SQLE (MySQL 5.7) – Step‑by‑Step Guide

SQLE, an open‑source SQL audit platform developed by ActionTech, provides features such as pre‑ and post‑audit, index optimization, and extensible database support; the article demonstrates how to add a custom review rule for MySQL 5.7.

First, the authentication flow is shown: a POST request to http://10.25.15.83:10000/v1/login returns a JWT token (default admin/admin) which must be included in subsequent audit API calls.

The audit request is sent to http://10.25.15.83:10000/v1/tasks/audits using a request body that matches the type CreateAuditTaskReqV1 struct definition, e.g., type CreateAuditTaskReqV1 struct { InstanceName string `json:"instance_name" form:"instance_name" example:"inst_1" valid:"required"` InstanceSchema string `json:"instance_schema" form:"instance_schema" example:"db1"` Sql string `json:"sql" form:"sql" example:"alter table tb1 drop columns c1"` }

An example SQL statement create table test (id int not null, name varchar(20)); fails the default audit because it lacks a primary key, comments, and an InnoDB engine; the API response confirms the error level and provides a task ID.

The detailed audit result can be retrieved from the database with: mysql> select audit_result from execute_sql_detail where task_id=1\G which returns notices about missing comments, missing primary key, and missing InnoDB engine.

To add a new rule that enforces the presence of audit columns (cdate, creator, edate, editor), the article inserts a record into the sqle.rules table using standard MySQL commands, then verifies the rule appears in the UI and adds it to a custom rule template.

The backend implementation is a Go function placed in sqle/driver/mysql/rule/rule.go that checks a *ast.CreateTableStmt for the required columns, increments a counter, and adds a result if any are missing:

// can be unit‑tested with dlv
func checkAuditColumn(ctx *session.Context, rule driver.Rule, res *driver.AuditResult, node ast.Node) error {
   var auditCols = [4]string{"cdate","edate","creator","editor"}
   set := make(map[string]struct{})
   for _, v := range auditCols { set[v] = struct{}{} }
   cnt := 0
   if stmt, ok := node.(*ast.CreateTableStmt); ok {
       for _, col := range stmt.Cols {
           if _, exists := set[col.Name.Name.String()]; exists { cnt++ }
       }
       if cnt != 4 { addResult(res, rule, rule.Name) }
   }
   return nil
}

The rule is registered with a mapping entry: { Rule: driver.Rule{ Name: DDLCheckAuditColumn, Desc: "建表语句必须包含审计列(cdate,creator,edate,editor)", Level: driver.RuleLevelError, Category: RuleTypeUsageSuggestion }, Message: "建表语句必须包含审计列(cdate,creator,edate,editor)", AllowOffline: true, Func: checkAuditColumn, } and a constant definition: DDLCheckAuditColumn = "ddl_check_audit_column" .

A unit test in sqle/driver/mysql/audit_offline_test.go validates the rule against a sample CREATE TABLE statement, confirming that the rule triggers correctly.

After successful testing, the code can be committed and the new rule becomes active in the SQLE UI, providing an extensible way to enforce organization‑specific SQL standards.

Overall, the guide offers a practical example of extending SQLE with Go, useful for developers interested in database tooling, rule‑based auditing, and open‑source contributions.