java - How to use Flyway configuration to handle multiple databases

Question

we have a java application configured with maven that uses multiple databases. It is one app - many schemas.

I've configured flyway, tested and it works well but my config is only for one database.

Here is my pom.xml tested with one schema:

<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>
  <groupId>com.test</groupId>
  <artifactId>test</artifactId>
  <version>0.0.1-SNAPSHOT</version>


  <build>
        <plugins>
            <!-- Flyway plugin configuration -->
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>3.0</version>
                <configuration>
                    <url>jdbc:mysql://localhost:3306/argentina</url>
                    <user>test</user>
                <password>test</password>
                </configuration>
                <dependencies>
                    <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.21</version>
            </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

  <dependencies>
        <dependency>
              <!-- alllll my dependency list -->
        </dependency>

        <!-- DB dependencies -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
        </dependency>

  </dependencies>
</project>

Update: by using the answer provided now I have the following pom.xml configured with 2 schemas.

<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>
  <groupId>com.test</groupId>
  <artifactId>test</artifactId>
  <version>0.0.1-SNAPSHOT</version>


  <build>
        <plugins>
            <plugin>
            <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>3.0</version>
                <executions>
                    <execution>
                        <id>argentina</id>
                        <phase>compile</phase> <!--whatever phase you need-->
                        <goals>
                            <goal>migrate</goal>
                        </goals>
                        <configuration>
                            <url>jdbc:mysql://localhost:3306/argentina</url>
                            <user>test</user>
                            <password>test</password>
                            <locations>
                                <location>
                                    filesystem:src/main/resources/db/migration                                  
                                </location>
                            </locations>
                        </configuration>
                    </execution>
                    <execution>
                        <id>brazil</id>
                        <phase>compile</phase> <!--whatever phase you need-->
                        <goals>
                            <goal>migrate</goal>
                        </goals>
                        <configuration>
                            <url>jdbc:mysql://localhost:3306/brazil</url>
                            <user>test</user>
                            <password>test</password>
                            <locations>
                                <location>
                                    filesystem:src/main/resources/test2/sql
                                </location>
                            </locations>
                        </configuration>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.21</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

  <dependencies>
        ...
  </dependencies>
</project>

I execute flyway operations but none worked, here is the error I got:

[INFO] Copying 5 resources
[INFO] [compiler:compile {execution: default-compile}]
[INFO] Nothing to compile - all classes are up to date
[INFO] [flyway:migrate {execution: argentina}]
[INFO] Database: jdbc:mysql://localhost:3306/argentina (MySQL 5.5)
[INFO] Validated 4 migrations (execution time 00:00.006s)
[INFO] Current version of schema `argentina`: 45678
[INFO] Schema `argentina` is up to date. No migration necessary.
[INFO] [flyway:migrate {execution: brazil}]
[INFO] Database: jdbc:mysql://localhost:3306/brazil (MySQL 5.5)
[INFO] Validated 1 migration (execution time 00:00.003s)
[INFO] Current version of schema `brazil`: 1
[INFO] Schema `brazil` is up to date. No migration necessary.
[INFO] [flyway:migrate {execution: default-cli}]
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO] ------------------------------------------------------------------------
[INFO] org.flywaydb.core.api.FlywayException: DataSource not set! Check your configuration!

The database configuration is ok. Also I checked the schemas are ok What I am missing?

UPDATE: I removed from the command line flyway: and it worked well. Thanks Jk1

Answer 1

You can specify multiple executions for a single plugin with different configurations:

 <plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>3.0</version>
    <executions>
        <execution>
            <id>first-execution</id>
            <phase>compile</phase> <!--whatever phase you need-->
            <goals>
                <goal>migrate</goal>
            </goals>
            <configuration>
                <url>jdbc:mysql://localhost:3306/schema2</url>
                <user>root</user>
                <password>root</password>
                <locations>
                    <location>
                        filesystem:/path/to/migrations/folder
                    </location>
                </locations>
            </configuration>
        </execution>
        <execution>
            <id>second-execution</id>
            <phase>compile</phase> <!--whatever phase you need-->
            <goals>
                <goal>migrate</goal>
            </goals>
            <configuration>
                <url>jdbc:mysql://localhost:3306/schema1</url>
                <user>root</user>
                <password>root</password>
                <locations>
                    <location>
                        filesystem:/path/to/other/migrations/folder
                    </location>
                </locations>
            </configuration>
        </execution>
    </executions>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
        </dependency>
    </dependencies>
</plugin>

You can use 'location' property to define migrations you want to run for each schema, just like it's done in example above. The location type is determined by its prefix. Unprefixed locations or locations starting with classpath: point to a package on the classpath and may contain both sql and java-based migrations. Locations starting with filesystem: point to a directory on the filesystem and may only contain sql migrations.