This example follows on from using the CLI to save database changes to the Schema Model how-to guide.  In this example, we'll capture all the changes in the Schema Model that haven't been included in a migration script for deployment in a migration script.  We'll also generate the corresponding undo script for these changes.

Setup

1. Complete the Use the CLI to save database changes to the Schema Model how-to guide.  Or, you can use the below as a guide to create your own script.

Generating migration scripts for Schema Model changes

1. Link your shadow database, which will be used to apply all exisitng migrations too so differences can be generated.
   TODO
   
   Your flyway.user.toml file will look like (with your own server/instance name):
   

   [environments.development]
   url = "jdbc:sqlserver://localhost;instanceName=SQLExpress;databaseName=AutoPilotDev;encrypt=true;integratedSecurity=true;trustServerCertificate=true"
   displayName = "Development database"
   
   [environments.shadow]
   url = "jdbc:sqlserver://localhost;instanceName=SQLExpress;databaseName=AutoPilotShadow;encrypt=true;integratedSecurity=true;trustServerCertificate=true"
   displayName = "Shadow database"
   provisioner = "clean"
   
   

   
   
   
2. In a command prompt at your project folder, run the following command to see all the differences between your SchemaModel and the result of running all your existing migration scripts on the Shadow Database.  In this example, we expect two changes:
   1. A new MiddleName column added to the Customer.Customers table 
   2. A new stored procedure to get Customer information by last name
   

   flyway diff -source=schemaModel -target=migrations -buildEnvironment=shadow

   
   The output will be a  diff artifact, which looks like:
   
   
   Nothing has been written to disk yet.
   
   
3. Optional, see more details about how each object in the diff artifact has changed.
   

   flyway diffText

   
   The output will look like:
   
   
   
4. Generate the next versioned and undo scripts for these changes. 
   

   flyway generate -types=versioned,undo -description=Added-Customers-MiddleName

   
   The output will look like:
   
   
   These files are saved to the migrations folder.
   
   
5. At this point, you could use the git command line to commit your migration scripts to your local repo.
   

   git commit -am "Migration scripts for adding MiddleName to Customers"

   
   
   
6. You could use the git command line to push to your remote repository or create a PR/MR.

Next Steps

1. Learn more about all the Flyway Commands.
2. Learn more about Flyway and other Redgate solutions with video training on Redgate University.