SpringBoot - Testing with Flyway and H2 database

Question

I'm coding acceptance tests with Cucumber, and I want to use a H2 database for the tests.

The application-test.properties looks like:

server.port:8090

spring.jpa.database=H2
spring.database.driverClassName=org.h2.Driver
spring.datasource.url:jdbc:h2:mem:database_user;DB_CLOSE_ON_EXIT=FALSE
flyway.locations=classpath:resources/db/migration
flyway.enabled=true

spring.datasource.username:SA
spring.datasource.password:

spring.h2.console.enabled=true
spring.jpa.show-sql=true

security.basic.enabled:false
spring.application.name=userService

In the directory resources/db/migration, I have a sql file with these scripts:

create table user_image(
 id int unsigned not null AUTO_INCREMENT,
 url varchar(1000) not null,
 s3_key varchar(200) not null,
 PRIMARY KEY (id)
);


create table user (
    id int unsigned not null AUTO_INCREMENT,
    email varchar(50) not null,
    password varchar(100) not null,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    description varchar(50),
    phone_number varchar(50),
    user_image_id int unsigned,
    need_refresh_pass boolean not null,
    PRIMARY KEY (id),
    CONSTRAINT fk_user_image FOREIGN KEY (user_image_id)
    REFERENCES user_image(id)
);

But when I run the tests, H2 creates the schema with default format instead of using the scripts:

As you can see, all the VARCHAR are created with 255 size, instead of the real value.

Could you help me to integrate flyway with H2?

Thanks!

Answer 1

Flyway has not found any migrations, and Hibernate has then created the tables from your entities.

The location should be:

# spring-boot 2.x
spring.flyway.locations=classpath:db/migration

# spring-boot 1.5.x
flyway.locations=classpath:db/migration

This is the default so could be omitted.

Answer 2

1- Make sure that your hibernate DDL generation is disabled:

spring.jpa.hibernate.ddl-auto=none

2- Make sure that the name of your SQL migration script respects flyway's convention. i.e.

V1__create_user_table_for_test.sql

Answer 3

I found that defining a dataSource fixed this problem for me:

Create a class under /test

package com.whatever;

import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

import javax.sql.DataSource;

public class TestDataSource {
    @Bean
    public DataSource dataSource() {
        SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
        dataSource.setDriverClass(org.h2.Driver.class);
        dataSource.setUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=TRUE;DATABASE_TO_UPPER=false;MODE=MYSQL");
        dataSource.setUsername("sa");
        return dataSource;
    }
}

Use the configuration in your test. Ex:

@RunWith(SpringRunner.class)
@ContextConfiguration(classes = {TestDataSource.class})
@SpringBootTest(classes = Application.class)
@AutoConfigureMockMvc
@ActiveProfiles({ "test" })
@TestExecutionListeners({DbUnitTestExecutionListener.class, DependencyInjectionTestExecutionListener.class})
public class SomethingControllerTest {
...
}