• Flyway
• Team-based Development
• Automation and workflow

The Flyway Desktop CLI: Automate your Database Development Process

Flyway Desktop, in the Enterprise Edition, will automatically detect and save any changes you make to a development database, in a schema model, and then generate migration scripts that you can use to deploy those changes. Other articles on this website have described how it works. See for example, Flyway Desktop in Database Development Work: An Overview.

This article will explore how we might automate this process using the Flyway Desktop CLI, flyway-dev, which is the interface used internally by Flyway Desktop to execute any task that relies on the built-in schema comparison technology. This automation capability means that developers can, if preferred, skip the Flyway Desktop UI entirely and take full control of the migration process through command-line instructions. Let’s see you how this might work in practice!

Please be aware, though, of the warning in the documentation: this is preview software and the syntax is likely to change! The flyway-dev interface is currently separate from the standard Flyway CLI, which Flyway Desktop continues to use for all of the core Flyway engine commands such as migrate, info, clean, and so on. At some point it’s likely that the flyway-dev CLI commands will be rolled into the standard Flyway CLI.

A typical database development workflow with Flyway Desktop

A typical Flyway Desktop (FWD) development workflow will include the following tasks: a) make changes to a local development database, connected to our Flyway Desktop project, b) save those changes to the project’s schema model, c) generate and save migration scripts to capture those changes, d) use those migration scripts to update other copies of database, e) commit all the changes to the version control system.

Two of these tasks, namely b) saving changes to the schema model and c) generating migrations, are executed ‘behind the scenes’ by running schema comparison tasks, using commands in the flyway-dev CLI.

Flyway Desktop uses schema comparison technology to detect differences between the current ‘states’ of the development database and the schema model folder, when saving changes to the schema model (step 2, below) or between schema model and shadow database, when generating migration scripts (step 3). You can verify this by opening the log files for one of your Flyway Desktop sessions!

Automating a typical Flyway Desktop workflow

To illustrate the automation capabilities of the Flyway Desktop CLI, we’ll walk through a script that demonstrates how to run steps 2-4 of this workflow using various flyway-dev commands. You can get the full script from GitHub and I’ve also provided a sample Flyway Desktop project. For brevity, I’ve omitted comments from the code listings in this article, but they are included in the source.

Please note that this demo currently only works with older versions of Flyway Desktop prior to v6.5.0, which still use the .json and .conf configuration files rather than the new unified .toml format.

Create the Flyway Desktop project

Setting up a project is a one-time task and is not covered here, but you can read in the documentation how to do this. Or you can just create it using the FWD UI, as usual.

Save dev database changes to schema model

Once we’ve made and tested changes to our development database, we would, in the Flyway Desktop UI, switch to the schema model tab, at which point, Flyway Desktop automatically detects these changes. Although this demo assumes Flyway Enterprise edition, the step of saving changes to the schema model is also supported in Flyway Teams edition.

It compares the database (source) to the schema model folder (target) and displays exactly which objects have been altered and how, with side-by-side comparison of the ‘before’ and ‘after’ state of each changed object. When you hit the Save to project button, Flyway Desktop updates the schema model folder, on disk, so that every CREATE script reflects the current state of its associated database object.

We can perform the same task in the flyway-dev CLI by initiating a comparison, or diff, between the development database and schema model folder to identify differences in their structures. We capture these differences in a temporary “diff artifact” and then take that artifact and use it to apply those differences to the schema model folder.

The first part of the script just sets up all the necessary paths for the Flyway Desktop project and migration files (in this case, a subdirectory called “Migrations”), and name and location of the diff artifact file that we’ll use to apply the changes to the schema model. You’ll notice that we need to assign a unique identifier (GUID) to the diff artifact, which we do using the New-Guid commandlet.

We also establish the full path of the temporary directory where Redgate’s Flyway Desktop stores its artifacts, which for this step is in the subdirectory comparison_artifacts_Dev_SchemaModel. We then create the temp directory structure, which shouldn’t be necessary, but we do anyway in case the user hasn’t started the UI. Finally, we establish a few common parameters to set the paths to the diff artifact file and Flyway Desktop project and to accept the EULA for the flyway-dev CLI (without this formal approval nothing works!):

The action really gets going in the next section of the script, which sets up the parameters for each of the flyway-dev commands that we need to invoke, namely diff, take and apply and then runs them.

The diff command detects all object-level differences between the development database and the schema model. It stored in the specified diff artifact the list of differences, each identified by an ID. If working interactively, we could use these IDs as an input parameter to pick and choose our changes, but in an automated pipeline, we’re just going to accept all of them.

With the diff artifact saved, we then pipe together the take and apply commands and execute them. The take command is used to select the differences detected in the previous comparison (diff) and the apply command saves the differences to the schema model.

Generating the migration script

Having saved our latest development changes to the schema model we would, in the Flyway Desktop UI, then switch to the Generate migrations tab, and the tool would automatically generate a script that includes all of those changes and invite us to give it a name and save it to the Migrations folder. This step is Enterprise edition only.

Let’s see how we can automate this part of workflow using PowerShell and flyway-dev. The first part of the script just sets up all the necessary paths for the Flyway Desktop project, migration files, diff artifact just as we saw earlier.

We then come to the section that sets up the parameters for each of the flyway-dev commands that we need to invoke, diff, take and generate, and then runs them. The diff this time is effectively between the schema model and the Migrations folder, with the intention of detecting any changes that have been saved to the schema model but are not yet captured in a migration script. However, Flyway Desktop cannot run this sort of comparison directly, so behind the scenes it builds the latest version by running all the required scripts in the Migrations folder on the Shadow database, and then compares the ‘states’ of the schema model folder (source) and shadow database (target).

Having performed the diff and saved the differences in the diff artifact, we then pipe together take and generate in a single command and run it. The take command selects the artifact containing the differences detected by the previous comparison (diff) and then the generate command writes the selected differences to a new migration file. It will use Flyway Desktop’s default version numbering and naming format:

[V/U]<version>_<datetimestamp>__<OS user name>

We can change the format using commands like next-migration-name, but here we’ll just accept the default.

We save the newly generated script to the Migrations folder (we skip promote keyword here because we don’t change the default migration file name). The --changes – switch indicates that the migration files should be based on all detected changes from the comparison.

Apply migrations and commit

In the final steps in the workflow, we apply the new migration script to the target database and then commit all the changes to version control. In the Flyway Desktop UI, we run migration scripts from the Migrations tab. As you can see in the View command window, the migrate command we need is generated for us.

Finally, we’d switch to the Version control tab to commit all the changes to our repository.

Both of these steps can be scripted and automated but since neither involve any flyway-dev logic, we won’t go into the details here. However, as mentioned previously, you can see exactly what commands get executed ‘behind the scenes’ by, first, turning on verbose logging:

Then you just run through the tasks in the UI and examine the log files. You’ll find:

• The Flyway command for the Migration tab, as:“[App] Calling “C:\Program Files\Red Gate\Flyway Desktop\flyway\flyway.cmd“
• The git actions as:”[App] git“.

Summary

Redgate has made Flyway Desktop’s CLI (flyway-dev) available for general use to allow us to experiment with ways of automating the process of capturing development changes and generating migrations.  It is currently preview software that is subject to change but even now it provides a useful tool as we explore ways of automating our database development workflows. We can use it to achieve more efficient database schema management and streamline the creation of migration scripts. If we can automate each step in this process, building in tests and checks each time to ensure that both the generated migration script and the resulting new database version work exactly as intended, then we significantly enhance the development workflow, ensuring consistency and reducing the chances of errors during the migration process.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Desktop

Flyway Desktop helps you easily and safely version control your database schema and prepare validated deployments

Find out more

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more