Description

Compares two comparison sources and returns a summary of the differences. A comparison source can either be a database environment, build environment, a schema model folder, a snapshot or empty type. Additionally, the diff command will generate and store the differences between the two comparison sources in the artifact that can subsequently be used with the generate, model and diffText commands.

For more information see Database comparisons

Usage Examples

Comparing an environment against a schema model

Here the environment will contain a database with a single view and table, view1 and table1 respectively. The schemaModel will have the same table1 with modifications, and an additional table2 table. The diff command can then be used to compare the dev environment to the schema model which would list out the object level differences between them.

> flyway diff -source=dev -target=schemaModel -artifactFilename=.\diffArtifacts\artifact

Flyway 11.8.0 by Redgate
diff artifact generated: C:\Users\Projects\diffArtifacts\artifact
+-----------------------------+--------+-------------+--------+----------------+
| Id                          | Change | Object Type | Schema | Name           |
+-----------------------------+--------+-------------+--------+----------------+
| mYwo9SPg2QEXPt8NaQlzTOW585o | Add    | View        | HR     | view1          |
| LEIqXw5rFvQUxosOl_zdDfBYyUA | Edit   | Table       | HR     | table1         |
| uRqqxnJVd2ostltNTo8j1WAWuCQ | Delete | Table       | HR     | table2         |
+-----------------------------+--------+-------------+--------+----------------+

Comparing a snapshot against an empty source with JSON output

In this example a snapshot has to be generated prior to performing diff. The snapshot is a JSON file that could be generated by performing the snapshot command against an environment/schemaModel. In this scenario the snapshot is of an environment which contains two tables, Table_1 and Table_2. The diff command will print the above two tables as differences.

> flyway diff -source=snapshot:./snapshot/snapshot.json -target=empty -artifactFilename=.\diffArtifacts\artifact -outputType=json

Flyway 11.8.0 by Redgate
diff artifact generated: C:\Users\Projects\diffArtifacts\artifact
{
  "differences" : [ {
    "id" : "0Vq7ldnZ06ES23tylJKD2KHrM5M",
    "differenceType" : "Add",
    "objectType" : "Table",
    "from" : {
      "schema" : "dbo",
      "name" : "Table_1",
      "definition" : null
    },
    "to" : null
  }, {
    "id" : "fvGooqeU94wq6HTCoAUlwvkd_bc",
    "differenceType" : "Add",
    "objectType" : "Table",
    "from" : {
      "schema" : "dbo",
      "name" : "Table_2",
      "definition" : null
    },
    "to" : null
  } ],
  "sourcePreparationInfo" : null,
  "targetPreparationInfo" : null
}

Comparing a build environment against an environment after migrating the build environment to a specified build version with rebuild

To perform this scenario of diff as a prerequisite the build environment has to be configured to use the clean provisioner in order to reprovision the environment upon setting the rebuild flag to true. The migration directory contains four migrations, V1__first.sql, V2__table1.sql,V3__view1.sql and V4__table2.sql with the target environment not being migrated to these versions. The build environment contains a table table_3 in the schema tested in this example.

As a result of this operation the build environment would be cleaned and all migration scripts including the build version 3 specified would be run and then be compared against the target environment which contains only the schema and schema_history table. The differences listed out would contain only the objects created from the migrations scripts specified as ADD type changes since the build environment is rebuild.

> flyway diff -source=migrations -target=prod -buildEnvironment="build" -buildVersion="3" -rebuild=true -artifactFilename=.\diffArtifacts\artifact

Flyway 11.8.0 by Redgate
INFO: Successfully dropped post-schema database level objects (execution time 00:00.059s)
Database: jdbc://url
Schema history table [HR].[HRSchema].[flyway_schema_history] does not exist yet
Successfully validated 3 migrations (execution time 00:00.099s)
Creating Schema History table [HR].[HRSchema].[flyway_schema_history] ...
Current version of schema [HR]: << Empty Schema >>
Migrating schema [HRSchema] to version "1 - first"
Migrating schema [HRSchema] to version "2 - table1"
Migrating schema [HRSchema] to version "3 - view1"
Successfully applied 3 migrations to schema [HRSchema], now at version v3 (execution time 00:00.228s)
diff artifact generated: C:\Users\Projects\diffArtifacts\artifact
+-----------------------------+--------+-------------+----------+-----------+
| Id                          | Change | Object Type | Schema   | Name      |
+-----------------------------+--------+-------------+----------+-----------+
| cFfTuinTzlVluhBSmi5ZXQB4kSA | Add    | Table       | HRSchema | allTables |
| a.o0T8ULN8u.YVZIz6UVT7dbhC0 | Add    | Table       | HRSchema | table_1   |
| .4vZ5aLCnQIafQFb5T5O2MkLX6U | Add    | View        | HRSchema | view_1    |
+-----------------------------+--------+-------------+----------+-----------+

Comparing a build environment against an environment after migrating the build environment with a list of migrations

The migration directory contains four migrations, V1__first.sql, V2__table1.sql,V3__view1.sql and V4__table2.sql with the target environment not being migrated to these versions. The build environment contains a table table_3 in the schema tested in this example.

As a result of this operation only versions 2 and 4 will be applied to the build environment, which will then be compared against the target environment which contains only the schema and schema_history table. The differences listed out would contain only the objects created from the migrations scripts run as ADD type changes since the build environment is rebuilt.

> flyway diff -source=migrations -target=prod -buildEnvironment="build" -buildCherryPick="2,4" -artifactFilename=.\diffArtifacts\artifact

Schema history table [HR].[HRSchema].[flyway_schema_history] does not exist yet
Successfully validated 4 migrations (execution time 00:00.083s)
Creating Schema History table [HR].[HRSchema].[flyway_schema_history] ...
Current version of schema [HRSchema]: << Empty Schema >>
Migrating schema [HRSchema] to version "2 - table1"
Migrating schema [HRSchema] to version "4 - table2"
Successfully applied 2 migrations to schema [HRSchema], now at version v4 (execution time 00:00.137s)
diff artifact generated: C:\Users\Projects\diffArtifacts\artifact
+-----------------------------+--------+-------------+----------+---------+
| Id                          | Change | Object Type | Schema   | Name    |
+-----------------------------+--------+-------------+----------+---------+
| a.o0T8ULN8u.YVZIz6UVT7dbhC0 | Add    | Table       | HRSchema | table_1 |
| 1gvOcO43loujJCPktuUkhBMFbSI | Add    | Table       | HRSchema | table_2 |
+-----------------------------+--------+-------------+----------+---------+

Parameters

Required

Optional

Universal commandline parameters are listed here.

Settings from the following sections of the Flyway namespace can be specified in relation to database connections:

• General settings
• Settings in database-specific namespaces
• Settings in secrets management namespaces

When the source or target is migrations is used, settings from the following sections of the Flyway namespace can also be set:

• Migration location and naming settings
• Migration reading settings
• Migration execution settings
• Flyway schema history settings
• Placeholders

JSON output format

{
  "differences": [
    "differenceType": "Add",
    "objectType": "Table",
    "from": {
      "schema": "dbo",
      "name": "Table1",
    },
    "to": null
  ],
  "sourcePreparationInfo": null,
  "targetPreparationInfo": {
    "sourceType": "migrations",
    "provisioner": "clean",
    "migrationState": {
      "pending": 1,
      "applied": 1,
      "failed": 0,
      "total": 1
    },
    "reprovisioned": true,
    "targetSchemaVersion": "001",
    "migrationsExecuted": 1,
  }
}

Error codes

This command can produce the following error codes:

• Generic error codes
• Comparison error codes