Phil Factor

28 March 2024

• Flyway
• Database migrations

The Flyway Migrate Command Explained Simply

Flyway is a CLI tool for creating and altering a database. It is an executable that does, and knows, nothing until you give it a command. The most important command, after Help, is the Migrate command. With this command, we execute a series of versioned migrations scripts, that describe the sequence of changes required to move a database from one known version to another, while preserving all existing data.

When you issue the Migrate command, Flyway connects to the database you specify, scans the available migration files and updates the database to the latest version, or the version you specify, by applying pending migrations, in the right order. Each script file represents a change in the version of the target database. If a script is successful, the database will be at the version that is embedded in the final script’s filename. These migrations files are typically either SQL scripts or Java-based migrations that contain changes to the database schema or reference data.

Migration files, database versions and the flyway schema history table

A set of ‘migration’ files is different from a classic ‘build script’. Originally, SQL-based database systems had no ALTER command and so developers had to create the entire database system from a build script. When maintaining an existing production database, this meant taking it offline for days while you transferred and transformed the existing production data into the new version you’d built.

When the SQL-92 version of the standard gained the ALTER command, developers could instead write migration scripts that transform tables, functions and other database objects from one state to another. This was an obvious blessing but came with risks. The ALTER command assumes that the database table that you want to alter, and its many associated attributes, are in a particular state. If that’s not so, perhaps because someone else modified the database without your knowledge after you created and tested your migration script, then you risk a failed migration or occasionally worse: one that seems to work but gives an incorrect result. When these sorts of errors creep into an online database deployment it will often result in failure, and the need for rollbacks or hotfixes.

To avoid this, we need a more controlled and repeatable way to migrate a database, one that allows developers to check that it is at the expected state, or version, before running the migration. Flyway achieves this by tracking the execution of each migration file, recording its version number and details in a special metadata table called flyway_schema_history, within the default schema of the database.

If you execute Flyway migrate without specifying a target version, Flyway will update the database with the latest changes defined in the migration scripts. However, you can use the target parameter to specify the required version. With the commercial editions of Flyway, you can also migrate to lower versions using undo scripts.

By keeping a record of the migration scripts that are applied to the database, Flyway manages the evolution of the database schema over time. It knows which migrations have already been applied to the database and in which order (V1 and V2, in the above example), and so ensures that each migration is only executed once. We can retrieve all these details using another command called Flyway info, covered in a separate article.

For more details of database versioning, and its advantages, see: The What, Why and How of Database versioning with Flyway.

Running a migration

When we issue the migrate command, Flyway will attempt to ‘prepare the ground’, gathering the information it requires and running checks that try to ensure that all the requirements are in place for a successful completion. The following diagram break to the process loosely into three steps:

1. Read in the configuration details – to determine what you want to do and get the details it needs to connect to the database and run the command.
2. Read and validate migration files – to ensure version consistency
3. Execute the migrate command – to take the connected database to the requested version

1. How Flyway determines what you want and gets the details it needs

The details that are needed to update a database can become quite bulky, which you’ll soon appreciate if you type them into the command-line interface, every time.

As well as the obvious information such as the connection URL to the database and the credentials, we will need to supply Flyway with configuration parameters defining the list of locations for the migration files, the schemas that Flyway needs to manage and update, and any placeholders. There are also parameters that determine the way that Flyway does its tasks, such as how it executes a migration run, and how it responds to and reports errors.

Fortunately, unlike many CLI applications, Flyway will discover the configuration you want for your database project from files saved in a hierarchical set of configuration files. First it looks in the installation directory to determine how you like your Flyway installation to work in general. It then looks in your user area to pick up your authentication, and personal preferences. Then it looks in your current working location, to pick up all the details of your project, which will usually include the list of migration locations (paths to directories) in which to find the migration files.

Therefore, when running the Migrate command, you can provide a few of the details of what you want at the command-line and then let Flyway fetch all the rest of the information it needs to perform the task from the configuration files.

2. Validating migrations

Once it has all this information, Flyway will access the database to read the Flyway Schema History table. It ascertains the current version of the database that you’ve specified and then it reads and sorts the migration files and validates any that were already applied. It does this using checksums to make sure no aspect of the previously applied migration sequence has been subsequently changed: no files have been subsequently altered, no new files injected into the previous sequence.

The contents of a migration file should, generally, be considered immutable once it has been applied. In the previous example, migrating an existing database from V2 to V4, Flyway recalculates the checksums for the V1 and V2 files in the project folder and, if nothing has changed, applies only V3 and V4. If a developer had subsequently altered the V2 file to correct a mistake or had slipped a new migration file into the existing sequence to correct the mistake, Flyway will detect it and complain. The Validate command is covered in detail in a separate article.

However, assuming all is well, it can proceed with the migration.

3. Executing the migrate command

Each versioned migration file contains all the code to take a database from one version to the next, and similarly a versioned ‘undo’ migration file takes it back to the previous version. Flyway will always execute the files in the correct sequence. If you never make changes to existing Flyway-managed databases without Flyway’s knowledge, or skip migrations in a sequence, then you can be confident that two databases with the same version number will have the same schema definition.

With Flyway migrate, each new or altered database object that gets released from the development environment is reliably rolled out to every other environment. This makes it simple to keep as many copies of a database as you need up to date with the current version, from a single collection of migration scripts. If you have a version of the database already in production, and all your migrations successfully preserve data while changing the metadata, then a release simply means executing Flyway migrate to update that database to the release version.

Flyway will do its best to wrap all migrations into a transaction. This happens at the JDBC driver level so there is no need to write any transaction logic or error handling into your migration scripts. If a SQL script fails with an error, Flyway will use whatever the RDBMS supports to roll back the changes made by the migration step to the previous version. Some RDBMSs such as MySQL cannot roll back DDL code in a transaction, and so don’t fully support this.

Flyway commercial editions (Teams and Enterprise) also provide support for returning to a previous version by running ‘undo’ (U) migrations that reverse the changes of the corresponding ‘versioned’ (V) migration script. Flyway does the management of the ‘versioning’ and any necessary rollback, on error. There is no way that Flyway, by itself, can provide automatic rollback of migrations once they have been applied

Summary

The Flyway Migrate command automates the process of applying the database schema changes that are defined in migration scripts, making it easier to manage database migrations and maintain consistency across different database environments, because every copy of the database has a defined version, and we know how each copy got to that version, when, and who did it.

Knowing with confidence the version of every copy of a database, and the ease of migrating a database to the version you need, is a fundamental feature of Flyway that facilitates continuous integration, continuous deployment, and database version control practices.