Tony Davis

13 May 2025

• Flyway
• Database Deployments
• Tracking development changes

Automated State-based Deployments in Flyway

Redgate Flyway offers flexible deployment approaches, supporting both state-based and migration-based deployments. In practice, teams vary widely in how they manage database changes, and no single approach works for every environment.

Flyway’s state-based approach is designed for teams who need better automation, visibility, and control over unreliable database deployments, and can’t yet rewrite their development workflow around fully versioned migrations. The model, prepare, and deploy commands provide a straightforward way to capture database objects and reference data in a ‘schema model’, on disk, generate a deployment script, and validate it before it’s applied to other environments.

Tackling high-risk database deployments

Deployments become “high risk” when their outcomes are unpredictable. They frequently go wrong, causing instability, performance issues, or, at worst, data loss and downtime. These incidents are costly to the business (see ROI data), difficult to diagnose, and even harder to fix, resulting in a cycle of emergency hotfixes, mounting technical debt, and teams becoming increasingly cautious about releasing database changes.

These risks often stem from development workflows where it’s difficult for teams to stay aligned on database changes. The output of development is often some definition of what the new version of the database should look like, perhaps just be a shared development database, but there’s no controlled and tested way to create a deployment script that will reliably reproduce this state in other environments.

Common pain points in this type of process include the following, all of which cause inconsistencies between environments and unstable deployments:

• Low visibility into database changes – developers can’t easily see which database objects are changing or why, so untested or unexpected changes slip through.
• No change history – deployment scripts are manually edited before execution, often with no record of what was applied or how to replicate it in other environments.
• Unknown or inconsistent target state – drift between environments, plus ad hoc fixes, leads to unpredictability.
• Untested deployments – deployment scripts are often assembled late in the cycle and don’t receive proper testing.
• Big releases – without a clear way to break down changes, teams end up with “big bang” deployments where many things can go wrong.

There are clear benefits to stabilizing deployments and shifting toward smaller, more frequent releases. However, moving to Flyway’s fully versioned migration-based approach isn’t always immediately practical, especially if it disrupts established workflows. Flyway’s state-based deployment model gives teams a way to gain control and reduce risk now, without needing to adopt a versioned migration workflow straight away.

Why Flyway state-based deployments?

Flyway provides both a CLI (Flyway command-line) and a GUI (Flyway Desktop) to help teams maintain the source of truth for the database in version control during development, and then automate how changes are deployed to existing, online databases while minimizing risk.

It supports two approaches. In Flyway migration-based deployments, teams use Flyway to script each progressive change as a sequence of versioned migration scripts. Developers test each script thoroughly, and deployments execute the tested set from version control, in order. In Flyway state-based deployments, developers maintain the desired end state of the database in a set of object creation scripts (DDL) called the schema model. Flyway compares this to the target and generates a synchronization script that aligns it with the desired state, ready for review, testing, and deployment.

The state-based approach offers a lower-overhead way to adopt Flyway for teams that already work with some definition of the ‘desired end state’ of the schema, whether it’s a dev database, a manually maintained set of object-level scripts, or some other artifact. These teams may not yet be ready to move to a system where developers must test and verify each individual migration file, but are often struggling with manual, uncoordinated processes, limited visibility into changes, and no safe way to reproduce the desired state in production while preserving existing data.

In such cases, Flyway’s state-based approach supports a familiar development workflow but adds greater visibility, oversight, and built-in checks to help stabilize deployments that might otherwise be unpredictable or error-prone. It’s also valuable for teams who haven’t yet established source control processes to prevent untested changes slipping into a release. With the Flyway state-based approach, they can take a snapshot of the tested database, compare it to the target, and generate a synchronization script to reproduce exactly what was tested. What you tested and approved is what you release.

How state-based deployments work

Flyway’s state-based approach brings structure, control, and automation to the database development process that many teams already use. Flyway maintains the schema model, a set of object creation scripts that define the current version of each object, in version control, as developers can continue to work directly on their development databases, in their preferred IDE.

When all changes are checked in, Flyway automatically prepares a synchronization script that makes the required changes in the correct dependency order, while preserving existing data. Flyway generates a change report for review (covered later), and integrates with existing CI systems for deployments, adding built-in checks such as drift detection to catch unexpected changes in the target database.

The increased visibility and control over database changes, and the support Flyway provides for automation at the right moment, mean that a development team will find it easier to deliver repeatable, testable releases that work across database environments and lead to more stable deployments to the production environment.

Flyway development: update the schema model in version control

In the state-based approach, the source of truth for the database are the SQL DDL scripts that define the desired state of the database. Developers use Flyway Desktop or the CLI to script out objects and reference data to the schema model, on disk, which can be version-controlled. Teams can either work in a single-user mode, where each developer works against their own development database, or they can all work on the same shared development database.

When developers save changes to the project from Flyway Desktop, or issue the model command in the CLI, Flyway uses schema comparison to extract the CREATE definitions of each object and store them as individual files in version control (the schema model). It automatically detects and tracks any differences between the working development database and the version-controlled model. This makes it fast and simple for developers to capture changes as they work, review object-level diffs, and commit updates to their version control system. It improves collaboration, visibility, and traceability, making it much easier to investigate changes that affect the behavior of related or dependent objects.

Prepare, test and deploy

In a state-based workflow, once all changes are committed and reviewed, Flyway can automatically generate a deployment script by comparing the version-controlled schema model with a target database. The team defines the target, and Flyway will 1) prepare the deployment script – running a ‘diff’ to detect the changes and then determining how best to apply them while respecting object dependencies and preserving data. Once this script is reviewed tested, it can be used to 2) deploy the script to the target database, synchronizing its schema with that defined in the source. If the database system allows rollback of DDL, Flyway will run the deployment script within a transaction, so if the deployment fails part way through, all changes are rolled back automatically.

Script generation and review can be automated using the Prepare and Deploy commands or done manually via Flyway Desktop. In both cases, teams gain a clear view of what will change and will test scripts thoroughly before deployment. Flyway will flag any changes that could result in data loss, such as dropped columns or renamed tables. The earlier these warnings are surfaced and dealt with, the easier it is to keep deployments safe and manageable. This is supported by two common best practices with the state-based approach:

• Regular build verification – if the target is empty, Flyway prepare generates a full build script that recreates the latest development version from scratch. Teams can use this for nightly builds to continuously verify a valid and working database
• Release candidate testing and deployment – once the team has a release candidate, Flyway prepare generates the deployment script that updates any downstream targets, such as a Test, QA or Production to match it. Teams can test this script against a reference copy of production to ensure it runs correctly. Redgate Test Data Manager can assist with creating sanitized, production-like test data for this purpose.

By automating script generation and surfacing risky changes early, Flyway helps teams avoid last-minute surprises and reduce deployment errors. By running regular builds and release tests, the process becomes more predictable and repeatable, allowing organizations to deploy regular updates with greater confidence.

Automating state-based deployments

Flyway’s state-based deployments can be automated using scripting or standard CI/CD pipelines. Teams can use PowerShell, Bash, or scripting frameworks to run the prepare and deploy commands or Flyway also integrates easily with GitHub Actions, Azure DevOps, and other CI systems. You can find some example CI/CD pipelines in the documentation. One simple approach is to use a GitHub Action to install the Flyway CLI directly onto the build agent and run prepare and deploy commands:

1. In your CI workflow (e.g., after merging to main), run prepare and save the generated script as an artifact.
2. Add a manual approval or scheduled release step (e.g., using GitHub Environments or release gates).
3. In your CD workflow, run deploy using the reviewed and approved script.

Incorporating automated checks into state-based deployments

Flyway includes a set of built-in checks and reports that help teams understand the impact of changes, validate releases before they reach production and then ensure that unexpected changes don’t derail the deployment:

• Change report –a summary of changes saved to the schema model but not yet applied, showing how they’ll be modified in the target. It allows DBAs to see how many objects will be added, modified, or deleted as part of a deployment and drill down to see the side-by-side differences for each object. This also helps reviewers quickly assess the scope of the release and spot unexpected changes or possible conflicts, before testing begins.
• Code analysis – Flyway performs static code analysis on the schema changes to highlight problems such as deprecated syntax or invalid object references. Incorporated into nightly builds or CI runs, these will flag issues before they block a deployment.
• Rollback script generation – When preparing a deployment, teams can also generate a rollback script by reversing the source and target, or by using a snapshot. This can be reviewed and tested alongside the main deployment script, although great care is needed when reversing transactional changes. See Failed Database Deployments: Roll Back or Fix Forward?
• Drift report – Before deploying to any downstream environment, Flyway can check for drift, differences between the expected schema, captured in a snapshot, and the actual schema in the target database. This final check helps catch out-of-process changes, like hot fixes, that could cause deployments to fail or behave unexpectedly.

These capabilities reduce the risk of late-stage surprises, improve release quality, and give teams the insight needed to move faster and lower risk.

When state-based works and when to consider migrations

Flyway’s state-based approach offers a fast, low-friction way to gain control over database deployments while continuing to work with familiar tools and workflows. It is ideal for teams that:

• Are already familiar with managing the ‘state’ of a source database for development work, such as through a shared dev environment or object-level scripts.
• Need to stabilize a manual deployment process without major process disruption
• Want to introduce automation, visibility, and early warning checks incrementally
• Make frequent code changes (programmable objects) rather than complex table changes

Some changes, such as updating transactional data, renaming tables or columns or splitting tables, cannot be safely automated through schema comparison alone. These changes carry a risk of data loss, and Flyway will flag them. Teams mitigate this risk by creating and testing pre- and post-deployment scripts to handle these changes. However, if such changes become common, it may be time to consider moving to Flyway’s migrations-based approach.

With migration-based deployments, developers define how changes are applied, not just what the end state should be. Each change is scripted as a versioned migration, and databases track which migrations have been applied. Deployments execute only the pending migrations, in order, using the exact scripts that were tested in development.

This approach enables teams to track both the version and the state of the schema, verify consistency across environments, and gain finer control over complex changes. It unlocks more advanced techniques such as undo migrations, and use of branching strategies to break down large deployments. It is also better suited to more complex database developments, where additional checks and advanced control logic need to be built into an automated pipeline.

Migration-based deployments support features not available in state-based Flyway, including Java-based migrations, Bash or PowerShell scripts, placeholders and callbacks. If your deployment process relies on these, migration-based will be the better fit.

How to move from state-based to migration-based deployments

For many teams, Flyway’s state-based approach provides a practical starting point: it makes it easier to manage database development, introduces version control, and adds structure and repeatability to deployments. It helps reduce inconsistencies between environments and lowers the risk of production deployments.

As teams gain confidence or face more complex requirements, such as handling data transformations or coordinating large-scale schema changes, they may choose to move to a migration-based approach. It is a simple, 3-step process, and will give them greater control over how changes are applied, make it easier to coordinate changes across environments, and supports more advanced automation pipelines. With practices such as test data automation in place, teams can adapt to testing and verifying individual migration scripts and improve code quality over time.

Because Flyway supports both approaches, teams can make this transition incrementally, evolving their development and deployment processes without needing to switch tools or rework their entire workflow.

Conclusion

Flyway has traditionally been a migration-based tool, applying database changes incrementally through versioned migration scripts. However, some teams are stuck with manual development and fragile release processes and can’t immediately adopt the approach of versioning the production database and migrating it using immutable scripts created during development.

Flyway’s flexibility, with its state-based deployment approach as a viable alternative, offers a practical way forward without requiring complex changes to existing workflows. By using the prepare and deploy commands you can synchronize database states efficiently while gaining much greater visibility over development changes, and much better control over deployment processes.

Flyway gives organizations a way to standardize database development across teams, while still allowing each team to work in the way that best suits them, whether that’s migration-based, state-based, or a combination of both. Teams benefit from:

• Clear, version-controlled insight into what’s changed
• Automatically generated deployment scripts you can trust
• Built-in checks that catch problems before they reach production
• A smoother, safer path from development to release

Whether you’re stabilizing existing processes or preparing for a shift to fully versioned migrations, Flyway’s state-based deployments will reduce risk, improve visibility, and accelerate delivery of changes, without disrupting or adding overhead to existing processes.