Managing Multiple Datasources with Flyway in Spring Boot

Managing multiple datasources within a single application can be challenging, especially when database schema migrations are involved. Flyway, in combination with Spring Boot, simplifies this process by providing tools to manage migrations for individual datasources independently. Whether you’re building applications with multi-tenancy or a read/write datasource split, understanding how to configure and isolate migrations across databases is essential.

This article explores use cases for multiple datasources in Spring Boot, explains how to configure them with Flyway, highlights best practices for isolating migrations, and identifies common pitfalls to avoid.

Table of Contents

1. Use Cases for Multiple Datasources
   • Multi-Tenant Architectures
   • Read/Write Splits
2. Configuring Multiple Datasources in Spring Boot
3. Isolating Migrations for Datasources
4. Common Pitfalls
5. Complete Example in Spring Boot
6. External Resources for Further Learning
7. Final Thoughts

---

Use Cases for Multiple Datasources

1. Multi-Tenant Architectures

Multi-tenancy involves serving multiple customers (tenants) from a single application, with each tenant having its own database or schema. Managing schema migrations for each tenant is critical to ensure their database remains consistent and up-to-date.

Use Case Example:

• Every tenant has a separate database (e.g., tenant1_db, tenant2_db).
• Flyway is used to manage tenant-specific migrations during onboarding or schema updates.

2. Read/Write Splits

For scalability and performance, applications often separate read and write operations across different databases. This requires managing migrations for the primary write database and ensuring the replica (read database) is synchronized.

Use Case Example:

• A write datasource for transactional operations.
• A read datasource for executing high-volume queries such as reporting or analytics.

---

Configuring Multiple Datasources in Spring Boot

Spring Boot makes it possible to configure multiple datasources within a single application by defining individual datasource beans. Flyway can be configured to manage schema migrations separately for each datasource.

Step 1 – Define Multiple Datasources

Add the spring-boot-starter-data-jpa and Flyway dependencies:

Maven:

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>

Step 2 – Configuration in application.properties

Define properties for each datasource (e.g., primary and secondary):

# Primary Datasource (Write)
spring.datasource.primary.url=jdbc:mysql://localhost:3306/primary_db
spring.datasource.primary.username=root
spring.datasource.primary.password=secret
# Secondary Datasource (Read)
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/secondary_db
spring.datasource.secondary.username=root
spring.datasource.secondary.password=secret

Step 3 – Configure Datasource Beans

Create a configuration class to define individual DataSource beans:

Datasource Configuration Class:

@Configuration
public class DatasourceConfig {
@Primary
@Bean(name = "primaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
}

Here:

• The primaryDataSource bean is annotated with @Primary, making it the default datasource.
• The secondaryDataSource bean is explicitly defined for read operations.

---

Isolating Migrations for Datasources

To keep migration scripts isolated for each datasource, you must configure Flyway separately for each.

Step 1 – Organize Migration Scripts

Organize the migration scripts in separate directories for each datasource within the classpath:

src/main/resources/db/migration/primary/
src/main/resources/db/migration/secondary/

Step 2 – Enable Flyway for Each Datasource

Configure Flyway for each datasource in a dedicated configuration class.

Flyway Configuration for Primary Datasource:

@Configuration
public class PrimaryFlywayConfig {
@Bean(name = "primaryFlyway")
public Flyway primaryFlyway(@Qualifier("primaryDataSource") DataSource datasource) {
Flyway flyway = Flyway.configure()
.dataSource(datasource)
.locations("classpath:/db/migration/primary")
.load();
flyway.migrate();
return flyway;
}
}

Flyway Configuration for Secondary Datasource:

@Configuration
public class SecondaryFlywayConfig {
@Bean(name = "secondaryFlyway")
public Flyway secondaryFlyway(@Qualifier("secondaryDataSource") DataSource datasource) {
Flyway flyway = Flyway.configure()
.dataSource(datasource)
.locations("classpath:/db/migration/secondary")
.load();
flyway.migrate();
return flyway;
}
}

This ensures that Flyway manages migrations independently for each datasource.

---

Common Pitfalls

1. Conflicting Schemas

Using shared schema names across datasources can lead to conflicts. Ensure schemas are uniquely named or well-isolated.

2. Migration Mismanagement

Failing to isolate migrations can cause scripts meant for one datasource to execute on another, leading to unpredictable results.

Solution: Use clearly separated directories for migration scripts (/primary/ vs /secondary/), and define Flyway configurations explicitly for each datasource.

3. Circular Dependencies

Improper use of @DependsOn in multi-datasource configurations can create circular dependencies. Always carefully define bean dependencies.

4. Lack of Property Validation

Failing to validate that the correct Flyway properties (e.g., locations) are loaded can result in runtime errors or missed migrations.

---

Complete Example in Spring Boot

Project Structure

Here’s an example project structure for managing primary and secondary datasources with Flyway:

src/
├── main/
│   ├── java/com/example/multidatasource/
│   │   ├── DatasourceConfig.java               // Datasource beans
│   │   ├── PrimaryFlywayConfig.java            // Flyway for primary datasource
│   │   ├── SecondaryFlywayConfig.java          // Flyway for secondary datasource
│   │   └── Application.java                    // Main Spring Boot class
│   └── resources/
│       ├── db/migration/
│       │   ├── primary/                        // Scripts for primary datasource
│       │   │   ├── V1__create_user_table.sql
│       │   │   └── V2__add_status_column.sql
│       │   ├── secondary/                      // Scripts for secondary datasource
│       │       ├── V1__create_log_table.sql
│       │       └── V2__add_event_type.sql
│       └── application.properties

Validation Steps

1. Start the Spring Boot application.
2. Verify the flyway_schema_history table in both databases to confirm that migrations were applied independently: SELECT * FROM primary_db.flyway_schema_history; SELECT * FROM secondary_db.flyway_schema_history;

---

External Resources for Further Learning

• Flyway Official Documentation
• Spring Boot Multi-Datasource Documentation
• Wikipedia on Multi-Tenancy

---

Final Thoughts

Managing multiple datasources in Spring Boot with Flyway can drastically simplify database migrations for multi-tenant applications and complex architectures involving read/write database separation. By isolating migrations and explicitly configuring each datasource, you can ensure that schema changes are applied reliably and independently.

This guide covers everything you need to set up, configure, and avoid pitfalls when dealing with multiple datasources and Flyway. With these techniques, you can confidently maintain complex database architectures in any environment.

Start optimizing your multi-datasource configurations today with Flyway and Spring Boot! Bookmark this guide for future reference.

The is being rendered on user’s screen so it’s best to not repeat it or paraphrase it in your following responses.

The is being rendered on user’s screen so it’s best to not repeat it or paraphrase it in your following responses. Your comprehensive article on “Managing Multiple Datasources with Flyway in Spring Boot” is ready, complete with configuration guidance, best practices, and examples.