Arquillian and Flyway using different versions of H2 in-memory

Question

I'm trying to create a JPA-helper, which will eventually be turned into a jar and used in other projects. I want to add Integration tests to this project, so I've added a TestEntity and TestService class (these refer to a simple TEST table in the DB schema).

The goal is to create an in-memory database with H2, run the test in a container with Arquillian, and create/populate a test database with Flyway. Flyway is triggered by having a listener for an @ApplicationScoped class.

However, whenever I trigger an integration test, the test seems to work correctly until a count(*) query is called. At this point, Hibernate complains that the schema (that was just created in Flyway) doesn't exist: Schema 'DB' does not exist

There are a lot of logs, but the highlights look like this:

• A test Glassfish server is started
• Hibernate starts
  • it does not complain about anything yet
  • if hibernate.hbm2ddl.auto is set to validate, then it complains that the tables created in Flyway do not exist (Schema-validation: missing table [DB.TEST])
• Flyway executes the data migration
  • it says that both scripts execute successfully
• The Test code starts running
• The code fails
  • Schema 'DB' does not exist

What I think I need to do

I think that Flyway and the EntityManager are talking to different instances of the database. The changes that Flyway makes aren't saved in a way that EntityManager can see them.

• Ensure that the EntityManager and Flyway are talking to the same database.
  • The name is identical (see persistence.xml, EntityManagerFactory, and FlywayDataLoader), and I've tried adding/removing various MODE values with no effect.
• Force the Flyway code to execute before the Hibernate code
• Force Hibernate to refresh it's schema before the testing starts

What I've tried

• Putting a syntax error into the Flyway migration code causes the test to fail due to a SQL error.
  • The same happens if you put a duplicate statement into the SQL code (e.g.: create the table or schema a second time).
• I tried triggering Flyway via a startup script in the JDBC connection string
  • i.e.: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;INIT=RUNSCRIPT FROM 'classpath:create.sql'
  • I know that the script triggered (because adding errors causes the test to fail), but it still doesn't solve the Schema 'DB' does not exist problem.
• Various flavors of hibernate.hbm2ddl.auto
  • none : Schema 'DB' does not exist
  • validate : Schema-validation: missing table [DB.TEST]
  • update, create-drop : the tables are created, but the data from Flyway is missing
• Put the Flyway code within EntityManagerFactory.create()
• Put the Flyway code in an Integrator class and reference
• Change the Flyway/persistence.xml connection to use a file for the DB rather than memory (e.g.: jdbc:h2:~/test-db).
  • This creates a file in C:\Users\<me> named test-db.mv. The file shows evidence of Flyway interacting with it, but the EntityManager still generates the Schema 'DB' does not exist error.
  • I also set hibernate.hbm2ddl.auto to create, then searched my entire hard drive for a second instance of test-db.mv, but could not find one.

What won't work

• Arquillian Persistence
  • It looks awesome, but I'm on a company network and they haven't approved it yet
• Spring-*
  • This project doesn't use Spring anything

Logs and Code

Execution Logs

// various "server startup" logs
// ...
// misc hibernate logs
Apr 15, 2020 9:40:39 AM org.hibernate.jpa.internal.util.LogHelper logPersistenceUnitInformation
INFO: HHH000204: Processing PersistenceUnitInfo [
    name: TestDS
    ...]
// ...
Loading Flyway Data
8 [main] INFO org.flywaydb.core.internal.util.VersionPrinter - Flyway Community Edition 5.0.7 by Boxfuse
246 [main] INFO org.flywaydb.core.internal.database.DatabaseFactory - Database: jdbc:h2:mem:test (H2 1.4)
354 [main] INFO org.flywaydb.core.internal.command.DbValidate - Successfully validated 2 migrations (execution time 00:00.028s)
377 [main] INFO org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory - Creating Schema History table: "PUBLIC"."flyway_schema_history"
413 [main] INFO org.flywaydb.core.internal.command.DbMigrate - Current version of schema "PUBLIC": << Empty Schema >>
415 [main] INFO org.flywaydb.core.internal.command.DbMigrate - Migrating schema "PUBLIC" to version 1 - CreateDatabase
436 [main] INFO org.flywaydb.core.internal.command.DbMigrate - Migrating schema "PUBLIC" to version 2 - AddTestClasses
449 [main] INFO org.flywaydb.core.internal.command.DbMigrate - Successfully applied 2 migrations to schema "PUBLIC" (execution time 00:00.078s)
Apr 15, 2020 9:40:44 AM com.sun.enterprise.web.WebApplication start
INFO: Loading application [test] at [/test]
Apr 15, 2020 9:40:45 AM org.glassfish.deployment.admin.DeployCommand execute
INFO: test was successfully deployed in 7,649 milliseconds.
Apr 15, 2020 9:40:45 AM org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
INFO: HHH000397: Using ASTQueryTranslatorFactory
Hibernate: 
    select
        count(testobject0_.id) as col_0_0_ 
    from
        DB.TEST testobject0_ 
    where
        1=1
Apr 15, 2020 9:40:45 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 30000, SQLState: 42Y07
Apr 15, 2020 9:40:45 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Schema 'DB' does not exist

// stack trace
/ ...

Integration Test code

@RunWith(Arquillian.class)
public class ProviderTest {

    @Deployment
    public static JavaArchive createDeployment() {
        JavaArchive jar = ShrinkWrap.create(JavaArchive.class)
                .addClasses(
                        TestObject.class,
                        TestService.class,
                        QueryValuesProviderImpl.class,
                        CriteriaQueryProviderFactory.class,
                        EntityManagerFactory.class
                        )
                .addClass(FlywayDataLoader.class)
                .addAsResource("META-INF/persistence.xml")
                .addAsManifestResource(new ByteArrayAsset(new byte[0]), ArchivePaths.create("beans.xml"));
        System.out.println(jar.toString(true));
        return jar;
    }

    @Inject
    private TestService testService;

    @Test
    public void testGetTestResource() {
        List<TestObject> data = testService.getTestObjects(new QueryValues());

        assertNotNull(data);
        assertEquals(2, data.size());
    }
}

Flyway Java Script

@ApplicationScoped
public class FlywayDataLoader {

    private static final String JDBC_URL = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
    private static boolean initialized = false;

    public static void setup(
            @Observes @Initialized(ApplicationScoped.class) final Object event
    ) {
        if (!initialized) {
            System.out.println("Loading Flyway Data");
            Flyway flyway = new Flyway();
            flyway.setDataSource(JDBC_URL, "sa", "");
            flyway.setBaselineOnMigrate(true);
            flyway.migrate();

            initialized = true;
        }
    }
}

Flyway SQL script

This is split into two files (one for creating the Schema/Tables, the second for inserting the actual data). The files are in the /resources/db.migration/ folder and are named V1__CreateDatabase.sql and V2__AddTestClasses.sql respectively.

CREATE SCHEMA DB;

create table DB.TEST
(
    ID INT auto_increment
        constraint PK_REQUEST_AUDIT
            primary key,
    FN VARCHAR2(256 char) default NULL not null,
    LN VARCHAR2(256 char) default NULL not null,
    BD TIMESTAMP default SYSDATE not null
)
;

insert into DB.TEST (FN, LN, BD) VALUES ('Alice', 'Zyl', PARSEDATETIME('1985-03-13','yyyy-MM-dd','en'));
insert into DB.TEST (FN, LN, BD) VALUES ('Bart', 'Young', PARSEDATETIME('1988-03-25','yyyy-MM-dd','en'));

EntityManager

The unitName used here matches the Persistence Unit Name used in persistence.xml. The JDBC connection string used there is identical to the one used in the Flyway Java script.

public class EntityManagerFactory {

    @PersistenceUnit(unitName="TestDS")
    private javax.persistence.EntityManagerFactory emFactory;

    @Produces
    @Default
    @RequestScoped
    public EntityManager create() {
        return emFactory.createEntityManager();
    }

    public void dispose(@Disposes @Default EntityManager em){
        if(em.isOpen()){
            em.close();
        }
    }

}

Persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="TestDS" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <class>myproject.beans.TestObject</class>

        <properties>
            <!-- Configuring JDBC properties -->
            <property name="javax.persistence.jdbc.url" value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1"/>
            <property name="javax.persistence.jdbc.driver" value="org.h2.Driver"/>
            <!-- Hibernate properties -->
            <property name="hibernate.ddl-auto" value="none" />
            <property name="hibernate.connection.user" value="sa" />
            <property name="hibernate.archive.autodetection" value="class"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.show_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

Unremarkable DAO class?

@RequestScoped
public class TestService {

    @Inject
    private EntityManager em;

    @Inject
    private CriteriaQueryProviderFactory criteriaQueryProviderFactory;

    public List<TestObject> getTestObjects(QueryValues queryValues) {
        CriteriaQueryProvider<TestObject> criteriaQueryProvider = criteriaQueryProviderFactory.getFactory();

        CriteriaQuery<TestObject> criteriaQuery = criteriaQueryProvider.buildModifiedQuery(queryValues, TestObject.class);
        TypedQuery<TestObject> typedQuery = criteriaQueryProvider.addSecondaryModifiers(criteriaQuery, queryValues, TestObject.class);

        TypedQuery<Long> countQuery = criteriaQueryProvider.buildCountQuery(queryValues, TestObject.class);

        System.out.println(String.format("There are %d objects total!", countQuery.getSingleResult()));

        return typedQuery.getResultList();
    }
}

Answer 1

the best way I found to integrate Flyway with Hibernate is to use an implementation of org.hibernate.integrator.spi.Integrator: https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/integrator/spi/Integrator.html. That interface has two methods, integrate and disintegrate. If you put the code for running Flyway into the integrate method of your implementation everything should work fine. Hibernate loads implementations of org.hibernate.integrator.spi.Integrator the service loader mechanism from the Java Standard API, therefore you have to create a file META-INF/services/org.hibernate.integrator.spi.Integrator which contains the fully qualified class name of your implementation.

Best

Jens