Spring Boot – Separate DataSource for Test, Dev and Prod

Learn to configure and use different DataSource in different environments (test, development, production etc.) using the Spring profile specific properties files, as well as Java configuration in a Spring boot application.

1. Introduction

A production grade application is deployed and tested in different environments before finally released to the end-user. In these environments, application must be able to connect to specific database instances using the environment specific database URL and credentials.

For example, in production, we might have costly Oracle database, and in development environment we can use comparatively cheaper (or free version) MySQL database. For running the tests, we can use a in-memory database such as H2. These requirements change application to application.

In a Spring boot application, the preferred way to configure the database connectivity is by defining the DataSource configuration in properties files. In this tutorial, we have following requirements:

• Application tests connect to in-memory database H2.
• In “dev” profile, the application connects to MySQL database.
• In “prod” profile, the application connects to Oracle database.

In dev and prod profiles, the database URL and credentials will be resolved at runtime, and should not be stored in the sourcecode for security reasons.

Similarily, it is advised to store the spring.profiles.active system property in the machines and specify property value specific to that environment.

spring.profiles.active=dev

2. Using Profile-specific Properties Files

Spring profiles help in defining different profiles where the application can run, and loading the profile specific configuration when application is deployed with that profile. One way to load configuration is by using the profile specific properties file using the naming convention application-{profile}.properties in addition to application.properties.

For dev and prod profiles, we will have following properties files:

• /src/main/resources/application.properties: for default properties applicable in all environments.
• /src/main/resources/application-dev.properties: development environment-specific properties.
• /src/main/resources/application-prod.properties: production environment-specific properties.
• /src/test/resources/application.properties: only tests specific properties. Application tests load this file. Its values override the default application.properties entries.

Note that profiles specific files override the default application.properties file.

Another important feature is that values in properties file can refer to System properties through the Environment bean in bootstrapping time. In the following example, the value of property theme.color is resolved using the System property THEME_COLOR. If such property is found then its value is used, else default value CCCCCC is assigned.

theme.color=${THEME_COLOR:CCCCCC}

3. DataSource Configuration using Properties File

Now when we understand the usage of profile-specific properties files, and property value resolution from environment values, we can define the separate DataSource definitions in profile specific files:

3.1. DataSource for test Profile

Specify the datasource definition in /src/test/resources/application.properties file as follows. It connects to H2 database in runtime.

spring.datasource.url=jdbc:h2:mem:inventory;DB_CLOSE_DELAY=-1
spring.datasource.username=sa
spring.datasource.password=sa
spring.datasource.driverClassName=org.h2.Driver

spring.jpa.show-sql=true
spring.jpa.format-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy

Do not forget to include the H2 database dependency in test scope.

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

3.2. DataSource for dev Profile

Specify the datasource definition in /src/src/resources/application-dev.properties file as follows. It connects to MySQL database in runtime.

The values of DB_HOST, DB_PORT, DB_USER and DB_PASSWORD are selected from the Environment or system properties. We can define these properties at the machine level, and the aplication will use them in loading time.

spring.datasource.url=jdbc:mysql://${DB_HOST:localhost}:${DB_PORT:3306}/inventory
spring.datasource.username=${DB_USER:root}
spring.datasource.password=${DB_PASSWORD:rootpassword}
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

spring.jpa.show-sql=true
spring.jpa.format-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy

3.3. DataSource for prod Profile

Specify the datasource definition in /src/src/resources/application-prod.properties file as follows. It connects to Oracle database in runtime. The values of DB_HOST, DB_PORT, DB_USER and DB_PASSWORD are selected from the Environment or system properties.

spring.datasource.url=jdbc:oracle:thin://${DB_HOST:@localhost}:${DB_PORT:1521}/inventory
spring.datasource.username=${DB_USER:dbuser}
spring.datasource.password=${DB_PASSWORD:dbpassword}
spring.datasource.driverClassName=oracle.jdbc.OracleDriver

spring.jpa.show-sql=true
spring.jpa.format-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy

4. Java Configuration

Although we can use @Profile annotation to specify profile specific bean definitions, still I will suggest defining DataSource url and credentials in properties files and specify a single DataSource bean definition in the Java code to keep things simple. It saves us from the hassles of having multiple DataSource beans in the application and managing them through @Primary and @Qualifier annotations.

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class DataSourceConfig {

  @Autowired
  private Environment env;

  @Bean
  public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(env.getProperty("app.datasource.driverClassName"));
    dataSource.setUrl(env.getProperty("app.datasource.url"));
    dataSource.setUsername(env.getProperty("app.datasource.username"));
    dataSource.setPassword(env.getProperty("app.datasource.password"));
    return dataSource;
  }
}

Now we can define the app.datasource.* properties in the profile specific properties files. The correct properties will be picked in the application loading time and the DataSource bean will be created pointing to correct URL with correct credentials.

app.datasource.url=jdbc:mysql://${DB_HOST:localhost}:${DB_PORT:3306}/inventory
app.datasource.username=${DB_USER:root}
app.datasource.password=${DB_PASSWORD:rootpassword}
app.datasource.driverClassName=com.mysql.cj.jdbc.Driver

app.datasource.url=jdbc:oracle:thin://${DB_HOST:@localhost}:${DB_PORT:1521}/inventory
app.datasource.username=${DB_USER:dbuser}
app.datasource.password=${DB_PASSWORD:dbpassword}
app.datasource.driverClassName=oracle.jdbc.OracleDriver

5. Verifying the Configurations

5.1. Test Profile

For verifying the test profile DataSource configuration, we can create a simple @DataJpaTest test and assert if the expected beans have been created. We can also run more tests to check the database CRUD operations.

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
public class InventoryMgmtServiceApplicationTests {

	@Autowired private TestEntityManager testEntityManager;
	@Autowired private DataSource dataSource;
	@Autowired private JdbcTemplate jdbcTemplate;
	@Autowired private EntityManager entityManager;
	@Autowired private ItemRepository itemRepository;

	@Test
	void contextLoads() {
		Assertions.assertNotNull(dataSource);
		Assertions.assertNotNull(jdbcTemplate);
		Assertions.assertNotNull(entityManager);
		Assertions.assertNotNull(testEntityManager);
		Assertions.assertNotNull(itemRepository);
	}

...
}

Note that AutoConfigureTestDatabase.Replace.NONE is mandatory to use if we want to use the DataSource configuration mentioned in application.properties file. Otherwise, Spring boot auto-configures a in-memory DataSource with a random connection URL.

2023-01-31_11:34:52.918 DEBUG o.s.j.d.SimpleDriverDataSource - Creating new JDBC Driver Connection to 
[jdbc:h2:mem:c9aa49cf-7c54-4973-8bc1-f3f3accdc8ce;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false]

5.2. Dev and Prod Profiles

For non-test profiles, we can autowire a Repository instance and use it using the CommandLineRunner at the application load time. Then check the application logs for the connection details.

@SpringBootApplication
@Slf4j
public class InventoryMgmtServiceApplication implements CommandLineRunner {

	@Autowired
	private ItemRepository itemRepository;

	public static void main(String[] args) {
		SpringApplication.run(InventoryMgmtServiceApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {

		//... use itemRepository
	}
}

Now check the logs. We have run the application in dev profile so the MySQL database connection should be made.

....HikariConfig - username........................"root"
....HikariConfig - password........................<masked>
....HikariConfig - driverClassName................."com.mysql.cj.jdbc.Driver"
....HikariConfig - jdbcUrl.........................jdbc:mysql://localhost:3306/inventory

6. Conclusion

In this Spring boot tutorial, we learned to configure multiple DataSources such that each DataSource connects to a different database in different environment. We learned to use in-memory H2 database in application tests, and other relational databases in dev and prod environments.

Happy Learning!!

Sourcecode on Github