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.
- From the Git Perspective in Eclipse, checkout the latest version of the branch
v9.x.x. - In the
/appdirectory of your workspace, thecloudstreetmarket.propertiesfile has been updated. Also, one extradb/migrationdirectory shows up with aMigration-1_0__init.sqlfile inside, as well as a new/logsdirectory. - Please do reflect all these changes to the app directory located in your OS user
homedirectory:<home-directory>/app. - Also ensure that your MySQL Server is running.
- Run the Maven clean and Maven install commands on the
zipcloud-parentproject (right-click on the project Run as… | Maven Clean and then Run as… | Maven Install). - Now, run the Maven clean and Maven install commands on the
cloudstreetmarket-parentproject. - At the top of the stack trace (at the package Maven phase), you should see the following logs:
- At this stage, the database should have been reset to match a standard state of structure and data.
- If you rerun the build again, you should now see the following logs:
- In the parent
pom.xml(incloudstreetmarket-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> - A few variables (for example,
${database.driver})used in this definition correspond to default properties, set at the top level of thispom.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>
- The
database.serverIdmust match a newServerentry in the Mavensettings.xmlfile (described in the next point). - Edit the Maven
settings.xmlfile (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> - In the parent
pom.xml(incloudstreetmarket-parent), a newProfilehas been added to optionally override the default properties (of thispom.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.