When capturing database changes in version control to use in automated deployments, it's important to also capture transactional or static data changes as well as schema object changes.  Transactional data could be specifying a default when adding a new column or updating transactional data already in a Production database, e.g., increasing salaries by x% or splitting a column into two new columns.

In addition, it is important to version control and deploy changes to static data as well.  Static data is also known as configuration or lookup data.  It's usually small sets of data that need to be deployed alongside the application changes for the application to run correctly.  This can include data such as zip/postal codes, country lists, or even application settings.

Static Data in Flyway Desktop

Static data can now be configured for Oracle and SQL Server projects using the enterprise edition of Flyway.  Learn more.

Capturing data changes as part of a versioned migration script

When you Generate migrations using Flyway Desktop, you select which changes in your schema model folder that you want to create migration scripts for, i.e., which changes are ready to be committed to version control as migration scripts.  After selecting these changes, there is a review step.  The changes are shown in a versioned migration script at the top of this page and are in the correct order from a dependency perspective.  In addition to specifying the exact version and filename you want, you can also edit this script to include any data changes as well.  Since this script was generated from changes already on your development database, it will not be re-run against the development database.  Most users will write and execute the data change script against the development database in the IDE of their choice and then just copy and paste it into the script at this point.  This script can then be committed/pushed to version control and the whole script will be run in CI/CD environments (learn more about Deploying database changes using Flyway).

If you are using Undo scripts, make sure you update any data changes in the corresponding Undo script as well.

Adding a new versioned migration script with data changes to the project folder

If you need to add a versioned migration script that doesn't correspond with any table schema changes (e.g., updating some transactional data), you can use the Migrations tab to add additional migration scripts to your project.  This automatically picks up the next Version name for you. 


Or, you can navigate to the project folder using the blue folder icon in Flyway Desktop and create or save your change script in the migrations directory using the correct filename.  Make sure the filename starts with a capital V, the number, which corresponds to the order you want it executed in, two underscores, and then a description, e.g., V003_20201022__UpdatingJobTitles.sql.

If you are using Undo scripts, make sure you add a U script as well to back out these changes as fit for your environment.  In some cases, you might not want/need to undo the data changes.

Advanced Options

• Using Repeatable Migrations to manage data
• Using Script Migrations to manage data
• Using Redgate's Data Compare technology to version control and generate migration scripts 
• Different data for different environments or clients