In Flyway, “Checks” is the collective term we use for the pre- or post-deployment analysis of some aspect of your database migration. Checks are instantiated using the top level check
command.
Before performing a deployment to the target database (most notably, production), you might want to look over what you’re about to do and understand one or more of the following:
Each of these scenarios can be met with the check
command, using the corresponding flag:
Scenario | Command & Flag | Output |
---|---|---|
Will these changes have the effect I am expecting? | check -changes Contact us | ChangeReport.html, ChangeReport.json |
What database changes have been made recently? | check -changes Contact us | ChangeReport.html, ChangeReport.json |
Is the production database in the state I am expecting it to be in? | check -drift Contact us | ChangeReport.html, ChangeReport.json |
In Preview Are our changes following internal policies? |
check -code | ChangeReport.html, ChangeReport.json |
What SQL will be executed in the next deployment? | check -dryrun Flyway Teams | ChangeReport.html, ChangeReport.json |
Check –changes
The –changes
flag produces a report indicating differences between applied migration scripts on your target database and pending migrations scripts (ie. the set of instructions you want to use to change your target database).
You can use this capability pre- and post-deployment:
In either scenario, using the -changes
flag will help you infer which database objects will be/have been affected - and how – when you execute/have executed your migration script(s).
There are 4 ways to generate a change report:
url
and check.buildUrl
check.appliedMigrations
and check.buildUrl
url
and check.nextSnapshot
check.deployedSnapshot
and check.nextSnapshot
url
and buildUrl
The check –changes
command and flag works by building a temporary database. This ‘build’ database is first made to reflect the state of your target schema, and then made to reflect your target schema with the pending changes applied.
The difference between the two states of this build database (target now, and target with changes applied) represents the effect your pending migrations will have (or have had) when the scripts are (or were) executed. This difference is captured as an artefact called a “Change Report”. The change report is available as both HTML (human readable) and JSON (machine readable) formats.
The process works like this:
clean
this database, so if you specify a full database, you must ensure it is ok to for Flyway to erase its schema)flyway check –changes -check.buildUrl="jdbc://build-url" -url="jdbc://url" -check.reportFilename="changeReport.html"
Flyway’s check –changes
will then:
snapshot
of the build database (now also at V2)snapshot
of the build databaseappliedMigrations
and buildUrl
The check –changes
command and flag works by building a temporary database. This ‘build’ database is first made to reflect the state specified by appliedMigrations
, and then made to reflect your appliedMigrations
with the pending changes applied.
The difference between the two states of this build database (appliedMigrations
, and appliedMigrations
with changes applied) represents the effect your pending migrations will have (or have had) when the scripts are (or were) executed. This difference is captured as an artefact called a “Change Report”. The change report is available as both HTML (human readable) and JSON (machine readable) formats.
The process works like this:
flyway info -infoOfState="success,pending,out_of_order" -migrationIds > appliedMigrations.txt
clean
this database, so if you specify a full database, you must ensure it is ok to for Flyway to erase its schema)flyway check –changes -check.buildUrl="jdbc://build-url" -check.appliedMigrations="$(cat appliedMigrations.txt)" -check.reportFilename="changeReport.html"
Flyway’s check –changes
will then:
appliedMigrations
to the build database (for simplicity, let’s say it’s at V2)snapshot
of the build database (now also at V2)snapshot
of the build databaseCheck –drift
The –drift
flag produces a report indicating differences between structure of your target database and structure created by the migrations applied by Flyway.
There are 2 ways to generate a drift report:
url
and check.buildUrl
url
and check.deployedSnapshot
url
and buildUrl
The check –drift
command and flag works by building a temporary database. This ‘build’ database is made to reflect the state of your target schema based on the migrations applied by Flyway.
The difference between the two states of this build database and your target database represents the drift between the expected structure according to Flyway and the actual structure. This difference is captured as an artefact called a “Drift Report”. The drift report is available as both HTML (human readable) and JSON (machine readable) formats.
The process works like this:
flyway check –drift -check.buildUrl="jdbc://build-url" -url="jdbc://url" -check.reportFilename="driftReport.html"
Flyway’s check –drift
will then:
snapshot
of the target databasesnapshot
of the build database (now also at V2)Check -code
The -code
flag produces a report showing the results of running static code analysis over your SQL migrations.
This report is an integration with SQLFluff which analyses your SQL according to a set of rules to ensure standards are met.
SQLFluff needs to be installed on the machine producing the report. We currently support version 1.2.1. You can install it by running:
pip3 install sqlfluff==1.2.1
As this is an integration, it can be used in Flyway Community too albeit with more manual steps.
You can invoke SQLFluff by running:
sqlfluff lint --dialect <dialect> [migrations]
The dialect should be the flavour of SQL you are using, such as ansi
or tsql
.
[migrations]
could be either a space-separated list of location(s) containing your migrations, or individual migration(s).
This will produce a report in your terminal.
In Flyway Teams, you can run:
flyway check -code -check.reportFilename=report.html -url=jdbc:postgresql://...
This will run SQLFluff under the hood, and produce a HTML and JSON report that you can use to check the standards of your migrations.
Flyway makes use of any configured locations
to determine what migrations to analyse.
If you have a URL
configured, Flyway will only run analysis on pending migrations.
If no URL
is configured, then all migrations (pending and applied) will be analysed.
If you provide a URL to check -code
Flyway will use it to automatically determine which SQL dialect to use when analysing 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 migration(s) being analysed.
You can find more information on SQLFluff configuration here.
You can also use this to configure more than just the dialect, such as which rules should be enabled or disabled.
You can configure your pipeline to fail when specified SQL Fluff rules are violated beyond a given tolerance level.
This can be done by configuring check.majorRules
,check.minorRules
,check.majorTolerance
and check.minorTolerance
.
majorRules
should contain a comma-separated list of SQL Fluff rule codes which are considered to be major
.
If the total number of majorRules
violations exceeds the majorTolerance
, Flyway will fail.
The same applies to minorRules
and minorTolerance
.
For example:
./flyway check -code '-check.majorTolerance=3' '-check.majorRules=L034,L042'
This will fail if rules L034
and L042
are violated 4 or more times in total across all scanned migration scripts.