flyway-run both SQL and Java based scripts in remote location

Question

Trying to run Java-based (compiled) migration files which are not in the project where Flyway is configured. Can anyone tell me is it possible to do so?

I've created a jar which use flyway to do migrations. Jar expects an argument which is migration scripts' location. Migration scripts are in a different location/project. So far all scripts are SQL based. (i.e. XXX.sql). Need to add a java based migration scripts to it, to do some complex logic.

Tried to add pom.xml to the script location and a sample java migration script in db/migration folder. Java-based migration file is ignored by Flyway. Is it due to checksum validation fail?

Java-based migrations are compiled and .class files are in the classpath. My folder structure as below.

C:/
└── database-migration-scripts
    └── src/main/java/
        └── db
            └── migration
                └── V1__m1.sql
                └── V2__m2.sql
                └── V3__SampleJava_script.java
    └── target/classes/
        └── db
            └── migration
                └── V3__SampleJava_script.class
    └── pom.xml

W:/
└── someLocationOutsideProject
    └── flyway-database-migration.jar

NOTE: flyway runner (jar) and scripts will be in different locations in the same or different machine. In the example above, migration scripts in C:/ and jar in W:/

Answer 1

In order to be discovered, Java migrations should go under src/main/java with the package db.migration

e.g.

package db.migration; // <-- classpath:db/migration

import org.flywaydb.core.api.migration.jdbc.JdbcMigration;
import java.sql.Connection;

public class V3__SampleJava_script implements JdbcMigration {
    public void migrate(Connection connection) throws Exception {
        // your code...
    }
}

Difficult to diagnose without seeing your pom.xml and how your jar is packaged but given the folder structure of your target directory above, perhaps either the V3__SampleJava_script.class is added to the jar under classpath:resources/db/migration or is just not included at all.

To check, try unzipping the jar file:

jar -xf flyway-database-migration.jar

or just listing the contents:

jar -tf flyway-database-migration.jar

It is also worth noting that if you have overridden the locations setting with a filesystem: path, the documentation states that the directory "may only contain sql migrations", any java migrations will just be ignored.

---

Update 2018-06-03

Given that the flyway-database-migration.jar is being pointed to a filesystem: location, only sql migrations will be discovered, not java ones. The database-migration-scripts directory needs to be added to the classpath and the flyway location set to classpath:db/migration.

java -cp C:\database-migration-scripts;<existing classpath> ...

Update 2018-06-09

Because of the way that the flyway-database-migrations.jar is packaged, using the Spring Boot Executable Jar format, all the application dependencies are placed in the BOOT-INF/lib directory inside the executable jar and are loaded by a separate classloader from the org.springframework.boot.loader.JarLauncher main class. So, contrary to the above, I'm not sure if it's possible to pass additional classpath entries to the application using the -cp command line option. I think you would need to remove spring boot and package it as a regular jar file. I certainly encountered class visibility issues and decided to try a different approach.

I downloaded the Flyway Command Line Runner and updated the <RUNNER_DIR>/conf/flyway.conf settings with the following:

flyway.url=jdbc:mariadb://localhost:3306/flyway?createDatabaseIfNotExist=true
flyway.user=root
flyway.password=yourPassword
flyway.locations=classpath:db/migration

I created a directory under <RUNNER_DIR>/jars/ called database-migration-scripts.jar with the following structure (the .jar is important as Flyway will only add files or directories with this suffix to the classpath):

database-migration-scripts.jar/
└── db
    └── migration
        ├── V1__m1.sql
        ├── V2__m2.sql
        └── V3__SampleJava_script.class

Finally, I added all of the runtime dependencies for the database-migration-scripts project to <RUNNER_DIR>/lib:

lib/
├── animal-sniffer-annotations-1.14.jar
├── checker-compat-qual-2.0.0.jar
├── checker-qual-2.3.0.jar
├── error_prone_annotations-2.1.3.jar
├── flyway-commandline-5.1.1.jar
├── flyway-core-5.1.1.jar
├── guava-23.6-jre.jar
├── j2objc-annotations-1.1.jar
├── jcl-over-slf4j-1.7.25.jar
├── jsr305-1.3.9.jar
├── jul-to-slf4j-1.7.25.jar
├── log4j-over-slf4j-1.7.25.jar
├── logback-classic-1.1.11.jar
├── logback-core-1.1.11.jar
├── slf4j-api-1.7.25.jar
├── snakeyaml-1.17.jar
├── spring-aop-4.3.13.RELEASE.jar
├── spring-beans-4.3.13.RELEASE.jar
├── spring-boot-1.5.9.RELEASE.jar
├── spring-boot-autoconfigure-1.5.9.RELEASE.jar
├── spring-boot-starter-1.5.9.RELEASE.jar
├── spring-boot-starter-jdbc-1.5.9.RELEASE.jar
├── spring-boot-starter-logging-1.5.9.RELEASE.jar
├── spring-context-4.3.13.RELEASE.jar
├── spring-core-4.3.13.RELEASE.jar
├── spring-expression-4.3.13.RELEASE.jar
├── spring-jdbc-4.3.13.RELEASE.jar
├── spring-tx-4.3.13.RELEASE.jar
├── tomcat-jdbc-8.5.23.jar
└── tomcat-juli-8.5.23.jar

After that I was able to successfully run:

./flyway migrate

And was able to verify that both sql and java migrations had been successfully applied:

./flyway info

+-----------+---------+-------------------+-------------+---------------------+---------+
| Category  | Version | Description       | Type        | Installed On        | State   |
+-----------+---------+-------------------+-------------+---------------------+---------+
| Versioned | 1       | m1                | SQL         | 2018-06-09 07:41:57 | Success |
| Versioned | 2       | m2                | SQL         | 2018-06-09 07:41:57 | Success |
| Versioned | 3       | SampleJava script | SPRING_JDBC | 2018-06-09 07:47:56 | Success |
+-----------+---------+-------------------+-------------+---------------------+---------+

Phew! This is much harder work than packaging the migrations with the application, in my opinion.

One of the other downsides of this approach is that if someone adds a new java migration with an additional dependency (e.g. commons-lang3, or whatever else) that dependency needs to be added to the <RUNNER_DIR>/lib directory as well.

Hope this helps!