How to Work with Multiple DataSources in Spring Boot - Read/Write Separation and AbstractRoutingDataSource

Even an application that has run on a single DataSource eventually reaches a scale where requirements like “we need to connect to multiple databases” or “we want to offload read queries to a read replica” start to appear. But the moment you try to add a second database to application.yml, auto-configuration stops working and you grind to a halt. In this article, we’ll organize this topic by splitting it into two patterns: static multiple DataSources, and dynamic read/write separation.

Two Scenarios That Require Multiple Data Sources

First, let’s be clear about which of the two problems you’re actually facing.

One is the case of connecting to independent databases with different purposes. For example, business data lives in the main DB, while audit logs live in a separate DB. Each has its own schema and its own repositories.

The other is the case of distributing the read/write load of a single logical database. You want to route writes to the primary and reads to a read replica. The data itself is the same; you only want to switch the destination.

The former is a good fit for static multiple DataSources, while the latter suits AbstractRoutingDataSource. This article assumes JPA. Switching per tenant is covered in the multi-tenancy implementation article, and pool configuration is left to the HikariCP tuning article.

Why Auto-Configuration Stops Working

Spring Boot’s DataSourceAutoConfiguration operates on the assumption that exactly one DataSource Bean exists in the context. Likewise, JpaRepositoriesAutoConfiguration assumes a single EntityManagerFactory.

So the instant you define two DataSources, you end up in a state of “which one should be the default?” — and you have to either annotate one of them with @Primary or switch to fully manual configuration. The auto-configuration mechanism itself is summarized in the auto-configuration article, so if you want to understand the background, take a look there as well.

Pattern 1: Static Multiple DataSource Configuration

Let’s start with a configuration that connects to two independent databases. First, separate the properties into namespaces in application.yml.

app:
  datasource:
    primary:
      jdbc-url: jdbc:mysql://localhost:3306/main
      username: app
      password: secret
    secondary:
      jdbc-url: jdbc:mysql://localhost:3306/audit
      username: app
      password: secret

Since we’re using HikariCP, note that you specify jdbc-url rather than url. Next, here’s the configuration class for each DataSource.

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.main.repository",
    entityManagerFactoryRef = "primaryEmf",
    transactionManagerRef = "primaryTx")
public class PrimaryDataSourceConfig {

    @Primary
    @Bean
    @ConfigurationProperties("app.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

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

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

Prepare the secondary side in the same form, but remove @Primary, change basePackages to com.example.audit.repository, and change packages to com.example.audit.entity. The key point is to annotate one side with @Primary to resolve the ambiguity.

Package Design and Pitfalls of Static Configuration

The most impactful thing in this configuration is to physically separate the entity and repository packages per DataSource. If the basePackages of @EnableJpaRepositories and the packages of LocalContainerEntityManagerFactoryBean overlap, both EntityManagerFactories end up scanning the same repository, and the Bean definitions collide at startup.

The three points where people commonly stumble are:

• Forgetting to specify entityManagerFactoryRef / transactionManagerRef in @EnableJpaRepositories. If omitted, they resolve to the default names and get bound to an unintended EMF.
• Forgetting to explicitly specify the TransactionManager on the secondary side, e.g. @Transactional("secondaryTx"), so the code ends up running inside the primary’s transaction.
• Collisions caused by overlapping package scan ranges.

For specifying the transaction manager, the transaction management article is also a helpful reference.

Pattern 2: Dynamic Routing with AbstractRoutingDataSource

From here we move on to read/write separation. To switch DataSources at runtime, we extend AbstractRoutingDataSource.

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }
}

Based on the key returned by determineCurrentLookupKey(), the actual connection target is chosen from the registered DataSources. Let’s register the primary and replica in the Bean definition.

@Bean
public DataSource routingDataSource(
        @Qualifier("primaryDataSource") DataSource primary,
        @Qualifier("replicaDataSource") DataSource replica) {
    Map<Object, Object> targets = new HashMap<>();
    targets.put("primary", primary);
    targets.put("replica", replica);

    RoutingDataSource routing = new RoutingDataSource();
    routing.setTargetDataSources(targets);
    routing.setDefaultTargetDataSource(primary);
    return routing;
}

The difference from the static pattern is that the DataSource passed to the EntityManagerFactory is “a single routing DataSource.” The repositories and entities are not split. Only the destination changes dynamically.

Managing the Switching Key with a ThreadLocal-Based ContextHolder

The routing key is held on a per-request-processing-thread basis. This is where ThreadLocal comes into play.

public final class DataSourceContextHolder {
    private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();

    public static void set(String key) { CONTEXT.set(key); }
    public static String get() { return CONTEXT.get(); }
    public static void clear() { CONTEXT.remove(); }
}

If you forget to call clear(), a stale key remains on a thread that gets reused in the thread pool, and the next request flows to the wrong destination. Keep in mind that you must always clear it here.

Using @Transactional(readOnly=true) as the Routing Trigger

You don’t want to write the switching by hand, right? So we use the attribute of @Transactional(readOnly = true) for the decision. Since TransactionSynchronizationManager.isCurrentTransactionReadOnly() tells you whether it’s read-only, you rewrite determineCurrentLookupKey() like this:

@Override
protected Object determineCurrentLookupKey() {
    return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
            ? "replica" : "primary";
}

With this, read methods annotated with @Transactional(readOnly = true) automatically flow to the replica, and write methods flow to the primary. Since you don’t have to touch the ContextHolder directly, your application code basically stays as is. For the propagation of read-only transactions, see the transaction management article.

LazyConnectionDataSourceProxy Is the Key to Making Routing Work

This is the biggest pitfall. Spring acquires a connection when a transaction begins, but this tends to happen before the readOnly decision has been finalized. Left as is, it always grabs the primary, and the switching doesn’t work.

That’s why you wrap the routing DataSource in LazyConnectionDataSourceProxy to defer connection acquisition until a query is actually issued.

@Bean
@Primary
public DataSource dataSource(@Qualifier("routingDataSource") DataSource routing) {
    return new LazyConnectionDataSourceProxy(routing);
}

By inserting this wrapping, the connection is determined after the readOnly attribute has been finalized, so determineCurrentLookupKey() can return the correct destination. The vast majority of the symptom “switching doesn’t work at all and everything flows to the primary” is caused by this.

Verifying Behavior and Watching Out for Replica Lag

To check whether traffic is flowing as intended, verify with SQL logs and connection information. Printing the HikariCP pool name and JDBC URL to the log makes it easy to tell.

logging:
  level:
    org.hibernate.SQL: DEBUG
    com.zaxxer.hikari: DEBUG

If reads use the replica’s pool and writes use the primary’s pool, you’re good.

However, one thing you must not forget in operation is replication lag. If you read the same data from the replica immediately after a write, you may get an old value that hasn’t been reflected yet. In places that require consistency, such as “register something and immediately display its contents,” you need to deliberately choose the primary. Adopt a workaround such as temporarily pinning the ContextHolder to primary, or reading within the same transaction as the write. Note that building the replication on the DB server side itself is outside the scope of this article.

Choosing Between the Two Approaches and Selection Criteria

Finally, let’s summarize the guidelines for making the decision.

• If you’re connecting to multiple independent databases, such as a business DB and a log DB, use static multiple DataSources. It’s natural for both entities and repositories to be separated.
• If you’re distributing the read/write load of a single logical database with the same contents, use AbstractRoutingDataSource. Only the destination is switched dynamically.

There are cases where you need both. In that case, you first split the databases statically, then internally turn one of them into a routing DataSource — a layered approach. However, the more complex the configuration becomes, the higher the maintenance cost, so I recommend considering things in this order: do you really need load distribution, and before introducing a read replica, can you first revisit your HikariCP pool settings?

Conclusion

For multiple data sources, the configuration you should adopt clearly diverges depending on whether the purpose is “connecting to multiple DBs” or “distributing reads and writes.” The key to the static pattern is to manually assemble the DataSource, EntityManagerFactory, and TransactionManager for each DataSource, and to physically split the packages. The dynamic pattern is established by the three-piece set of AbstractRoutingDataSource, @Transactional(readOnly=true), and LazyConnectionDataSourceProxy. Choose the one that fits your use case, and manually configure only the parts you need.