FLYWAY ENTERPRISE

1. After a successful deployment, use flyway snapshot to save the state of the database you just deployed to in a snapshot file.  Save this snapshot to a location that is accessible to your release pipeline.
   

   flyway snapshot -source=Prod -filename=deployedSnapshot.json

   
   In the example above, "Prod" is the name of a database environment that is defined in the TOML file  and provides the connection information for the database that you want to capture in a snapshot file.
   
   
   
2. Before doing a deployment, use flyway check -drift to compare the snapshot saved above to the target database you're about to deploy to.  If there are any changes, then your target database has drifted and it no longer matches the expected state captured above after your last deployment.
   

   flyway check -drift -deployedSnapshot="deployedSnapshot.json" -environment="Prod"

   
   In this example, 1 object has changed since the snapshot was taken.  Drilling down, you can see the exact changes - a new "DriftedColumn" was added to the Customers table. 
   
   
   
3. To check for drift in an automated process, use flyway diff with a JSON output instead.
   

   flyway diff -source=snapshot:deployedSnapshot.json -target=Prod -outputType=json

   
   If there are not any differences, then the target database hasn't drifted, which means the state of the objects has not changed since you took the snapshot.  The output looks like:
   

   {
     "differences" : [ ],
     "sourcePreparationInfo" : null,
     "targetPreparationInfo" : null
   }

   
   If there is are differences, then the target database has drifted.  You may want to stop the release or review these changes carefully before deploying so the changes are not reverted during the state-based deployment. The output looks like:
   

   {
     "differences" : [ {
       "id" : "z9tJ1q3EXzZNagsTvLbcCJf5tgA",
       "differenceType" : "Edit",
       "objectType" : "Table",
       "from" : {
         "schema" : "dbo",
         "name" : "Customers",
         "definition" : null
       },
       "to" : {
         "schema" : "dbo",
         "name" : "Customers",
         "definition" : null
       }
     } ],
     "sourcePreparationInfo" : null,
     "targetPreparationInfo" : null
   }

4. In an automated pipeline, you could use jq to query the JSON results to ensure nothing has drifted before proceeding with the deployment.  Learn more about jq.
   

   IF (jq .differences | length > 0) THEN
        -- Stop the pipeline; drift has been found and needs to be reviewed
    
   ELSE
        -- The database has not drifted; it still matches the state from when the snapshot was taken