EDITION: ENTERPRISE

Flyway Desktop allows you to make changes directly to your development database while you are working on features and improvements. When you are happy with your changes you can capture them in the schema model.  Once you are ready to deploy your changes, you can generate migration scripts.  This compares the current state of the schema model folder on disk to the end result of running all your project migration scripts against the Shadow Database.  This captures all development changes which have not been included in a migration script yet.  Once saved in your project, migration scripts can be deployed to other environments, or committed to your version control system and used in your deployment pipeline.

Generating migrations from the Schema model

1. Make changes to your development database
   In this example below, we are adding a new column called `TWITTER_ID` to the `CUSTOMERS` table and updating the `GetCustomers` procedure to return this new column.
   
   
2. In Flyway Desktop, save the changes to your Schema model.
   
   
3. Navigate to the Generate migrations tab
   
   If the project does not specify a shadow database source, you will have to setup your shadow database.
   
   
   In the connection dialog, specify your shadow database connection details, check the box to confirm it's ok to erase the data and objects in this database, and click Test and save.
   
   
   
4. There's information that shows how long ago the project was refreshed. If there are any changes in your schema model, a little red asterisk will appear on the Refresh button.
5. 

After clicking Refresh the changes are shown:

2. Review the differences

Click on the objects in the top list to show the differences for that object at the bottom.  This allows you to see exactly how each object will change.  You can also sort the columns by clicking on the different column headers.

3. Generate scripts for the selected objects.

Select each object that you are ready to script out changes for.  You can select all objects by using the checkbox in the column header. Then click Generate scripts in the top-right.

You can multi select objects using Shift + Click.

4. Review scripts and update the version and description 

This next page allows you to review the generated scripts.  If your changes involve an object that stores data, such as a table, then a versioned migration script will be created.  The version number will automatically incremented, and a timestamp will be included to minimize the chance of merge conflicts.

You can specify your own version number (e.g., maybe this is the next major version, V2.0).  You can also provide a clearer description, which is the second part of the filename, so it's more obvious what change is contained in the script.  You can also customize the generated versioned migration script.  An example of this is if a new NOT NULL column is added without a default, and an initial value needs to be set for existing records.

See an example about setting your own versioning numbers. 

You can learn more about the different migration scripts in our Terminology Reference.  

Once you're happy with your changes, click Save. This will write the scripts on the screen to your project folder on disk.

5. Review the summary

The summary page confirms which scripts were generated and their names.

Next steps

• Commit the files to version control using the Version control tab or your VCS's command line or preferred tool.
• Once your changes are in version control, they can be included in your CI/CD processes.  Learn more about this at Deploying database changes via a pipeline.

Generating undo scripts 

You can also generate undo scripts automatically alongside your versioned migrations. The undo script compares the Shadow Database, which is the result of the migration scripts in the projects, to the Schema model folder on disk.  This will capture the schema differences between these two states and give you a starting point to undo the changes in the corresponding Versioned migration script.  You may need to be manually edit the Undo migration script to ensure data integrity, capture data changes, or other optimizations.

The first time you generate a migration script in the project, you will be asked if you also want to generate undo scripts.  You can change your mind later in the settings cog in the top right:

→

Here's additional important information to understand when using Undo migration scripts.

Writing your own migration scripts 

You can also add your own migration scripts in the project folder.  Make sure you are following your project's naming convention (V###__<description>.sql,  R__<description>.sql, or U###<description>.sql).  This can be useful for migration scripts that deal with updating transactional data or for scripts that are supplied to you by someone else.  

After opening a project, use the folder icon in the top right to jump to your project on disk:

You can also add new migration scripts on the Migrations tab:
→ 

Organizing your migration scripts 

Once you save your migration scripts, you can organize them into subfolders under the project's migrations folder as you like.  All subfolders will be processed.  The order of execution still relies purely on the version number in the script regardless of what folder it is in.  

After opening a project, use the folder icon in the top right to jump to your project on disk and get to the migrations folder.  Create and move your scripts into subfolders to organize them:

If you would like to see this capability within the GUI, please vote/comment on our suggestion forum.

Additional information

• Configuring the shadow database
• Creating a baseline for existing databases
• Setting your version numbering
• SQL Server - Working with Memory Optimized Tables
• Generated scripts that may need extra review