Tutorial - Track static data for schema model and migrations
Published 24 January 2025
EDITION: TEAMS
- Open your project in Flyway Desktop. We currently support SQL Server and Oracle projects. We have a Preview available for PostgreSQL.
- On the Schema model tab, click on the Manage Static Data button.
- Using the drop down, select the tables that you want to identify as containing static data from the list. You can start typing in the select box to more easily find the static tables you're interested in tracking:
- Click + Track selected tables and close the drop down to see the tables you selected in the list below:
- Optionally, you can select a table from the list and choose what columns to include in version control. This could come in handy if you have a CreatedDate column that automatically gets populated when the record is created. Therefore, this column may differ across environments.
- Click Save changes.
- Since this is the first time identifying these tables as static data, they will appear in the list of schema model changes. You can click on the table name with the "Data" object type and see the data at the bottom. Select the changes you're ready to Save to project to save this changes to disk so they can be tracked in version control.
Any new changes to static data that have been saved to the project will be displayed on the Generate migrations tab and can be selected to generate INSERT/UPDATE/DELETE statements in a versioned migration script. If you're project is setup to generate undo scripts, the corresponding undo changes for the static data will also be included in the undo migration.
→
Now, the process is similar to making schema changes. Edit the data directly in your development database. Changes to static data will show up in the Schema model. Once saved to the project, migrations can be generated for deployment.
You can configure static data by adding tables to track to the toml config file as follows
- [[redgateCompare.staticDataTables]]
- schema = "dbo"
- table = "Table_1"
- [[redgateCompare.staticDataTables]]
- schema = "dbo"
- table = "Table_2"
- excludedColumns = [ "col2" ]
With this configuration, data changes will be picked up alongside schema changes when running the diff command and any associated command acting upon the diff artifact.