Separate the read and write databases

A mature service must handler large volumes of data.
To efficiently manage this data, we separate the reading database server(Read Replica) from the writing database server, thereby distribution the load on the database.

We plan to use the @Transactionl annotation, setting it to readOnly = true to utilize the read database, and if not, we will direct the operations to the write database.

1 Modify application.yml

I removed the existing datasource and defined the read and write database servers in the application.yml.

spring:
  datasource:
    read:
      jdbc-url: jdbc:mysql://localhost:3308/mydb # Read replica
      username: root
      password : rootpassword
      driver-class-name: com.mysql.cj.jdbc.Driver
    write:
      jdbc-url: jdbc:mysql://localhost:3307/mydb # Write replica
      username: root
      password : rootpassword
      driver-class-name: com.mysql.cj.jdbc.Driver

2 Create DataSourceRouter Class

We plan to implement the separated databases only on the production server, so specified that it should be activated only in the prod profile using the @Profile annotation.
The AbstractRoutingDataSource is an abstract class that provides the funcionality to select the appropriate data source at runtime. By extends this class, we can ovverride the determindCurrentLookupKey method. The return value of this method will be the data source that is used.

Through this method, we configure the application to use the Read replica when the @Transactional annotaion has readOnly set to true.

import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;

@Profile("prod")
public class DataSourceRouter extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        boolean readOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        return readOnly ? "read" : "write";
    }
}

3 Create DataSourceConfig Class

We configure and manage the data source through DataSourceConfig.
Fisrt, @EnableJpaRepositories(basePackages = {package name}) activates the JPA Repository beans. If the basePackages attribute is not specified, it scans beans within the same range as the one defined in @SpringBootApplication.

@ConfigurationProperties(prefix = "spring.datasource.read") automatically retrieves the setting that start with spring.datasource.read from application.yml and configures the data source properties.

The DataSource objects created in readDataSource() and writeDataSource() allow the application to connect to the database.

The routeDataSource() method is configured to select the appropriate data source based on the operation, either the read-only data source or the write-only data source.

With this setting dataSourceRouter.setDefaultTargetDataSource(writeDataSource), the default data source is set to the write data source. While the write database can handle both write and read operations, the read-only database cannot perform write operations. Therefore, to avoid exceptions during write opreations, the default value is set to the write data source.

The dataSource() method sets the default data source. The DataSourceRouter object returnd from the previous method is configured as the default data source. In other words, the data that manages the read-only and write data sources serves as the default data source for our application.

By using LazyConnectionDataSourceProxy, we can delay the database connection, creating a connection only when we actually access the data source. This optimizes resource usage and helps avoid unnecessary connections.

import javax.sql.DataSource;
import java.util.Map;
import com.zaxxer.hikari.HikariDataSource;
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.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.Profile;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;

@Configuration
@Profile("prod")
@EnableJpaRepositories(basePackages = "develup")
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.read")
    public DataSource readDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Bean
    @DependsOn({"writeDataSource", "readDataSource"})
    public DataSource routeDataSource() {
        DataSourceRouter dataSourceRouter = new DataSourceRouter();
        DataSource writeDataSource = writeDataSource();
        DataSource readDataSource = readDataSource();

        Map<Object, Object> dataSourceMap = Map.of(
                "write", writeDataSource,
                "read", readDataSource
        );
        dataSourceRouter.setTargetDataSources(dataSourceMap);
        dataSourceRouter.setDefaultTargetDataSource(writeDataSource);

        return dataSourceRouter;
    }

    @Bean
    @Primary
    @DependsOn("routeDataSource")
    public DataSource dataSource() {
        return new LazyConnectionDataSourceProxy(routeDataSource());
    }
}