Redgate Flyway

ENTERPRISE

Feedback is welcome as we continue to improve this example. Email the development team if you are using any of the approaches below or for any questions.

Flyway tracks static data at a table level. You can configure which tables contain static data and limit what columns are tracked in Flyway Desktop. These tables/columns can be included in version control and included if you are generating versioned migration scripts.

At this point, you can not specify a subset of rows. If you need this capability, vote on this suggestion to help us prioritize this work.

In the meantime, you can write your own Versioned or Repeatable migration scripts to deploy the exact data you need between environments or use our comparison tools (SQL Compare or Data Compare for Oracle) to compare and synch a subset of rows in the tables.

First, setup the project in SQL Data Compare:
A where clause can only be specified in the SQL Data Compare GUI.

Create a project
1. Source = development database
2. Target = target database
Select which tables you're interested in
Filter the comparison using a WHERE clause
Run the comparison to check that it returns the results you want.
Save the SQL Data Compare project as a sdc file.
Save the sdc file to the same repository as your Flyway project. We suggest an "other-scripts" folder.

Automate:

You have the following options.

Run the SQL Data Compare CLI directly and override the target for each of your environments.
See if there are any differences:

sqldatacompare /Project:"<projectLocation>\<projectName.sdc>" /Server2:"<targetServer"> /Database2:"<targetDatabase>" /UserName2:"<targetUserName>" /Password2:"<targetPassword>"

Synchronize the differences:

sqldatacompare /Project:"<projectlocation>\<projectname.sdc>" /Server2:"<targetServer"> /Database2:"<targetDatabase>" /UserName2:"<targetUserName>" /Password2:"<targetPassword>" /synch

Generate a script that will synchronize the differences:

sqldatacompare /Project:"<projectlocation>\<projectname.sdc>" /Server2:"<targetServer"> /Database2:"<targetDatabase>" /UserName2:"<targetUserName>" /Password2:"<targetPassword>" /ScriptFile="<sdcScript.sql>"

Create a pipeline that can be manually or automatically triggered and execute the SQL Data Compare CLI (as above). SQL Data Compare would need to be installed on your pipeline's agent or use the SQL Data Compare docker image.
1. You can save the output to a script.
2. Then deploy the script using flyway:
  deploy -scriptFilename="<sdcOutputScript>" -environment=<definedTargetEnvironment>
Use a flyway afterMigrate callback to execute this. SQL Data Compare would need to be installed on your pipeline's agent or use the SQL Data Compare docker image.
1. In your Flyway project's migration's folder, create a file called afterMigrate.sh that contains the CLI command above.
2. When you run flyway migrate, it will deploy the pending versioned migrations, any repeatable migrations that have changed, and then executes the afterMigrate script, which will run the SQL Data Compare project and synch the data changes in the tables/columns/rows you specified in the SQL Data Compare project.

Learn more about the SQL Data Compare CLI.

First, setup the project in SQL Data Compare:
A where clause can only be specified in the SQL Data Compare GUI.

Create a project
1. Source = development database
2. Target = target database
Select tables you're interested in comparing
Filter the comparison using a WHERE clause
Run the comparison to check that it returns the results you want.
Save the Data Compare for Oracle project as a dco file.
You should probably save the same repository as your Flyway project. We suggest a "other-scripts" folder

Automate:

Use the Data Compare for Oracle CLI to automate these changes.

Run theData Compare for Oracle CLI directly and override the target for each of your environments.
See if there are any differences:

dco /project:"<projectLocation>\<projectName.sdc>" /target:"<targetInformation>"

Synchronize the differences:

dco /project:"<projectlocation>\<projectname.sdc>" /target:"<targetInformation>" /synch

Generate a script that will synchronize the differences:

dco /project:"<projectlocation>\<projectname.sdc>" /target:"<targetInformation>" /ScriptFile="<sdcScript.sql>"

Create a pipeline that can be manually or automatically triggered and execute the Data Compare for Oracle CLI (as above). Data Compare for Oracle would need to be installed on your pipeline's agent or use the SQL Data Compare docker image.
1. You can save the output to a script.
2. Then deploy the script using flyway:
  deploy -scriptFilename="<sdcOutputScript>" -environment=<definedTargetEnvironment>
Use a flyway afterMigrate callback to execute this. Data Compare for Oracle would need to be installed on your pipeline's agent or use the Data Compare for Oracle docker image.
1. In your Flyway project's migration's folder, create a file called afterMigrate.sh that contains the CLI command above.
2. When you run flyway migrate, it will deploy the pending versioned migrations, any repeatable migrations that have changed, and then executes the afterMigrate script, which will run the Data Compare for Oracle project and synch the data changes in the tables/columns/rows you specified.

Learn more about the Data Compare for Oracle CLI.

Write your own Versioned or Repeatable migration scripts to deploy the exact data you need between environments.

Redgate Flyway

Tutorial - Using Redgate's Data Compare to limit rows