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 different 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!

Can you show us how you make the test for one of h2 or MySQL — Youcef LAIDANI
– Youcef LAIDANI, Commented Aug 29, 2018 at 12:47
Why don't you use @Profile with separated classes putting the common logic in an another class? — Stefano Curcio
– Stefano Curcio, Commented Aug 29, 2018 at 13:10

Manuel Jordan · Accepted Answer · 2023-04-07 14:06:17Z

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 the scripts parameter, it is required to be a compile time constant. You cannot simply fetch a current profile value from the application.properties file 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

change the current spring.profiles.active=mysql profile in the application.properties file
change this constant to mysql;

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

Manuel Jordan · Accepted Answer · 2023-04-07 14:08:05Z

4

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();
   }

}

edited Apr 7, 2023 at 14:08

Manuel Jordan

16.5k26 gold badges113 silver badges187 bronze badges

answered Aug 29, 2018 at 13:51

Stefano Curcio

3853 silver badges11 bronze badges

1 Comment

Andreas Gelever Over a year ago

I think this is quite laborious approach as I have hundreds of IT tests to rewrite like this, and furthermore such the tests look weird

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related