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.
Tools in this post
You may also like
-
Live training session
Level up your database deployments | Safely migrate sanitized production data with Flyway
SQL Provision and SQL Data Catalog enable you to refresh every stage of your development process in seconds with sanitized production data, but how do you ensure everyone is working from the same version and then safely migrate your deployments? In this 30-minute training session, Redgate Solutions Engineer David Ong will show you how Flyway introduces version control for your database, so you can migrate with ease and confidence.
-
Article
Undoing Actions on Groups of Database Tables
During development you'll occasionally need an undo script that drops a group of tables, or you might need to truncate a group of tables and then insert fresh data in order to run some tests. Unless you perform the required actions in the correct dependency order, you'll be tripped up by foreign key constraint violations. This article provides a SQL function that returns the list of tables in the correct dependency order.
-
Article
Rollbacks, Undos and Undonts
Rollback scripts are designed to allow us to recover safely from a failed deployment that leaves the database in an indeterminate state. They must check exactly what needs to be reverted before doing so. If you work with an RDBMS that cannot support transaction DDL rollback they are vital. This article proposes a strategy where you create and test a rollback file, at the same time as the forward migration, and reuse it as a Flyway undo script.
-
Article
10 reasons to choose Flyway
Thousands of organizations use DevOps practices every day to deliver application changes. But what about the database? Excluding the database from your DevOps pipeline and processes risks application performance, data security and integrity. With Flyway, Redgate solves this challenge in one complete solution that sits inside your existing DevOps platform and processes. Find out the
-
Live training session
Flyway 101: Standardizing once the team and project grows
Once the above challenges are resolved how do they expand the process. Adopting standardized processes for script generation and version control from the developer perspective.
-
Article
Getting Started with Flyway Desktop
Robert Sheldon helps you get started with Flyway Desktop by walking you through the process of creating a Flyway Desktop project for an existing database. It covers the basics of building a schema model, generating and running migration scripts, and saving changes to source control.
