Spring Boot Test: execute different sql scripts in tests depending on the active profile?

Question

Is it possible with Spring Boot Test to set a conditional execution of sql scripts depending on the active profile? I mean, I have my integration tests for repositories annotated with some @sql annotations like:

@Sql(scripts = "/scripts/entity_test_clear.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)

• For a profile h2 I want to execute entity_test_clear.sql

• For a profile mysql I want to execute entity_test_clear_mysql.sql

The reason for it is that I use diffrent syntax for these databases, particularly this one:

• ALTER TABLE organisation ALTER COLUMN org_id RESTART WITH 1;
• ALTER TABLE organisation AUTO_INCREMENT = 1;

Mysql doesn't understand the syntax #1, while h2 doesn't understand the syntax #2 (despite the mysql mode set, like MODE=MYSQL)

By default, I use h2 for IT tests, but also, in some rarer cases, I would like to check everything works smoothly with mysql too.

P.S I could of course try a straight-forward solution with @Profile and hard code two copies of each test for h2 and mysql, but it is coupled with huge code duplication in tests, which I would like to avoid.

EDITED: The test case looks like this:

@RunWith(SpringRunner.class)
@DataJpaTest
@AutoConfigureTestDatabase(replace= AutoConfigureTestDatabase.Replace.NONE)
public class EntityRepositoryTestIT {

    @Autowired
    private EntityRepository entityRepository;

@Test
@Sql(scripts = {"/scripts/entity_test_data.sql", "/scripts/entity_test_data_many.sql"}, executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
@Sql(scripts = "/scripts/entity_test_clear.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
public void findTest() {
    Page<Entity> e = entityRepository.findBySomeDetails(1L, PageRequest.of(0, 20));
    Assert.assertEquals(3, e.getContent().size());
    Assert.assertEquals(1, e.getContent().get(0).getResources().size());
// more asserts

}

Thank you for any suggestions!

Answer 1

You could use @Profile annotation with separated classes, each for every DMBS, putting the common logic in an another class to avoid the code duplication. You are using Spring so you could get it with something as below.

@Profile("mysql")
@Sql(scripts="... my mysql scripts...")
public class MySqlTests{

    @Autowired
    private CommonTestsLogic commonLogic;

    @Test
    public void mySqlTest1(){
       commonlogic.test1();
    }

}

@Profile("oracle")
@Sql(scripts="... my oracle scripts...")
public class MyOracleTests{

   @Autowired
   private CommonTestsLogic commonLogic;

   @Test
   public void myOracleTest1(){
       commonlogic.test1();
   }

}

Answer 2

After some deeper digging into the issue I ended up with this simple workaround.

@Sql(scripts = "/scripts/entity_test_clear.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)

For scripts parameter, it is required to be a compile time constant. You cannot simply fetch a current profile value from application.properties and substitute it to run the right script name.

Introducing @After and @Before methods with ScriptUtils executing the right scripts is rather verbose and, in fact, didn't work for me (some freezing during script executing process occurred).

So what I did was just introducing a class with a single constant:

/**
 * Constant holder for exceptionally database IT testing purposes
 * for switching between h2 and mysql
 */
public class ActiveProfile {
    /**
     * Current profile for database IT tests.
     * Make sure the value is equal to the value of
     * <i>spring.profiles.active</i> property from test application.properties
     */
    public static final String NOW = "h2";
}

Then the @sql line becomes:

@Sql(scripts = "/scripts/test_data_clear_"+ ActiveProfile.NOW+".sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)

To use another database for testing (mysql) I just need to 1) change the current spring.profiles.active=mysql profile in application.properties and 2) change this constant to mysql;

That doesn't mean to be the exemplary solution, just a workaround that simply works.