How to re-create database before each test in Spring?

Question

My Spring-Boot-Mvc-Web application has the following database configuration in application.properties file:

spring.datasource.url=jdbc:h2:tcp://localhost/~/pdk
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver

this is the only config I made. No any other configurations made by me anywhere. Nevertheless the Spring and subsystems are automatically recreate database on each web application run. Database is recreated namely on system run while it contains data after application ends.

I was not understanding this defaults and was expecting this is suitable for tests.

But when I started to run tests I found that database is recreated only once. Since tests are executed at no predefined order, this is senseless at all.

So, the question is: how to make any sense? I.e. how to make database recreate before each test as it happens at application first start?

My test class header is follows:

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = myapp.class)
//@WebAppConfiguration
@WebIntegrationTest
@DirtiesContext
public class WebControllersTest {

As you see, I tried @DirtiesContext at class level and it didn't help.

UPDATE

I have a bean

@Service
public class DatabaseService implements InitializingBean {

which has a method

@Override
    @Transactional()
    public void afterPropertiesSet() throws Exception {
        log.info("Bootstrapping data...");
        User user = createRootUser();
        if(populateDemo) {
            populateDemos();
        }
        log.info("...Bootstrapping completed");
    }

Now I made it's populateDemos() method to clear all data from database. Unfortunately, it does not called before each test despite @DirtiesContext. Why?

Igorot

This is custom logic. Spring doesn't know anything about your database(s). Write a @Before and @After to set up and clean up. – Febricity

@SotiriosDelimanolis I know it's short, but shouldn't your comment be an answer? – Aniline

Answer 1

Actually, I think you want this:

@DirtiesContext(classMode = ClassMode.BEFORE_EACH_TEST_METHOD)

javadoc: Annotation Type DirtiesContext

@DirtiesContext may be used as a class-level and method-level annotation within the same class. In such scenarios, the ApplicationContext will be marked as dirty after any such annotated method as well as after the entire class. If the DirtiesContext.ClassMode is set to AFTER_EACH_TEST_METHOD, the context will be marked dirty after each test method in the class.

You put it on your Test class.

Urga

But this doesn't solve the problem completely right? After the first tests the sql script will run again and the database might be dirty resulting in duplicate key violations. Is there a smarter way of dropping the tables after a test, before inserting again? – Cort

It won't give duplicate key violations because it recreates the database, not just delete all values from tables. It drops the database. So, every test will run with a brand new database. This way, a test won't affect another. – Urga

for some reason it doesn't clear my h2 in memory database. – Latashialatch

@Latashialatch I was just investigating similar issue and found solution, however can't say why exactly it works for me. My setup is: Spring-boot5, junit5, in-memory H2, DirtiesContext on class level. What I foung is that when H2 url is named like 'jdbc:h2:mem:mem1' (mem1 is important here) then tests are failing (mvn test). But making H2 url anonimous like 'jdbc:h2:mem' fixes it! – Narcolepsy

If you use ClassMode.BEFORE_EACH_TEST_METHOD make sure to use @TestExecutionListeners({DirtiesContextBeforeModesTestExecutionListener.class,...}) otherwise it's not supported. – Appliance

For posterity, if you use a data.sql file H2 appears to not reset the IDs so if you leave those in your script you will get duplicate key errors – Kandi

I think dirties context has a very high impact on performance. Normally only the transaction is rolled back after each test to reset the db. – Inhabiter

This solution somewhat worked for me, in that it worked in some environments but not others. To avoid the flakyness I configured spring to use a different test database each time it reloaded the config: spring.datasource.url=jdbc:h2:mem:${random.uuid} – Vernalize

@Vernalize solution's worked super fine for me. Before each test, a complete new database is used by Spring's test environment – Homo

DirtiesContext helped, but I needed to set hibernate.hbm2ddl.auto to create-drop from create-only – Shortchange

Answer 2

Using the accepted answer in Spring-Boot 2.2.0, I was seeing JDBC syntax errors related to constraints:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Constraint "FKEFFD698EA2E75FXEERWBO8IUT" already exists; SQL statement: alter table foo add constraint FKeffd698ea2e75fxeerwbo8iut foreign key (bar) references bar [90045-200]

To fix this, I added @AutoConfigureTestDatabase to my unit test (part of spring-boot-test-autoconfigure):

import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase;
import org.springframework.boot.test.autoconfigure.jdbc.AutoConfigureTestDatabase.Replace;
import org.springframework.test.annotation.DirtiesContext;
import org.springframework.test.annotation.DirtiesContext.ClassMode;
import org.springframework.boot.test.context.SpringBootTest;
import org.junit.runner.RunWith;
import org.springframework.test.context.junit4.SpringRunner;


@RunWith(SpringRunner.class)
@SpringBootTest
@DirtiesContext(classMode = ClassMode.BEFORE_EACH_TEST_METHOD)
@AutoConfigureTestDatabase(replace = Replace.ANY)
public class FooRepositoryTest { ... }

Textbook

After upgrading to Spring-Boot 2.2.x, I had this surfacing. I only wish I could upvote this more than once. Wasted half a day trying to figure out how to fix this. – Monia

Answer 3

To create the database you have to do what the other answers say with the spring.jpa.hibernate.ddl-auto=create-drop, now if your intent is to pupulate the database on each test then spring provides a very usefull anotation

@Transactional(value=JpaConfiguration.TRANSACTION_MANAGER_NAME)
@Sql(executionPhase=ExecutionPhase.BEFORE_TEST_METHOD,scripts="classpath:/test-sql/group2.sql")
public class GroupServiceTest extends TimeoffApplicationTests {

that is from this package org.springframework.test.context.jdbc.Sql; and you can run a before test method and a after test method. To populate the database.

Regarding creating the database each time, Say you only want your Test to have the create-drop option you can configure your tests with a custom properties with this annotation

@TestPropertySource(locations="classpath:application-test.properties")
public class TimeoffApplicationTests extends AbstractTransactionalJUnit4SpringContextTests{

Hope it helps

Strontium

Answer 4

If you are looking for an alternative for the @DirtiesContext, this code below will help you. I used some code from this answer.

First, setup the H2 database on the application.yml file on your test resources folder:

spring: 
  datasource:
    platform: h2
    url: jdbc:h2:mem:test
    driver-class-name: org.h2.Driver
    username: sa
    password:

After that, create a class called ResetDatabaseTestExecutionListener:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.TestContext;
import org.springframework.test.context.support.AbstractTestExecutionListener;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

public class ResetDatabaseTestExecutionListener extends AbstractTestExecutionListener {

    @Autowired
    private DataSource dataSource;

    public final int getOrder() {
        return 2001;
    }

    private boolean alreadyCleared = false;

    @Override
    public void beforeTestClass(TestContext testContext) {
        testContext.getApplicationContext()
                .getAutowireCapableBeanFactory()
                .autowireBean(this);
    }

    @Override
    public void prepareTestInstance(TestContext testContext) throws Exception {

        if (!alreadyCleared) {
            cleanupDatabase();
            alreadyCleared = true;
        }
    }

    @Override
    public void afterTestClass(TestContext testContext) throws Exception {
        cleanupDatabase();
    }

    private void cleanupDatabase() throws SQLException {
        Connection c = dataSource.getConnection();
        Statement s = c.createStatement();
   
        // Disable FK
        s.execute("SET REFERENTIAL_INTEGRITY FALSE");

        // Find all tables and truncate them
        Set<String> tables = new HashSet<>();
        ResultSet rs = s.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  where TABLE_SCHEMA='PUBLIC'");
        while (rs.next()) {
            tables.add(rs.getString(1));
        }
        rs.close();
        for (String table : tables) {
            s.executeUpdate("TRUNCATE TABLE " + table);
        }

        // Idem for sequences
        Set<String> sequences = new HashSet<>();
        rs = s.executeQuery("SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA='PUBLIC'");
        while (rs.next()) {
            sequences.add(rs.getString(1));
        }
        rs.close();
        for (String seq : sequences) {
            s.executeUpdate("ALTER SEQUENCE " + seq + " RESTART WITH 1");
        }

        // Enable FK
        s.execute("SET REFERENTIAL_INTEGRITY TRUE");
        s.close();
        c.close();
    }
}

The code above will reset the database (truncate tables, reset sequences, etc) and is prepared to work with H2 database. If you are using another memory database (like HsqlDB) you need to make the necessary changes on the SQLs queries to accomplish the same thing.

After that, go to your test class and add the @TestExecutionListeners annotation, like:

@TestExecutionListeners(mergeMode =
        TestExecutionListeners.MergeMode.MERGE_WITH_DEFAULTS,
        listeners = {ResetDatabaseTestExecutionListener.class}
)
@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class CreateOrderIT {

This should work.

If you not see any performance difference between this approach and @DirtiesContext, probably you are using @MockBean in your tests, what marks the Spring context as dirty and automatically reload the entire context.

Element

This worked for us! I've adapted it for our use, you can see the code here: https://mcmap.net/q/188481/-how-to-re-create-database-before-each-test-in-spring – Miff

For some reason. Disabling FK s.execute("SET REFERENTIAL_INTEGRITY FALSE") won't update the db setting, and thus preventing the rest of clean up actions. – Possibly

Answer 5

With spring boot the h2 database can be defined uniquely for each test. Just override the data source URL for each test

 @SpringBootTest(properties = {"spring.config.name=myapp-test-h2","myapp.trx.datasource.url=jdbc:h2:mem:trxServiceStatus"})

The tests can run in parallel.

Within the test the data can be reset by

@DirtiesContext(classMode = DirtiesContext.ClassMode.BEFORE_EACH_TEST_METHOD)

Whencesoever

Answer 6

If you use spring.jpa.hibernate.ddl-auto=create-drop should be enough to create/drop database?

Euromarket

This is probably used by default by Spring, which is not very clear why. – Igorot

Drop-create is only helpful when the JVM actually exits - if you have multiple testclasses and you want to drop and create between these testclasses this won't work – Manners

Answer 7

There is library that covers "reset H2 database" feature in JUnit 5 tests:

https://github.com/cronn/test-utils#h2util

Sample usage:

@ExtendWith(SpringExtension.class)
@Import(H2Util.class)
class MyTest {

    @BeforeEach
    void resetDatabase(@Autowired H2Util h2Util) {
        h2Util.resetDatabase();
    }

    // tests...
}

Maven coords:

<dependency>
    <groupId>de.cronn</groupId>
    <artifactId>test-utils</artifactId>
    <version>0.2.0</version>
    <scope>test</scope>
</dependency>

Disclaimer: I’m the author of suggested library.

Bin

not working for me: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'de.cronn.testutils.h2.H2Util': Lookup method resolution failed – Eady

can you add more info about your error? preferably as gh issue? – Braces

please have a look here: github.com/cronn/test-utils/issues/10 – Eady

Answer 8

Unless you're using some kind of Spring-Data integration (which I don't know at all), this seems like custom logic you'll need to implement yourself. Spring doesn't know about your databases, its schemas, and tables.

Assuming JUnit, write appropriate @Before and @After methods to set up and clean up your database, its tables, and data. Your tests can themselves write the data they need, and potentially clean up after themselves if appropriate.

Febricity

But who deletes database currently on program startup? If logic is custom, then why it is already clearing database without my explicit orders? – Igorot

It is NOT in memory database, since url is dbc:h2:tcp://localhost/~/pdk. It's real database and I can see it's file and access it separately from database tools. It is probably deleted by underlying Hibernate default configuration which is set to create or drop-create. The question is is it possible to kick reinitalizing not explicitly... – Igorot

Answer 9

A solution using try/resources and a configurable schema based on this answer. Our trouble was that our H2 database leaked data between test cases. So this Listener fires before each test method.

The Listener:

public class ResetDatabaseTestExecutionListener extends AbstractTestExecutionListener {

    private static final List<String> IGNORED_TABLES = List.of(
        "TABLE_A",
        "TABLE_B"
    );

    private static final String SQL_DISABLE_REFERENTIAL_INTEGRITY = "SET REFERENTIAL_INTEGRITY FALSE";
    private static final String SQL_ENABLE_REFERENTIAL_INTEGRITY = "SET REFERENTIAL_INTEGRITY TRUE";

    private static final String SQL_FIND_TABLE_NAMES = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='%s'";
    private static final String SQL_TRUNCATE_TABLE = "TRUNCATE TABLE %s.%s";

    private static final String SQL_FIND_SEQUENCE_NAMES = "SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA='%s'";
    private static final String SQL_RESTART_SEQUENCE = "ALTER SEQUENCE %s.%s RESTART WITH 1";

    @Autowired
    private DataSource dataSource;

    @Value("${schema.property}")
    private String schema;

    @Override
    public void beforeTestClass(TestContext testContext) {
        testContext.getApplicationContext()
            .getAutowireCapableBeanFactory()
            .autowireBean(this);
    }

    @Override
    public void beforeTestMethod(TestContext testContext) throws Exception {
        cleanupDatabase();
    }

    private void cleanupDatabase() throws SQLException {
        try (
            Connection connection = dataSource.getConnection();
            Statement statement = connection.createStatement()
        ) {
            statement.execute(SQL_DISABLE_REFERENTIAL_INTEGRITY);

            Set<String> tables = new HashSet<>();
            try (ResultSet resultSet = statement.executeQuery(String.format(SQL_FIND_TABLE_NAMES, schema))) {
                while (resultSet.next()) {
                    tables.add(resultSet.getString(1));
                }
            }

            for (String table : tables) {
                if (!IGNORED_TABLES.contains(table)) {
                    statement.executeUpdate(String.format(SQL_TRUNCATE_TABLE, schema, table));
                }
            }

            Set<String> sequences = new HashSet<>();
            try (ResultSet resultSet = statement.executeQuery(String.format(SQL_FIND_SEQUENCE_NAMES, schema))) {
                while (resultSet.next()) {
                    sequences.add(resultSet.getString(1));
                }
            }

            for (String sequence : sequences) {
                statement.executeUpdate(String.format(SQL_RESTART_SEQUENCE, schema, sequence));
            }

            statement.execute(SQL_ENABLE_REFERENTIAL_INTEGRITY);
        }
    }
}

Using a custom annotation:

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@TestExecutionListeners(mergeMode =
    TestExecutionListeners.MergeMode.MERGE_WITH_DEFAULTS,
    listeners = { ResetDatabaseTestExecutionListener.class }
)
public @interface ResetDatabase {
}

You can easily mark each test in which you want to reset the database:

@SpringBootTest(
    webEnvironment = RANDOM_PORT,
    classes = { Application.class }
)
@ResetDatabase
public class SomeClassIT {

Miff

Answer 10

You can annotate your test class with @Transactional:

import org.springframework.transaction.annotation.Transactional;
...

...
@RunWith(SpringRunner.class)
@Transactional
public class MyClassTest {

    @Autowired
    private SomeRepository repository;

    @Before
    public void init() {
       // add some test data, that data would be rolled back, and recreated for each separate test
       repository.save(...);
    }

    @Test
    public void testSomething() {
       // add some more data
       repository.save(...);
       // update some base data
       repository.delete(...);
       // all the changes on database done in that test would be rolled back after test finish
    }
}

All tests are wrapped inside a transaction, that is rolled back at the end of each test. There are unfortunately some problems with that annotation of course, and you need to pay special attention, when for example your production code uses transactions with different score.

Corse

I don't know why, but when I used @Transactional, the operations inside the same test method weren't visible during the method scope. For instance, I added an element in DB (and got the Id value the sequence assigned to it), but just after if I queried the same item from DB, it wasn't available. – Jedjedd

@Jedjedd this is how a transaction-managed test method behave as mentioned in docs.spring.io/spring-framework/reference/testing/… – Colum

Answer 11

I use

• spring 3.1.0
• hibernate 6.1.7
• jakarta 4.0.+
• h2 and mysql

I have 2 data sources configs. So I solved it by combining them in test class:

@SpringBootTest(classes = {H2JpaTestIntegrationConfig.class})
@ActiveProfiles("test")
@DirtiesContext(classMode = 
DirtiesContext.ClassMode.AFTER_EACH_TEST_METHOD)
public class MechanismTest {}

And in data source config:

@Profile("test")
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean customEntityManagerFactory() 
{
 //.........
 Properties properties = new Properties();
 properties.setProperty("hibernate.hbm2ddl.auto", "create-drop");
 properties.setProperty("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
//.........
}

Krakau

Answer 12

You could also try out https://www.testcontainers.org/ which helps you to run databases inside containers and you can create a fresh database for each test run too. It will be very slow though, since each time a container has to be created and the database server has to be started, configured and then migrations have to be run, then the test can be executed.

Above

Answer 13

Nothing worked for me, but the following: For every test class you can put the following annotations:

@TestMethodOrder(MethodOrderer.OrderAnnotation.class) //in case you need tests to be in specific order
@DataJpaTest // will disable full auto-configuration and instead apply only configuration relevant to JPA tests
@AutoConfigureTestDatabase(replace = NONE) //configures a test database to use instead of the application-defined or auto-configured DataSource

To order specific tests within the class you have to put also @Order annotation:

@Test
    @Order(1) //first test
@Test
    @Order(2) //second test, etc.

Rerunning the tests will not fail because of previous manipulations with db.

Cami

What about run individual test in the class? In this case we should always run whole test cases in the class – Carlile