Flyway is convenient tool to manage your database changes. You can use it to create and populate your database from scratch, or to manage changes on a pre-existing database. You can add it to your application so that when it starts up it invokes Flyway, or you can use the command-line version. This blog describes a convenient way of using the Flyway command-line version.

Command-line version

The Flyway command-line version has several advantages over adding it to the startup of your application:

  • It gives you more control over the update process.

  • Time-consuming changes would cause the application to take a very long time to start when performing the updates built into the application startup. When doing them in the command-line you can do them in advance without the need to stop the application. So the downtime will be much less.

Example command to run against a MariaDB database (with the sql files in the sql subdirectory of the working directory):

flyway -user=<username> -password=<password> -url="jdbc:mariadb://<db_host>:<db_port>/<schema_name>" -locations=filesystem:sql -baselineOnMigrate=true migrate

Repair

Sometimes you want to make changes to your database outside of Flyway. Or you want to skip (a part of) one or more Flyway scripts, with the intention of doing those later manually. This is something you usually only do in your test environment. For example:

V1.7__drop_table_city.sql:

ALTER TABLE IF EXISTS city RENAME TO deprecated_city;

U1.7__drop_table_city.sql:

ALTER TABLE IF EXISTS deprecated_city RENAME TO city;

Downgrading from 1.7 to 1.6 would require manually to run the U1.7 script. If for a while you regularly up- and downgrade between the two versions this could be cumbersome. So you could comment out the content of the V1.7 script and run Flyway. Next restore the V1.7. To prevent flyway to fail on a next run you will have to repair the flyway_schema_history table to resynchronize it with the actual sql files. For example, like this:

flyway -user=<username> -password=<password> -url="jdbc:mariadb://<db_host>:<db_port>/<schema_name>" -locations=filesystem:sql repair

This will update the checksum of the V1.7 entry in the flyway_schema_history table.

Branches

Suppose you have version 1.0.0 of your system in production, and have been working on the new version 1.1.0 for a while. And suppose version 1.0.0 has the following Flyway files:

V1.0__init_db.sql
V1.1__add_column_person_display_name.sql
U1.1__add_column_person_display_name.sql

And version 1.1.0 has:

V1.0__init_db.sql
V1.1__add_column_person_display_name.sql
V1.2__add_column_person_2nd_phone_numer.sql
V1.3__add_table_address.sql
U1.1__add_column_person_display_name.sql
U1.2__add_column_person_2nd_phone_numer.sql
U1.3__add_table_address.sql

Now a production issue occurs requiring a fix version 1.0.1. In the release branch release-1.0 you do the fix, which happens to require a database change as well. So in branch release-1.0 you now get:

V1.0__init_db.sql
V1.1__add_column_person_display_name.sql
V1.2__add_column_person_display_name_longer.sql
U1.1__add_column_person_display_name.sql
U1.2__add_column_person_display_name_longer.sql

Deploying this to production should not be a problem with Flyway. But merging it to your main branch will give a conflict, because we already have a V1.2 and V1.3 file in main. And you probably already ran it in your test environment database. How to fix this:

  1. Rename the V1.3 and U1.3 files in main to V1.4 and U1.4 respectively.

  2. Rename the V1.2 and U1.2 files in main to V1.3 and U1.3 respectively.

  3. Copy the V1.2 and U1.2 files from release-1.0 to main.

Now you should have in main:

V1.0__init_db.sql
V1.1__add_column_person_display_name.sql
V1.2__add_column_person_display_name_longer.sql
V1.3__add_column_person_2nd_phone_numer.sql
V1.4__add_table_address.sql
U1.1__add_column_person_display_name.sql
U1.2__add_column_person_display_name_longer.sql
U1.3__add_column_person_2nd_phone_numer.sql
U1.4__add_table_address.sql

To fix your Flyway history:

  1. Run undo script U1.4__add_table_address.sql against your test database manually to undo the old V1.3 (now renamed to V1.4).

  2. Run undo script U1.3__add_column_person_2nd_phone_numer.sql against your test database manually to undo the old V1.2 (now renamed to V1.3).

  3. Manually delete the V1.2 and V1.3 entries from the flyway_schema_history table.

  4. Run flyway migrate against your test database.

Now your test database is fixed (check with select * from flyway_schema_history;).

shadow-left