CODEWITHSUNDEEP
javapostgresqldatabase-migrationflyway
Russell Winkler
Russell Winkler

Reputation: 277

Use an SQL migration file in the Flyway Java API

I'm not sure if this is possible, but I am trying to load a migration saved in an SQL file programmatically through the Flyway Java API. I have an SQL file saved in the base classpath (for simplicity right now) as V1__Initial_version.sql with a simple table creation statement:

CREATE TABLE test_user (
    name VARCHAR(25) NOT NULL, PRIMARY KEY(name)
);

Then in the Java program I use

Flyway flyway  = new Flyway();
flyway.setDataSource(jdbc_url, user, password);
int migrations = flyway.migrate();  // Just to check the # of migrations applied

I see the ClassPathScanner looking through the classpath for valid migrations, but it seems to ignore the sql migration file. The same sql migration file works fine when I call flyway migrate from the command line. I created a java class and inherited from the JdbcMigrations class and the ClassPathScanner picks that up just fine. What do I need to do to get the .sql file picked up by the Java ClassPathScanner and used as a valid migration?

BIGGER PICTURE

Maybe I am going about this incorrectly in the first place so I'll add what I'm trying to do. I am trying to set up a test DB that I can use, nuke, and rebuild in between tests. I.E. TestA enters something into the table, the pre-test function would then clean up the DB and reset it, then TestB would have a clean DB and empty tables to execute it's tests against. I am using flyway.clean() and flyway.migrate() in the pre-test function but it destroys the schema_version table along with everything else then the migrate doesn't rebuild from the previous baseline.

Any help is appreciated! Thanks!

Upvotes: 2

Views: 2118

Answers (1)

Guenther
Guenther

Reputation: 2045

Not sure why it's not picking up any of your migrations. Maybe they need to be in a specific directory? You'd need to check the src code to see why it doesn't pick your files for migration.

Regarding the bigger picture:

We usually migrate a test database using flyway:migrate before we run the build and test cases. We use DBUnit, Spring testing or a similar unit testing frameworks, which allow pre test DB setup with data. The structure shouldn't change during testing, hence no db migrations needed. Arquillian can be very useful for db initialization before running tests as well, see here https://github.com/arquillian/arquillian-extension-persistence

EDIT 1 So I created a small example

import org.flywaydb.core.Flyway;
public class TestClass {
public static void main(String args[]) {
    Flyway flyway = new Flyway();
    for (String location : flyway.getLocations()) {
        System.out.println(location);
    }
    flyway.setDataSource("jdbc:h2:~/test", "sa", "");
    System.out.println("Result: " + flyway.migrate());
}
}

put the sql file into src/main/resources/db/migration and ran it. It picks up the sql migration just fine. If you want to put the sql in a different directory, use setLocations()

classpath:db/migration
Aug 30, 2016 10:48:41 PM org.flywaydb.core.internal.util.VersionPrinter printVersion
INFO: Flyway 4.0.3 by Boxfuse
Aug 30, 2016 10:48:41 PM org.flywaydb.core.internal.dbsupport.DbSupportFactory createDbSupport
INFO: Database: jdbc:h2:~/test (H2 1.4)
Aug 30, 2016 10:48:41 PM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 1 migration (execution time 00:00.009s)
Aug 30, 2016 10:48:41 PM org.flywaydb.core.internal.command.DbMigrate migrate
INFO: Current version of schema "PUBLIC": << Empty Schema >>
Aug 30, 2016 10:48:41 PM org.flywaydb.core.internal.command.DbMigrate applyMigration
INFO: Migrating schema "PUBLIC" to version 1.1 - Initial version
Result: 1
Aug 30, 2016 10:48:41 PM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.024s).

Upvotes: 3

Related Questions