Spring BootMultiple Databases
Spring Boot

Multiple Databases

Spring Boot supports connecting to multiple databases in a single application by defining separate DataSource, EntityManagerFactory, and TransactionManager beans for each database. Auto-configuration must be disabled and each database's JPA repositories must be directed to the correct EntityManagerFactory. This entry covers dual-database setup, separate transaction managers, Flyway and Liquibase with multiple datasources, read/write splitting, and distributed transactions with JTA.

Dual DataSource Configuration

Disable Spring Boot's DataSource and JPA auto-configuration and define each DataSource manually. Mark one as @Primary so Spring uses it as the default when no qualifier is specified. Each DataSource reads its properties from a distinct prefix in application.yml, keeping configuration isolated.
yaml
# ── application.yml ────────────────────────────────────────────────────
app:
  datasource:
    primary:
      url: jdbc:postgresql://primary-host:5432/primary_db
      username: ${PRIMARY_DB_USER}
      password: ${PRIMARY_DB_PASS}
      driver-class-name: org.postgresql.Driver
      hikari:
        pool-name: PrimaryPool
        maximum-pool-size: 20
        minimum-idle: 5
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000

    secondary:
      url: jdbc:mysql://secondary-host:3306/secondary_db
      username: ${SECONDARY_DB_USER}
      password: ${SECONDARY_DB_PASS}
      driver-class-name: com.mysql.cj.jdbc.Driver
      hikari:
        pool-name: SecondaryPool
        maximum-pool-size: 10
        minimum-idle: 2
        connection-timeout: 30000

// ── Disable auto-configuration ────────────────────────────────────────
@SpringBootApplication(exclude = {
    DataSourceAutoConfiguration.class,
    DataSourceTransactionManagerAutoConfiguration.class,
    HibernateJpaAutoConfiguration.class
})
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

// ── DataSource configuration ───────────────────────────────────────────
@Configuration
public class DataSourceConfig {

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

    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.primary.hikari")
    public DataSource primaryDataSource(
            @Qualifier("primaryDataSourceProperties")
            DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
    }

    // ── Secondary DataSource (MySQL) ──────────────────────────────────
    @Bean
    @ConfigurationProperties("app.datasource.secondary")
    public DataSourceProperties secondaryDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("app.datasource.secondary.hikari")
    public DataSource secondaryDataSource(
            @Qualifier("secondaryDataSourceProperties")
            DataSourceProperties properties) {
        return properties.initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
    }
}

Primary JPA Configuration

Each database needs its own EntityManagerFactory and PlatformTransactionManager. The @EnableJpaRepositories annotation on each configuration class directs Spring Data to scan a specific package and wire its repositories to the correct EntityManagerFactory and TransactionManager.
Java
// ── Primary JPA configuration (PostgreSQL) ───────────────────────────
@Configuration
@EnableJpaRepositories(
    basePackages            = "com.myapp.primary.repository",
    entityManagerFactoryRef = "primaryEntityManagerFactory",
    transactionManagerRef   = "primaryTransactionManager"
)
public class PrimaryJpaConfig {

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean
            primaryEntityManagerFactory(
                @Qualifier("primaryDataSource") DataSource dataSource,
                EntityManagerFactoryBuilder builder) {

        return builder
            .dataSource(dataSource)
            .packages("com.myapp.primary.domain")   // entity scan package
            .persistenceUnit("primary")
            .properties(jpaProperties())
            .build();
    }

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

    private Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.hbm2ddl.auto",          "validate");
        props.put("hibernate.dialect",
            "org.hibernate.dialect.PostgreSQLDialect");
        props.put("hibernate.show_sql",              "false");
        props.put("hibernate.format_sql",            "true");
        props.put("hibernate.jdbc.batch_size",        "50");
        props.put("hibernate.order_inserts",         "true");
        props.put("hibernate.order_updates",         "true");
        return props;
    }
}

Secondary JPA Configuration

The secondary configuration mirrors the primary but references the secondary DataSource, scans a different entity package, and uses a different persistence unit name. Keeping entities in separate packages is the clearest way to ensure Hibernate does not mix entities between the two databases.
Java
// ── Secondary JPA configuration (MySQL) ──────────────────────────────
@Configuration
@EnableJpaRepositories(
    basePackages            = "com.myapp.secondary.repository",
    entityManagerFactoryRef = "secondaryEntityManagerFactory",
    transactionManagerRef   = "secondaryTransactionManager"
)
public class SecondaryJpaConfig {

    @Bean
    public LocalContainerEntityManagerFactoryBean
            secondaryEntityManagerFactory(
                @Qualifier("secondaryDataSource") DataSource dataSource,
                EntityManagerFactoryBuilder builder) {

        return builder
            .dataSource(dataSource)
            .packages("com.myapp.secondary.domain")
            .persistenceUnit("secondary")
            .properties(jpaProperties())
            .build();
    }

    @Bean
    public PlatformTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryEntityManagerFactory")
            LocalContainerEntityManagerFactoryBean factory) {
        return new JpaTransactionManager(
            Objects.requireNonNull(factory.getObject()));
    }

    private Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.hbm2ddl.auto",   "validate");
        props.put("hibernate.dialect",
            "org.hibernate.dialect.MySQLDialect");
        props.put("hibernate.show_sql",       "false");
        props.put("hibernate.format_sql",     "true");
        return props;
    }
}

// ── Package structure ──────────────────────────────────────────────────
// com.myapp.primary.domain       — PostgreSQL entities
//   User.java
//   Order.java
//   Product.java
// com.myapp.primary.repository   — PostgreSQL repositories
//   UserRepository.java
//   OrderRepository.java
//
// com.myapp.secondary.domain     — MySQL entities
//   LegacyCustomer.java
//   LegacyOrder.java
// com.myapp.secondary.repository — MySQL repositories
//   LegacyCustomerRepository.java

Using Multiple Transaction Managers

With multiple transaction managers, every @Transactional annotation must specify which manager to use via the transactionManager attribute, or the @Primary manager is used by default. Services that interact with both databases call each manager separately — there is no distributed coordination between them.
Java
// ── Service using primary database ────────────────────────────────────
@Service
@RequiredArgsConstructor
public class UserService {

    private final UserRepository userRepo;   // primary repository

    // Uses @Primary transaction manager automatically
    @Transactional
    public UserResponse create(CreateUserRequest request) {
        User user = User.from(request);
        return UserResponse.from(userRepo.save(user));
    }

    @Transactional(readOnly = true)
    public UserResponse findById(Long id) {
        return userRepo.findById(id)
            .map(UserResponse::from)
            .orElseThrow(() -> new UserNotFoundException(id));
    }
}

// ── Service using secondary database ──────────────────────────────────
@Service
@RequiredArgsConstructor
public class LegacyCustomerService {

    private final LegacyCustomerRepository legacyRepo;

    // Explicit qualifier — secondary transaction manager
    @Transactional(transactionManager = "secondaryTransactionManager")
    public LegacyCustomer sync(LegacySyncRequest request) {
        return legacyRepo.save(LegacyCustomer.from(request));
    }

    @Transactional(
        transactionManager = "secondaryTransactionManager",
        readOnly = true
    )
    public List<LegacyCustomer> findAll() {
        return legacyRepo.findAll();
    }
}

// ── Service coordinating both databases (no distributed tx) ───────────
@Service
@RequiredArgsConstructor
@Slf4j
public class MigrationService {

    private final UserService          userService;
    private final LegacyCustomerService legacyService;

    // Two independent transactions — NOT atomic across databases
    // If the second fails, the first has already committed
    public MigrationResult migrate(Long legacyCustomerId) {
        // Step 1: Read from secondary (legacy)
        LegacyCustomer legacy = legacyService
            .findById(legacyCustomerId);

        // Step 2: Write to primary (new system)
        try {
            UserResponse user = userService.create(
                CreateUserRequest.from(legacy));
            return MigrationResult.success(user.id());
        } catch (Exception ex) {
            log.error("Migration failed for legacy customer {}",
                legacyCustomerId, ex);
            return MigrationResult.failure(ex.getMessage());
        }
    }
}

Flyway with Multiple DataSources

Spring Boot's Flyway auto-configuration applies only to the primary DataSource. Define a separate Flyway bean for each additional database, pointing it to the correct DataSource and migration location. Disable auto-configuration and configure each Flyway instance manually.
Java
// ── Disable Flyway auto-configuration ────────────────────────────────
@SpringBootApplication(exclude = {
    DataSourceAutoConfiguration.class,
    FlywayAutoConfiguration.class,
    DataSourceTransactionManagerAutoConfiguration.class,
    HibernateJpaAutoConfiguration.class
})
public class Application { ... }

// ── Flyway configuration for both databases ───────────────────────────
@Configuration
public class FlywayConfig {

    // ── Primary Flyway (PostgreSQL) ───────────────────────────────────
    @Bean
    @Primary
    public Flyway primaryFlyway(
            @Qualifier("primaryDataSource") DataSource dataSource) {
        Flyway flyway = Flyway.configure()
            .dataSource(dataSource)
            .locations("classpath:db/migration/primary")
            .baselineOnMigrate(false)
            .validateOnMigrate(true)
            .table("flyway_schema_history")
            .load();
        flyway.migrate();
        return flyway;
    }

    // ── Secondary Flyway (MySQL) ──────────────────────────────────────
    @Bean
    public Flyway secondaryFlyway(
            @Qualifier("secondaryDataSource") DataSource dataSource) {
        Flyway flyway = Flyway.configure()
            .dataSource(dataSource)
            .locations("classpath:db/migration/secondary")
            .baselineOnMigrate(false)
            .validateOnMigrate(true)
            .table("flyway_schema_history")
            .load();
        flyway.migrate();
        return flyway;
    }

    // ── Ensure Flyway runs before EntityManagerFactory ────────────────
    @Bean
    @Primary
    @DependsOn("primaryFlyway")
    public LocalContainerEntityManagerFactoryBean
            primaryEntityManagerFactory(
                @Qualifier("primaryDataSource") DataSource dataSource,
                EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(dataSource)
            .packages("com.myapp.primary.domain")
            .persistenceUnit("primary")
            .build();
    }

    @Bean
    @DependsOn("secondaryFlyway")
    public LocalContainerEntityManagerFactoryBean
            secondaryEntityManagerFactory(
                @Qualifier("secondaryDataSource") DataSource dataSource,
                EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(dataSource)
            .packages("com.myapp.secondary.domain")
            .persistenceUnit("secondary")
            .build();
    }
}

// ── Migration file structure ───────────────────────────────────────────
// src/main/resources/
// └── db/
//     └── migration/
//         ├── primary/
//         │   ├── V1__create_users.sql
//         │   └── V2__create_orders.sql
//         └── secondary/
//             ├── V1__create_legacy_customers.sql
//             └── V2__add_legacy_indexes.sql

Read/Write Splitting

Route write operations to a primary (master) database and read operations to one or more replicas (read replicas). Spring's AbstractRoutingDataSource selects the target DataSource at runtime based on the current transaction's read-only flag, set on the thread via a TransactionSynchronizationManager lookup.
Java
// ── Routing key enum ──────────────────────────────────────────────────
public enum DataSourceType {
    PRIMARY, REPLICA
}

// ── Thread-local context ──────────────────────────────────────────────
public class DataSourceContextHolder {

    private static final ThreadLocal<DataSourceType> CONTEXT =
        new ThreadLocal<>();

    public static void set(DataSourceType type) {
        CONTEXT.set(type);
    }

    public static DataSourceType get() {
        return CONTEXT.get();
    }

    public static void clear() {
        CONTEXT.remove();
    }
}

// ── Routing DataSource ────────────────────────────────────────────────
public class RoutingDataSource
        extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        // Route to replica for read-only transactions
        boolean readOnly = TransactionSynchronizationManager
            .isCurrentTransactionReadOnly();

        DataSourceType explicit = DataSourceContextHolder.get();
        if (explicit != null) return explicit;

        return readOnly
            ? DataSourceType.REPLICA
            : DataSourceType.PRIMARY;
    }
}

// ── DataSource configuration with routing ─────────────────────────────
@Configuration
public class RoutingDataSourceConfig {

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

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

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

    @Bean
    public DataSource replicaDataSource(
            @Qualifier("replicaDataSourceProperties")
            DataSourceProperties props) {
        return props.initializeDataSourceBuilder()
            .type(HikariDataSource.class).build();
    }

    @Bean
    @Primary
    public DataSource routingDataSource(
            @Qualifier("primaryDataSource") DataSource primary,
            @Qualifier("replicaDataSource")  DataSource replica) {

        Map<Object, Object> targets = Map.of(
            DataSourceType.PRIMARY, primary,
            DataSourceType.REPLICA, replica
        );

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

    // ── Wrap routing DataSource in LazyConnectionDataSourceProxy ──────
    // Delays connection acquisition until actually needed
    @Bean
    public DataSource dataSource(
            @Qualifier("routingDataSource") DataSource routing) {
        return new LazyConnectionDataSourceProxy(routing);
    }
}

// ── Service — automatic routing via readOnly flag ─────────────────────
@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)   // all reads → replica
public class ProductQueryService {

    private final ProductRepository productRepo;

    public Page<ProductResponse> findAll(Pageable pageable) {
        // readOnly = true → RoutingDataSource picks REPLICA
        return productRepo.findAll(pageable).map(ProductResponse::from);
    }

    @Transactional                // write → primary
    public ProductResponse create(CreateProductRequest request) {
        return ProductResponse.from(
            productRepo.save(Product.from(request)));
    }
}

Liquibase with Multiple DataSources

Liquibase auto-configuration applies to the primary DataSource only. Define a SpringLiquibase bean for each additional DataSource, pointing each to its own changelog location. Use @DependsOn to ensure Liquibase runs before the corresponding EntityManagerFactory initialises.
Java
// ── Disable Liquibase auto-configuration ─────────────────────────────
@SpringBootApplication(exclude = {
    DataSourceAutoConfiguration.class,
    LiquibaseAutoConfiguration.class,
    DataSourceTransactionManagerAutoConfiguration.class,
    HibernateJpaAutoConfiguration.class
})
public class Application { ... }

// ── Liquibase beans for each database ────────────────────────────────
@Configuration
public class LiquibaseMultiConfig {

    // ── Primary Liquibase (PostgreSQL) ────────────────────────────────
    @Bean("primaryLiquibase")
    @Primary
    public SpringLiquibase primaryLiquibase(
            @Qualifier("primaryDataSource") DataSource dataSource) {

        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setDataSource(dataSource);
        liquibase.setChangeLog(
            "classpath:db/changelog/primary/db.changelog-master.yaml");
        liquibase.setContexts("production");
        liquibase.setShouldRun(true);
        liquibase.setDropFirst(false);
        liquibase.setDefaultSchema("public");
        return liquibase;
    }

    // ── Secondary Liquibase (MySQL) ───────────────────────────────────
    @Bean("secondaryLiquibase")
    public SpringLiquibase secondaryLiquibase(
            @Qualifier("secondaryDataSource") DataSource dataSource) {

        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setDataSource(dataSource);
        liquibase.setChangeLog(
            "classpath:db/changelog/secondary/db.changelog-master.yaml");
        liquibase.setContexts("production");
        liquibase.setShouldRun(true);
        liquibase.setDropFirst(false);
        return liquibase;
    }

    // ── EntityManagerFactory depends on migrations completing ─────────
    @Bean
    @Primary
    @DependsOn("primaryLiquibase")
    public LocalContainerEntityManagerFactoryBean
            primaryEntityManagerFactory(
                @Qualifier("primaryDataSource") DataSource dataSource,
                EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(dataSource)
            .packages("com.myapp.primary.domain")
            .persistenceUnit("primary")
            .build();
    }

    @Bean
    @DependsOn("secondaryLiquibase")
    public LocalContainerEntityManagerFactoryBean
            secondaryEntityManagerFactory(
                @Qualifier("secondaryDataSource") DataSource dataSource,
                EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(dataSource)
            .packages("com.myapp.secondary.domain")
            .persistenceUnit("secondary")
            .build();
    }
}

// ── Changelog structure ────────────────────────────────────────────────
// src/main/resources/
// └── db/
//     └── changelog/
//         ├── primary/
//         │   ├── db.changelog-master.yaml
//         │   └── migrations/
//         │       ├── 001-create-users.yaml
//         │       └── 002-create-orders.yaml
//         └── secondary/
//             ├── db.changelog-master.yaml
//             └── migrations/
//                 ├── 001-create-legacy-customers.yaml
//                 └── 002-add-legacy-indexes.yaml

Distributed Transactions with JTA

When a single operation must write to two databases atomically — both commit or both roll back — a JTA (Java Transaction API) distributed transaction coordinator is required. Atomikos is the most common embedded JTA provider for Spring Boot. Use distributed transactions sparingly: they are slower, more complex, and introduce coordinator failure modes.
XML
<!-- pom.xml — Atomikos JTA provider -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>

# ── application.yml ────────────────────────────────────────────────────
spring:
  jta:
    enabled: true
    atomikos:
      datasource:
        primary:
          xa-data-source-class-name:
            org.postgresql.xa.PGXADataSource
          unique-resource-name: primaryDataSource
          max-pool-size: 20
          min-pool-size: 5
          xa-properties:
            serverName: primary-host
            portNumber: 5432
            databaseName: primary_db
            user: ${PRIMARY_DB_USER}
            password: ${PRIMARY_DB_PASS}
        secondary:
          xa-data-source-class-name:
            com.mysql.cj.jdbc.MysqlXADataSource
          unique-resource-name: secondaryDataSource
          max-pool-size: 10
          min-pool-size: 2
          xa-properties:
            url: jdbc:mysql://secondary-host:3306/secondary_db
            user: ${SECONDARY_DB_USER}
            password: ${SECONDARY_DB_PASS}

// ── JTA configuration ─────────────────────────────────────────────────
@Configuration
public class JtaConfig {

    @Bean
    @Primary
    public AtomikosDataSourceBean primaryDataSource() {
        AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
        ds.setUniqueResourceName("primaryDataSource");
        ds.setXaDataSourceClassName(
            "org.postgresql.xa.PGXADataSource");
        ds.setXaProperties(primaryXaProperties());
        ds.setMaxPoolSize(20);
        ds.setMinPoolSize(5);
        return ds;
    }

    @Bean
    public AtomikosDataSourceBean secondaryDataSource() {
        AtomikosDataSourceBean ds = new AtomikosDataSourceBean();
        ds.setUniqueResourceName("secondaryDataSource");
        ds.setXaDataSourceClassName(
            "com.mysql.cj.jdbc.MysqlXADataSource");
        ds.setXaProperties(secondaryXaProperties());
        ds.setMaxPoolSize(10);
        ds.setMinPoolSize(2);
        return ds;
    }

    private Properties primaryXaProperties() {
        Properties p = new Properties();
        p.setProperty("serverName",   "primary-host");
        p.setProperty("portNumber",   "5432");
        p.setProperty("databaseName", "primary_db");
        p.setProperty("user",         "${PRIMARY_DB_USER}");
        p.setProperty("password",     "${PRIMARY_DB_PASS}");
        return p;
    }

    private Properties secondaryXaProperties() {
        Properties p = new Properties();
        p.setProperty("URL",      "jdbc:mysql://secondary-host:3306/secondary_db");
        p.setProperty("user",     "${SECONDARY_DB_USER}");
        p.setProperty("password", "${SECONDARY_DB_PASS}");
        return p;
    }
}

// ── Service with distributed transaction ──────────────────────────────
@Service
@RequiredArgsConstructor
@Slf4j
public class DistributedMigrationService {

    private final UserRepository           userRepo;
    private final LegacyCustomerRepository legacyRepo;

    // Single @Transactional spans BOTH databases via JTA
    // If either write fails — BOTH roll back
    @Transactional   // JTA TransactionManager coordinates both
    public void migrateAtomically(Long legacyId) {
        LegacyCustomer legacy = legacyRepo.findById(legacyId)
            .orElseThrow(() -> new ResourceNotFoundException(
                "Legacy customer not found: " + legacyId));

        // Write to primary (PostgreSQL)
        User user = userRepo.save(User.fromLegacy(legacy));

        // Write to secondary (MySQL) — marks legacy as migrated
        legacy.setMigratedUserId(user.getId());
        legacy.setMigratedAt(LocalDateTime.now());
        legacyRepo.save(legacy);   // if this throws → user write also rolls back

        log.info("Atomically migrated legacy customer {} → user {}",
            legacyId, user.getId());
    }
}
Prev
Liquibase Migration
Next
Connection Pooling