Configure specific in memory database for testing purpose in Spring
SpringSpring BootSpring DataSpring TestSpring Test-DbunitSpring Problem Overview
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 [Postgre/MySQL] ?
Spring Solutions
Solution 1 - Spring
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")
.
Solution 2 - Spring
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.
Solution 3 - Spring
With @SpringBootTest magic, you just need to do following two changes.
- Add 'h2' test dependency in pom.xml
>
> @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.
Solution 4 - Spring
Simplest solution:
-
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.
- Annotate your test class with @ActiveProfiles("test").
Worked like charm in my case.
Solution 5 - Spring
@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).
Solution 6 - Spring
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 :)
Solution 7 - Spring
This solution enables common settings for develop and test. Is based on this solution: Override default Spring-Boot application.properties settings in Junit Test
- 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
- 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
- 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>
- In test class:
@RunWith(SpringRunner.class)
@SpringBootTest
@TestPropertySource(locations = "classpath:test.properties")
public class ModelTest {
}
Solution 8 - Spring
I have a multi-module Gradle SpringBootApplication with below Modules
- employeemanagerApp - Where my SpringApplication main class
- 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