SQL auditing | How to use SQLE for development phase SQL auditing

Author: Sun Jian

Sun Jian, R&D engineer of Aikesheng, responsible for SQLE related development;

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

1. Introduction to SQLE

SQLE is a program initiated by the Aikesen open source community and aimed at database development and management personnel. It realizes the whole process coverage of SQL "development" - "testing" - "online", and refined management of resources and permissions, taking into account simplicity and efficiency. , an open source project that is easy to maintain and expand, and aims to provide users with a set of safe, reliable, self-controllable SQL quality control solutions.

2. Function introduction

The SQL audit in the development phase of SQLE is mainly completed through the functional audit task of SQLE. This function is positioned as a SQL quality control solution for all scenarios, and can control the quality of SQL in different stages such as application development, testing, launch, and production. Combining SQL collection and SQL auditing to conduct long-term auditing of the database, it currently supports Mybatis scan, slow log, database table metadata, TopSQL, and application SQL crawl (java application) scene auditing. In the application development stage, the application code can be audited in real time through the Mybatis scan type task of the audit task. The audit task of Mybatis scan needs to collect SQL through the Scanner tool provided by SQLE and push it to SQLE for SQL auditing. The following is the SQL collected by SQLE through Scanner. Flowchart of:

At present, the development stage basically uses the CI/CD platform for continuous integration, such as Jenkins, GoCD, CI/CD of the git platform, etc. This stage is mainly used for code inspection, unit testing, and packaging. The benefit is to standardize the development process and reduce complex workloads. Therefore, SQL auditing can also be integrated into the CI process as a check module during the development phase. Bringing SQL auditing matters to the development stage can reduce the pressure of later SQL auditing. Audit tasks currently using SQLE can do:

1. SQLE provides Scanner mode that can integrate almost all CI/CD, simple configuration, and can be easily integrated into the existing development process;
2. Immediate feedback of SQL audit results, and development can be optimized immediately for failed SQL audits;
3. In addition to the CI process, you can also view SQL classification statistics and audit reports on the SQLE interface.

3. Effect demonstration

1. Create an audit task

2. Add a build process to the task corresponding to jenkins

The integration of SQLE's Scanner and CI/CD is by adding bash-type tasks, which are generally supported by CI/CD. SQLE Scanner is essentially a binary command-line tool, which is integrated in SQLE and can be found under bin in the SQLE platform installation directory. For the specific usage and command-line parameter explanation, please refer to:https://actiontech.github.io/ sqle-docs-cn/3.modules/3.6_auditplan/scanner_management.html .

3. SQL auditing will be performed when the jenkins task is triggered

The effect of SQL audit after Jenkins integration is shown in the following two figures:

The audit result will be returned immediately through the Jenkins task. When the SQL non-compliance triggers the rule recommendation, you can see the result of the build failure as shown in the figure above. At this time, the CI process will notify the development, and the development needs to optimize the non-compliant SQL again. Submitting the code triggers the CI build. If the SQL optimization is successful, the SQL audit will build successfully.

4. View the results on the SQLE page

The person in charge of development can view the audit results and SQL statistics through the SQLE platform, and know in advance the quality of SQL development and the problems encountered in the current project. The SQL statistics and audit results are shown in the following two figures:

4. Summary

The simplest trial of SQL audit and CI/CD process integration above, you can download and install SQLE and try it with your own company's CI/CD. SQLE installation reference homepage: https://github.com/actiontech/sqle ;

For more usage scenarios, please refer to: https://actiontech.github.io/sqle-docs-cn/3.modules/3.6_auditplan/introduction.html .