Phil Factor

22 January 2021

• Flyway
• CLI basics
• Database Versioning

Managing database changes using Flyway: an Overview

Flyway builds databases from scripts. It is a tool that enables developers without specialized database skills to integrate SQL migration scripts into a database application, to guarantee that the database and application are always at compatible versions. It works as a database-independent library for tracking, managing and applying database changes.

When Flyway migrates a database to the version you need, it looks for migrations that must be applied, sorts them, and applies them in order, directly against the database. By running scripts within a transaction, it can rollback a migration if there is an error. In a special table within the target database, it records a history of what it has done to build the database.

Although Flyway has special features for managing migration files, it is versatile enough to support a wide variety of techniques used by teams to design and build databases. It can also integrate with a range of existing development tools, without disrupting established development systems. You can start to get a useful service from Flyway very quickly without needing to immerse yourself in the forest of options and actions.

Flyway was designed to provide an easy way to integrate simple databases into the build tools for Java-based applications, requiring only plain SQL code. It allows these databases to be tightly integrated with the deployment process for the application. Flyway’s great strength is that it supports many different databases, application build systems and platforms. It now supports 18 different types of databases. It is available for Java, Android, Windows, macOS, Linux and Docker. There are integrations available for several build systems including Maven, Gradle, Ant and SBT, Spring Boot, Play, Jenkins, XL Deploy and NPM. The developers’ community has many other plugins. The application has been written to be as configurable as possible, allowing the execution of Java files at various points in the process. In the ‘Flyway Teams’ edition, one can even execute scripting files such as PowerShell. This makes the system very versatile.

What Flyway does

If your database development is currently rather haphazard then Flyway, together with a good source management and control system, is a well-proven way to introduce structure. It provides a system that is reliable and easy to comprehend. As well as its obvious use of setting up a database to a particular version, it allows a team to determine the state of a copy of a database in any environment. It gives a history of all the changes made, and so can show whether a particular script has been applied. If, for example, a hotfix is applied to a production system in response to a production fault, it can be easily deployed retrospectively to other database environments.

Flyway executes SQL migration scripts in sequence to build a database to the version you want. These scripts are held in a directory. Whenever developers need to alter a database in any way, either changing the metadata (schema) or its data, they create a SQL script, whose filename follows the Flyway naming convention, and place it in the database directory.

Each migration script takes a database from one version to the next, and similarly an ‘undo’ script takes back it to the previous version. The filename of each script provides the information about the script and what it does. Flyway checks the current version of the database by reading it from a special table held in the target database. Whenever a database of a higher version is required it will read the database directory and apply all new SQL or Java migration scripts automatically, in the order specified by the version number of each script, before deploying the latest version of the application under development. The database is altered, within a transaction if possible, to bring it to the correct version, and you can specify the version of the database that you want to build or merely default to ‘the latest’.

If you require a database to be altered to a lower version, you can initialize the database by removing all objects and then running Flyway to the version you need, will lose any added unscripted data.

How Flyway Works

The command line version of Flyway provides the means of running the Flyway Java library from the console or script. It runs in its own pre-configured Java environment. Flyway connects to a database via Java Database Connectivity (JDBC) drivers.

Flyway uses a naming convention to determine whether the file is a versioned migration script rather than an undo or repeatable migration, what version the database will be if the script executes successfully, and its description. The suffix defaults to SQL.

Flyway maintains a table on a database, called schema_version, that holds the version number, description, and checksum. It records when the script was executed, how long it took, who did it, and whether it was successful. This can provide information for audit purposes and assessing failure rates and the need for rollbacks.

Much of the way that Flyway works can be changed by editing its config files or by creating environment variables. These settings can be over-ridden by providing command-line parameters.

The Flyway Command Line client is designed to be generic and behave like a native application. You use it to specify what action you need to perform, such as ‘migrate’, ‘clean’, ‘info’, ‘validate’, ‘baseline’, ‘repair’ or ‘undo’, either as parameters or from the configuration file. Nonetheless, sometimes it’s necessary to deal with the underlying Java platform for advanced configuration, and to enable certain features. This can be done by editing the run file.

Flyway comes in two editions, a Community edition (Apache License Version 2.0) and a Flyway Teams Edition. Teams is a superset of Community and adds more functionality, such as support for PowerShell scripting and ‘undo’ migrations, and support for older databases. Only the Community edition is necessary for this ‘getting started’ series of articles, but there is powerful magic in the Teams edition, such as PowerShell migrations, which I’ll cover in a later article.

Flyway’s SQL Migration files

All SQL Migration files come in three flavors, Versioned Migrations (V), Undo Versioned Migrations (U) and Repeatable Migrations (R). All filenames start with a prefix indicating the type of migration, followed by a version number, followed by a double underscore, followed by a description (use an underscore instead of a space). The description with its preceding double underscore is optional.

• Versioned Migrations take you from the preceding version to the version named in the file. The filename has a V prefix.
• Undo Versioned Migrations (Teams version only) take you from the version specified in the file to the previous version. The filename has a U prefix.
• Repeatable Migrations have a description and a checksum, but no version. They have an ‘R’ prefix. They are reapplied every time their checksum changes. In other words, if you alter the text of this type of file in any way, flyway detects this, and the file is executed once again. If there is more than one R file, they are executed in the ASCII order of their descriptions in the structured filenames. These scripts need to be idempotent in the sense that executing them again causes no errors. If, for example, you use them for views, procedures, functions or packages, you’d need CREATE OR ALTER or CREATE OR REPLACE statements. They can also be used for bulk data inserts. Repeatable migrations are always applied last, after all the other migration files.

When a SQL file executes, it may return results to Flyway. These will be displayed as a table or passed to the output stream.

As well as using SQL files, you can execute script migration files of each type, using the same prefixes as for the SQL files, but with the appropriate filetype, depending on the scripting language. In any edition, you can write Java-based migrations to do anything that can’t be done using SQL. In ‘Flyway Teams’ edition, you can also use scripts written in PowerShell (.ps1), Dos Batch (.bat or .cmd) and Python (.py) as well as the Linux, .sh, .bash batch files.

Transactions

SQL Transactions are managed by Flyway, so don’t put them into the SQL Code within the migration file. This allows several files to be run as a group within one transaction if you opt for that.

PostgreSQL, Aurora PostgreSQL, SQL Server and SQLite all have DDL SQL code that cannot be run within a transaction, so Flyway will check and react accordingly depending on how it is configured. If required, it will run the migration file or group outside a transaction. Migration files shouldn’t use explicit COMMIT commands because Flyway won’t then be able to perform a clean rollback in case of failure and will instead mark the migration as ‘failed’. This will require a Flyway repair action and possibly some manual cleanup.

Maintaining History and reporting

Flyway needs to know, for every copy of a database, what migrations have already been applied, when, and by whom, and what happened. To do this, Flyway adds a special ‘schema history table’ to the target database. This provides an audit trail of all the changes executed against the schema of the database. To detect whether a repeatable migration has changed, it also tracks migration checksums. To see whether a migration file needs to be applied, it records whether any previous attempts at that migration were successful. The name of this history table can be changed via a Flyway parameter.

Callbacks

Callbacks provide any extra functionality that becomes difficult to provide from within a migration script. They are either SQL or Java files and the Teams Edition also supports script callbacks, such as using PowerShell scripting.

Callback scripts are called before or after the major Flyway events, such as the execution of any migration file, a specific type of migration file, or the occurrence of a specific type of error or failure. You can include them before or after a Flyway action such as a repair, clean, info, repair, baseline or validate.

SQL Files are kept in a default location using the naming convention of having the event name followed by an optional description, with the same syntax as the migration files, followed by the SQL migration suffix.

Reporting

The simplest way of finding the current version of a database, or collection of databases that are part of a project, is to read the Flyway schema_version table on each database. This can be done via SQL, or from within Flyway by using the ‘info’ action. It can also be done from a Java file that is included in a development.

This is a very expandable system. Before Flyway executes any file, it looks through it for placeholders. These are equivalent to ‘macros’, and you can use any of the values that are defined in a config file, including your own project variables. When using Flyway with SQL Server, you can, for example, write a extended property to the database that gives the current version level, or during a build of a SQL Server database you can write a log entry so that a monitoring system such as SQL Monitor can pick it up. You can send an email alert too.

Error Handling

When Flyway executes SQL statements it reports all warnings and messages returned by the database. If there is an error, Flyway displays the accompanying message and, marks the migration as failed. It then automatically rolls it back if possible. This action can be overridden, but only in the Flyway Teams edition.

Dry Runs

Dry runs are available to ‘Flyway Teams’ and allow you to preview the changes Flyway will make to the database rather than allowing Flyway to go ahead immediately. This allows a deployment process to accommodate reviews of the SQL Code that would be executed, by team members such as DBAs, for issues such as security, access control, resilience and compliance. Flyway Teams determines what needs updating and assumes that you’ll use a different tool to apply the actual database changes.

Summary

Flyway is a Java application that can be used to build a database to the version you want. It uses ordinary SQL Migration scripts and relies on tags in the filename to determine the order in which it executes them, each within its own transaction. It has developed, with a lot of community input and assistance, to be very flexible and configurable in the way it can be used. Its inherent configurability means it is surprisingly easy to integrate this type of migration-based build system with other ways of developing SQL databases, especially with the right software tools.

The interesting part of the story is how one goes about integrating this sort of migration script with the way that database teams need to work, and other tools such as data generation, masking, ER diagramming and source control. We’ll tackle these issues in subsequent articles.