Flyway in the command-line
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:
-
Rename the
V1.3
andU1.3
files inmain
toV1.4
andU1.4
respectively. -
Rename the
V1.2
andU1.2
files inmain
toV1.3
andU1.3
respectively. -
Copy the
V1.2
andU1.2
files fromrelease-1.0
tomain
.
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:
-
Run undo script
U1.4__add_table_address.sql
against your test database manually to undo the oldV1.3
(now renamed toV1.4
). -
Run undo script
U1.3__add_column_person_2nd_phone_numer.sql
against your test database manually to undo the oldV1.2
(now renamed toV1.3
). -
Manually delete the
V1.2
andV1.3
entries from theflyway_schema_history
table. -
Run
flyway migrate
against your test database.
Now your test database is fixed (check with select * from flyway_schema_history;
).