Published 11 October 2021
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
- 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.
- In Flyway Desktop, save the changes to your Schema model.
- Navigate to the Generate migrations tab
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.
- 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.
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.
Make sure that only your changes are selected if your team is using a shared development database.
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.
Timestamps are included in versioned migration filenames by default. Flyway Desktop cannot apply two migrations with the same version number, which can happen when multiple developers are merging parallel migrations. The sequential timestamp ensures that even identical version numbers are still able to be applied to a database, since their timestamps will almost certainly differ.
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.
Flyway Desktop will not permit saving migrations that do not conform to the migration naming convention specified in your config. The defaults can be found here.
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.
- 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.
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 option to generate undo scripts can be configured per project. Within a project, configure this option using the cogin the top right corner.
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 (
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