Supporting Monolithic Database Deployment with Flyway
This article explains how a Flyway migration-first approach, backed by consistent versioning and testing, helps teams bring structure and repeatability to even the most complex release processes. Coupled with techniques such as branch-based development, Flyway can help turn large, fragile deployments into smaller, more frequent, and more reliable operations.
Monolithic databases are large, tightly coupled systems that serve many data-related purposes and support more than one application or service. They inevitably contain many interdependent features. These interdependencies often mean that the system evolves slowly, with changes bundled into large, scheduled releases.
While unmatched for transactional effectiveness and throughput, monolithic databases can be difficult to update without affecting existing functionality. If the data architecture is poor or has evolved haphazardly over time, changes to one area can ripple into many others. Testing environments are often shared, deployments carry high risk, and the cost of release failure is significant.
How can Flyway be introduced to an existing enterprise database development to help resolve these issues? This article outlines an incremental approach, starting with versioning, generating migrations from existing development artifacts, and gradually moving toward branch-based database development. The goal is to bring structure, traceability, and confidence to database changes without disrupting existing workflows:
- Establish reliable database versions
Version your schemas and track what’s been applied. - Create a Flyway database that can be rebuilt
Build databases from versioned migration scripts and separate schema from data for testing. - Adopt versioned migrations for releases
Generate and test migration scripts without reworking your development process. - Shift to migration-first development
Treat migrations as the source of truth across all environments. - Use branches to isolate work and manage risk
Develop features independently and avoid collisions. - Improve the quality of releases
Make releases safer, more predictable, and easier to automate. - Deploy to federated and variant databases
Handle cross-DB dependencies, shared logic, and multi-tenant variation.
How to adapt an existing database development to Flyway
What happens if you introduce Flyway into an existing corporate-scale database development? Its first contribution is to provide stricter versioning to simplify provisioning, testing, release, and branch development, for a range of relational database systems. The team can migrate databases freely between versions and meet the demands of rapid release, from the smallest microservice database to the largest corporate database. The advantages of a migration-first approach to database development are well-known, and present no puzzles for a new database project; but how do you start to introduce it when you already have a well-established database application?
1: Establish reliable database versions
One of Flyway’s first and most important benefits is database versioning. More precisely, it versions one or more schemas within a database, meaning a single database can contain multiple versioned components.
Each time Flyway runs a versioned migration script, it assigns a new, sequential version number to the database and records it in a special table called the schema history table. This table tracks a complete history of changes: which migrations were applied, when, by whom, and how long they took to run.
By keeping track of which migration scripts have already been applied, Flyway ensures that only pending scripts, with a higher version number, are applied during each run.
Any developer-based or scripted process can interrogate the database using Flyway info (or even SQL) to find out the current version of any database and its history. This ability to migrate a database from one known and verifiable version to another has immediate benefits, because it removes a lot of uncertainty in a release. Similarly, the ability to reliably create a database at any version greatly simplifies any team-based database development, and provisioning.
2: Create a Flyway database that can be rebuilt
A development team will want copies of the database to work with, for a variety of tasks. How do you create them? You might just decide to make copies by restoring a database backup, using the tool provided by the RDBMS. However, it is better to build or update just the database objects, such as tables, functions, procedures and types, to the version you need without the data. A database version defines only the current state of the database objects (the metadata), not the data. Loading the test data is a separate process, and the appropriate type of test data depends on the type of testing you need to do.
The baseline migration
How can we create a copy of an existing database, at a known version, if that database is not currently managed by Flyway? The first task is to create a ‘baseline migration’ script. This is very close to a build script, except that it leaves the creation of the schemas to Flyway and likewise doesn’t have transactions within the baseline migration script. The alternative is to use a schema-only backup.
When you create a baseline migration file from an existing database it will, in effect, consolidate all the metadata changes described in your historical change files, whatever form they take, into a single script that recreates the same metadata, and has a version number.
Why do this? Instead of duplicating entire databases, including data, it’s better to use Flyway to recreate the schema from scripts, ensuring every environment starts from a known version.
Once a database is baselined, developers and testers can create copies as needed. Any future changes are managed as versioned migration scripts. If a developer modifies the database, they write a migration script, assign it the next version number, and Flyway applies the change consistently to upgrade all database copies to the new version. Flyway goes further still: its version chain allows you to upgrade from any version to any other, using only the scripts relevant to that change path.
Data handling and testing
To create a working copy of a database, we also need data, which we load separately, at the point Flyway successfully creates the new schema version. Handling data separately helps testers particularly but also means that developers can load different datasets for different types of unit tests.
It also emphasizes that the objective is to make changes to a database with existing data. If a Flyway migration includes schema changes that disrupt existing data, developers will discover it immediately when trying to reload the existing dataset into the new version, for testing.
This forces developers to confront the reality that schema changes must account for existing data, not just structure. In other words, a migration file, unlike a build script, must always be able to upgrade its target database in a way that transforms existing data to conform to changes in the database table design.
3: Adopt versioned migrations for releases
Teams who manage the development of complex database systems, including monoliths, usually adopt a tool like Flyway to bring order to high-risk, error-prone deployments. Flyway’s versioned migrations help teams manage changes with far more control, and with far lower risk that a deployment will cause instability or disrupt services. However, many teams are unsure how to adapt their existing development lifecycle to a Flyway migrations approach. Fortunately, Flyway’s ability to generate migration files from many common database artifacts means that teams can adopt it incrementally, without disrupting existing processes. Developers can continue to submit an object-level model of the updated database, or whatever artifact they prefer, and let Flyway generate a suggested migration script to upgrade from the current version to the new one. This script must still be thoroughly tested before deployment. This step becomes the foundation for a transition to a fully versioned and branch-based development and deployment model.
What is the ‘source of truth’ of a database?
The source of truth for a database in development must be the most accurate and reliable way to build or update it. But what should that be? In Flyway, the migration files define the database versions, making them the obvious source of truth for the database.
However, legacy database developments are often based around a state-based model where the source of truth is a set of object-level scripts (individual definitions of tables, procedures, and functions). This artifact alone, though, is not a reliable way to fully recreate a working database. Often, in such systems, the subtleties of creating or amending database objects in the right dependency order, while making sure no data is lost, can be delayed until deployment, where it can cause significant delays, especially in complex databases with many dependencies.
During early adoption, teams can continue to use their existing development systems and incorporate Flyway to generate migration files for deployment.
How can I get migration files from my existing development system?
Flyway Enterprise incorporates a powerful schema comparison engine. It will take a ‘source’ version of a database and autogenerate a script that migrates a ‘target’ database to the same version. It works directly with a variety of database representations as a source or target, including live databases, build scripts, object-level directories (‘schema models’) and snapshots. We can also create Flyway-compatible migrations from SSDT DACPACs (once they have been converted to object-level scripts using native tools) or Entity Framework models (via extraction or conversion).
This means that teams don’t need to overturn their existing development processes but rather just insert a stage that expands the ways that developers can express their changes. For example, they can continue to submit an object-level model of the updated database, or a build script, and then let Flyway create a suggested migration script, from an existing version.
The generated script will automatically create or modify objects in the correct dependency order and aim to preserve existing data. For simple schema changes, like updating stored procedures, this is easy. However, when the committed changes involve redesigning tables or restructuring relationships, the developers must carefully test the migration code to ensure it produces the new version correctly, while preventing data loss. The review and acceptance process must be adapted to ensure that each change results in a robust, versioned migration file that can be applied consistently across environments.
Releasing between known versions
Having followed the previous sections, you’ll find that Flyway is now getting into its element, maintaining a list of versioned files it has used or is poised to use, on each copy of the database.
At every opportunity it checks (‘validates’) the files that have already been applied to the target database, to make sure that none have changed at all since being used. It will then apply only the pending migrations required to produce the new version. If anything goes wrong, Flyway will, if the RSDBMS supports DDL transactions, roll back any errors to the current version rather than leave the version at an indeterminate state. The result should be a far more stable and reliable release process.
4. Shift to migration-first development
In this stage of Flyway adoption, the team shifts to a versioned development system, where the migration files are the true source of truth for every copy of the database, across all environments.
Each migration file is added in sequence, preserving the exact order of changes, so that dependencies are always handled correctly. Unlike procedural code, migration files do not change over time, you only add to the collection, creating a complete and traceable history of modifications. Every database change made during development is a versioned migration, designed and tested to define a safe method for modifying or renaming tables while preserving existing data, before it’s even committed to Develop. Since the same migration scripts are then applied across development, staging, and production, every environment remains consistent and aligned.
The only downside is that a migration file itself tells you little about the overall database structure. However, after applying migrations, we can generate other useful artifacts to provide insight into the database’s current state, such as a build script, an object-level script directory, or a schema snapshot. Flyway Enterprise can subsequently use any of these artifacts as a ‘source’ or ‘target’ for schema comparison. This allows teams to generate a report of differences, verify changes, and even generate scripts to reconcile inconsistencies between versions.
Once the development team can reproduce any version of the database and generate migration scripts from a variety of artefacts, they can move to a branch-based development model. Each task can be supported with a dedicated branch and developed, tested, and merged independently.
5. Use branches in database development
Every database has at least two permanent branches that define its evolution. The first, usually called ‘main’, represents stable, released versions. It’s used to create each release via forward (and sometimes backward) migrations. The second, typically called ‘develop’, tracks the current development version being prepared for release.
For teams working on multiple features in parallel, feature branches offer a structured way to isolate, test, and manage changes without disrupting others. In monolithic databases, where many developers may be working on different areas of a tightly coupled schema, branching becomes even more important but also more challenging.
In any team-based developments, it isn’t always easy to test changes in isolation, and shared objects increase the risk of collisions. Introducing branch-based development requires discipline, coordination, and sometimes architectural changes. However, it lays the groundwork for the move away from big bang releases. Teams can prioritize and release individual features or fixes independently, merging them to develop and promoting them to main in a controlled, auditable way. The merge can vary from the simple to the complex, depending on the extent of the check for broken dependencies. However, the need for merges brings these checks further back into development, and forces developers to become aware of the need for checks and the techniques whereby good database design can minimize the risks of subsequent relational conflicts.
Flyway Enterprise supports the branching and merging model well. For example, developers can:
- Use baseline migrations to quickly create a branch version of the current schema.
- Use undo scripts to experiment safely and revert changes when needed.
- Use schema comparison to 1) script and test only feature branch changes, 2) detect any conflicting changes made to the shared develop branch in the meantime and 3) generate a script that will safely merge changes into the parent
This makes it easier to resolve merge conflicts early, identify dependency issues, and avoid last-minute surprises during integration. Even in monolithic systems, branching gives teams a practical way to isolate work, manage risk, and move toward smaller, safer releases.
6. Improve the quality of database releases
In theory, the release process can be simple, even risk-free, if the principles of structured, version-controlled database evolution are implemented. The rewards for getting it right include better collaboration across teams and the confidence to evolve the database as business needs change.
So, what goes wrong, and how does Flyway help?
Why are releases so difficult?
Database releases are the times in the database lifecycle when the gods of database geeks finally call in all that technical debt. Deployments usually fail not because of flawed vision, but because of details. It’s relatively easy to survive mistakes in new features using feature flags or blue/green deployments, but a broken dependency is a deployment-stopper.
In monolithic databases that were developed iteratively, even small changes can have a widespread impact, because without a positive design, many application areas depend on shared procedures, tables, and functions. In my experience, most failed deployments happen because teams neglect the unglamorous work. Perhaps certain aspects of testing are rushed or omitted, or migrations aren’t thoroughly checked within the ‘develop’ branch, or documentation is sparse, or migration files are an unreadable mess. These issues will make it more difficult for the review, merge and validation processes to verify the quality of what’s being released. When the quality metrics decline, the risk of a failed deployment will rise accordingly, especially in systems where a lot is changing at once.
Improving the ease and reliability of releases
We’ve already seen some of the ways that Flyway’s migration-first strategy encourages a more structured and predictable approach to schema database changes. With versioning and a clear change history, Flyway makes it easy to track, audit, and understand how the database evolves. Migrations can be linked to specific features or fixes, compared across versions, and rolled back if needed. Flyway also encourages automating migration execution, reducing the risk of human error.
Under the covers, all database releases to an existing database are migrations. Migration scripts are written, reviewed, and tested early. Unit and integration tests can be triggered automatically. Flyway will also check pending migrations for compliance with coding standards, using static code analysis, and warn you about anything likely to cause problems.
By applying the same set of tested migrations, in the same order, to all database environments throughout the software development lifecycle, a migration-first approach ensures a high degree of consistency in database schemas. Errors show up early, not in production.
Before we run the release, we can do a final check that the target environment is at the Flyway version we expect, with no drift, by comparing the current database to a reference snapshot, at the same level. This significantly reduces the likelihood of encountering unexpected issues in production due to database schema mismatches.
Of course, there must still be a way to rapidly and reliably revert to a stable database state if a new deployment does encounter problems, minimizing the impact on users and business operations. Flyway Enterprise can generate first-cut undo scripts that can be refined to the point that they can safely roll back a release.
7. Deploy to multi-database services
The reality of the complexity of commercial databases can come as a shock to the layman. The diagrams where the database is represented by a can of beans on its side might reflect a simple microservice, but not the many enterprise-scale databases.
Handling Dependencies
It is very easy for monolith databases to have external dependencies or to provide objects that can be called or referenced externally by ‘satellite’ databases with specialized roles. The most common architecture that uses this are Federated Database Systems (FDBS), Data Fabrics or Data Meshes.
Several problems arise from these dependencies, all of which Flyway can help teams handle.
Mutual or circular dependencies
One interesting problem is how to overcome build failures caused by circular, or mutual, dependencies between objects in different databases. Once you’ve identified the external references, you must account for them. I’ve described a solution in Dealing with Mutually Dependent Databases in Flyway that shows how to create and manage ‘stub’ objects in Flyway migrations, or synonyms (in SQL Server only) if the dependencies are cross-server.
The alternative would be to use a tool like Redgate Clone to ensure that each environment has full copies of all the databases, merely updating them as required.
Common components and ‘drift’
You’ll often find common procedures duplicated across multiple databases, which are intended to be the same, but gradually diverge as each team makes its own local changes. It becomes difficult to say which version is correct, and which databases are running it.
Flyway supports several strategies to prevent ‘drift’ in shared logic. One option is to move shared objects into a dedicated schema, that is versioned and deployed independently, via a separate Flyway project, across all relevant databases. I’ve described this strategy in Maintaining a Utilities Schema in a Flyway Project. The other option discussed in the referenced article is to manage shared code using repeatable migrations, although the drawback to this strategy is that repeatable migration files are not versioned.
Multi-tenant databases and variant releases
Multi-tenant databases often need to support variants, meaning customized versions of tables, views, or logic for different regions or customers. These must be carried forward from version to version without disrupting the core schema.
The best way to handle variants in Flyway, in my experience, is to use a simple, hierarchical directory structure, where each variant is in its own sub-directory for tenant-specific scripts. We then use Flyway’s locations feature to expose both the migration scripts that perform special modifications as well as the general ones. See Maintaining Variants of a Database using Flyway Locations.
Orchestrating deployments
With Flyway, it’s common to use an orchestrating script to manage the deployment of a group of Flyway projects, each of which deals with a specific database within ‘the forest’. If there is a risk from a version mismatch, which is unlikely if the design uses version-independent interfaces, there may be problems if just one of the databases fails its deployment.
Whereas a single Flyway deployment can be rolled back within a transaction, this isn’t true of ‘federated deployments’. In this case, any successfully upgraded databases will have committed their changes, so you’ll need Flyway undo scripts to roll them back. Fortunately, Flyway Enterprise can generate these for you in advance, so they can be well-tested in advance.
Conclusion
A migration-first approach with versioned releases isn’t just for conventional OLTP systems or microservices. It offers a structured, controlled way to manage change across all types of database architectures, including monoliths and federated systems.
This article has shown how teams can introduce Flyway into monolithic environments without large, upfront changes. By generating migration scripts from existing artifacts, teams can move gradually toward a versioned model. Because each migration becomes well-tested in development, it’s far less likely to cause issues in production. Over time, Flyway encourages a more disciplined development approach using branches and merges, shifting the detection of issues earlier in the process, and making collaboration easier.
By treating database changes as first-class artifacts, versioned and traceable, teams can deploy more frequently and with greater confidence. Releases become easier to automate, staging environments can more closely mirror production, and the risk of surprises during deployment is reduced.
