While running automated tests, we’d often like to use a light-weight in-memory database instead of a persistent one like MySQL. So, in this post, we’ll discuss how to configure your Spring Boot application to use MySQL in development (or production), whereas in memory HSQL when running automated tests.

We’d use Spring profiles for this. If you don’t know about Spring profiles and how to segregate application properties using Spring profiles, please read this post first.

So, below are my steps for configuring an in-memory database for testing.

First, have the following environment specific properties files, as below.

application.properties:

spring.profiles.active: dev

application-dev.properties:

spring.jpa.database: MYSQL
spring.jpa.hibernate.ddl-auto: update
spring.datasource.url: jdbc:mysql://localhost:3306/dbname
spring.datasource.username: username
spring.datasource.password: password

application-test.properties:

spring.jpa.database: HSQL

The first file above tells Spring to run the application under dev profile (which we’ll override in tests). The second and third files are configurations specific to dev and test profiles, i.e. for MySQL and HSQL respectively.

Then, have both MySQL and H2 drivers in pom.xml, as below.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>test</scope>
</dependency>

Finally, annotate test classes with @ActiveProfiles("test"), so that they run under test profile.

Hope you find it useful. Our Spring Framework Course IV covers it in more details, and also includes topics like how to truncate the database before each test, etc.. Have a look at that!