It is a good idea to run drift analysis against your production environments to make sure that they are in the expected state and reduce the risk of deployment issues occurring.

It is particularly important to run drift analysis as part of an automated deployment pipeline, ensuring that the database is in the expected state immediately before deployment, though this check can be run at any time.

How Flyway drift detection works

Flyway uses database snapshots in order to perform drift analysis. There are alternative approaches to making this work, all of which rely on the Flyway check command, using the drift  flag:

Option 1: Capture a snapshot of the database after every deployment and embed it for reuse (suitable for state-based or migrations-based deployments)

In this approach, every time a deployment is run, a snapshot is taken immediately after the deployment to capture the state of the database, and is embedded in the snapshot history table in that database.

This snapshot can then be compared directly against your database ahead of a new deployment (or on a scheduled or ad hoc basis).

The benefits of this solution are:

• it works for both state-based and migrations-based deployments
• it only captures drift since the last deployment
• it is easy to configure as no build environment or external storage is needed
• is is faster than using a build environment
• the snapshot being in the database means that there is less room for configuration error (e.g. it would be hard to accidentally compare against the wrong snapshot)

See this tutorial for exact steps.

Option 2: Capture a snapshot of the database after every deployment and persist it for reuse (suitable for state-based or migrations-based deployments)

This is the same as option 1, except that instead of embedding the snapshot in the target database, the snapshot is saved to a file on disk.

This approach requires more configuration effort as the snapshot needs to be saved somewhere future pipelines can access (e.g., an artifact repository, a network share) so that it can then be used whenever you perform subsequent drift checks.

This solution is only worth picking over option 1 if you don't want to store the snapshot in your database.

The main drawback with this approach is the overhead of persisting the snapshot between deployment runs, and the approach here will vary depending upon your infrastructure.

See this tutorial for exact steps.

Option 3: Use a build environment to capture migrations at different stages of deployment (suitable for migrations-based deployments only)

In this approach, Flyway performs the following steps (via a single check command invocation):

• Take a snapshot of the target environment
• Clean the build environment
• Query the target environment for the list of applied migrations
• Apply these migrations to the build environment
• Take a snapshot of the build environment
• Compare the two snapshots
• Report on drift

This approach is simple enough to configure if you have access to a build environment in your pipeline (which is required if you want to generate a deployment report for a migrations-based deployment).

There are some drawbacks in that this approach only works for migrations, it captures all drift relative to migrations rather than just drift since the last deployment, and it is likely to be a bit less performant than option 1 as there are more steps under the hood.

This solution is probably only worth picking over option 1 and 2 if you don't want to store the snapshot in your database or worry about external storage of the snapshot.

Configuring which objects are included in drift reporting using a filter

By default all objects which are different will be reported on, whether modified or existing only in the target.

Drift analysis uses the same database comparison configurations as are used for generating the schema model or for generating migrations.

It is possible to override the filtering logic for drift analysis by configuring a filter file parameter on the check command.

Resolving instances of drift

The first step to resolve drift is to make a decision about how to handle the drift.  Is the drift valid and therefore needs to be reflected in version control and your other environments for a consistent development experience that is reflective of changes on other environments (usually Production)?  Or, is the drift invalid and should be reverted to bring the environment back in line with what's in version control?  A team may make quite different choices depending on the nature of the drift and the use case for the target environment.  Below are some options for how to handle drift.

Relevant tutorials

• Tutorial - Drift Report for deployments using embedded snapshot
• Tutorial - Drift Report for deployments using snapshot files