Using Maven to Integrate Flyway Migrations in an Existing Database- Part 2

This is the second article of a two-part series on using Maven to integrate Flyway in an existing database. It addresses several key issues including using Maven profiles to manage promotion of migrations to hierarchical levels of a database as well as managing migrations across multiple disparate projects that use the same database.

Unless you are an experienced Maven and Flyway user, it is suggested you review the first article of this series, Using Maven to Integrate Flyway in an Existing Database- Part 1, as a foundation for the details presented in this article. You can find it here. We will be using the same project as an example.

Continuing Flyway’s Configuration…

As we stated in the first article, Flyway has proven to be a good tool for managing database versioning and migrations, but it lacks a “built-in” facility to manage migrations to multiple levels of the same database, e.g., development, QA, staging, and production. However, we did learn the concept of using configuration parameters to communicate database specifics such as the JDBC driver, database URL, database user name, and user password among others to the Flyway tasks.

Unfortunately, the format of the configuration medium varies depending on the method used to employ Flyway. So depending on which Flyway component(s) you use, you may need to maintain several versions of the configuration data.

1. The Flyway Maven plugin configuration may be specified via system properties or via the <configuration> group of tags inside the definition of the plugin in the <pluginManagement> section of a parent POM or in the <plugins>section of the Maven POM for the build in which the plugin is to be executed.
2. The Flyway Ant task configuration may be specified via system properties or via either task attributes or specific configuration tags inside the execution of the task in the Ant build file.
3. The Flyway Command Line Tool uses a properties file in a specific location within a specific directory structure.
4. And finally, the Java migrations may set a Properties object on the Flyway core class object or set them individually on the object.

Although system properties could be used to convey the database specifics to most of the Flyway components since they all reference the same system properties, that would preclude running the Flyway component on the same system for more than one database, unless, of course, the execution script itself sets the system properties. This would include multiple levels of the same database.

At a minimum, you should maintain at least one set of configurations for each of the levels of the database for which you are intending to automate migrations. You should also devise a methodology to use those differing configurations in executing the Flyway tasks against the different levels of your database. In this article we are going to modify the sample project we used in the first article to use Maven profiles for this purpose.

Maven Profiles to the rescue…

Again, this is not a tutorial on the purpose of or how to use Maven profiles. Suffice it to say Maven profiles can be used for a myriad of things, one of which is the setting of property values. This permits one to define properties and values in a profile and then use that profile for a given execution of Maven. Maven then substitutes the reference of the property in all POMs of the execution with the value specified in the profile.

Maven profiles can be defined in two contexts: in the Maven settings file and in a POM for a particular project. The obvious advantage of defining a profile in the Maven settings file is that it can then be referenced in any Maven execution on the system on which it is defined. However, that may be a disadvantage as well in that, generally, properties whose values are specific to a project should be defined within the project’s POM, not in a global settings file.

So we modified the parent POM for our project adding four profiles, one for each of the levels of our database: development, QA, staging, and production. Within each of these profiles, we defined properties for each of the Flyway configuration items that may differ from one database level to the next, specifically the JDBC driver, the database URL, the database schema, the database user credentials, and the database name.

NOTE: We have also employed the Maven password encryption feature to eliminate clear text passwords in our Maven POM file (see Password Encryption in the Maven Guide). When using this feature, you must encrypt a master password and define it in the Maven security-settings.xml file as well as encrypt the password for each individual database. The database passwords as well as the database user names are then specified via a <server> definition in the Maven settings.xml file. The id of the server is then specified via the <serverId> Flyway <configuration> parameter in place of the individual database user's credentials.

We added these server definitions to the <servers> section of the Maven settings.xml file:

<settings>
   . . .
   <servers>
      <server>
         <id>flyway-dev</id>
         <username>SA</username>
         <password>{QUXCK6DU8FtcS5P=}</password>
      </server>
      <server>
         <id>flyway-QA</id>
         <username>SA-QA</username>
         <password>{AXWK8jU8ob5dse#}</password>
      </server>
      <server>
         <id>flyway-staging</id>
         <username>SA-ST</username>
         <password>{MCVFo59x0#sdlk=}</password>
      </server>
      <server>
         <id>flyway-prod</id>
         <username>SA-PR</username>
         <password>{ZYpl57s2kchD3%}</password>
      </server>
   </servers>
</settings>

We added these profiles to the <profiles> section of our parent POM:

<profiles>
   . . .
   <profile>
      <id>flyway-dev</id>
      <properties>
         <database.name>flyway_dev</database.name>
         <database.driver>
            com.microsoft.sqlserver.jdbc.SQLServerDriver
         </database.driver>
         <database.url>
            jdbc:sqlserver://localhost:1433;databaseName=flyway_dev
         </database.url>
         <database.serverId>flyway-dev</database.serverId>
         <database.schemas>dbo</database.schemas>
      </properties>
   </profile>
   <profile>
      <id>flyway-QA</id>
      <properties>
         <database.name>flyway_QA</database.name>
         <database.driver>
            com.microsoft.sqlserver.jdbc.SQLServerDriver
         </database.driver>
         <database.url>
            jdbc:sqlserver://QAserver:1040;databaseName=flyway_QA
         </database.url>
         <database.serverId>flyway-QA</database.serverId>
         <database.schemas>dbo</database.schemas>
      </properties>
   </profile>
   <profile>
      <id>flyway-staging</id>
      <properties>
         <database.name>flyway_staging</database.name>
         <database.driver>
            com.microsoft.sqlserver.jdbc.SQLServerDriver
         </database.driver>
         <database.url>
            jdbc:sqlserver://stagingDB:1040;databaseName=flyway_staging
         </database.url>
         <database.serverId>flyway-staging</database.serverId>
         <database.schemas>dbo</database.schemas>
      </properties>
   </profile>
   <profile>
      <id>flyway-prod</id>
      <properties>
         <database.name>flyway_prod</database.name>
         <database.driver>
            com.microsoft.sqlserver.jdbc.SQLServerDriver
         </database.driver>
         <database.url>
            jdbc:sqlserver://prodDB:1040;databaseName=flyway_prod
         </database.url>
         <database.serverId>flyway-prod</database.serverId>
         <database.schemas>dbo</database.schemas>
      </properties>
   </profile>
</profiles>

We also changed the configuration in our plugin definitions to reference the properties.

<plugin>
   <groupId>com.googlecode.flyway</groupId>
   <artifactId>flyway-maven-plugin</artifactId>
   . . .
   <configuration>
      <driver>${database.driver}</driver>
      <url>${database.url}</url>
      <serverId>${database.serverId}</serverId>
      <schemas>${database.schemas}</schemas>
   </configuration>
</plugin>

One more note about Maven profiles. You can, if you wish, specify a default active profile so that when executing Maven for the project you do not need to specify the profile to use on the command line. If you were creating a Maven project (POM) exclusively for Flyway migrations, you might want to set the flyway-dev profile as the default using the <activeByDefault> tag so you wouldn’t need to specify it when running migrations against your dev database.

Alternatively, if you’ve modified an existing Maven project as we have you may wish to just add the Flyway properties to existing profiles you use for the differing environments. In either case, activation of the profile, either by default or explicitly via the command line -P argument, will cause Maven to use the values for the properties defined in the respective profile. In our example, we specify the profile via the -P command line argument but the results are the same.

[:flyway-test ]$ mvn flyway:history -P flyway-dev 
[INFO] Scanning for projects...
[INFO] ------------------------------------------------------------------------
[INFO] Reactor Build Order:
[INFO] 
[INFO] MyApp :: Parent
[INFO] MyApp :: XSD :: XMLBeans Generated
[INFO] MyApp :: Domain Layer
[INFO] MyApp :: Web Application
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Parent 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:history (default-cli) @ myapp-parent ---
[INFO] <skip>true</skip> detected.
[WARNING] Skipping execution.
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: XSD :: XMLBeans Generated 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:history (default-cli) @ myapp-xmlbeans-generated ---
[INFO] <skip>true</skip> detected.
[WARNING] Skipping execution.
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Domain Layer 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:history (default-cli) @ myapp-domain ---
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | 0           | << Flyway Init >>      | 2012-06-27 08:39:40 | SUCCESS |
[INFO] | 1.0         | load-flyway test-schem | 2012-06-27 08:40:02 | SUCCESS |
[INFO] | 1.1         | add-flyway test-table  | 2012-06-27 09:00:36 | SUCCESS |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Web Application 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:history (default-cli) @ myapp-web ---
[INFO] <skip>true</skip> detected.  
[WARNING] Skipping execution.
[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary:
[INFO] 
[INFO] MyApp :: Parent ..................................... SUCCESS [1.002s]
[INFO] MyApp :: XSD :: XMLBeans Generated .................. SUCCESS [0.057s]
[INFO] MyApp :: Domain Layer ............................... SUCCESS [0.894s]
[INFO] MyApp :: Web Application ............................ SUCCESS [0.188s]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.211s
[INFO] Finished at: Wed Jun 27 14:21:50 EDT 2012
[INFO] Final Memory: 10M/554M
[INFO] ------------------------------------------------------------------------
[:flyway-test ]$ 

Flyway Placeholders…

You may have noticed that there is one property we defined in the profiles that we did not use in the configuration of the Flyway Maven plugin- <database.name>. Although we have not used it as yet in our example, we have used it in the real project.

Flyway has a feature much like the Maven profile <properties> feature in that you can specify a set of property-like value pairs in the Flyway component’s configuration which it then uses for substitution in SQL migration scripts prior to actually executing the script. These properties are called “placeholders” and are specified in a <placeholders> section of the configuration like the following:

<configuration>
   . . .
   <placeholders>
      <database>${database.name}</database>
   </placeholders>
   <placeholderPrefix>$[</placeholderPrefix>
   <placeholderSuffix>]</placeholderSuffix>
</configuration>

You may also specify the delimiters which you will use when including the placeholders in your SQL scripts. In this example, we’ve added the <database> placeholder definition to our configuration and specify the $[ ] notation for placeholders in our migration scripts. When the database name is required in our migration scripts, then, we use the $[database] notation. This allows us to use the same migration script for all levels of the database.

Versioning for horizontal migrations…

One last topic which is also not addressed by the “vanilla” Flyway implementation is how to automatically maintain the “version” of SQL migrations across multiple projects that use the same database. Basically, the Flyway documentation suggests a numbering scheme for versioning which must be specified as part of the SQL migration script file name in a specific position within the name.

In fact, it’s a requirement since Flyway uses the ascending values of the version to determine which migrations should be applied to a database. When applying migrations, Flyway looks at the highest value of this version that has already been applied and only applies migrations of a higher value (see “How Does Flyway Work”).

Although this concept may be satisfactory for a siloed project with a team of developers all using the same code base, i.e., src/main/resources/database/migrations, it presents a problem for multiple teams of developers using disparate code bases, each with their own src/main/resources/database/migrations directories, but programming to the same database. You may think this is a rarity and presents additional source code management issues beyond that which we describe here, but in my consulting experience I have seen this scenario more than once and, in fact, is the case with the client whose project I used as an example.

Although the Release.Version.Modification (1.0.1) numeric algorithm works fine for most projects, we needed a more universal numbering scheme if we were to be able to automatically manage version numbers across multiple projects. The naming requirement for the version portion of Flyway SQL migration file name requires all numeric digits separated by periods (.) to denote the levels of migrations.

We chose a date-time stamp to the hundredth of a second as our version numbering template. Shell script and batch files were developed to generate a SQL migration file to the project’s database migrations source directory. The file name follows the Flyway convention of V-version__description.sql. This script is then executed by all developers of all of the projects and virtually guarantees the scripts will have unique names and be in the correct sequence.

Using this concept for our example project, we renamed the two SQL migration script files from V-1.0__load-flyway_test-schema.sql and V-1.1__add-flyway_test-table.sql to V-20120627.13.03.55_00__load-flyway_test-schema.sql and V-20120627.13.05.12.00__add-flyway_test-table.sql, respectively.

After cleaning the database and running the renamed scripts, the history of database migrations looks like this:

[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Domain Layer 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:history (default-cli) @ myapp-domain ---
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | 0           | << Flyway Init >>      | 2012-06-27 15:40:44 | SUCCESS |
[INFO] | 20120627.13.03.55.00| load-flyway test-schem | 2012-06-27 15:41:08 | SUCCESS |
[INFO] | 20120627.13.05.12.00| add-flyway test-table  | 2012-06-27 15:41:08 | SUCCESS |
[INFO] +-------------+------------------------+---------------------+---------+

Handling horizontal migrations from multiple projects…

In reality, the sample project we are using in this example is one of several which use the same database, each with their own code base and, as a result, individual database migration source directories. Up to this point in our example we have been running the Flyway plugin in only the build for our domain module. We now need to add all of the projects that use the database to our Flyway migration project.

But, because of Flyway’s method of applying migrations, if we run the Flyway migrate goal in each of the modules independently, we cannot guarantee they will all be applied and in the correct sequence. Since Flyway uses the version of the last migration applied as a starting point in applying new migrations, we need to insure that ALL migrations from ALL projects are considered in the process. This means we need to maintain all migrations in the same directory that Flyway will reference in its migration algorithm. We can use a Maven plugin for this.

We will need to change our migration process. Rather than run Flyway in each of the modules that have database migrations, we will change the process to copy the database migrations from each individual module’s target directory to a parent target directory using the maven-resources-plugin. We will then add a final module to the build that will perform the Flyway tasks.

The maven-resources-plugin facilitates copying of resources from non-standard locations to a common target destination. By binding the maven-resources-plugin's copy-resources goal to the process-resources phase, it will execute after the default process-resources goal which copies and filters resources to the build output directory. We will configure the plugin to copy the database migration files from that build output directory to a top-level target/database/ migrations directory.

To do this, we added the plugin’s definition to the <pluginManagement> section of our parent POM so it is executed in each module. When additional modules are added to the parent project, their database migrations will be copied automatically since the plugin is inherited.

The plugin configuration specifies the top-level target/database/migrations directory as the output destination. The input directory, i.e., the source of the copy, is specified as a <resource>. Since this plugin is executed after the default process-resources goal, the resources to be copied, i.e., the database migrations, are in the project’s build output directory. Filtering is not required since the files would have been filtered in the default goal if requested.

The definition of the maven-resources-plugin looks like this:

<plugin>
   <artifactId>maven-resources-plugin</artifactId>
   <version>2.5</version>
   <executions>                                                                                   
      <execution>                                                                                
         <id>copy-resources</id>                                                                
         <phase>process-resources</phase>                                                       
         <goals>                                                                                
            <goal>copy-resources</goal>                                                        
         </goals>                                                                               
         <configuration>                                                                        
            <outputDirectory>
               ../target/database/migrations
            </outputDirectory>                   
            <encoding>UTF-8</encoding>                                                         
            <resources>                                                                        
               <resource>                                                                     
                  <directory>
                     ${project.build.outputDirectory}/database/migrations
                  </directory>
                  <filtering>false</filtering>                                               
               </resource>                                                                    
            </resources>                                                                       
         </configuration>                                                                       
      </execution>                                                                               
   </executions>                                                                                  
</plugin>

We test the plugin by running mvn compile flyway:history –P flyway-dev.

[INFO] 
[INFO] --- maven-resources-plugin:2.5:resources (default-resources) @ myapp-domain ---
[debug] execute contextualize
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 136 resources
[INFO] Copying 3 resources
[INFO] Copying 7 resources
[INFO] 
[INFO] --- maven-resources-plugin:2.5:copy-resources (copy-resources) @ myapp-domain ---
[debug] execute contextualize
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 2 resources
[INFO] 
[INFO] --- maven-compiler-plugin:2.3.2:compile (default-compile) @ myapp-domain ---
[INFO] Nothing to compile - all classes are up to date
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:history (default-cli) @ myapp-domain ---
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | 0           | << Flyway Init >>      | 2012-06-27 15:40:44 | SUCCESS |
[INFO] | 20120627.13.03.55.00| load-flyway test-schem | 2012-06-27 15:41:08 | SUCCESS |
[INFO] | 20120627.13.05.12.00| add-flyway test-table  | 2012-06-27 15:41:08 | SUCCESS |
[INFO] +-------------+------------------------+---------------------+---------+

We can see from the output that the copy-resources goal was run after the default-resources goal and copied two files. Those two files are the database migration files which were copied to the parent target/database/migrations directory.

Completing the modifications…

Next we added an additional child module to the parent POM. Like the domain child module, the services child module contains a database migration to add a new table to the database. Then we added a new module to the parent POM which we called database-migrations. The sole purpose of this module is to run the Flyway plugin.

We created the database-migrations directory under our parent project directory and created a pom.xml file in the new database-migrations directory including the Flyway plugin. This is the only file in that directory since the purpose of this module is to only run the Flyway plugin.

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
                             http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>com.myapp</groupId>
        <artifactId>myapp-parent</artifactId>
        <version>3.15.BUILD-SNAPSHOT</version>
    </parent>
    <artifactId>myapp-database</artifactId>
    <packaging>pom</packaging>
    <name>MyApp :: Database Migrations</name>

    <build>
        <outputDirectory>../target</outputDirectory>
        <plugins>
            <!-- Flyway database versioning / migration plugin -->
            <plugin>
                <groupId>com.googlecode.flyway</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>1.6.2-SNAPSHOT</version>
                <configuration>
                    <skip>false</skip>
                    <baseDir>database/migrations</baseDir>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Since Flyway looks in the runtime classpath for its migrations files, we need to override the default build output directory for this module. The build output directory is always included in the runtime classpath. The default build output directory is target/classes. We override it by specifying ../target which points to the target directory one level above this project’s build directory, or to the target directory of the parent directory. Flyway will look in the classpath, i.e., the parent target directory, for a directory named database/migrations which we specified as the value of the <baseDir> configuration parameter.

We then added the definitions of the additional child modules to the parent POM. If you do this, make sure the database-migrations child module is specified last as in the following example:

<modules>
    <module>xsd</module>
    <module>domain</module>
    <module>services</module>
    <module>web</module>
    <module>database-migrations</module>
</modules>

Our last modification is to remove the Flyway plugin definition from the child domain module POM file so the Flyway plugin is not executed. To test our modifications we will clean the database using the flyway:clean goal, initialize it with the flyway:init goal, and then run flyway:migrate to insure Flyway uses the top-level parent target directory for its migration process.

After running the migration, we can see from the output of flyway:history that all three migrations were applied to our database:

[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Database Migrations 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:history (default-cli) @ myapp-database ---
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | 0           | << Flyway Init >>      | 2012-06-28 13:27:05 | SUCCESS |
[INFO] | 20120627.13.03.55.00| load-flyway test-schem | 2012-06-28 13:27:32 | SUCCESS |
[INFO] | 20120627.13.05.12.00| add-flyway test-table  | 2012-06-28 13:27:33 | SUCCESS |
[INFO] | 20120628.13.15.52.00| add-another-flyway test-table  | 2012-06-28 13:27:33 | SUCCESS |
[INFO] +-------------+------------------------+---------------------+---------+

Mission Accomplished…

Using Maven profiles we are now able to apply the database migrations to each succeeding level of the database. And via a standard naming convention using a generated file name containing a date-timestamp as a version number we are able to guarantee database migration file names are unique throughout several projects using the same database. Using the maven-resources-plugin to copy all database migrations files to a common location, we can also guarantee that all database migrations will be applied in sequence by restricting execution of the Flyway plugin goals to only one child module, the last one specified.

The only desire left is to be able to apply any migration to a database if it has not already been applied. Currently, Flyway looks at the highest value of the version that has already been applied to a database which it has recorded in the schema_version table and only applies migrations in the <baseDir> directory whose file name’s version is of a higher value (see “How Does Flyway Work”). Hopefully, a future release of Flyway will support this behavior, as it will then solve the last of our database migrations issues, namely developers committing database migrations out of sequence in multiple projects.

Using Maven to Integrate Flyway Migrations in an Existing Database- Part 1

I was recently asked by a client to investigate database versioning / migration tools. Having had previous experience with Rails db Rake tasks and Liquibase, both of which require a DSL, I looked for a more Java-friendly tool and found Flyway.

One of the pre-requisites was to be able to integrate the tool with an existing database and SQL scripts to reduce the learning curve for application development personnel. We were also looking for a tool we could run with either Ant or Maven as the client has multiple projects using both technologies. Flyway offered that with its support of native SQL migrations including support for stored procedures and triggers as well as a Maven plugin and an Ant task. The Flyway command line tool (CLT) and Java migrations are an added bonus.

Disclaimer: This is not a testimonial for Flyway versus any other tool, just an accounting of what it took to get it working for an existing database. Although the Flyway wiki and documentation is adequate, I had a difficult time getting all the wheels turning in the same direction. In fact, I found myself doing things that were not really explained nor even touched on in the Flyway wiki pages. I thought, “What better way to communicate that than through a blog?” BUT, YMMV- use this article at your own risk.

Java migrations are Java classes that invoke the Flyway Java core classes to perform database versioning and migration. These can be useful for “on-the-fly” migrations, i.e., at application startup, to automagically bring the target database up to speed with respect to its infrastructure, etc., as opposed to requiring a DBA or whomever to execute a series of steps prior to starting the application in a production environment. There is a sample available on the Flyway Downloads page that illustrates this feature.

For this exercise, I concentrated on getting the Maven plugin to work, but this was not without its trials. What follows is an outline of the steps I took to get Flyway working using the Maven plugin. Although we are using Maven 3 for this project, I don’t think there is anything in this article that is Maven 3-specific. That being said, you may see different results if you are not using Maven 3.

I selected a sample Maven project from the client that had several modules. In addition to the domain module, the project also has a web application as well as XML-centric tasks. The database for this sample project is a SQLServer database as are most of this client’s databases. The DBM should not matter either unless you are using an unsupported database. Use caution if your DBM is not one of those listed as a Flyway-supported database.

Installing Flyway…

First I installed Flyway according to the “Installation” section of Flyway’s home page. You should install the tool to support your specific needs. I added the Flyway dependency and the Maven plugin definition to the respective sections of the parent POM of my sample project as described in the installation instructions.

<dependencies>
    <dependency>
        <groupId>com.googlecode.flyway</groupId>
        <artifactId>flyway-core</artifactId>
        <version>1.6.1</version>
    </dependency>
...
</dependencies>
...
<build>
    ...
    <plugins>
        <plugin>
            <groupId>com.googlecode.flyway</groupId>
            <artifactId>flyway-maven-plugin</artifactId>
            <version>1.6.1</version>
            <configuration>
                ....
            </configuration>
        </plugin>
        ...
    </plugins>
    ...
</build>

That was easy enough… or was it?

To test the installation, I figured I’d start simple and just execute the flyway:status goal. So I looked at the Maven Plugin Status goal page on the Flyway wiki and found I nedeed to tell Flyway about our database. You do that via configuration parameters.

Configuring Flyway…

The basic configuration of Flyway is simple. You need to tell Flyway which DBM you are using via the JDBC driver, the JDBC URL to the database, the database user to be used in Flyway operations, the password for that user, and the database schema to be acted upon. There are other configuration parameters that are not used by the flyway:status goal and we will detail them later when we use them.

For now, I added the following configuration to the plugin definition in the POM file:

<configuration>
    <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
    <url>jdbc:sqlserver://localhost:1433;databaseName=flyway_db</url>
    <user>SA</user>
    <password>thePwd</password>
    <schemas>dbo</schemas>
</configuration>

Then I ran mvn flyway:status which resulted in my first error:

[INFO] --- flyway-maven-plugin:1.6.1:status (default-cli) @ parent-pom ---
[ERROR] com.googlecode.flyway.core.exception.FlywayException: Unable to
        instantiate jdbc driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

Although the SQL Server driver dependency was already configured in the Maven project, the error above stated that it couldn't find the driver. Why didn't this work?

A word about clear text passwords in configuration files-

It’s a no-no. Ok, that was three (or four) words. But for simplicity’s sake, we show it here (it’s not real anyway). Maven provides a facility to encrypt passwords included in settings files (see Password Encryption in the Maven Guide). I’d recommend the password value be encrypted and in this example be replaced by a property, e.g., ${flyway.password}, which is set in a profile in a settings file. In fact, use of Maven profiles is a topic in the next blog in this series.

Including Flyway’s Dependencies…

After referring to the Maven POM Reference, I remembered plugin dependencies should be declared with the plugin definition itself. So I included the SQL Server Driver as a dependency as well as the dependency to Flyway Core since it was the only component using it anyway. The Flyway wiki doesn’t touch on this, so lesson learned.

So now the Flyway plugin definition looked like this:

<plugin>
    <groupId>com.googlecode.flyway</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>1.6.1</version>
    <dependencies>
        <dependency>
            <groupId>com.microsoft.jdbc</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>3.0</version>
        </dependency>
        <dependency>
            <groupId>com.googlecode.flyway</groupId>
            <artifactId>flyway-core</artifactId>
            <version>1.6.1</version>
        </dependency>
    </dependencies>
    <configuration>
      <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
       <url>jdbc:sqlserver://localhost:1433;databaseName=flyway_dev</url>
       <user>SA</user>
       <password>thePwd</password>
       <schemas>dbo</schemas>
    </configuration>
</plugin>

Success… or was it? The Maven goal ran and produced the expected output, but it ran only in the build for the parent POM. So I moved the plugin definition to the POM for the module in which I wanted it to run, i.e., where the db migrations are actually defined, and ran mvn flyway:status again. This time Maven failed with a message indicating the plugin could not be found. That made sense- I was trying to run a plugin goal from the top level but the plugin is not defined in the top level POM.

Plugin, Plugin, Who’s Got the Plugin?

So, after referring to the Maven POM Reference again, I copied the plugin definition to the <pluginManagement> section of the <build> section of the parent POM. So, at this point I had the plugin defined in the <pluginManagement> section in the parent POM and in the <plugins> section of the <build> section of the child POM. However, I removed the <dependencies> from the plugin definition in the child POM since they are inherited from the <pluginManagement> definition in the parent POM.

I again ran mvn flyway:status and it executed the plugin in the parent POM as well as in each of the child POMs. Hmmm… that’s not what I wanted. I wanted the plugin to execute only in the build for the domain child POM in which it is defined. Since the requirements for executing the migration goal of the plugin, e.g., directories, etc., are only present in the domain project built by the domain child POM, running that goal of the plugin in the parent and other child projects will result in an error.

The trouble with plugin definition location in parent and child projects…

I struggled with this for a while and finally came to the conclusion (since it is not definitively described in the Maven POM Reference) that the <pluginManagement> section is a convenient way to define plugins in a parent POM to be inherited in ALL child POMs unlike I was lead to believe by these statements in the Maven documentation under <pluginManagement>:

“However, this only configures plugins that are actually referenced within the <plugins> element in the children.”

and

“However, if we apply them under the <pluginManagement> element, then this POM and all inheriting POMs that add the maven-jar-plugin to the build will get the pre-process-classes execution as well.”

Including the plugin in the child POM is NOT an indication that it is to be run in the build for that POM, but rather a mechanism to override whatever was specified for the plugin configuration in the parent <pluginManagement> section. Plugins are inherited from the <pluginManagement> section and will be invoked in all child POMs. Apparently enough people complained about this since there is an issue and work being done for Maven 3.1 to support being able to “skip” execution of a plugin in a child module (see http://jira.codehaus.org/browse/MNG-3102).

Although there isn’t a hint about running the Maven plugin in a multi-module environment in the Flyway documentation, there is a Flyway issue (here) addressing this behavior. Unfortunately, the suggested workaround (wrapping the Flyway execution in a Maven profile) did not work for my scenario.

I actually ended up modifying the Flyway Maven plugin to accept the <skip> tag printing a message that the execution is being skipped. I then modified the <pluginManagement> plugin definition to include the <skip>true</skip> tag so it is inherited by all of the child modules. I also modified the POM of the child module for the build in which I do want the plugin to execute adding <skip>false</skip> to the configuration.

Now I had the behavior I wanted- I ran mvn flyway:status from the top level and it only executed in the child module desired.

[:flyway-test ]$ mvn flyway:status  
[INFO] Scanning for projects...
[INFO] ------------------------------------------------------------------------
[INFO] Reactor Build Order:
[INFO] MyApp :: Parent
[INFO] MyApp :: XSD :: XMLBeans Generated
[INFO] MyApp :: Domain Layer
[INFO] MyApp :: Web Application
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Parent 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:status (default-cli) @ myapp-parent ---
[INFO] <skip>true</skip> detected.
[WARNING] Skipping execution.
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: XSD :: XMLBeans Generated 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:status (default-cli) @ myapp-xmlbeans-generated ---
[INFO] <skip>true</skip> detected.
[WARNING] Skipping execution.
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Domain Layer 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:status (default-cli) @ myapp-domain ---
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | No migrations applied yet                                            |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Web Application 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:status (default-cli) @ myapp-web ---
[INFO] <skip>true</skip> detected.
[WARNING] Skipping execution.
[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary:
[INFO] MyApp :: Parent ..................................... SUCCESS [0.846s]
[INFO] MyApp :: XSD :: XMLBeans Generated .................. SUCCESS [0.058s]
[INFO] MyApp :: Domain Layer ............................... SUCCESS [0.974s]
[INFO] MyApp :: Web Application ............................ SUCCESS [0.171s]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.762s
[INFO] Finished at: Wed Jun 27 08:30:08 EDT 2012
[INFO] Final Memory: 10M/554M
[INFO] ------------------------------------------------------------------------
[:flyway-test ]$ 

Let’s be a little more adventurous. How about actually running a migration?

Finally! Running migrations…

Before we do that, we first have to follow the roadmap for implementing Flyway in an existing database provided by the Flyway Existing Database Setup wiki page. As suggested, I dumped the database I was using and created a script. But being the skeptic I am, I decided to create another database to experiment with as opposed to mucking up a real live database that I use for productive work.

For those of you trying this yourselves, don’t forget to change the plugin configuration to point to the new database especially if you originally configured it to point to the database you just dumped.

Since the roadmap instructs you to clean the existing database, as opposed to dropping it and recreating it, it was obvious the roadmap is starting with a clean database. So I created an empty test database and ran mvn flyway:init as suggested in the roadmap. Perfect- Flyway created the schema_version table with the default entries.

[INFO]  ------------------------------------------------------------------------
[INFO] Building MyApp :: Domain Layer 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:init (default-cli) @ myapp-domain ---
[INFO] Metadata table created: schema_version (Schema: dbo)
[INFO] Schema initialized with version: 0

I named the script I created from dumping the database schema to follow the suggested naming conventions for migrations, which I don’t like (but that’s a subject for a future blog). I also didn’t like Flyway’s default name for the directory to hold the database migrations, db/migration. I like to be a little more explicit, so I created a resources directory named database/migrations. I added the <baseDir> configuration parameter specifying the directory:

<configuration>
   <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
   <url>jdbc:sqlserver://localhost:1433;databaseName=flyway_db</url>
   <user>SA</user>
   <password>thePwd</password>
   <schemas>dbo</schemas>
   <baseDir>database/migrations</basedir>
</configuration>

I then copied the script file to this directory and gave it a shot – mvn flyway:migrate. Hmmm…. -

"Unable to find path for sql migrations: database/migrations"

Why couldn’t it find the migrations? The directory I created, src/main/resources/database/migrations wasn’t exactly named as suggested on the Flyway SQL Migrations page, but I specified it in the configuration and it was there.

Again, a struggle- I tried moving the directory, renaming it, changing the configuration to include the full path to the directory, all to no avail. Then I searched the Flyway issues and found one in which the reply was to run mvn compile flyway:migrate.

Although there is a warning at the top of the migrate goal plugin page,

Important: To make sure new migrations are taken into account, mvn compile must be invoked first!

I really didn’t pay attention to it- my bad. And I must have missed this statement on the SQL Migrations page:

New sql migrations are discovered automatically through classpath scanning at runtime.

The keyword here is runtime. It’s the runtime classpath that is being scanned, which includes the Maven output (target) directory, not the source directory. So just defining the migration file in the source directory is only half the job. You need to force Maven to copy the resources to the target directory via compile, package, etc., so they can be discovered by Flyway. As part of the build, Maven copies the contents of src/main/resources, including the database/migrations directory and its contents, to the target/class/database/migrations directory.

That did the trick. The plugin ran and updated the database using the initial load script defined in the src/main/resources/database/migrations directory. It also updated the schema_version table with the version and description of the migration file.

[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Domain Layer 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- maven-resources-plugin:2.4.3:resources (default-resources) @ myapp-domain ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 136 resources
[INFO] Copying 3 resources
[INFO] Copying 7 resources
[INFO] 
[INFO] --- maven-compiler-plugin:2.3.2:compile (default-compile) @ myapp-domain ---
[INFO] Nothing to compile - all classes are up to date
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:migrate (default-cli) @ myapp-domain ---
[INFO] Validated 0 migrations (mode: ALL) (execution time 00:00.004s)
[INFO] Current schema version: 0
[INFO] Migrating to version 1.0
[INFO] Successfully applied 1 migration (execution time 00:03.040s).

Adding another migration…

Next I created another script to add a table and some reference data. I again ran mvn compile flyway:migrate and Flyway updated the database schema and the schema_version table with the appropriate information.

[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Domain Layer 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- maven-resources-plugin:2.4.3:resources (default-resources) @ myapp-domain ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 136 resources
[INFO] Copying 3 resources
[INFO] Copying 7 resources
[INFO] 
[INFO] --- maven-compiler-plugin:2.3.2:compile (default-compile) @ myapp-domain ---
[INFO] Nothing to compile - all classes are up to date
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:migrate (default-cli) @ myapp-domain ---
[INFO] Validated 1 migration (mode: ALL) (execution time 00:00.003s)
[INFO] Current schema version: 1.0
[INFO] Migrating to version 1.1
[INFO] Successfully applied 1 migration (execution time 00:00.115s).

I then ran each of the remaining Maven Flyway plugin goals to insure the plugin was fully functional. Here’s the output from running mvn flyway:history.

[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building MyApp :: Domain Layer 3.15.BUILD-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- flyway-maven-plugin:1.6.2-SNAPSHOT:history (default-cli) @ myapp-domain ---
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | Version     | Description            | Installed on        | State   |
[INFO] +-------------+------------------------+---------------------+---------+
[INFO] | 0           | << Flyway Init >>      | 2012-06-27 15:40:44 | SUCCESS |
[INFO] | 1.0         | load-flyway test-schem | 2012-06-27 15:41:08 | SUCCESS |
[INFO] | 1.1         | add-flyway test-table  | 2012-06-27 15:41:08 | SUCCESS |
[INFO] +-------------+------------------------+---------------------+---------+

What I learned…

So, the bottom line is Flyway is a good tool for database versioning and migration. It does what it is advertised to do. And the documentation is decent. After tweaking the Maven configuration I was able to get Flyway running in a matter of hours. I didn’t count the time it took to dump the database and tweak the resulting SQL script or write the second script to add a table and reference data. And I also didn’t include the time it took me to make changes to the scripts to get them to run successfully (I’m not an SQL expert).

As a side note, I also did not include the fact that I had to modify the Flyway SQL Migration ClassPathResource class to handle UTF-8 encoded SQL scripts in a friendlier manner. And the reason I did not include that in this article is because the issue has been accepted by the Flyway support team and will be included in a near future release of Flyway.

Post Script:

Although Flyway has proven to be a good tool for managing database versioning and migrations, there is no “built-in” facility to manage multiple databases, i.e., development, QA, staging, and production. Although the “Existing Database Setup” page implies that migrations are automagically applied to all databases with this statement:

As soon as you add a new migration, it will be applied identically to all databases.

Flyway’s <url> configuration specification determines which database is being acted upon. It’s up to you to devise a methodology to “promote” migrations from one database to the next. And that’s one of the topics in the next blog in this series, “Using Maven to Integrate Flyway Migrations in an Existing Database- Part 2”.