Flyway can run static code analysis over your SQL migration scripts. This validates your SQL to check for errors or bad practice.

No database connection is required to run this check so it can easily performed as a validation step in a continuous integration build process.

Flyway provides built in support for integrating with SQLFluff. This is an analysis engine which supports a large number of SQL dialects and can detect a variety of SQL violations.

At Flyway Enterprise tier it is also possible to create regular expression rules, which allow for a simple way of detecting simple undesirable patterns.

Code analysis is run using the Flyway check command with the `code` flag

SQL Fluff integration

Prerequisites

SQLFluff needs to be installed on the machine producing a report.

SQL dialect detection/configuration

If you provide a URL/environment to check -code Flyway will use it to automatically determine which SQL dialect to use when analyzing your SQL.

If no URL is provided, then you need to configure the dialect in a .sqlfluff configuration file. This file needs to be located in the same location as the migrations being analysed.

Further reading

You can find more information on configuration in the SQLFLuff documentation.

Regular expression rules

EDITION: ENTERPRISE

You are able to create a regular expression (regex) rule to be applied to your code to identify particular combinations of keywords that are problematic. It is not checking for valid SQL but for text that is deemed bad practice as it could be a problem for database and it's ecosystem.

Rule format

You can add regex rules by adding a TOML rule file per rule. We'd suggest taking one of the supplied regex rules in the default \rules folder and adapting it to your needs.

For the rule file syntax see the reference docs.

Built in rules

Redgate has added a set of rules that are of interest to customers with a larger and more complex database infrastructure.

Rules location

Rules are located in the rules folder of your project by default but a custom configuration can be specified using the check.rulesLocation setting,

Rule file naming

The file name will be used as the source of rule metadata: A__B.toml (that's two underscores)

• Where A is the rule identifier (e.g. MyRule01)
• Where B is a short rule description (this will be replaced by the description field in the file content if supplied