Phil Factor

25 February 2025

• Flyway
• Tracking development changes
• Database Build tasks

Scripting Databases with Flyway Enterprise CLI

Flyway Enterprise CLI’s generate command automates script generation, making it easier to produce the scripts needed to support the deployment process, and helping developers generate the scripts they need for various development tasks. This might be undoing branch changes, getting a build script for a particular version, producing a script to merge branches without conflicts, or simply just preserving work-in-progress changes.

Previously, integrating SQL Compare with Flyway required additional scripting due to their differing approaches to database connectivity. With schema comparison capabilities built directly into Flyway CLI, generating versioned, undo, and baseline migration scripts is much more straightforward.

The different types of Flyway scripts and their uses

Migration scripts are central to a Flyway database development, but other scripts become more necessary as databases grow. Typical examples are baseline scripts and undo migration scripts. Unlike versioned migration, these merely support the process. Whenever possible, they ought to be generated automatically as development proceeds. Some of these scripts, such as the undo scripts or the migration used to merge a branch back into its parent, are tedious to do manually and error-prone.

Versioned migrations

A versioned migration (V) script takes the database from a previous migration to the next, applying a discrete set of changes (e.g., adding a column, creating a new table) and is tied to a specific version in Flyway’s versioning system. These scripts are executed sequentially to apply changes to the database schema or data.

When working in a branch, you can use Flyway to retrospectively capture changes to a development database into a migration script, whether the changes are made via SSMS or using an ER Modelling tool or a table builder. We can do much more, besides. For example, by comparing the model of the database created at the point of branching with the current state of the branch database, Flyway will generate a script capturing only the changes made within the branch. This can then form the basis of a script to merge a feature branch back into its parent after we’ve checked when the interim changes in the parent branch to see if there are any collisions.

Undo scripts

An undo (U) script aims to revert to the previous version, leaving no trace in the metadata that it happened. The script defines how to reverse the changes introduced in a migration, such as dropping objects created by the migration or restoring modified data. Ideally, you’d want to have an undo script for every version, because if there is a complete chain, we can do undo runs down any number of versions.

Undo scripts are always useful in branch work for continuously making, testing, and reverting changes and I’ve been using Redgate comparison tools for years to provide them.

Baseline migrations

The Baseline migration (B) script is very similar to a build script for a particular version of the database. Starting with an empty database, it will migrate it to the version specified in the file name, creating the database schemas where necessary and then all the database objects, in the correct dependency order. I’ve explained the main uses for this script, and how they work, in Flyway’s Baseline Migrations Explained Simply.

Baseline migrations have a use as a reference even if they’re not used as a script. They are a good way of understanding the overview, and the object inter-relationships. With a baseline migration for each version, teams that prefer can review the changes made between any two versions by viewing the textual differences through the version control system.

However, the same information is available using Flyway’s DiffText command on the diff artefact, with the added attraction that only the objects that have differences are listed.

Creating the diff artefact

I’ve already described how to use the diff command to compare databases and generate a diff artefact in Comparing Databases and Generating Schema Models with Flyway. This is merely a machine-readable report of the differences between the source and target. A Source or target can be a database, a directory of object-level build scripts called a schema model, a snapshot, or a list of directories containing the migration files. It is generally tidiest to use snapshots to represent any database that doesn’t currently exist, though models are just as good.

Generating the Scripts

Once we have the diff artefact, we can then use the generate command to produce versioned, undo, and baseline scripts. We can leave the details to Flyway or specify such matters as the type of file, the filename, or the file location. There are plenty of options, depending on what we wish to accomplish.

• generate.target – the target for which the script will be generated This must be either the source or the target used in the current diff artefact. If you specify nothing, it uses the diff.target
• generate.changes – used when you need to specify those changes listed in the DIFF artefact that you want the script for. To specify them, you must prepare a comma-separated list of change IDs, gleaned from the Diff Artefact by using the DiffText command. If nothing is specified, then all changes are scripted
• generate.types – This will be a list of the types of script you want to be generated. All the types of script that you want to be generated should be presented as a comma-separated list of script types, being one of versioned, undo or baseline. If you don’t specify otherwise, just a versioned script is generated (prefix V).
• generate.version – the version part of the migration name to be used in the generated script. If you don’t specify it, Flyway will calculate it, where necessary, to be the next migration version if a versioned migration is specified. Otherwise, it will use the current version. This is not needed if you specify the baselineFilename, undoFilename or versionedFilename
• generate.description – this specifies the ‘description’ part of the migration’s filename to be used in the generated script. If nothing is specified, there will be no description unless you specify the baselineFilename.
• generate.baselineFilename – if you have the filename (or full path) to use for the generated baseline migration. This cannot be used if you specify the description & version.
• generate.versionedFilename – the filename (or full path) to use for the generated versioned migration. This cannot be used if you specify description & version.
• generate.undoFilename – the filename (or full path) to use for the generated undo migration. This cannot be used if you specify description & version.
• generate.location – the location to place the generated migration, specified as a path..
• generate.artifactFilename – the location of the diff artefact to apply to the target. Defaults: diff.artifactFilename or %temp%/flyway.artifact.diff.
• generate.addTimestamp – adds a timestamp to the calculated version if one is not already present. Default: false
• generate.force – deletes any existing file of the same name in the generate.location you specify. Otherwise, you get an error if the file already exists

As a simple example, here’s a PowerShell script that provides you with a baseline migration from the main database. For illustration, I’ve made details of the main environment explicit, in an array, but generally, you’d read the environment from a TOML configuration file.

Workflows for auto-generating Flyway scripts

In my experience, the approach you use for Flyway development will vary with the type of project, size of project, and stage of a project. Whichever one you choose the scripting facility in Flyway Enterprise can automatically generate whatever scripts you need to support it.

The Flyway Desktop workflow

If you use the Flyway Desktop GUI, then you will be familiar with a workflow where changes made to a local development database are saved to the schema model, which is then compared to the latest releasable version, represented by the migrations folder, to generate a versioned migration script. This workflow can now be automated using the Flyway CLI, as described in detail by Tonie Huizer in Automating Flyway Desktop Development using the Flyway CLI.

A migration-first workflow

Flyway will adapt to a range of development workflows. My preferred approach is ‘migration first’, which assumes that only the migration chain contains the true definition of the database version. I typically maintain one Flyway-managed database per branch. I generate a first-cut of each versioned (V) and undo (U) migration, testing and refining the V script repeatedly, using the U script to reset the branch each time. In this way, I can ensure clean, reliable migrations before committing them to the migration or undo chains. This ‘cautious’ approach can be especially relevant for any migration where, for example, the schema changes require additional logic to migrate and preserve existing data.

When Flyway runs the versioned migration, officially recording the version in its schema history table, I generate and save a range of scripts and artifacts that record what’s in that version and the changes applied to produce it. As a routine, I treat these as artifacts and save them separately, for each version of the database.

 – Autogenerating scripts in a callback

In team-based development with branching, I like to take a structured approach that uses an afterMigrate callback to automatically save the necessary artifacts for each version at the point when Flyway registers the database at that version. These artifacts (generated scripts, schema models, or snapshots) are stored in per-version subfolders within the project directory. The schema version number is extracted from Flyway’s info command output (JSON format) and used to name the artifact files, ensuring that they are always tied to the correct version.

I’ll present the full callback script in another article, but here I’ll show a more bare-bones script you’d run after Flyway runs migrate. This script can be used to auto-generate a forward migration (versioned), reverse migration (undo), and build (baseline migration) for each version.

As described earlier, the undo scripts are immediately useful for branch work or if you are the only developer. The baseline migration can be useful if you ever use this version as a baseline. The forward (V) migration script is only included for completeness, though it can be useful if you wish to save speculative work beyond the current version, or in branch-based development, where migration files tend to get messy due to frequent alterations.

 – The worked example

The example will run in the example Pubs database. A sample Flyway project for Pubs is available in my Flyway Teamwork PowerShell framework on GitHub. This framework uses a directory system that supports branching and merging files, but it should be easy to edit it for your own setup. You only need the versions directory, and the individual per-version subdirectories are created for you.

The script generates V and U migration scripts by creating a snapshot for the current version and comparing it against a snapshot of the previous version. It assumes the previous snapshot exists in a standard location (Reports/Snapshot.json) so you’ll need to create the initial snapshot (e.g. of an empty database). The Flyway commands are simple. The work is in gathering credentials and fetching the name and version number of both the current version and the previous version. You need both.

Conclusion

These scripts can make life easier during development, not only in development to have free undo scripts for every version, but for rapidly looking through the CREATE statements of each version to see what is in the database, or to see what is changed. Although the code that is generated is functional rather than immediately intelligible, it is handy to have, particularly with a DIFF tool. The more people there are in the team, the more useful it becomes. It is very good for bug post-mortems because one can see quickly how and when a bug was introduced.

I’ve been generating scripts with Flyway for years. Before Flyway provided native comparisons, I used the separate SQL Compare and Oracle Compare UI tools in CLI mode to provide the same feature. It was a great help. The problem was that ODBC and JDBC are incompatible database interfaces, so combining Flyway (JDBC) and SQL Compare (ODBC) inevitably meant having to massage the credentials.