Using Liquibase to initialize in-memory H2 for unit tests in Spring Boot application

Question

I've used in-mem databases in Spring JPA tests many times, and never had a problem. This time, I have a bit more complex schema to initialize, and that schema must have a custom name (some of the entities in our domain model are tied to a specific catalog name.) So, for that reason, as well as to ensure that the tests are fully in sync and consistent with the way we initialize and maintain our schemas, I am trying to initialize an in-memory H2 database using Liquibase before my Spring Data JPA repository unit tests are executed.

(Note: we use Spring Boot 2.1.3.RELEASE and MySql as our main database, and H2 is only used for tests.)

I have been following the Spring Reference guide for setting up Liquibase executions on startup. I have the following entries in my Maven POM:

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-core</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-test-autoconfigure</artifactId>
        <scope>test</scope>
    </dependency>

My test files look like this:

 @RunWith(SpringRunner.class)
 @ContextConfiguration(classes = PersistenceTestConfig.class)
 @DataJpaTest
 public class MyRepositoryTest {

     @Autowired
     private MyRepository myRepository;

     @Test
     public void someDataAccessTest() {
         // myRepository method invocation and asserts here...
         // ...
     }
 }

The app context class:

  @EnableJpaRepositories({"com.mycompany.myproject"})
  @EntityScan({"com.mycompany.myproject"})
  public class PersistenceTestConfig {

       public static void main(String... args) {
           SpringApplication.run(PersistenceTestConfig.class, args);
       }
  }

According to the reference guide,

By default, Liquibase autowires the (@Primary) DataSource in your context and uses that for migrations. If you need to use a different DataSource, you can create one and mark its @Bean as @LiquibaseDataSource. If you do so and you want two data sources, remember to create another one and mark it as @Primary. Alternatively, you can use Liquibase’s native DataSource by setting spring.liquibase.[url,user,password] in external properties. Setting either spring.liquibase.url or spring.liquibase.user is sufficient to cause Liquibase to use its own DataSource. If any of the three properties has not be set, the value of its equivalent spring.datasource property will be used.

Obviously, I want my tests to use the same datasource instance as the one Liquibase uses to initialize the database. So, at first, I've tried to specify the spring.datasource properties without providing the spring.liquibase.[url, user, password] properties - assuming that Liquibase would then use the default primary Spring datasource:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS corp
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.hibernate.ddl-auto=validate

# LIQUIBASE (LiquibaseProperties)
spring.liquibase.change-log=classpath:db.changelog.xml
#spring.liquibase.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS corp
#spring.liquibase.user=sa
#spring.liquibase.password=
spring.liquibase.default-schema=CORP
spring.liquibase.drop-first=true

That didn't work because Liquibase did not find the CORP schema where I must have my tables created:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource  [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguratio n$LiquibaseConfiguration.class]: Invocation of init method failed; nested  exception is liquibase.exception.DatabaseException:  liquibase.command.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exception.LockException: liquibase.exception.DatabaseException: Schema "CORP" not found; SQL statement:
 CREATE TABLE CORP.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)) [90079-197] [Failed SQL: CREATE TABLE CORP.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))]

So, I took out the explicit spring.datasource property definitions and provided only the following Liquibase properties:

 spring.jpa.hibernate.ddl-auto=validate

 # LIQUIBASE (LiquibaseProperties)
 spring.liquibase.change-log=classpath:db.changelog.xml
 spring.liquibase.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS corp
 spring.liquibase.user=sa
 spring.liquibase.password=
 spring.liquibase.default-schema=CORP
 spring.liquibase.drop-first=true

That resulted in the Liquibase task executing successfully and seemingly loading all the necessary tables and data into its native datasource at startup - using the provided changelog files. I understand that this happens because I have explicitly set the Liquibase DS properties, and, per Spring documentation, that would cause Liquibase to use its own native datasource. I suppose, for that reason, while the Liquibase job now runs successfully, the tests are still attempting to use a different [Spring default?] datasource, and the database schema fails the pre-test validation. (No "corp" schema found, no tables.) So, it is obvious that the tests use a different datasource instance from the one that I am trying to generate using Liquibase.

How do I make the tests use what Liquibase generates?

Nothing I try seems to work. I suspect that there is some kind of conflict between the auto- and explicit configurations that I am using. Is @DataJpaTest a good approach in this case. I do want to limit my app context configuration to strictly JPA testing, I don't need anything else for these tests.

It should be simple... However I have not been able to find the correct way, and I can't find any documentation that would clearly explain how to solve this.

Any help is much appreciated!

Answer 1

The problem lies in @DataJpaTest you are using. See the Documentation of @DataJpaTest

By default, tests annotated with @DataJpaTest will use an embedded in-memory database (replacing any explicit or usually auto-configured DataSource). The @AutoConfigureTestDatabase annotation can be used to override these settings.

That means that your auto-configured data source is overriden, and url spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS corp is not taken into account

You will find something similar in the log

EmbeddedDataSourceBeanFactoryPostProcessor : Replacing 'dataSource' DataSource bean with embedded version

To fix, use:

spring.test.database.replace=none

Answer 2

To summarize the solution... Per @Lesiak's suggestion, I have added @AutoConfigureTestDatabase annotation to my test class to override the usage of the default datasource imposed by @DataJpaTest. (Shame on me for missing the obvious in the Javadoc!) The test class now looks like this:

   @RunWith(SpringRunner.class)
   @ContextConfiguration(classes = PersistenceTestConfig.class)
   @DataJpaTest
   @AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
   @Sql(executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD, scripts = {"classpath:init.sql"})
   public class MyRepoTest {
       ...
    }

Context configuration:

 @EnableJpaRepositories({"com.mycompany.myproject"})
 @EntityScan({"com.mycompany.myproject"})
 public class PersistenceTestConfig {

     public static void main(String... args) {
          SpringApplication.run(PersistenceTestConfig.class, args);
     }

}

My application.properties in test/resources:

  spring.jpa.hibernate.ddl-auto=none

  # adding this line seems to make no difference (perhaps, it targets the default DS, not the one used by Liquibase and tests), but using @Sql to execute 'use corp;' statement before tests works!
  # spring.jpa.properties.hibernate.default_schema=corp

  # LIQUIBASE (LiquibaseProperties)
  spring.liquibase.change-log=classpath:db.changelog.xml
  spring.liquibase.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS corp
  spring.liquibase.user=sa
  spring.liquibase.password=
  spring.liquibase.default-schema=CORP
  #spring.liquibase.liquibase-tablespace=CORP
  spring.liquibase.drop-first=true

The init.sql script resides in /test/resources and contains a single line: use corp;. (That's important because some of my JPA entities are explicitly mapped to the corp catalog, and some are not, but in the tests they all have to be found in the same corp schema.)

The Liquibase task succeeds, I see in the logs that the CORP schema gets generated - with all the tables, etc. Without the @Sql annotation pointing to the use corp; script, the tests start but only seem to be ok with Spring-Data-JPA-generated queries that use the corp. prefix on tables. That is, when the queries are generated for the entity classes that are mapped to the tables whith the explicitly specified catalog: @Table(name="my_table", catalog="corp"). If the test attempts to use an entity that is not mapped to the "corp" catalog explicitly, a SQL exception is thrown stating that the table is not found - as if it is looking for the table in some other default schema. So, I have added the @Sql annotation to the test class (as shown above) to execute the use corp; statement before the tests. That did the job. (Note that adding spring.jpa.properties.hibernate.default_schema=corp to the configuration does not seem to have any effect.)

Thank you, @Lesiak, for your help!

Answer 3

Your schema name must be the same as your db name.

The correct answer in your case will look like this

spring.liquibase.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS testdb

spring.liquibase.default-schema=testdb