Multiple Database Configuration for Microservices in Spring Boot: A Comprehensive Guide

In modern microservice architectures, it's common to have services that need to interact with multiple databases. This could be due to various reasons such as legacy system integration, different types of data storage needs, or simply for optimizing performance. Spring Boot, with its flexible configuration and powerful data access libraries, makes it straightforward to configure multiple databases. In this comprehensive guide, we'll explore how to set up and manage multiple database connections in a Spring Boot microservice.

Introduction
Why Use Multiple Databases?
Setting Up a Spring Boot Project
Configuring Multiple Data Sources
Creating Data Source Configuration Classes
Defining Entity Managers
Creating Repositories
Testing the Configuration
Conclusion

1. Introduction

Microservices often need to interact with various databases. Each microservice might require a different type of database, such as an SQL database for transactional data and a NoSQL database for unstructured data. Spring Boot provides excellent support for configuring and managing multiple data sources, making it an ideal choice for modern microservice architectures.

2. Why Use Multiple Databases?

There are several reasons why you might need to use multiple databases in a microservice:

Legacy System Integration: Integrating with existing databases that are part of legacy systems.
Optimized Performance: Using different databases optimized for specific types of data (e.g., relational vs. non-relational).
Data Segregation: Separating data for security, compliance, or organizational reasons.
Scalability: Distributing the data load across different databases to improve performance.

3. Setting Up a Spring Boot Project

To get started, create a new Spring Boot project. You can use Spring Initializr or your preferred IDE to set up the project.

Maven Dependencies

In your pom.xml, include dependencies for Spring Data JPA and the databases you will use (e.g., H2 for in-memory, PostgreSQL, MySQL, etc.).

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!-- Add other database dependencies as needed -->
</dependencies>

4. Configuring Multiple Data Sources

In the application.yml or application.properties file, configure the connection properties for each database.

application.yml

spring:
  datasource:
    primary:
      url: jdbc:h2:mem:primarydb
      driver-class-name: org.h2.Driver
      username: sa
      password: password
    secondary:
      url: jdbc:postgresql://localhost:5432/secondarydb
      driver-class-name: org.postgresql.Driver
      username: postgres
      password: password

  jpa:
    primary:
      database-platform: org.hibernate.dialect.H2Dialect
      hibernate:
        ddl-auto: update
    secondary:
      database-platform: org.hibernate.dialect.PostgreSQLDialect
      hibernate:
        ddl-auto: update

5. Creating Data Source Configuration Classes

Next, create separate configuration classes for each data source.

Primary Data Source Configuration

package com.example.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.primary.repository",
    entityManagerFactoryRef = "primaryEntityManagerFactory",
    transactionManagerRef = "primaryTransactionManager"
)
public class PrimaryDataSourceConfig {

    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            @Qualifier("primaryDataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan(new String[] { "com.example.primary.entity" });

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        return em;
    }

    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("primaryEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Secondary Data Source Configuration

package com.example.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.secondary.repository",
    entityManagerFactoryRef = "secondaryEntityManagerFactory",
    transactionManagerRef = "secondaryTransactionManager"
)
public class SecondaryDataSourceConfig {

    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);
        em.setPackagesToScan(new String[] { "com.example.secondary.entity" });

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        return em;
    }

    @Bean(name = "secondaryTransactionManager")
    public PlatformTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

6. Defining Entity Managers

Define entity classes for each database. Make sure to place them in the respective packages specified in the configuration classes.

Primary Database Entity

package com.example.primary.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class PrimaryEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    // getters and setters
}

Secondary Database Entity

package com.example.secondary.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class SecondaryEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String description;

    // getters and setters
}

7. Creating Repositories

Create repository interfaces for each database, ensuring they are placed in the correct packages as configured.

Primary Repository

package com.example.primary.repository;

import com.example.primary.entity.PrimaryEntity;
import org.springframework.data.jpa.repository.JpaRepository;

public interface PrimaryRepository extends JpaRepository<PrimaryEntity, Long> {
}

Secondary Repository

package com.example.secondary.repository;

import com.example.secondary.entity.SecondaryEntity;
import org.springframework.data.jpa.repository.JpaRepository;

public interface SecondaryRepository extends JpaRepository<SecondaryEntity, Long> {
}

8. Testing the Configuration

Finally, create a simple REST controller to test the setup. This controller will use both repositories to perform CRUD operations.

Sample Controller

package com.example.controller;

import com.example.primary.entity.PrimaryEntity;
import com.example.primary.repository.PrimaryRepository;
import com.example.secondary.entity.SecondaryEntity;
import com.example.secondary.repository.SecondaryRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class TestController {

    @Autowired
    private PrimaryRepository primaryRepository;

    @Autowired
    private SecondaryRepository secondaryRepository;

    @GetMapping("/test")
    public String test() {
        PrimaryEntity primaryEntity = new PrimaryEntity();
        primaryEntity.setName("Primary Entity");
        primaryRepository.save(primaryEntity);

        SecondaryEntity secondaryEntity = new SecondaryEntity();
        secondaryEntity.setDescription("Secondary Entity");
        secondaryRepository.save(secondaryEntity);

        return "Entities saved!";
    }
}

Running the Application

Run

Blog

A Comprehensive Guide to Multiple Database Configuration for Microservices in Spring Boot

JavaFullStackDev.in