EverSQL and GitHub CI/CD : Automatic SQL Optimization

EverSQL's optimization capabilities can be integrated into your GitHub CI workflow, to automatically and continuously optimize your SQL queries. The integration can be implemented by creating a GitHub action, which utilizes EverSQL's Optimization API capabilities, to automatically optimize queries from *.sql files and report back the results as a pull request check results.

If you're looking to implement an automatic SQL profiler (or an SQL performance linter), which is integrated with GitHub, please follow the steps below. The integration is very easy and takes 60 seconds to implement.

Integrating EverSQL as a GitHub Workflow

  1. Navigate to your GitHub repository, and click the Actions tab.
  2. Click New Workflow, choose Simple Workflow, and copy the code below to the code section.
  3. Copy your EverSQL API Key and place it inside the code instead of the API_KEY placeholder.

Use the following code as the GitHub Action workflow code (for AWS PrivateLink based integrations, please see the comment below before applying the code):

name: EverSQL

on:
  push:
  pull_request:
    paths:
      - '**/**.sql'

jobs:
  automatic-sql-optimization:
    runs-on: ubuntu-latest
    steps:
    - name: Checkout repository
      uses: actions/[email protected]
    - name: Get changed files
      id: files
      uses: jitterbit/[email protected]
    - name: Get optimization package
      run: wget https://eversql.com/install/eversql_github_sql_file_optimizer.py
    - name: Optimize SQL queries
      run: |
        for changed_file in ${{ steps.files.outputs.all }}; do
          echo "#####  Optimizing file: ${changed_file}  #####:"
          python eversql_github_sql_file_optimizer.py ${changed_file} API_KEY https://www.eversql.com/api/optimize
        done

For AWS PrivateLink integrations: if you're integrating EverSQL and GitHub Enterprise over AWS PrivateLink, please modify the code above, by:

  • Please replace https://eversql.com/install/eversql_github_sql_file_optimizer.py with http://<your_endpoint_dns>/eversql_github_sql_file_optimizer.py
  • Please replace https://www.eversql.com/api/optimize with http://<your_endpoint_dns>/api/optimize

Then, please copy the code and apply it as the GitHub action code.

EverSQL & GitHub Action integration demo

Once you integrate the new GitHub action into your repository, pull requests that contain *.sql files will be automatically validated by EverSQL for optimal performance. Once a pull request is created, the GitHub action will be automatically queued to validate the *.sql file.

The new GitHub Check will include a report with optimization recommendations for each of the queries in the SQL file. You can click the link next to each query to get a more detailed report directly on EverSQL.

Managing pull request and merge status

When there are pending optimization recommendations, the GitHub Check will fail.

By default, you can still merge the pull request even if checks fail. If you're interested in blocking merges when checks fail, you can configure it in the repository settings, by navigating to the repository Settings => Branches => Add Rule => Enable the option Require status checks to pass before merging => Select the workflow name created above.