Store spring sessions in Separate Database

Storing session details in Redis or database is usually a good idea. However, the default implementation of spring-session-jdbc uses the primary data source to store and retrieve data from the session related tables. This can be a problem when there is a large amount of session related operations to the database.

To avoid these situations, it is a good idea to move the session related schema to a separate database. In this example we will see how we can use two separate databases, one for the application and one for storing sessions.

Before going further I would like you to take a look at Spring session using JDBC for a little background on how to implement JDBC session store.

The easiest way we can supply data source to the sessions related auto-configuration is by defining a DataSource bean with @SpringSessionDataSource annotation. At first, it may seem easy, but it is not. When you define your own DataSource bean, It will mess up the default autowiring. To safely provide a second data source, you need to follow these instructions.

Storing sessions in a different Database

First, create two sets of properties for each database. In my case, I’m using two separate in-memory h2 databases for the demo.

debug=true

# Properties for primary database
spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=root
spring.datasource.password=root
spring.h2.console.enabled=true

# Properties for session database
session.datasource.url=jdbc:h2:mem:sessions
session.datasource.username=sessions
session.datasource.password=sessions

Then manually create both the data source using DataSourceProperties properties. The spring.datasource configurations will be used to create the primary data source. The session.datasource properties will be used for creating the session data source.

@Configuration
public class DataSourceConfig {
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource")
    @Qualifier("dataSource")
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource primaryDataSource(@Qualifier("dataSource") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    @ConfigurationProperties("session.datasource")
    @Qualifier("sessionDataSourceProperties")
    public DataSourceProperties sessionDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Qualifier("sessionDataSource")
    @SpringSessionDataSource
    public DataSource sessionDataSource(@Qualifier("sessionDataSourceProperties") DataSourceProperties sessionDataSourceProperties) {
        return sessionDataSourceProperties
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }
}

In the above configuration class, notice how I marked sessionDataSource as @SpringSessionDataSource. Also, I have made sure both the data sources and their properties are distinguishable by their names using @Qualifier.

Initializing session schema

If you are using a database like Oracle or MySQL, You are already done. Create the session related tables (SPRING_SESSION and SPRING_SESSION_ATTRIBUTES) in the database pointed by sessionDataSource and you are good to go. But if you want to use an embedded data source like in this demo or if you set the spring.session.jdbc.initialize-schema to always, then you have a problem.

The default auto-configuration does not know which dataSource to autowire for JdbcSessionDataSourceInitializer. So it may end up creating session tables in the primary data source. To avoid this you need to provide your own JdbcSessionDataSourceInitializer bean as shown below.

@Configuration
@EnableConfigurationProperties(JdbcSessionProperties.class)
public class JdbcSessionConfig {
    @Bean
    public JdbcSessionDataSourceInitializer
    jdbcSessionDataSourceInitializer(@Qualifier("sessionDataSource") DataSource sessionDataSource,
                                     ResourceLoader resourceLoader,
                                     JdbcSessionProperties properties) {
        return new JdbcSessionDataSourceInitializer(sessionDataSource, resourceLoader, properties);
    }
}

In the above configuration note how I have used @Qualifier to load the sessionDataSource. Also, note that you need to EnableConfigurationProperties for autowiring JdbcSessionProperties.

If we do all of this right, You can see that the session database having two tables, and the primary database will be used by the application.

You can find this example in the GitHub link below. Feel free to take a look.