Why use code analysis

Code analysis is a crucial part of the software development lifecycle. It helps in identifying potential issues and maintaining standards across your codebase. By integrating code analysis into your workflow you can catch bugs early, improve maintainability, and enhance collaboration among team members. Flyway has had an integration with code analysis for a while but it was complex to setup and configure - we aim to simplify this process with our new approach.

How to adapt your process

The first step is to see where you are today. Running check -code will give you a report on all pending migrations in your project.

Flyway comes with a default set of rules configured that will help you get started. You can then adapt these rules to fit your projects need. There are several ways to configure a rule, although the precise mechanism depends on the analysis engine being used (Regex or SQLFluff)

Policy configuration

A policy is a set of rules and their configuration across all analysis engines. It formalizes what your organization considers bad practice in SQL development.

The rules that could make up a policy have a state and a configuration

Rule state

A rule can be specified in one of the following states

• Disabled: the rule is not run
• Warning: the rule is run but will only issue a notification
• Error: the rule is run and will cause the check -code operation to throw an error if there is a violation

How these are set for each analysis engine is covered below.

SQLFluff configuration

Flyway ships with a default configuration called sqlfluff.cfg in the /conf folder of the distribution. We recommend that you move this to your project folder to keep it under version control. You can then direct Flyway to pick up the configuration in it's new name and location using the rulesConfig parameter By default, the Flyway template version of the file disables all SQLFluff rules through setting this parameter:

exclude_rules = all

This is to ensure we minimize the noise Flyway makes as you transition to using code analysis.

Rules

You can find a reference to the standard SQLFluff rules here.

Redgate will be providing additional rules in the version of SQLFluff shipped with Flyway, these are documented in here. This list will be growing over the coming months.

Configuration parameters

Many of the rules can be configured by setting parameters in the configuration file. As an example the rule CV09 / Blocked words can be configured by adding the following to the SQLFluff configuration file like this:

[sqlfluff:rules:convention.blocked_words]
blocked_words = DELETE,PASSWORD 

Regex rule configuration

Rules location

Flyway ships with a default set of rules in /rules folder of the distribution.

Rules configuration

The mechanism to apply a policy for regex rules is through setting the severity field for each rule, see Code Analysis Rules for details

Updating your Flyway Process

We're borrowing from the SQLFluff onboarding model

Pre CI/CD

In this stage you are setting up the rules and configurations to capture what good looks like for your organization. You may want to configure the check.scope=all to analyze all the migrations and get a feel for what kind of issues you need to guard against.

You are using this information to establish which SQLFLuff and Regex rules are relevant and how you want to configure them.

Soft CI/CD

Having established your rules and configurations, you can begin to integrate code analysis into your CI/CD pipeline. This means running the check -code command as part of your build process and by default this will output a report but not fail the build. You may want a manual review stage in the release pipeline to judge whether you are comfortable to proceed.

Hard CI/CD

The change here is that you are having Flyway fail the check operation if any of the analysis engines report an error. This is done using the failOnError parameter and means that if no issues are identified in the code analysis then the pipeline could proceed straight to deployment `

How to transition from the previous code analysis system

Previously, Flyway used majorRules/minorRules & majorTolerance/minorTolerance to configure how policy was set however this is planned for deprecation and will be removed in a future version.

The changes to move onto the new system are essentially to look at the rules you care about (majorRules/minorRules) and set these in the new system.

In Regex rules this means setting/adding the severity field to the specific rules.

SQLFLuff treats all violations as errors by default but it is possible to selectively enable a specific set of rules using the SQLFLuff "rules" configuration. Essentially the list if rules you previously specified in majorRules/minorRules would go into here.

Some things to be aware of:

• Chaining the check command with other commands like migrate may not result in the behavior you expect - we suggest that you perform any checks as a separate CI/CD step before proceeding with operations that will change your database.
• There is no longer a concept of tolerance for this system (or you could view it as the tolerance is always 0).
• There is no concept of major or minor rules anymore - a violation of a rule is a violation.