Managing Multiple Databases in Spring Boot: A Complete Guide

Modern enterprise applications often need to work with multiple databases — whether for scalability, microservices integration, or data segregation. In this blog, we’ll explore why multiple database support is needed, whether it’s a good practice, and how to configure it correctly in Spring Boot, along with a real-life example.

---

Why Do We Need Multiple Database Support?

1. Microservices Architecture
   Each microservice may have its own database, but sometimes a service needs to communicate with more than one.

2. Legacy System Integration
   Applications often need to interact with both new (PostgreSQL/MySQL) and legacy (Oracle/SQL Server) databases.

3. Data Security & Compliance
   Sensitive data can be isolated in a secure database, while general data is stored separately.

4. Performance & Scalability
   Heavy-read operations can be offloaded to a reporting database, reducing load on the primary DB.

---

Is It a Good Practice?

Yes, if:

• You clearly separate responsibilities of each database.

• It’s required by business or compliance.

No, if:

• You’re adding multiple databases just for convenience.

• It complicates transactions unnecessarily.

💡 Best Practice: Keep the number of DataSources minimal. Use them only when business logic truly demands.

---

How Spring Boot Handles Multiple Databases

By default, Spring Boot auto-configures a single DataSource and enables JPA repositories automatically.

But with multiple DataSources, you must:

1. Define separate DataSource beans.

2. Define EntityManagerFactory and TransactionManager beans for each.

3. Use @EnableJpaRepositories with scoped basePackages, entityManagerFactoryRef, and transactionManagerRef.

Note: You must remove any global @EnableJpaRepositories to avoid conflicts.

---

Key Things to Keep in Mind

• Repository Scoping: Always specify basePackages in @EnableJpaRepositories to avoid binding repos to the wrong DataSource.

• Transactions: Use the right TransactionManager for each DB.

• Primary DB: Mark one DataSource as @Primary to serve as the default.

• Avoid Cross-DB Transactions: They can be complex and lead to performance issues unless you use a distributed transaction manager.

• Configuration Management: Keep credentials and URLs externalized in application.yml or a secret manager.

---

How Repositories Map to Each DataSource

In a multi-DataSource setup, repository-to-database mapping is handled via three key attributes in @EnableJpaRepositories:

• basePackages → Defines which repositories belong to the configuration.

• entityManagerFactoryRef → Connects repositories to the correct database.

• transactionManagerRef → Ensures transactions use the right database manager.

Example

• Repositories under com.example.repo.primary → PostgreSQL.

• Repositories under com.example.repo.secondary → Oracle.

So, when you call paymentRepository.findAll(), Spring automatically uses:

• PostgreSQL DataSource

• PostgreSQL EntityManagerFactory

• PostgreSQL TransactionManager

---

Real-Life Example: Spring Boot with Two Databases

Imagine a payment service that uses:

• PostgreSQL for transaction records

• Oracle for legacy user accounts

1. application.yml

yaml
spring:
  datasource:
    primary:
      url: jdbc:postgresql://localhost:5432/payments
      username: postgres
      password: secret
      driver-class-name: org.postgresql.Driver

    secondary:
      url: jdbc:oracle:thin:@localhost:1521:xe
      username: oracle_user
      password: secret
      driver-class-name: oracle.jdbc.OracleDriver

2. Primary Database Config

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

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.primary")
    public DataSourceProperties primaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource primaryDataSource() {
        return primaryDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource())
                .packages("com.example.model.primary")
                .persistenceUnit("primary")
                .build();
    }

    @Bean
    @Primary
    public PlatformTransactionManager primaryTransactionManager(
            EntityManagerFactory primaryEntityManagerFactory) {
        return new JpaTransactionManager(primaryEntityManagerFactory);
    }
}

3. Secondary Database Config

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

    @Bean
    @ConfigurationProperties("spring.datasource.secondary")
    public DataSourceProperties secondaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    public DataSource secondaryDataSource() {
        return secondaryDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondaryDataSource())
                .packages("com.example.model.secondary")
                .persistenceUnit("secondary")
                .build();
    }

    @Bean
    public PlatformTransactionManager secondaryTransactionManager(
            EntityManagerFactory secondaryEntityManagerFactory) {
        return new JpaTransactionManager(secondaryEntityManagerFactory);
    }
}

4. Usage in Repositories

java
@Repository
public interface PaymentRepository extends JpaRepository<Payment, Long> {
    // Uses PostgreSQL
}

@Repository
public interface LegacyUserRepository extends JpaRepository<LegacyUser, Long> {
    // Uses Oracle
}

---

Conclusion

Adding multiple databases in Spring Boot is sometimes necessary and powerful, but it should be done cautiously. Always:

• Define separate configurations,

• Remove global @EnableJpaRepositories,

• Scope repositories correctly,

• Avoid unnecessary cross-database transactions.

This ensures your application remains maintainable, performant, and easy to scale.