Configure specific in memory database for testing purpose in Spring

Question

How do I configure my Spring Boot application so that when I run unit tests it will use in-memory database such as H2/HSQL but when I run Spring Boot application it will use production database PostgreSQL/MySQL ?

Mcnamee

Answer 1

Spring profiles can be used for this. This would be a specific way:

Have environment specific properties files:

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

Have both MySQL and H2 drivers in pom.xml, like this:

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

Last but not the least, annotate Test classes with @ActiveProfiles("test").

Vouvray

The dev profile seems weird to me. Isn't that supposed to be the "production" one? In this case, just put that info in application.properties, no need for spring.profiles.active either. – Earthen

I have a separate prod profile. If we put the dev info in application.properties, wouldn't that info leak to the test profile? – Vouvray

All I am saying is that you're not answering the question. Dev/test is confusing in your answer. I'd expect a prod profile and then a test profile but not a "Dev" one. You could just as well have a "prod" profile with the production and leave the default for the test. Since Spring Boot does not find any specific information it'll use the embedded container. – Earthen

Do I put application-test.properties on src/main/resources ? My tests unfortunately still pointing to the production database – Mcnamee

Yes, you should be putting that right beside application.properties. Where have you put these? – Vouvray

Isn't the use of colons for yml files? Should that be = instead? – Clyte

Answer 2

Another approach is to add the annotation @AutoConfigureTestDatabase to you test class. My tests usually look like this:

@RunWith(SpringRunner.class)
@DataJpaTest
@AutoConfigureTestDatabase(connection = EmbeddedDatabaseConnection.H2)
public class MyRepositoryTest {

    @Autowired
    MyRepository repository;

    @Test
    public void test() throws Exception {
        // Tests...
    }
}

Note that the embedded database dependency needs to be added in the pom.xml file. For embedded database this annotation is not necessary it will work even if only the dependency is added in pom file.

Disavowal

This is the best approach. No need to configure anything through properties, because you need H2 only in testing scenario. – Jodoin

Answer 3

With @SpringBootTest magic, you just need to do following two changes.

1. Add 'h2' test dependency in pom.xml

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

2. Use @AutoConfigureTestDatabase

@RunWith(SpringRunner.class)
@SpringBootTest(classes = MySpringBootApplication.class)
@AutoConfigureTestDatabase
public class SpringBootTest{

    @Autowired
    private RequestRepository requestRepository;
}

Now all the spring jpa bean/repositories used in test will use h2 as backing database.

2019-04-26 13:13:34.198 INFO 28627 --- [ main] beddedDataSourceBeanFactoryPostProcessor : Replacing 'dataSource' DataSource bean with embedded version

2019-04-26 13:13:34.199 INFO 28627 --- [ main] o.s.b.f.s.DefaultListableBeanFactory : Overriding bean definition for bean 'dataSource'

2019-04-26 13:13:36.194 INFO 28627 --- [ main] o.s.j.d.e.EmbeddedDatabaseFactory : Starting embedded database: url='jdbc:h2:mem:2784768e-f053-4bb3-ab88-edda34956893;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'

Note: I still have 'spring-jpa' properties defined in 'application.properties' and I don't use any profiles. @AutoConfigureTestDatabase will override existing jpa configurations with test defaults AutoConfigureTestDatabase.Replace.

Scrooge

Answer 4

Simplest solution:

1) in src/main/resources have application.properties (production config):

spring.datasource.url=jdbc:mysql://localhost:3306/somedb
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.database-platform = org.hibernate.dialect.MySQL5Dialect

and application-test.properties with HSQL config like:

spring.jpa.hibernate.ddl-auto = create-drop
spring.jpa.database = HSQL
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.HSQLDialect
spring.datasource.driverClassName = org.hsqldb.jdbcDriver
spring.datasource.url= jdbc:hsqldb:mem:scratchdb
spring.datasource.username = sa
spring.datasource.password =

2) Add HSQL dependency in pom.xml if you don't have it already.

3) Annotate your test class with @ActiveProfiles("test").

Worked like charm in my case.

Enfranchise

Hey, how do you set up hsql itself? – Handball

Hi @AlexBondar. You can use the sample before it but you can use replace=Replace.NONE inside AutoConfigureTestDatabase – Reamonn

spring.datasource.driverClassName = org.hsqldb.jdbcDriver should be spring.datasource.driver-class-name = org.hsqldb.jdbcDriver – Difference

Answer 5

@Sanjay has one way to put it but I find it confusing. You could just as well have only a production profile that you enable when you're in production, something like:

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

And don't specify anything else. If you add an embedded database in test scope, it will be available in your tests. If you run your tests with the default profile (no customization whatsoever), it won't find any database information (since these are stored in the production profile). In that case, it will try to find an embedded database and start it for you. If you need more customization for some reason, you can have a application-test.properties for those (you'll need to add ActiveProfiles("test") to your test(s).

Earthen

In my scenarios, I would have a MySQL database in my PC, which would be used when running the app locally while developing. That's why I thought we needed a dev profile to run the app, and a test profile to run the tests through my IDE. Any simplification could be done in this case? – Vouvray

Just add application-dev.properties with your MySQL DB and enable the dev profile when you run the app from your IDE. – Earthen

Okay, what I understand then is that my answer above looks suiting this case, except that I could have skipped having the test profile and the @ActiveProfile if the test profile had nothing except just the DB. Let me know if this sounds wrong. – Vouvray

I don't think your answer suits this case. Nobody asked for a dev profile; I am not denying it is useful but that's not what the question is all about. The dev profile is always enabled in your answer. That feels wrong as well. Anyway, no need to discuss it any further IMO :) - – Earthen

Answer 6

Simple solution if building with maven: just place an application.properties file under src/test/resources and edit as appropriate for testing.

The Spring (Boot) Profile mechanism is a pretty powerful tool that, in scope, goes way beyond "swapping settings between test time and run time". Although, clearly, as demonstrated, it can do that also :)

Rema

Answer 7

This solution enables common settings for develop and test. Is based on this solution: Override default Spring-Boot application.properties settings in Junit Test

1. application.properties in src/main/resources/application.properties

    #common settings for DEVELOPMENT and TEST:
    ......
    ......

    ## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
    spring.datasource.url=jdbc:postgresql://localhost:5432/databasename
    spring.datasource.username=postgres
    spring.datasource.password=somepassword

    # The SQL dialect makes Hibernate generate better SQL for the chosen database
    spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
    spring.jpa.properties.hibernate.jdbc.time_zone=UTC

    # Hibernate ddl auto (create, create-drop, validate, update)
    spring.jpa.hibernate.ddl-auto = none
    spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

2. test.properties (src/main/resources/application.properties) which overrides and adds properties in application.properties:

    spring.datasource.url=jdbc:h2:mem:testdb;MODE=PostgreSQL
    spring.datasource.driverClassName=org.h2.Driver
    spring.datasource.username=sa
    spring.datasource.password=
    spring.jpa.hibernate.ddl-auto=update
    spring.h2.console.enabled=false

3. settings in pom.xml for H2 and Postgre databases

      <!-- h2 -->
      <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
      </dependency>

    <!-- postgress -->
      <dependency>
         <groupId>org.postgresql</groupId>
         <artifactId>postgresql</artifactId>
      </dependency>

4. In test class:

    @RunWith(SpringRunner.class)
    @SpringBootTest
    @TestPropertySource(locations = "classpath:test.properties")
    public class ModelTest { 

    }

Hutcherson

Answer 8

I have a multi-module Gradle SpringBootApplication with below Modules

1. employeemanagerApp - Where my SpringApplication main class
2. employeemanagerIntTests - Where i have my cucumber tests

My requirement was to use MySQL DB when the application boots up and H2 during my Cucumber Integration testing

Solution: In my employeemanagerApp module, src/main/resources i placed the application.properties with below content

#My SQL Configuration
spring.datasource.url=jdbc:mysql://localhost:3306/employeemanager
spring.datasource.username=root
spring.datasource.password=password
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update  spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

And in the Integration Test Module (employeemanagerIntTests) src/test/resources I placed the application.properties with below content

#H2 In-Memory DB Configuration
spring.datasource.url=jdbc:h2://mem:db;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.properties.hibernate.format_sql=true

And in my Step Definition Class i added only these annotations

@CucumberContextConfiguration
@SpringBootTest(classes = SpringBootApplicationMainClass.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)

In the build.gradle file i added H2 dependency

testImplementation 'com.h2database:h2:1.4.200'

So when I ran my tests, H2 was active and all tests with Create, Update, Read and Delete were successful

Gemination

Answer 9

Some ppl told that best approach is using @AutoConfigureTestDatabase , but this will not work if you are using spring.jpa.hibernate.ddl-auto=validate , that was my case.

If you use this, @AutoConfigureTestDatabase will only create the connection, but will still miss the tables.

Due for this the best ansewer was from @Sanjay , using the application-test.properties.

In this case, is easy to configure the new database and override the configuration validate to update:

spring.jpa.database: HSQL
spring.jpa.hibernate.ddl-auto=update

Doing this and importing in your test class:

@DataJpaTest
@ActiveProfiles("test")
public class UserRepositoryTests {
    ...
}

It will finally work.

PS: Just like Sanjay said in accepted ansewer, you will need to add the dependency in your pom.xml:

<dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <scope>test</scope>
</dependency>

Propositus