For years I’ve used a database build script framework written in-house to recompile all schema objects and run unit tests. However, the actual DDL code to migrate a schema from one version to the next was always left as a hand-rolled script that is executed up-front before the normal build process.
A colleague introduced me to Flyway DB and I am now a fan. The introduction, documentation, and tutorial material is top-notch.
Installing Flyway on Linux
Flyway DB can be found here:
Although it has an API for integration, I specifically like the command-line utility version: https://flywaydb.org/getstarted/firststeps/commandline
Unzip the command-line tool version into your preferred program location. In my Linux VM, I decided on ~/bin:
and created a simlink to put flyway into the path:
$ ln -s ~/bin/flyway-4.2.0/flyway ~/bin/flyway
Now I can run it from any directory.
How Flyway works
Flyway scans the contents of a “migration scripts” folder using a file naming convention to understand which scripts belong to which specific version.
It then uses a special table in the target database called “schema_version” that contains metadata about the current version of the database.
Using Flyway
The basic syntax is
> flyway VERB
where VERB is one of:
- migrate
- clean
- info
- validate
- baseline
- repair
In our case, we need to:
- “baseline” our database so that Flyway understands the current starting “version”;
- write a DDL script to move the DB to the next version;
- “migrate” our database instance to that version.
Setting up a working project directory
- Create a working folder
- copy the {install}/conf/flyway.conf file into the working directory, for project-specific overrides;
- create a sub-folder called sql
The working directory I set up was:
/home/colin/Projects/flyway
/home/colin/Projects/flyway/sql
I copied the flyway.conf file, ready for local configuration.
Selecting the target DB
I’ve been working through the “MySQL Cookbook” tutorial, so I have a user and a database installed in the VM locally, called “cookbook”, with one table “limbs” already created.
Editing the .conf file:
flyway.url=jdbc:mysql://localhost:3306/cookbook
flyway.user=root
flyway.locations=filesystem:./sql
We could supply the password as well, but for interactive use it is safer to have flyway prompt for it. For automated processes, you’d want to put the password in the .conf file.
Note the syntax of specifying the location of the SQL migration scripts, with a “filesystem:” prefix.
Just for kicks, let’s run the “migrate” command:
$ flyway migrate
Flyway 4.2.0 by Boxfuse
Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Successfully validated 0 migrations (execution time 00:00.003s)
ERROR: Found non-empty schema(s) `cookbook` without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the
$
We see a couple of issues:
- the target DB is missing the metadata table, and therefore Flyway doesn’t know what version it is;
- we don’t have any upgrade scripts to process.
Baselining our target DB
We can update the .conf file to include information about the database. Let’s assume we are starting at version 5:
flyway.baselineVersion=5
flyway.baselineDescription=Baseline prior to Flyway
Now we can use the “baseline” command to create the metadata:
$ flyway baseline
Flyway 4.2.0 by Boxfuse
Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Creating Metadata table: `cookbook`.`schema_version`
Successfully baselined schema with version: 5
$
Querying MySQL shows the table now exists:
mysql> show tables;
+--------------------+
| Tables_in_cookbook |
+--------------------+
| limbs |
| schema_version |
+--------------------+
2 rows in set (0.00 sec)
And the contents:
mysql> select * from schema_version;
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
| installed_rank | version | description | type | script | checksum | installed_by | installed_on
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
| 1 | 5 | Baseline prior to Flyway | BASELINE | Baseline prior to Flyway | NULL | root | 2017-06-12 14
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
1 row in set (0.00 sec)
We can use the “info” verb on the command-line to review the current version according to Flyway:
$ flyway info
Flyway 4.2.0 by Boxfuse
Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Baselin |
+---------+--------------------------+---------------------+---------+
$
Both schema and table name can be changed in the .conf, but it is probably better to leave it at default. For testing migrations on a freshly-built test database, there is a setting in the .conf that allows automatic default baselining if the schema table is not detected:
flyway.baselineOnMigrate=true
This can be very handy, along with specifying the DB user password, if security is not a priority.
Migrating the DB to a new version
Our current DB version is 5, but I’m going to create scripts for the versions either side of that, in order to see what Flyway does.
The script filenaming convention is documented here:
There are a number of possibilities including versioning scripts; repeatable scripts; and SQL callback scripts for invoking at various points during the process. For now, we’re just looking at versioning scripts.
Consider the following script files:
$ ls -l sql/*.sql
-rw-rw-r-- 1 colin colin 64 Jun 12 14:35 sql/V4__other_objects.sql
-rw-rw-r-- 1 colin colin 80 Jun 12 14:35 sql/V5__limbs.sql
-rw-rw-r-- 1 colin colin 62 Jun 12 14:36 sql/V6__test_table2.sql
$
Note: it may be tricky to remember that the name convention requires a double-underscore (“dunder”) between the version number and the file name!
Now, if we run the “info” verb:
Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6 | test table2 | | Pending |
+---------+--------------------------+---------------------+---------+
$
Flyway detects this information from a) the script file naming convention, and b) the contents of the schema_version table in the target DB.
Questions:
- Can we have more than one script belonging to a specific version?
- If so, what is the execution sequence?
- Can we have sub-versions (e.g. 5.1, 5.2 etc)?
The answer is that Flyway will throw an error if it detects two scripts associated with the same version number, so in fact you MUST use sub-version numbers for multiple scripts, like this:
V6_0__test_table2.sql
V6_1_1_test_table1.sql
V6_1_2__test_table3.sql
Of course, this now dictates the execution sequence:
$ flyway info
Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6.0 | test table2 | | Pending |
| 6.1.1 | test table1 | | Pending |
| 6.1.2 | test table3 | | Pending |
+---------+--------------------------+---------------------+---------+
Now, let’s migrate!
$ flyway migrate
Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Successfully validated 5 migrations (execution time 00:00.018s)
Current version of schema `cookbook`: 5
Migrating schema `cookbook` to version 6.0 - test table2
Migrating schema `cookbook` to version 6.1.1 - test table1
Migrating schema `cookbook` to version 6.1.2 - test table3
Successfully applied 3 migrations to schema `cookbook` (execution time 00:00.115s).
$
mysql> show tables;
+--------------------+
| Tables_in_cookbook |
+--------------------+
| limbs |
| schema_version |
| test1 |
| test2 |
| test3 |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from schema_version;
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
| installed_rank | version | description | type | script | checksum | install
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
| 1 | 5 | Baseline prior to Flyway | BASELINE | Baseline prior to Flyway | NULL | root
| 2 | 6.0 | test table2 | SQL | V6_0__test_table2.sql | 224077121 | root
| 3 | 6.1.1 | test table1 | SQL | V6_1_1__test_table1.sql | 669488585 | root
| 4 | 6.1.2 | test table3 | SQL | V6_1_2__test_table3.sql | -1574979834 | root
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
4 rows in set (0.00 sec)
And now:
$ flyway info
Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+--------------------------+---------------------+---------+
| 4 | other objects | | <Baseln |
| 5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6.0 | test table2 | 2017-06-12 15:01:22 | Success |
| 6.1.1 | test table1 | 2017-06-12 15:01:22 | Success |
| 6.1.2 | test table3 | 2017-06-12 15:01:22 | Success |
+---------+--------------------------+---------------------+---------+
It’s pretty cool!
If you un-select that option, you get this generated instead:
Recent Comments