Spring Boot connect to multiple datasources

In many cases, we need to connect to multiple datasources from the same service due to business requirements. In this article, we discuss how to configure Spring Boot to connect to multiple datasources. Additionally, we address configuring the datasources to create tables if they don’t exist.

An imaginary example

Let’s assume that we have a song lyrics service that allows users to submit lyrics of songs using a REST endpoint. The service has two tables, songs and lyrics. Due to poor design, each of them resides in a different database. The songs table is in a MySQL 8 server, whereas the lyrics table is in a PostgreSQL database.

When a user’s request arrives, after proper validation, we first need to insert a record in the lyrics table. Then insert another record to the song table, which has a column referring to the corresponding lyricsId, like below,

Configuring Spring Boot to use multiple datasources

What we have to do is to configure Spring Boot to use multiple datasources at the same time. That is achievable by defining multiple transaction managers. We found the best way is to create Java configuration classes and add @Configuration. However, since we don’t want any hardcoded value, we can externalize the actual values to application.properties. Exactly like how we configure a single datasource. So let’s get started.

The first step is to add all datasources’ configurations to our application.properties file. We name the databases: ds1, which hosts the songs table, and ds2, which hosts the lyrics table,

spring.datasource.initialization-mode=always
spring.datasource.jdbcUrl=jdbc:mysql://localhost/ds1
spring.datasource.username=root
spring.datasource.password=secret

spring.datasource2.initialization-mode=always
spring.datasource2.jdbcUrl=jdbc:postgresql://localhost/ds2
spring.datasource2.username=postgres
spring.datasource2.password=secret


# To supress 'Caused by: java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgConnection.createClob() is not yet implemented.'
logging.level.org.hibernate.engine.jdbc.env.internal.LobCreatorBuilderImpl: ERROR

The configuration names can be anything, as long as they are consistent. So don’t limit yourself to datasource2.

The next step is to create the config Java files. That is accomplished by creating three Spring Beans for DataSource, PlatformTransactionManager, and LocalContainerEntityManagerFactoryBean,

package com.madadipouya.example.multiple.datasource.song.config

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.Primary
import org.springframework.data.jpa.repository.config.EnableJpaRepositories
import org.springframework.orm.jpa.JpaTransactionManager
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter
import org.springframework.transaction.PlatformTransactionManager
import jakarta.persistence.EntityManagerFactory
import javax.sql.DataSource

@Configuration
@EnableJpaRepositories(
        entityManagerFactoryRef = "songEntityManagerFactory",
        transactionManagerRef = "songTransactionManager",
        basePackages = ["com.madadipouya.example.multiple.datasource.song"])
class SongDatasourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    fun songDatasource(): DataSource = DataSourceBuilder.create().build()

    @Bean(name = ["songTransactionManager"])
    @Primary
    fun songTransactionManager(entityManagerFactory: EntityManagerFactory): PlatformTransactionManager {
        val transactionManager = JpaTransactionManager()
        transactionManager.entityManagerFactory = entityManagerFactory
        return transactionManager
    }

    @Bean(name = ["songEntityManagerFactory"])
    @Primary
    fun songEntityManagerFactory(): LocalContainerEntityManagerFactoryBean {
        val entityManager = LocalContainerEntityManagerFactoryBean()
        entityManager.dataSource = songDatasource()
        entityManager.setPackagesToScan("com.madadipouya.example.multiple.datasource.song")
        entityManager.persistenceUnitName = "songDatasource"

        val vendorAdapter = HibernateJpaVendorAdapter()
        vendorAdapter.setGenerateDdl(true)
        entityManager.jpaVendorAdapter = vendorAdapter
        return entityManager
    }
}

Now let’s create a configuration for the lyrics, similar to the above,

package com.madadipouya.example.multiple.datasource.lyrics.config

import org.springframework.beans.factory.annotation.Qualifier
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.data.jpa.repository.config.EnableJpaRepositories
import org.springframework.orm.jpa.JpaTransactionManager
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter
import org.springframework.transaction.PlatformTransactionManager
import jakarta.persistence.EntityManagerFactory
import javax.sql.DataSource


@Configuration
@EnableJpaRepositories(entityManagerFactoryRef = "lyricsEntityManagerFactory",
        transactionManagerRef = "lyricsTransactionManager",
        basePackages = ["com.madadipouya.example.multiple.datasource.lyrics"])
class LyricsDatasourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource2")
    fun lyricsDatasource(): DataSource = DataSourceBuilder.create().build()

    @Bean(name = ["lyricsTransactionManager"])
    fun lyricsTransactionManager(@Qualifier("lyricsEntityManagerFactory") entityManagerFactory: EntityManagerFactory): PlatformTransactionManager {
        val transactionManager = JpaTransactionManager()
        transactionManager.entityManagerFactory = entityManagerFactory
        return transactionManager
    }

    @Bean(name = ["lyricsEntityManagerFactory"])
    fun lyricsEntityManagerFactory(): LocalContainerEntityManagerFactoryBean {
        val entityManager = LocalContainerEntityManagerFactoryBean()
        entityManager.dataSource = lyricsDatasource()
        entityManager.setPackagesToScan("com.madadipouya.example.multiple.datasource.lyrics")
        entityManager.persistenceUnitName = "lyricsDatasource"

        val vendorAdapter = HibernateJpaVendorAdapter()
        vendorAdapter.setGenerateDdl(true)
        entityManager.jpaVendorAdapter = vendorAdapter
        return entityManager
    }
}

There are a couple of important things to note here:

One datasource must be annotated as @Primary. Otherwise, Hibernate gets confused and won’t work. In this example, we annotated the SongDatasource as primary.
When defining the PlatformTransactionManager bean, any non-primary datasources should have @Qualifier in the method parameter. Otherwise, persistence will not work.
Since we want Hibernate to create tables if they do not exist or handle the schema changes, we have enabled the setGenerateDdl flag. That is a per datasource property.
We must configure packages properly in two places, inside of the LocalContainerEntityManagerFactoryBean, entityManager.setPackagesToScan and inside of the @EnableJpaRepositories basePackages parameter. It makes sense for each datasource to have a separate package consisting of a model, repository, and service.

Once the above configuration is done, we can create the repositories, services, and other things similar to any Spring Boot application, as long as we separate each datasource in a separate package.

Conclusion

In this tutorial, we explained how to configure multiple datasources in a single Spring Boot application. We presented an imaginary song-lyrics example and configured two datasources by handcrafting entity manager factories, one of which, songs marked as primary. If you are wondering how to handle distributed transactions in such a setup, check our Spring transaction with multiple datasources article.

You can find the complete working example on GitHub at the link below,
https://github.com/kasramp/spring-multiple-datasources