Multiple Data Sources

Some applications must talk to more than one database: a primary transactional store plus a reporting database, a legacy system, or a tenant-specific shard. Spring Boot’s auto-configuration assumes a single datasource, so once you need two, you disable some of that magic and wire each datasource, EntityManagerFactory, and TransactionManager by hand.

The shape of the problem

A single-datasource app gets a DataSource, an EntityManagerFactory, and a JpaTransactionManager for free. With two databases, Spring cannot know which bean to inject where, so you must:

Define two DataSource beans, marking one @Primary.
Build a separate EntityManagerFactory per datasource, each scanning its own entity package.
Provide a matching TransactionManager per datasource.
Scope each set of JPA repositories to its own package.

Note: Keep each database’s entities and repositories in distinct packages (for example com.shop.primary and com.shop.reporting). The package boundary is how Spring decides which EntityManagerFactory a repository uses.

Externalized properties

Define both datasources in application.yml under custom prefixes. @ConfigurationProperties binds each prefix to a DataSource.

app:
  datasource:
    primary:
      url: jdbc:postgresql://localhost:5432/orders
      username: orders
      password: secret
      driver-class-name: org.postgresql.Driver
    reporting:
      url: jdbc:mysql://localhost:3306/reporting
      username: report
      password: secret
      driver-class-name: com.mysql.cj.jdbc.Driver

Primary datasource configuration

The @Primary beans are the defaults Spring injects when a type is ambiguous. Note @EnableJpaRepositories ties the primary repository package to the primary EntityManagerFactory and TransactionManager.

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

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

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

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("primaryDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.shop.primary.entity")
                .persistenceUnit("primary")
                .build();
    }

    @Primary
    @Bean
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("primaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean emf) {
        return new JpaTransactionManager(emf.getObject());
    }
}

Secondary datasource configuration

The reporting configuration mirrors the primary one but without @Primary, and it scans the reporting packages.

@Configuration
@EnableJpaRepositories(
    basePackages = "com.shop.reporting.repo",
    entityManagerFactoryRef = "reportingEntityManagerFactory",
    transactionManagerRef = "reportingTransactionManager"
)
public class ReportingDataSourceConfig {

    @Bean
    @ConfigurationProperties("app.datasource.reporting")
    public DataSourceProperties reportingDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    public DataSource reportingDataSource(
            @Qualifier("reportingDataSourceProperties") DataSourceProperties props) {
        return props.initializeDataSourceBuilder().build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean reportingEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("reportingDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.shop.reporting.entity")
                .persistenceUnit("reporting")
                .build();
    }

    @Bean
    public PlatformTransactionManager reportingTransactionManager(
            @Qualifier("reportingEntityManagerFactory") LocalContainerEntityManagerFactoryBean emf) {
        return new JpaTransactionManager(emf.getObject());
    }
}

Tip: Reuse DataSourceProperties and EntityManagerFactoryBuilder (both Spring Boot beans) instead of hand-rolling builders. They honour your existing Spring Boot conventions, including HikariCP pool settings under each prefix.

Package-scoped repositories

Place each repository under the package its @EnableJpaRepositories points at. Spring routes it to the matching EntityManagerFactory automatically.

// com.shop.primary.repo
public interface OrderRepository extends JpaRepository<Order, Long> { }

// com.shop.reporting.repo
public interface SalesReportRepository extends JpaRepository<SalesReport, Long> { }

Choosing the transaction manager

Because there are two TransactionManager beans, @Transactional on a primary-store method uses the primary one by default (it is @Primary). To run inside the reporting database’s transactions, name it explicitly.

@Transactional("reportingTransactionManager")
public List<SalesReport> monthly() {
    return salesReportRepository.findAll();
}

Warning: A single @Transactional method spans only one datasource. There is no automatic two-phase commit across both. If you truly need atomic writes across databases, introduce a JTA transaction manager (for example, Atomikos) or redesign to avoid distributed transactions.

Best Practices

Separate entities and repositories into distinct packages per datasource; this is what makes routing unambiguous.
Mark exactly one datasource @Primary so single-datasource concerns (Actuator health, default injection) still work.
Bind each datasource with @ConfigurationProperties so HikariCP and driver settings stay externalized.
Name the TransactionManager explicitly on non-primary @Transactional methods.
Avoid cross-database transactions; prefer eventual consistency or a dedicated JTA setup when unavoidable.