Use an SQL migration file in the Flyway Java API

Question

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!

I don't really understand the point in nuking the schema. The data, yes, but the schema? I would use DbSetup (dbsetup.ninja-squad.com). Disclaimer: I'm the author. — JB Nizet
– JB Nizet, Commented Aug 30, 2016 at 19:53
You are correct, there really isn't a point to nuking the schema. All I'm trying to do is clear out the data but using flyway.clear removes my schema table also. I didn't see another way for flyway to just clear out the data but leave the tables and schema. — Russell Winkler
– Russell Winkler, Commented Aug 30, 2016 at 20:16
That's not what Flyway is for. Flyway's job is to make schema evolutions. — JB Nizet
– JB Nizet, Commented Aug 30, 2016 at 20:18
Most of my Unit tests use an in memory databases, so after test runs the schema is gone anyway (but to make sure the table is empty between tests I use JDBC delete in the @Before fixture method). — eckes
– eckes, Commented Aug 30, 2016 at 21:00

Guenther · Accepted Answer · 2016-08-30 20:54:33Z

3

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).

edited Aug 30, 2016 at 20:54

answered Aug 30, 2016 at 19:59

Guenther

2,0452 gold badges17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Russell Winkler Over a year ago

I have placed the sql migration file in the same location as the java class migration, which is in the classpath that flyway looks in. The java class does get picked up, the sql file does not.

Guenther Over a year ago

You'll be using one of the db unit testing frameworks I mentioned. Clearing the db can be a simple SQL which disables all constraints and truncates all tables. Using flyway to build a db schema for each test will not scale well, especially once you have more db migrations and more tests.

Russell Winkler Over a year ago

Thanks, that's similar to the advice JB Nizet gave in the comments and clears up part of the problem. I would still like to get the base migration from sql rather than a java JdbcMigration class.

Guenther Over a year ago

I still don't recommend doing this. simply run mvn flyway:migrate before running the tests. I have edited my question to provide a working sample, just to answer your request :-)

Russell Winkler Over a year ago

Thanks! That worked creating and adding the sql schema to the db/migration dir but not when I try to set the location. I think there is a permissions issue there but this definitely answered this question! I'll look into updating the schema in the build too, that will probably be smarter!

Collectives™ on Stack Overflow

Use an SQL migration file in the Flyway Java API

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related