In the delivery life cycle, maintaining databases across versions and multiple environments can be a real headache. Flyway is an assertive protection against the entropy that schema changes can induce. Managing and automating migrations, Flyway stands as a tremendously valuable asset for software makers.
In this recipe, we review the Flyway configuration. We especially review its integration in to Maven. This will get every build to upgrade (if necessary) the corresponding database so that it matches the expectation level.
v9.x.x.
/app
directory of your workspace, the cloudstreetmarket.properties
file has been updated. Also, one extra db/migration
directory shows up with a Migration-1_0__init.sql
file inside, as well as a new /logs
directory.home
directory
: <home-directory>/app
.zipcloud-parent
project (right-click on the project Run as… | Maven Clean and then Run as… | Maven Install).cloudstreetmarket-parent
project.pom.xml
(in cloudstreetmarket-parent
), you can notice a new plugin definition:<plugin> <groupId>com.googlecode.flyway</groupId> <artifactId>flyway-maven-plugin</artifactId> <version>2.3.1</version> <inherited>false</inherited> <executions> <execution> <id>package</id> <goals> <goal>migrate</goal> </goals> </execution> </executions> <configuration> <driver>${database.driver}</driver> <url>${database.url}</url> <serverId>${database.serverId}</serverId> <schemas> <schema>${database.name}</schema> </schemas> <locations> <location> filesystem:${user.home}/app/db/migration </location> </locations> <initOnMigrate>true</initOnMigrate> <sqlMigrationPrefix>Migration-</sqlMigrationPrefix> <placeholderPrefix>#[</placeholderPrefix> <placeholderSuffix>]</placeholderSuffix> placeholderReplacement>true</placeholderReplacement> <placeholders> <db.name>${database.name}</db.name> </placeholders> </configuration> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> </dependencies> </plugin>
${database.driver})
used in this definition correspond to default properties, set at the top level of this pom.xml
:<database.name>csm</database.name> <database.driver>com.mysql.jdbc.Driver</database.driver> <database.url>jdbc:mysql://localhost</database.url> <database.serverId>csm_db</database.serverId>
database.serverId
must match a new Server
entry in the Maven settings.xml
file (described in the next point).settings.xml
file (that you must have created in Chapter 1, Setup Routine for an Enterprise Spring Application) located at <home-directory>/.m2/settings.xml
. Add somewhere in the root node the following block:<servers> <server> <id>csm_db</id> <username>csm_tech</username> <password>csmDB1$55</password> </server> </servers>
pom.xml
(in cloudstreetmarket-parent
), a new Profile
has been added to optionally override the default properties (of this pom.xml
):<profiles> <profile> <id>flyway-integration</id> <properties> <database.name>csm_integration</database.name> <database.driver>com.mysql.jdbc.Driver</database.driver> <database.url>jdbc:mysql://localhost</database.url> <database.serverId>csm_db</database.serverId> </properties> </profile> </profiles>
Flyway is a database versioning and migration tool licensed Apache v2 (free software). It is a registered trademark of the company Boxfuse GmbH.
Flyway is not the only product in this category but is widely present in the industry for its simplicity and easy configuration. The migration scripts can be written in plain old SQL and many providers are supported. From classical RDBMS (Oracle, MySQL, SQL Server, and so on) to in-memory DB (HSQLDB, solidDB, and so on), and even cloud-based solutions (AWS Redshift, SQL Azure, and so on).
Flyway provides the six following commands for reporting and operation purposes.
The Migrate
command is the goal we have integrated to the Maven package phase. It looks up the classpath or the filesystem for potential migrations to be executed. Several locations
(script repositories) can be configured. In the Flyway Maven plugin, these locations
are defined in the root configuration
node. Patterns are set up to retain specific filenames.
The Clean
command restores pristine a database schema. All the objects (tables, views, functions, and so on) are dropped with this command.
The Info
command provides feedback about the current state and the migration history of a given schema. If you have a look into your local MySQL server, in the csm
schema, you will notice that a metadata table has been created with the name schema_version
. Flyway uses the following table to compare the script repository state with the database state and to fill the gaps.
version |
description |
script |
installed on |
success |
---|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
The Info
command basically prints out this table as a report.
The
Validate
command can be useful to ensure that the migrations executed on a database actually correspond to the scripts currently present in the repositories.
The Flyway Maven plugin provides the interface for Maven to control the Flyway program. Our configuration of the plugin has been the following:
<plugin> <groupId>com.googlecode.flyway</groupId> <artifactId>flyway-maven-plugin</artifactId> <version>2.3.1</version> <inherited>false</inherited> <executions> <execution> <id>package</id> <goals> <goal>migrate</goal> </goals> </execution> </executions> <configuration> <driver>${database.driver}</driver> <url>${database.url}</url> <serverId>${database.serverId}</serverId> <schemas> <schema>${database.name}</schema> </schemas> <locations> <location> filesystem:${user.home}/app/db/migration </location> </locations> <initOnMigrate>true</initOnMigrate> <sqlMigrationPrefix>Migration-</sqlMigrationPrefix> <placeholderPrefix>#[</placeholderPrefix> <placeholderSuffix>]</placeholderSuffix> <placeholderReplacement>true</placeholderReplacement> <placeholders> <db.name>${database.name}</db.name> </placeholders> </configuration> </plugin>
As usual with Maven plugins, the executions section allows the binding of Maven phases to one or more Goals of the plugin. For Flyway Maven plugin, the goals are the Flyway commands presented previously. We tell Maven when to consider the plugin and what to invoke in this plugin.
Our configuration
section presents a few parameters checked during migrations. For example, the locations
specifies migration repositories to be scanned recursively (they can start with classpath
: or filesystem:
). The schemas
defines the list of schemas managed by Flyway for the whole set of migrations. The first schema will be the default one across migrations.
An interesting feature is the ability to use variables in migration scripts so that these scripts can be used as template for multiple environments. Variable names are defined with placeholders
, and the way variables are identified in scripts is configurable with placeholderPrefix
and placeholderSuffix
.
Flyway is well-documented and actively supported by its community. Read more about the product online at http://flywaydb.org.
You can also follow or contribute to the project through the GitHub repository at https://github.com/flyway/flyway.