Static data

EDITION: TEAMS EDITION: ENTERPRISE
One thing to consider when working with databases is how to maintain static data in your project.  This is also referred to as reference data, configuration data, or lookup data.  This can include data such as a list of Countries, U.S. States, or even application settings.  This static data should be tracked in version control so users can understand how, when, and why it changed over time and who made the changes.  It's also important that this data be deployed alongside other database changes.  By capturing static data changes with Flyway in Versioned Migration Scripts, your static data can be tracked and deployed as part of your same version control repository and deployment pipelines.

Tracking static data

EDITION: TEAMS
On the Schema model page, click Static data & comparisons to configure the relevant tables that contain static data.  Once selected, these data changes will show up alongside schema differences.

Primary Key requirement

In order to track the data within your tables, the table must include a Primary Key, which is used for the comparison. If a Primary Key is not present, the data within the table will be ignored.  If possible, add a Primary Key to the table to use the Static Data feature.  This will help performance when comparing the data.  If this is not possible, please add a suggestion/vote to support Static Data for Tables without a primary key and you need this.  

Workaround: Use our data comparison tools for SQL Server and Oracle, which come with Flyway Enterprise, to compare your development database to the schema-model in your local repository.  On the Tables & Views tab, you can specify a comparison key that is unique and save these changes to the schema-model on disk. 
You can then Generate a deployment script using our data comparison tools.  In Flyway Desktop, go to the Migrations tab and click Add a Migration. You can then import or copy and paste the into a new versioned migration script.  This change can then be version controlled and deployed through your CI/CD process just like other versioned migration scripts. 

Controlling static data

EDITION: ENTERPRISE

In Flyway Enterprise, you can also control exactly what columns are included in version control.  Some tables may include a CreatedDate or LastUpdatedDate, which differs across environments, so that data should not be versioned and deployed.   

Flyway Enterprise also give you access to customize the Data Options.

Baselining static data

If you are using migrations-based deployments, it is important to include any static data that has already been deployed to your downstream environments as part of a baseline.  This means new environments can be built from scratch using the scripts in the repository.  This also means we won't generate a new migration script for static data that already exists in your downstream environments.

Different data for different environments or clients

Some teams want to capture some data scripts that are only executed in Test environments.  Some teams need to have certain data for Client 1 and other data for Client 2.  This is possible in Flyway.

There are options:

• Use different folders for your different targets

• Use placeholders within your migration scripts

• Use placeholders and script configuration files

• Create multiple projects

  • Have a project for your core scripts.

  • Have a different project for your test scripts.

  • Have a different project for your prod scripts.
  • For deployment, deploy core and test projects in your test environments.  Then deploy core and prod in your staging and production environments.

Note: If different scripts or different parts of scripts are ran on different environments, then you are not testing the exact deployment script that will be run on Production as part of your pipeline.  If possible, create a Staging/Pre-Production environment that matches Production as close as possible and setup your pipeline to deploy to this environment first to try and catch issues before going to Production. 

Transactional data

Updates to transactional data may also need to be deployed from time to time.  For example, if you need to fix a data issue in the Production database or maybe you need to apply a percent increase across your product listings. 

If you are using migrations-based deployments, it is possible to handle these data manipulation scripts by editing a migration script after it has been generated by schema changes.  If the data changes are not also associated with schema changes, then it's best to just add a new migration script to capture this change.

If you are using state-based deployments, then you will need to use a pre/post deployment script for this.  Please get in touch with our Development Team to learn more about this

.  

Other approaches to handling data

Using repeatable migrations to manage data

Flyway also supports repeatable migrations that will execute on the target environment after all the pending migration scripts run every time the contents in the repeatable migrations change.  You can write an idempotent script to manage data or use MERGE if your database supports that.

Read an example of using PowerShell to Bulk Load Data in SQL Server.

Using beforeMigrate/afterMigrate/afterMigrateApplied callbacks

If you are interested in this approach, learn more about Callback Events. You can write an idempotent script to manage data or use MERGE if your database supports that.

Using script migrations to manage data

Flyway Teams/Enterprise also supports the use of scripting languages as migrations (e.g., Bash or PowerShell).  A script migration could be used to load data as part of a migration (e.g., V001.001__InsertTestData.bat). 

See this tutorial for an example using Oracle SQL Loader.

Read an example of using PowerShell to Bulk Load Data in SQL Server.

Using Redgate's Data Compare technology to version control and generate migration scripts 

You can use our Data Compare technology as part of Flyway Enterprise to capture changes and generate a deployment script for data changes in static/lookup tables.  This can be useful if your data sets are large and do not change frequently and you don't want to check for data changes every time you refresh in Flyway Desktop.

You can use the comparison tools to synch to a scripts folder and version control how the data changes over time.  You can also compare your development database to a scripts folder containing the latest version from version control or to a downstream database like Production to generate a deployment script that captures any changes to your static data.  Once you generate the deployment script, simply save it into your project folder using the naming convention (V<number>__<description>.sql).  The versioned migration script can then be committed and pushed to version control and flow through your automated deployment process to your downstream environments (e.g., Test, QA, Stage, Production).

Learn more about how to use Data Compare for Oracle or SQL Data Compare, which are part of Flyway Enterprise.  Both of these tools have a GUI, command line, and Docker containers.

Relevant tutorials

• Tutorial - Track static data for schema model and migrations
• Tutorial - Baseline static data for new projects
• Tutorial - Baseline static data for projects that have already been deployed to downstream environments
• Tutorial - Use different data migration locations for your different targets
• Tutorial - Use placeholders to allow for conditional data insertion
• Tutorial - Handle transactional data changes as part of a versioned migration script
• Tutorial - Add a new versioned migration script with data changes to the project folder
• Tutorial - Use repeatable migrations to manage data
• Tutorial - Use Oracle SQL*Loader to Load Data with Flyway