Multiple Databases With Flyway in Spring Boot: A Comprehensive Guide

Modern enterprise applications often require integration with multiple databases to address diverse needs—such as segregating transactional and analytical data, supporting multi-tenant architectures, or integrating with legacy systems. Spring Boot simplifies multi-database configuration, but managing schema migrations across these databases adds complexity.

Flyway is a popular open-source schema migration tool that ensures database schemas evolve reliably over time. In this guide, we’ll walk through setting up multiple databases in Spring Boot, integrating Flyway for schema migrations, and following best practices to maintain consistency and avoid pitfalls.

Table of Contents#

  1. Prerequisites
  2. Setting Up a Spring Boot Project with Multiple Databases 2.1 Add Dependencies 2.2 Configure Data Sources 2.3 Define Data Source Beans
  3. Integrating Flyway for Multiple Databases 3.1 Manual Flyway Configuration 3.2 Customizing Migration Locations 3.3 Handling Baselines
  4. Example Migration Scripts 4.1 Directory Structure 4.2 Sample SQL Migrations
  5. Common Scenarios & Best Practices 5.1 Migration Version Management 5.2 Transaction Safety 5.3 Testing Migrations 5.4 Rollback Strategies
  6. Troubleshooting Common Issues
  7. Advanced Topics 7.1 Spring Profile Integration 7.2 Flyway Teams Features
  8. Conclusion
  9. References

Prerequisites#

To follow along, you’ll need:

  • Java 17+ (compatible with Spring Boot 3.x)
  • Spring Boot 3.2+
  • Flyway 9.x+
  • Two SQL databases (e.g., PostgreSQL for primary, MySQL for secondary)
  • Maven or Gradle build tool

Setting Up a Spring Boot Project with Multiple Databases#

2.1 Add Dependencies#

Add the following dependencies to your pom.xml (Maven) or build.gradle (Gradle) to support multi-data sources and Flyway:

Maven pom.xml#

<dependencies>
    <!-- Spring Boot Starter JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- Flyway Core -->
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
    <!-- Database Drivers -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

2.2 Configure Data Sources#

Add configuration properties for both databases in src/main/resources/application.properties:

# Primary Database (PostgreSQL)
spring.datasource.primary.url=jdbc:postgresql://localhost:5432/primary_db
spring.datasource.primary.username=postgres
spring.datasource.primary.password=your_postgres_password
spring.datasource.primary.driver-class-name=org.postgresql.Driver
 
# Secondary Database (MySQL)
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/secondary_db?createDatabaseIfNotExist=true
spring.datasource.secondary.username=root
spring.datasource.secondary.password=your_mysql_password
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
 
# Disable Hibernate auto-DDL (Flyway will manage schema)
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true

2.3 Define Data Source Beans#

Spring Boot auto-configures a single data source by default, but for multiple databases, we need to define explicit beans. Create configuration classes for each data source:

Primary Data Source Configuration#

package com.example.multidb.config;
 
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
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.transaction.PlatformTransactionManager;
 
import javax.sql.DataSource;
 
@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.multidb.repository.primary",
    entityManagerFactoryRef = "primaryEntityManagerFactory",
    transactionManagerRef = "primaryTransactionManager"
)
public class PrimaryDataSourceConfig {
 
    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return org.springframework.boot.jdbc.DataSourceBuilder.create().build();
    }
 
    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
        EntityManagerFactoryBuilder builder,
        @Qualifier("primaryDataSource") DataSource dataSource,
        JpaProperties jpaProperties
    ) {
        return builder
            .dataSource(dataSource)
            .packages("com.example.multidb.entity.primary")
            .persistenceUnit("primary")
            .properties(jpaProperties.getProperties())
            .build();
    }
 
    @Primary
    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
        @Qualifier("primaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory
    ) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }
}

Secondary Data Source Configuration#

Repeat the same pattern for the secondary database, omitting the @Primary annotation and updating packages/references:

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.multidb.repository.secondary",
    entityManagerFactoryRef = "secondaryEntityManagerFactory",
    transactionManagerRef = "secondaryTransactionManager"
)
public class SecondaryDataSourceConfig {
 
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return org.springframework.boot.jdbc.DataSourceBuilder.create().build();
    }
 
    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
        EntityManagerFactoryBuilder builder,
        @Qualifier("secondaryDataSource") DataSource dataSource,
        JpaProperties jpaProperties
    ) {
        return builder
            .dataSource(dataSource)
            .packages("com.example.multidb.entity.secondary")
            .persistenceUnit("secondary")
            .properties(jpaProperties.getProperties())
            .build();
    }
 
    @Bean(name = "secondaryTransactionManager")
    public PlatformTransactionManager secondaryTransactionManager(
        @Qualifier("secondaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory
    ) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }
}

Integrating Flyway for Multiple Databases#

Spring Boot’s auto-configured Flyway works for a single data source, but for multiple databases, we need to manually configure Flyway beans for each data source.

3.1 Manual Flyway Configuration#

Add Flyway beans to each data source configuration class. For example, in PrimaryDataSourceConfig:

@Bean(name = "primaryFlyway")
public Flyway primaryFlyway(@Qualifier("primaryDataSource") DataSource dataSource) {
    return Flyway.configure()
        .dataSource(dataSource)
        .locations("classpath:db/migration/primary") // Migration files directory
        .baselineOnMigrate(true) // Baseline existing schemas
        .baselineVersion("0") // Initial baseline version
        .load()
        .migrate(); // Run migrations on bean initialization
}

For the secondary database, add a similar bean in SecondaryDataSourceConfig:

@Bean(name = "secondaryFlyway")
public Flyway secondaryFlyway(@Qualifier("secondaryDataSource") DataSource dataSource) {
    return Flyway.configure()
        .dataSource(dataSource)
        .locations("classpath:db/migration/secondary")
        .baselineOnMigrate(true)
        .baselineVersion("0")
        .load()
        .migrate();
}

3.2 Customizing Migration Locations#

By default, Flyway looks for migrations in classpath:db/migration, but we’ve customized this to separate directories for each database. This ensures migrations for one database don’t accidentally apply to another.

3.3 Handling Flyway Baselines#

If your databases already have existing schemas, use baselineOnMigrate=true to let Flyway record the current state as a baseline. This prevents Flyway from trying to apply older migrations to an already populated database.

Example Migration Scripts#

4.1 Directory Structure#

Organize migration scripts into database-specific directories:

src/main/resources/
└── db/
    └── migration/
        ├── primary/
        │   ├── V1__create_users_table.sql
        │   └── V2__add_email_column.sql
        └── secondary/
            ├── V1__create_orders_table.sql
            └── V2__add_total_amount_column.sql

4.2 Sample SQL Migration Files#

Primary Database: V1__create_users_table.sql#

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Secondary Database: V1__create_orders_table.sql#

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Note: Cross-database referential integrity is enforced at the application layer

Common Scenarios & Best Practices#

5.1 Migration Version Management#

  • Use independent versioning for each database: Migrations for the primary and secondary databases don’t need to share version numbers. For example, the primary database could be at version 3 while the secondary is at version 2.
  • Use descriptive migration names: Avoid generic names like V1__init.sql. Instead, use names like V1__create_users_and_roles_table.sql for clarity.

5.2 Transaction Safety#

  • Flyway runs each migration script in a single transaction (for databases that support transactional DDL, e.g., PostgreSQL, MySQL with InnoDB).
  • Disable transactions for non-transactional operations (e.g., CREATE INDEX CONCURRENTLY in PostgreSQL) using /* Flyway: NON_TRANSACTIONAL */ at the top of the migration script.

5.3 Testing Migrations#

  • Use Testcontainers to spin up temporary databases for testing. This ensures migrations work as expected in an environment identical to production.
  • Validate migrations with Flyway’s validate command: Checks if local migration scripts match those applied to the database.

5.4 Rollback Strategies#

  • Flyway Community Edition: No automatic rollbacks. To reverse changes, manually write and execute a new migration script (e.g., V3__drop_email_column.sql) that undoes the modification.
  • Flyway Teams: Use built-in undo migrations by naming scripts with the U prefix. Teams also supports dry runs and rollback commands.

Troubleshooting Common Issues#

6.1 Migration Version Conflicts#

  • Issue: Flyway throws an error stating a version already exists.
  • Fix: Check if the migration script was already applied, or if the version number is duplicated. For non-production environments, you can delete the conflicting entry from the flyway_schema_history table.

6.2 Data Source Configuration Errors#

  • Issue: Flyway can’t connect to a database.
  • Fix: Verify connection properties (URL, username, password) in application.properties and ensure the database server is running.

6.3 Schema History Table Issues#

  • Issue: The flyway_schema_history table is corrupted.
  • Fix: Rebuild the table by running flyway baseline with the correct baseline version, or delete the table and re-run migrations (use only in non-production).

Advanced Topics#

7.1 Spring Profile Integration#

Use Spring Profiles to apply different Flyway configurations for dev, staging, and production:

# application-dev.properties
spring.flyway.primary.clean-on-validation-error=true # Only for dev
 
# application-prod.properties
spring.flyway.primary.clean-on-validation-error=false # Never clean production

Then, annotate Flyway beans with @Profile to enable/disable them conditionally:

@Bean(name = "primaryFlyway")
@Profile("!prod")
public Flyway primaryFlyway(@Qualifier("primaryDataSource") DataSource dataSource) {
    // Dev-specific configuration
}

7.2 Flyway Teams Features#

Flyway Teams adds enterprise-grade features for multi-db management:

  • Undo Migrations: Automatic rollback of migrations.
  • Dry Runs: Preview migrations without applying them.
  • Multi-Environment Support: Centralize migration management across environments.

Conclusion#

Integrating Flyway with multiple databases in Spring Boot requires manual configuration, but it ensures reliable schema evolution across all your data sources. By following best practices like separating migration directories, testing migrations, and using baselines, you can avoid common pitfalls and maintain a consistent database schema.

References#

  1. Flyway Official Documentation: Flyway Docs
  2. Spring Boot Multi-DataSource Guide: Spring Boot Docs
  3. Testcontainers for Spring Boot: Testcontainers Docs
  4. Flyway Teams Features: Flyway Teams
2026-06