Flyway

Flyway development and deployment pipelines

Flyway comes in three editions, each suited to different needs.

Flyway CommunityFlyway TeamsFlyway Enterprise
Perfect for individual developers, or non-commercial projects  looking for a basic and reliable framework for versioning and automating the deployment of database changesIdeal for organizations looking to improve collaboration  and fine tune their processes during development and the deployment of database changesIdeal for medium and large enterprises  who require secure and automated processes with compliance controls to develop, test and deploy database changes


Flyway and its role in supporting the development and deployment process

The extent to which Flyway contributes to an organization's chosen database development practices, and the accompanying automated delivery pipeline, varies depending on the Flyway edition.




Agile Database Development

Flyway provides a development experience as well as an automated deployment pipeline capability. Flyway Enterprise provides the richest experience, both in the productivity gains, as well as providing the most integrated and flexible development and automated deployment workflow.

Flyway Community doesn't provide object-level versioning, and requires developers to manually author migration scripts. As the migration scripts are the only artifact that is version controlled, these need to be created early in the process to ensure the development is safeguarded. This could be problematic if the order of deployment matters, and isn't known up front, as Flyway deploys migration scripts in order. A more flexible approach is provided by Flyway Teams and Flyway Enterprise, which allows the database object changes to be saved and versioned without requiring a corresponding migration script to be created yet.

Flyway Community has no support for undo scripts, so if this is a requirement, Flyway Teams (manual undo script creation) or Flyway Enterprise (auto-generation of undo scripts) should be considered.

Flyway Teams provides object-level versioning, unlocking many benefits of version control, such as an audit trail of what changed, who made the change and why, and the ability to recover historic states of the objects. But unlike Flyway Enterprise, although Flyway Teams offers a CLI to execute migration scripts, it is not equipped with the capability to automate the generation of SQL scripts for deployment or roll back. This means that for teams wanting migrations-based deployments, the authoring of migration scripts and roll back scripts remains a fully manual exercise, and for teams wanting automated state-based deployments, Flyway Enterprise needs to be employed.

Flyway Enterprise adds static data versioning to the database objects that are versioned. Development teams are now equipped with an advanced automated script generation capability, providing huge productivity gains in generating migrations scripts based on any changes to schema and static data changes detected in development. In a state-based deployment model, these scripts are generated dynamically at deployment time, and in a migrations-based model they are generated by developers, which leaves room for script customization, where required. An advanced set of code analysis checks are available to ensure team development standards are being met.

Note that the full set of Flyway Enterprise capabilities is available for SQL Server, Oracle and Postgres and MySQL.



Community

Teams

Enterprise

Flyway Desktop GUI and CLI

Flyway Desktop surfaces the functionality available via the command line in a cross-database GUI, available for Windows macOS and with Linux on the backlog.

The features that are available in the GUI are dependent on the features unlocked in each Flyway tier.

Built-in Git client

Flyway Teams and Enterprise both provide a basic git client for common operations such as pull, commit, push, create, delete and switch branches. 

Database object-level version control

Flyway Teams and Enterprise provides object-level versioning for schema objects, providing development history, revert, audit trail for database changes, similar to the value provided by SQL Source Control.

Note that static data versioning is only available in Flyway Enterprise.

Static data versioning

Many database projects include tables that include static data (often called lookup tables, reference tables, or seed data tables) which may incur new entries or edits. Flyway Enterprise allows users to specify which tables are static data tables, which means that changes are versioned just as if they were schema objects.

Like for schema changes, any changes to the static data changes will be automatically scripted as migration scripts.

Migration scriptsself-authoredself-authoredauto-generated

Flyway being a migrations-based solution provides a migrations-based deployment capability at all tiers. However only Flyway Enterprise offers developers migration script autogeneration. Flyway Enterprise will not only identify all "candidate" changes that have yet to be reflected in migration scripts, it will leverage Redgate's industry-leading comparison technology to auto-generate the migrate SQL on behalf of development teams. This provides a huge productivity boost, not only for seasoned SQL experts, but also those developers who historically would have lacked confidence in authoring their own SQL.

Flyway Enterprise will auto-generate the SQL for schema changes and static data changes for both "forward" and "undo" migrations. Developers can inspect and customize these migration scripts as required.

Note that when using Flyway Teams, it is up to the teams to manually track which changes have been versioned, and which changes have yet to be reflected in migration scripts. 

Deployment warnings 

✓ (coming soon)

Although auto-generated code is a huge productitivy boon, it is important for developers to be aware of various limitations of a comparison engine. Therefore, this capability comes equipped with an advanced set of checks highlighting potential pitfalls that could endanger the deployment, such as unexpected data loss, index rebuilds and even the introduction of a NOT NULL column without a DEFAULT. Flyway Enterprise is able to check for up to 80 different possible deployment warnings, which  which help developers customize the script if required.

Undo script auto-generation

Many organizations have a policy to maintain a set of undo scripts. Undo scripts can be used to revert a database quickly to its previous schema state, and can also be leveraged as ready-to-go "fix-forward" migration scripts, which is more recommended best practice.

Like commenting code and writing unit tests, many developers are generally not motivated or enthused at the prospect of having to author and test undo scripts, as they know that these will only ever be employed in very rare (but important) disaster recovery circumstances. This is why the ability to auto-generate undo scripts is so valuable, as it reallocates the "undesirable" task of undo script generate to the Flyway Enterprise script generation engine.

Identify database changes missing from migrations

Flyway Teams provides object-level versioning, as well as a basic migrations runner capability in Flyway. However, it is entirely up to developers to track which changed objects need to be added to migration scripts, and of course it is up to development teams to write the SQL to deploy these changes.

Flyway Enterprise automates both the detection and identification of the "yet to be migrated" changes, which avoids situations in which developers or DBAs think they're done because their changes are "versioned" but have forgotten to prepare the corresponding migration script necessary for the change to be deployed.

Code analysis – code standards Basic rule setStandard rule set (roadmap)Advanced rule set and custom rules

Flyway extends the open source SQLFluff framework to provide a built-in static code analysis capability in Flyway.

In Flyway Enterprise, an advanced set of rules will aim to catch non-adherence to company coding standards early. For example, spotting new tables being created without a primary key. Flyway Enterprise offers the ability for custom rules to be created, catching issues in the code that are specific to a organization or project.

On-demand clones for dev and test instances - available if you partner Flyway with Redgate Test Data Manager

You can also use Flyway together with Redgate Test Data Manager to take advantage of self-service provisioning, making it easier to run tests in agile test cells or parallel test runs to achieve much more in the same testing window and ensure bugs are caught earlier before they disrupt the business.

Development teams prefer working on production data. This allows new features to be tested in actual data at the earliest possible stage, which dramatically reduces the risk of the feature missing the mark when deployed, or causing issues when exposed to real customer data. Also, without access to production-like data, developers often struggle to reproduce customer bugs, which makes the troubleshooting and issue resolution far less efficient.

As organizations are increasingly exposed to compliance standards which restrict access to PII data, developers are often asked to make do with synthesized or dummy data.

With Redgate Test Data Manager a dataset based on production-realistic data can be prepared (eg masked) for use in development environments, in the guise of am "image". From these images, clones can be spun up and refreshed on demand by development teams, providing access to the production-like data that developers crave for higher productivity.

Spin up database when switching branches - available if you partner Flyway with Redgate Test Data Manager

The advent of distributed version control, namely git, has meant that developer practices are now changing. Feature development is often done isolation on short-live branches, with changes being merged back often to the main branch, and the feature branch deleted when the work is complete. This causes problems when considering the database, which is a static unchanging entity, unable to co-exist in the multiple states required for branch development.

The cloning capability in Redgate Test Data Manager lends itself perfectly to branch switching. As new branches are created, you can use Redgate Test Data Manager to spin up a new clone for the new branch, which can be independently changed in isolation of the other development instances. When the feature development is done, and the branch merged and removed, the branch-specific clone can now be safely deleted. 

Because Redgate Test Data Manager can create many clones both fast and without paying the storage cost (as clones leverage copy on write virtualization technology), its pairing with modern development branching practices becomes a game changer.

Schema and data comparison tools

Flyway Enterprise comes equipped with advanced schema and data comparison tools. In the course of database development, tools that understand the differences between multiple database environments can be employed to increase productivity across many different scenarios, including:

  • Troubleshooting a faulty database environment by comparing it with a working environment
  • Moving test datasets from one environment to another
  • Dealing with drift in production or any downstream environments, either by removing the drift, or moving "accepted drift" back into development
  • Extracting changes from an environment that isn't linked to a Flyway project
  • Using the command lines to automate manual processes, such as checking for drift, or storing schema snapshots of a regularly changing target database


Automated CI/CD pipelines

Flyway includes capabilities in support of safe and reliable database deployment automation.

Flyway Community is equipped with a CLI, which allows for a simple flyway migrate to be run to automate deployments. No testing or validation is provided to provide confidence that a deployment will execute as expected.

Flyway Teams can output a "dry run" script, which shows the aggregated set of migration scripts that are to be run against the target. Athough this can be used as part of a pre-deployment review/approval process, understanding what changes will be made by inspecting the SQL alone is tedious at best.

Flyway Enterprise provides a comprehensive set of reports and checks to equip DevOps teams and DBAs with the confidence they need within their continuous delivery pipeline. Confidence can be gained in the knowledge that migration scripts are generated by Redgate's tried and tested comparison tech. Where DBAs need to review and approve a deployment, a detailed change report accompanies the dry run script, listing all the changed objects and detailing and highlighting the precise changes that are to be effected. A code analysis report will spot other potential red flags that may be of interest to a DBA. Although organizations try to avoid it, "production drift" can occur "out of process", which is highlighted to the DevOps team and DBA via the drift report, as it is possible that if production differs from the earlier pipeline environments, this could compromise the success of a deployment. 

With Redgate Test Data Manager, full database clones with production-like data can be spun up at different points in the pipeline to ensure realistic testing, further instilling confidence that the deployment to production itself will be successful.

If the worst happens and a rollback is required, the existence of undo scripts (also auto-generated by the comparison technology) will support a speedy rollback (or roll forward, depending on the circumstances).



Community

Teams

Enterprise

Deployment approaches supported

Migrations

Migrations

Migrations and State

There are two main approaches to database deployments: migrations and state.

  • In a migrations-based approach, the deployment artifact is the set of migration scripts, each on containing the SQL required to modify the target database.
  • In a state-based approach, the deployment artifact is the "schema model" in version control, which represents the "desired state" to apply to the target database at deployment time. A sophisticated comparison engine is used to dynamically generate the deployment script, which is then applied against the target.

There are many pros and cons of each approach, too many to list here. The state-based approach is the simpler approach on paper, as it removes the need for development teams to author the migration scripts. However, although comparison engines are powerful and mostly generate the correct SQL to effect a change, they have their limitations. So where increasingly deployment automation is a need, for example in support of DevOps, the migrations approach becomes the preferred approach.

Flyway Enterprise offers both approaches. For teams adopting the migrations-based approach, Flyway Enterprise takes on the entire burden of authoring the migration scripts by leveraging the built-in comparison technology.

Find invalid objects

(on the roadmap)

It is important to know if any objects are referencing objects that have been dropped and no longer exist in the database. Invalid objects not only won't execute, which may be impactful for functionality within the application, but their presence can cause difficulties when trying to "build" a database from the source code. It is therefore recommended that invalid objects not only identified early, but fixed or removed from the database.

Deployment script preview


Flyway Teams can ouput a deployment script preview (or "dry run script"), which creates a single script concatenating all the pending migration scripts for a given target. This script can serve as both a record of what has been executed as part of a deployment, or could serve as a basic review/approval report within a deployment process. Bear in mind that although the deployment script will describe all changes that are to be made, it is unwieldy to read through pages of SQL to understand the changes, especially as any given object may change multiple times over the course of successive migration scripts. Therefore it only serves effectively as a review/approval artifact if the number of database changes is small. If your deployment process requires a manual review/approval step, the Database Object Changes Report available in Flyway Enterprise is designed for this purpose.

Database objects changes report



The Database Object Changes report available in Flyway Enterprise is designed to provide a DBA or release manager with the best understanding of the nature of the object changes that will be made on deployment. Unlike the dry run script, which is no more than the concatenation of all pending migration scripts, the Database Object Changes report outputs a human readable report containing all the changed objects with their accompanying change type (create, alter, drop), and a side by side comparison highlighting the before and after states for each new, dropped or changed object. 

Code analysis


✓ (advanced and customizable rules)

In organizations where the database is mission critical, it is not enough to know that the changes are functionally sound, but also that the changes aren't introducing risk. It can therefore be prudent to be on the lookout for unexpected changes, and code analysis can play an important role in sniffing out potential issues. Flyway Enterprise provides an advanced set of rules, including the ability to suppliment these with custom rules.

  • Best practices - organizations or projects might adhere to best practices, such as requiring descriptions on tables, columns, views and other objects, or requiring primary keys or clusted indexes on tables, and more.
  • Security - spotting attempts in the deployment script to change the security of objects, including granting unwarranted permissions to individuals or roles, or changing passwords.
  • Data loss - highlighting changes being made that could potentially result in data loss, such as TRUNCATE TABLE, DROP TABLE, DROP COLUMN and DROP PARTITION.

Drift report



The production database can diverge from development. This is called database drift. This happens when deployments are made out-of-process, sometimes due to poor adherence to best practice, sometimes because emergency hotfixes need to be applied quickly. Either way, drift means that all development, including manual and automated testing, is done assuming a database state that is now out of date. This could endanger a deployment, as the pending migrations might not be compatible with the state of the production target.

Even if drift is harmless, the fact that it exists implies that there are likely changes in production that should be pulled back into the development environments.

Flyway Enterprise comes with a drift check that will inspect the state of production and compare it against the set of migrations, but building these on a temporary database. If changes are identified, the deployment can be paused or even aborted until the drift is resolved.

Provision build and test clones - available if you partner Flyway with Redgate Test Data Manager



Continuous integration is now a standard practice for development teams. The ability to get fast feedback on the quality of a code change the instant it is committed is now taken for granted. However, the database has traditionally thwarted this process, as restoring a database for a CI run can be too time-consuming to be done for every change. One option is to run automated tests on empty databases, which although faster, means that the testing is less realistic, and therefore less valid. Another option is to 'reset' the database to a known state, either via a custom script or database snapshotting capabilities, but scripts can be brittle, and relying on a fixed database means that test parallization is hard. 

Redgate Test Data Manager uses data virtualization technology to spin up production-sized databases quickly, without incurring the storage cost, which makes them ideal for use in CI pipelines.

Run deployment rehearsal on production data - available if you partner Flyway with Redgate Test Data Manager



It is standard practice to run a deployment rehearsal on a production-like database prior to deployment. This database is often referred to as a Staging database, an Acceptance database or a Pre-Production database. Deployment rehearsals are often run just once, prior to deployment, owing to the time it takes to restore a production database.

With Redgate Test Data Manager, full-sized clones can be created almost instantly from an overnight image take from a production backup, which can not only be used for one-off deployment rehearsals, but for continuous deployment testing as part of an automated pipeline. 

State-based deployment warnings



For some projects, a state-based deployment approach is more appropriate.

If employing the state-based deployment approach, which delegates the job of crafting the deployment SQL to a comparison engine, it is important for this to be accompanied with a set of checks that would alert a DBA or release manager to potential pitfalls that could endanger a deployment, such as unexpected data loss, index rebuilds and even the introduction of a NOT NULL column without a DEFAULT. For this reason, Flyway Enterprise comes equipped with a report that check for up to 80 different possible deployment warnings.

Deploy migration scripts

Flyway's migrate command has one core job, which is to identify which migration scripts haven't yet been applied against a target, and to apply any chosen pending scripts against the target. A log of the deployment is written to the target database, which ensures that Flyway won't attempt to re-run the migration script in future. 

Flyway runs migration scripts in strict version number order. If the chosen deployment order differs from the ordering defined by the migration script ordering, the Cherry Pick feature in Flyway Teams and Flyway Enterprise serves to specify a new order.

Undo scripts


Self-authored

Generated

Undo scripts are a features design for organzations who have a policy to maintain a working set of rollback scripts. An undo script will revert the schema and static changes made by a versioned migration, but only a backup can recover a database to a previous state. Undo scripts can run in instances where no data neets to be reverted, ideally before a database becomes 'live' after a deployment. For all other instances, an undo script should form the basis of a new migration script using a "fix forward" strategy.

Flyway Enterprise can auto-generate undo scripts, which means they are always at the disposal of teams in the unlikely event that they need to be employed. Flyway Teams users have access to the Flyway undo command, but all undo scripts must be authored for each corresponding migration script.


Other benefits of Flyway Enterprise


Community

Teams

Enterprise

Database version supportCurrent databases only (5 years from release)Standard supportExtended support

Flyway not only supports the latest set of databses, but also has support for older versions.

Organizations who are pinned to an older release of a database must be mindful of which Flyway edition is right for them.

  • Flyway Community supports databases five years from their release date.
  • Flyway Teams extends this with an additional standard set of databases versions, which is usually up to ten years from their release date.
  • Flyway Enterprise covers the widest range of database support, including a set of selected "popular" database versions.

Consult the documentation for a full list of supported database versions.

Secrets management

A problem that organizations often encounter is where to store and how to access sensitive data such as the credentials for connecting to a database or their Flyway license key.

Flyway Enterprise comes with support for the following secrets management solutions that enable you to successfully handle sensitive data:

  • AWS Secrets Manager
  • Dapr Secret Store
  • Google Cloud Secret Manager
  • Vault
Product supportCommunity support onlyRedgate standard supportPremium support (roadmap)

Didn't find what you were looking for?