Undo @Sql after each test

Question

I have some junit tests where I want to pre-populate the DB with some data that actually makes sense for the test:

@RunWith(SpringRunner.class)
@SpringBootTest
@ActiveProfiles("test")
public class MyTest {


@Sql("test_insert.sql") 
@Test
public void testInsert(){
  ...performs some inserts...
}
@Sql("test_delete.sql") //makes inserts to the db so to delete them
@Test
public void testDelete(){
    ...
}

I noticed that junit executes the tests by reverse order, meaning that my testDelete will be executed first.

Test insert seems to fail for a "duplicate row constraint" and that happens because the test_delete.sql scripts actually adds that row.

Would it to be possible to rollback the operations made by the @Sql and the test itself so that one test does not influence the others?

Answer 1

You just need to add @Transactional on top of the JUnit test class. This will revert all changes made to the database after every test has run.

@RunWith(SpringRunner.class)
@SpringBootTest
@ActiveProfiles("test")
@Transactional           //  <-- @Transactional added
public class MyTest {


@Sql("test_insert.sql") 
@Test
public void testInsert(){
  ...performs some inserts...
}
@Sql("test_delete.sql") //makes inserts to the db so to delete them
@Test
public void testDelete(){
    ...
}