Automatically Tracking and Deploying Static Data in Flyway Enterprise
Static data is often required for the basic functioning of a database and any dependent applications. Therefore, it's vital that we can track this static data to understand how, when, and why it changed, and that we include any static data changes in our database deployments. Flyway Enterprise will now do both tasks automatically.
Managing Static Data
Redgate Flyway updates a database by running a series of versioned migration scripts that describe the sequence of changes required to move a database from one version to another. Generally, these scripts will contain the DDL code to create a table, alter a column, modify a stored procedure and so on. However, we can’t build a functioning database just from the DDL code. Most databases also require static data, sometimes referred to as configuration data, reference data, or lookup data.
A common example of static data is the need to keep a list of states and their abbreviations in a table to use in your application’s dropdown menus. Other examples could be static data that drives how your application works or determines what error messages are displayed. Static data must be kept in the version control system, with the DDL code, because if it changes without us knowing, or we neglect to include it in a deployment, it can cause broken applications and downtime.
Managing and deploying static data has always been possible in Flyway because users could write their own SQL migration scripts for the task. For example, Managing Static Data in Flyway Database Development describes a technique that stores the static data in views, based on derived tables, and then deploys it using versioned migrations. This means that a change in static data is reflected in a change in version, which is what we want (unlike changes to development data, which shouldn’t affect the database version).
Flyway Enterprise makes this task much simpler. We just want inform Flyway which tables contain static data and then let it manage the rest.
Identifying and Tracking Static Data
Users specify which tables contain static data as part of their Flyway project. We can do this in Flyway Desktop (Flyway’s development GUI) on the Schema Model tab:
We capture any static data that has already been deployed in a Data folder of the Schema Model directory in the project, and then users can commit static data directly to the version control repository, from the version control tab.
This will allow you to see the initial set of data and how it changes. If we add or alter static data in the development database, Flyway Desktop will automatically detect it, highlighting what data changed and how, so we can save it to the project.
Deploying static data
Once your static data changes are saved to the Flyway project, and committed to version control, Flyway will generate a migration script to capture the changes. These migration scripts are then used in your automated deployment pipelines. So, in addition to the object level database changes (tables, functions, procedures, etc.), any new migration scripts containing Static Data changes will also be generated.
The INSERT
, UPDATE
, and DELETE
static data changes appear in Flyway’s check dry run report. This report allows Database Administrators (DBAs) to see the SQL script that will be run on the target environments. Teams can set up a manual intervention step for DBAs to review the SQL script and approve it before deploying to Production.
Evolving Static Data Support in Flyway
Our goal for Redgate Flyway is a x-RDBMS DevOps solution for including your database schema and static data changes in version control and deploying them reliably.
This static data feature is currently limited to SQL Server, Azure SQL, and Oracle databases. We are planning to add advanced support for Static Data in PostgreSQL and MySQL later in 2023. Please contact our development team to learn more about the workaround for Static Data in PostgreSQL and to help us prioritize this and other features.
To learn more about Flyway and start a free trial, visit our website.