• Flyway
• Deployment checks and safeguards
• Database Deployments

How to Detect Database Drift using Flyway Snapshots

Flyway Enterprise supports several approaches to drift detection. One of the simplest and cleanest uses a snapshot to capture what a given database version ‘should look like’ and then compares it to the target database.

However, as always, Flyway supports a range of team workflows, and the method you choose to verify a database version depends on how that version is defined. Teams can instead detect drift by comparing to a fresh build of the version from the migration files, if those files always define the ‘expected state’ of the version.

Why and when to check for database drift

Drift detection is a common requirement in a database release pipeline, helping prevent deployment failures caused by unexpected changes. The term database drift typically refers to changes made directly to a database, outside the controlled deployment process.

Flyway migrates a database between versions, tracking the schema version number, but it cannot prevent out-of-band changes, meaning those made without using Flyway migrate and a versioned migration script. Drift occurs when two representations of a database claiming the same Flyway schema version number, such as a deployed database and one rebuilt from migrations, differ in structure or state.

Drift often happens in production when ad hoc or emergency fixes are applied manually and not added to the versioned code base, so automated drift checks in your deployment process are essential. However, it’s also necessary to run drift checks throughout the development cycle. For example, we may want to verify that the version being released is identical to the one that passed testing or that there are no discrepancies between environments due to modified repeatables or versioned migrations being skipped or run out of order.

A successful deployment depends on our confidence that the database has not drifted. Even a seemingly minor, untested change in the release or the target database can introduce risk. These checks help teams catch inconsistencies early and prevent deployment issues.

Automated drift checks during deployment using Flyway check -drift

Flyway Enterprise supports various representations of database metadata for comparison, including scripts, snapshots, or live databases. This approach eliminates the need to retain old versions of live databases on the server. The comparison process can be conducted in multiple ways, but in all cases, Flyway uses -diff command to generate a ‘diff artifact’ that itemizes each difference between the source and target databases.

The check -drift command essentially acts as a wrapper around the diff command, which performs the comparison and produces a report of any differences found, both in JSON and HTML formats.

In a Flyway pipeline, we would generally use the check -drift command to check for drift in the state of a production environment, before a deployment is allowed to proceed. It supports multiple strategies to detect drift, including comparing the target database to a stored snapshot or a build environment, generated from the migration scripts.

Using stored snapshots for drift detection

Snapshots were originally devised as an immutable file-based representation of a database’s metadata, for comparing databases when a direct connection isn’t available. In Flyway, they are also very useful when identifying differences between the current database state and its expected state.

When running stored snapshot drift checks, the expected state of the target database at a given version is defined by a snapshot captured immediately after a successful migration to that version. For example, we can capture the snapshot using an afterMigrate callback, and store it in a repository with the correct version number, captured from the JSON output of the migrate or info commands.

Before a deployment is allowed to proceed, the check -drift command will compare this stored snapshot to the target database to verify that it matches the recorded version or has ‘drifted’ in any way:

The resulting drift report (JSON and HTML) will display full details of which objects, if any, in the target database have drifted from the ‘expected state’ defined by the snapshot.

The benefits of the snapshot approach are:

• Snapshots are quick to produce, immutable and easy to store
• It works with any development and deployment process (state-based or migration-based).
• It provides a clear record of changes since the last deployment.
• It does not require access to the original migration scripts or a separate build environment.

The only limitation is that you can’t examine the metadata directly from the snapshot, and so, if they are in version control, they cannot be used for working out who made what changes, and when. However, with Flyway Enterprise the team can get around this limitation easily as it can generate from the snapshot a set of object-level scripts (the ‘schema model’), or a build script.

Either is a better source-control artefact because you can use it to track changes to individual objects, but object-level scripts make it easier to locate individual objects and scale much better for larger databases.

Using migrations and a build environment for drift detection

In this method, the expected state of the target database, at a given version, is defined not by a stored snapshot but by the set of migrations used to create the version.

Flyway detects drift by performing a diff (schema comparison) between the target database and the same version of the database freshly built from the migration files. In this method, Flyway will:

1. Take a snapshot of the target database, such as the release candidate, immediately after it has passed all tests
2. Clean a build environment and apply the same migrations that were applied to the target database.
3. Take a snapshot of the freshly built database.
4. Compare the two snapshots to detect any differences.

To run this type of check, the check -drift command might look like this:

This defines the build environment in which to recreate the expected schema, and the set of migration IDs that should be used to recreate it, which is the migrations already applied to the target environment (‘main’). We retrieve these from the Flyway info command. Note that rebuilding from source can be slow if there are many migration scripts to run.

The resulting drift report (JSON and HTML) will display full details of any drift from the ‘expected state’, defined by the migrations.

This method assumes that the migrations still accurately represent the expected state of the target database at the point of last deployment, and many teams will have version-control processes in place to ensure this is guaranteed.

But what if they don’t, the drift check can at least give some warning of trouble. In the following simple example, the changes were deployed to the test database, a snapshot was taken of what was tested, but then subsequently someone made a change to a Repeatable migration in the source and to a view in the target database. Both are reported as a drift from the expected state, but one is drift in the target and the other is “drift in the source”.

In both cases, what is shown in “Actual”, on the right, is what was tested. The view change needs to be backported into the source. For the stored proc, you would need to repeat the tests (or, if possible, delay releasing the source change).

A PowerShell example

The following PowerShell example uses the migration files as the definition of the expected state. However, it would be very easy to adapt to the stored snapshot technique. The target database environment (‘main’, in this example) and the ‘build’ environment can be defined in the project-level TOML file (flyway.user.toml) like this:

However, I prefer to take the connection information from TOML files stored in the safe user area, as demonstrated in the following script. Having gathered the necessary credentials for each environment, it recreates the build environment from the list of migrations applied to the target, which is used as the source database in the drift check against the target (‘main’):

Here is the resulting HTML report:

If you drill down, you can see the differences.

In this case, the differences aren’t significant. They are entirely due to the fact that any constraints that are declared without an explicit name are given a slightly different name when regenerated to guarantee uniqueness. There is a schema comparison setting called IgnoreSystemNamedConstraintAndIndexNames that will avoid this.

Refining what gets compared

By default, Flyway reports all differences, but some objects, like documentation tables or static data, might not be relevant to drift detection. To refine the comparison, you can use Flyway’s configuration options or provide a filter file to exclude certain objects when running the drift check.

Resolving Drift

Once drift has been identified, you have three options:

1. Revert the changes – Generate a ‘synchronization’ script that restores the database to the expected version and apply it before proceeding with the next deployment.
2. Ignore irrelevant changes – If the drift is not meaningful (e.g., minor documentation updates), simply proceed with the planned migration, knowing that the changes are ephemeral and will be automatically discarded in the next deployment unless included in a subsequent migration.
3. Incorporate the changes – If the drift represents meaningful updates, generate a migration script from the differences, add it to source control, to include it in future deployments. Then re-start the deployment pipeline, including tests, with the new version

Running drift checks during development

The check -drift command is useful way to run automated drift checks in a Flyway deployment pipeline. However, it’s equally important that developers check the expected state of a version of the database throughout development.

Imagine, for example, that the team agrees that version v1.3.11 in version control is a release candidate and they send it for QA. During testing, someone spots a problem and applies a quick manual fix. It passes testing and is approved for release. Later, the deployment process packages v1.3.11 from version control, but that version doesn’t include the fix, because it was never added as a versioned migration. Alternatively, as discussed earlier, if someone updates a repeatable migration after a test environment is deployed, then, without any other controls in place, we might risk an untested change in the subsequent release. In either case, we increase the risk of deployment failures.

Drift detection during the development process helps prevent this kind of inconsistency between Flyway versions and, especially, between what was tested and approved and what is released.

Ad-hoc drift checks using the diff command and snapshots

Flyway provides several methods for checking database drift in addition to the check -drift command and supports several ways of representing the source and target versions of the database, depending on how the team prefer to define the ‘expected state’ of a database at any given schema version. This might be a post-migration snapshot, an immutable reference database, a schema model in source control, or the migration files.

When checking for drift as part of the development process, I often find it simpler and quicker to use Flyway’s -diff command and directly and explicitly define both -source and -target depending on what needs to be checked.

I generally use per-version snapshots as the ‘source of truth’ for what a particular version should look like. I use a callback to capture and save a snapshot automatically, each time Flyway creates a new version. As these snapshots should be immutable, it may be best to avoid overwriting the snapshot for a particular version. You could usefully lodge it in version control along with the migrations. If a disaster makes it necessary to redo part of a migration, then the related snapshot is best removed first.

The demo code

In the following demo, we’ve a directory for each version with subdirectories for each type of artefact.

In the following drift check, we have selected the snapshot for the release candidate as the source, and the build database as the target, which contains a build of the release candidate version from source control. We want to be sure that the version that we’ve tested for release hasn’t changed since testing!

Summary

Flyway makes drift detection straightforward, whether by comparing stored snapshots or recreating the expected state in a build environment. By using these tools effectively, teams can ensure database integrity, catch unauthorized changes early, and maintain a clear version history.

 

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more