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.

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

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.

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.

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. 

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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. 

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.

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.

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.

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. 

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.

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 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.

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.

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