Managing multiple databases in a Spring Boot application can be challenging, especially when you want to maintain database migrations consistently. Flyway provides an elegant solution to version control your database schemas and ensure smooth migrations. Let us delve into understanding how we can use Flyway & Spring Boot to migrate multiple databases and how they can be efficiently managed in an application.
1. What is Flyway?
Flyway is an open-source database migration tool that helps manage and version control schema changes in a consistent and automated way. It supports multiple databases, including PostgreSQL, H2, MySQL, and many others. Flyway uses a simple folder-based structure where SQL scripts are stored in a db/migration directory. Each script follows a naming convention like V1__Initial_Setup.sql, V2__Add_Column.sql, etc., ensuring that changes are applied in order.
In a Spring Boot application, Flyway integrates seamlessly to run database migrations automatically during application startup. This eliminates manual intervention, reduces errors, and ensures that all environments development, staging, production) stay synchronized with the latest database schema changes.
2. Setting up Database
This section demonstrates how to configure a PostgreSQL container using Docker Compose. Feel free to change the database name, username, and password values as per your environment or security requirements.
version: '3.8'
services:
postgres-secondary:
image: postgres:15
container_name: postgres-secondary
environment:
POSTGRES_DB: secondarydb
POSTGRES_USER: admin
POSTGRES_PASSWORD: password
ports:
- "5432:5432"
volumes:
- postgres_secondary_data:/var/lib/postgresql/data
volumes:
postgres_secondary_data:
The above Docker Compose configuration defines a PostgreSQL service named postgres-secondary using the official postgres:15 image. It sets up environment variables for database name, username, and password, maps the container’s PostgreSQL port 5432 to the host, and attaches a named volume postgres_secondary_data to persist data across container restarts. Ensure that you modify sensitive values like POSTGRES_PASSWORD to meet your security guidelines.
3. Code Example
3.1 Maven Configuration (pom.xml)
This section adds essential Spring Boot, Flyway, and database dependencies to the pom.xml file.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-database-postgresql</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
The above configuration includes dependencies for Spring Boot’s JPA and Web modules, Flyway for database migrations, PostgreSQL as the primary database driver, and H2 for in-memory database testing. These dependencies enable seamless database setup and migration handling in a Spring Boot application.
3.2 Configuration for Multiple Databases
This section provides the basic application properties required for working with multiple databases in a Spring Boot project.
spring.application.name=demo-multipledatabases # JPA Configuration spring.jpa.hibernate.ddl-auto=none spring.jpa.show-sql=true # H2 Console (for development/debugging) spring.h2.console.enabled=true spring.h2.console.path=/h2-console
The above configuration sets the Spring Boot application name, disables automatic schema generation (ddl-auto=none), and enables SQL statement logging. It also activates the H2 database console for debugging and sets its access path to /h2-console, which is useful when working with an in-memory database during development.
3.3 Datasource Configuration Classes
3.3.1 Creating Primary Database Configuration
This section defines the configuration for connecting Spring Boot to the primary database using H2 and Flyway for migrations. Feel free to update database URL, username, and password as per your environment needs.
package com.demo.config;
import com.zaxxer.hikari.HikariDataSource;
import jakarta.persistence.EntityManagerFactory;
import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "primaryEntityManagerFactory",
transactionManagerRef = "primaryTransactionManager",
basePackages = {"com.demo.repository.primary"}
)
public class PrimaryDatabaseConfig {
@Primary
@Bean(name = "primaryDataSource")
public DataSource primaryDataSource() {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl("jdbc:h2:mem:primarydb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE");
dataSource.setUsername("sa");
dataSource.setPassword("");
dataSource.setDriverClassName("org.h2.Driver");
return dataSource;
}
@Primary
@Bean(name = "primaryEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
@Qualifier("primaryDataSource") DataSource dataSource) {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan("com.demo.entity.primary");
em.setPersistenceUnitName("primary");
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
Map<String, Object> properties = new HashMap<>();
properties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
properties.put("hibernate.hbm2ddl.auto", "none");
properties.put("hibernate.show_sql", true);
em.setJpaPropertyMap(properties);
return em;
}
@Primary
@Bean(name = "primaryTransactionManager")
public PlatformTransactionManager primaryTransactionManager(
@Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory) {
return new JpaTransactionManager(primaryEntityManagerFactory);
}
@Primary
@Bean(name = "primaryFlyway")
public Flyway primaryFlyway(@Qualifier("primaryDataSource") DataSource dataSource) {
return Flyway.configure()
.dataSource(dataSource)
.locations("classpath:db/migration/primary")
.baselineOnMigrate(true)
.load();
}
}
This configuration class defines beans for the primary database connection using HikariCP, sets up the entity manager and transaction manager for handling JPA operations, and configures Flyway to manage database migrations from the classpath:db/migration/primary location. The use of @Primary ensures that these beans take precedence when multiple data sources are present. Update database connection properties if needed for different environments.
3.3.2 Creating Secondary Database Configuration
This section sets up the secondary database connection using PostgreSQL and Flyway for schema migrations. Ensure that the username, password, and database URL are updated to match your environment configuration.
package com.demo.config;
import com.zaxxer.hikari.HikariDataSource;
import jakarta.persistence.EntityManagerFactory;
import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Qualifier;
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 org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "secondaryEntityManagerFactory",
transactionManagerRef = "secondaryTransactionManager",
basePackages = {"com.demo.repository.secondary"}
)
public class SecondaryDatabaseConfig {
@Bean(name = "secondaryDataSource")
public DataSource secondaryDataSource() {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl("jdbc:postgresql://localhost:5432/secondarydb");
dataSource.setUsername("postgres");
dataSource.setPassword("postgres");
dataSource.setDriverClassName("org.postgresql.Driver");
return dataSource;
}
@Bean(name = "secondaryEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
@Qualifier("secondaryDataSource") DataSource dataSource) {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan("com.demo.entity.secondary");
em.setPersistenceUnitName("secondary");
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
Map<String, Object> properties = new HashMap<>();
properties.put("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
properties.put("hibernate.hbm2ddl.auto", "none");
properties.put("hibernate.show_sql", true);
em.setJpaPropertyMap(properties);
return em;
}
@Bean(name = "secondaryTransactionManager")
public PlatformTransactionManager secondaryTransactionManager(
@Qualifier("secondaryEntityManagerFactory") EntityManagerFactory secondaryEntityManagerFactory) {
return new JpaTransactionManager(secondaryEntityManagerFactory);
}
@Bean(name = "secondaryFlyway")
public Flyway secondaryFlyway(@Qualifier("secondaryDataSource") DataSource dataSource) {
return Flyway.configure()
.dataSource(dataSource)
.locations("classpath:db/migration/secondary")
.baselineOnMigrate(true)
.load();
}
}
This configuration connects the application to a PostgreSQL database for secondary operations, defines a separate EntityManagerFactory and TransactionManager, and uses Flyway to manage schema migrations from classpath:db/migration/secondary. It ensures isolation of JPA operations between primary and secondary data sources.
3.3.3 Creating Migration Runner
This section defines a runner that triggers Flyway migrations for both primary and secondary databases during application startup.
package com.demo.config;
import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Component
@Order(1) // Run before DataInitializer
public class FlywayMigrationRunner implements CommandLineRunner {
private final Flyway primaryFlyway;
private final Flyway secondaryFlyway;
public FlywayMigrationRunner(@Qualifier("primaryFlyway") Flyway primaryFlyway,
@Qualifier("secondaryFlyway") Flyway secondaryFlyway) {
this.primaryFlyway = primaryFlyway;
this.secondaryFlyway = secondaryFlyway;
}
@Override
public void run(String... args) throws Exception {
System.out.println("Running database migrations...");
System.out.println("Migrating primary database...");
primaryFlyway.migrate();
System.out.println("Migrating secondary database...");
secondaryFlyway.migrate();
System.out.println("Database migrations completed successfully!");
}
}
This class implements CommandLineRunner to execute Flyway migrations when the application starts. It runs both the primary and secondary database migrations in sequence and ensures that schema updates are applied before other initialization tasks. The @Order(1) annotation guarantees that this runner executes early in the application startup process.
3.4 Flyway Configuration
This section contains the SQL migration scripts that Flyway uses to initialize database schemas for both primary and secondary data sources.
3.4.1 Create Primary Datasource SQL Script
The following script creates the users table for the primary database.
-- src/main/resources/db/migration/primary/V1__Create_users_table.sql
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This migration script defines a users table with unique constraints on username and email, along with timestamps for tracking creation and updates. Flyway automatically executes it when the application starts.
3.4.2 Create Secondary Datasource SQL Script
The following script creates the products table for the secondary database.
-- src/main/resources/db/migration/secondary/V1__Create_products_table.sql
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
category VARCHAR(50) NOT NULL,
stock_quantity INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This migration script initializes the products table with fields for product details, pricing, stock quantity, and timestamps. Flyway applies it to the secondary database during startup.
3.5 Code Run and Output
When you start the Spring Boot application, Flyway will automatically detect the migrations and apply them to the respective databases. You can verify this in the application logs, which will show migration details for both primary and secondary databases.
2025-09-02T12:38:26.149+05:30 INFO 35473 --- [demo-multipledatabases] [ main] c.demo.DemoMultipledatabasesApplication : Started DemoMultipledatabasesApplication in 2.047 seconds (process running for 2.366) Running database migrations... Migrating primary database... 2025-09-02T12:38:26.171+05:30 INFO 35473 --- [demo-multipledatabases] [ main] org.flywaydb.core.FlywayExecutor : Database: jdbc:h2:mem:primarydb (H2 2.3) 2025-09-02T12:38:26.181+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.c.i.s.JdbcTableSchemaHistory : Schema history table "PUBLIC"."flyway_schema_history" does not exist yet 2025-09-02T12:38:26.182+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.core.internal.command.DbValidate : Successfully validated 1 migration (execution time 00:00.005s) 2025-09-02T12:38:26.185+05:30 INFO 35473 --- [demo-multipledatabases] [ main] org.flywaydb.core.Flyway : All configured schemas are empty; baseline operation skipped. A baseline or migration script with a lower version than the baseline version may execute if available. Check the Schemas parameter if this is not intended. 2025-09-02T12:38:26.185+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.c.i.s.JdbcTableSchemaHistory : Creating Schema History table "PUBLIC"."flyway_schema_history" ... 2025-09-02T12:38:26.198+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.core.internal.command.DbMigrate : Current version of schema "PUBLIC": <> 2025-09-02T12:38:26.200+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.core.internal.command.DbMigrate : Migrating schema "PUBLIC" to version "1 - Create users table" 2025-09-02T12:38:26.207+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.core.internal.command.DbMigrate : Successfully applied 1 migration to schema "PUBLIC", now at version v1 (execution time 00:00.002s) Migrating secondary database... 2025-09-02T12:38:26.212+05:30 INFO 35473 --- [demo-multipledatabases] [ main] org.flywaydb.core.FlywayExecutor : Database: jdbc:postgresql://localhost:5432/secondarydb (PostgreSQL 17.5) 2025-09-02T12:38:26.233+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.core.internal.command.DbValidate : Successfully validated 1 migration (execution time 00:00.009s) 2025-09-02T12:38:26.243+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.core.internal.command.DbMigrate : Current version of schema "public": 1 2025-09-02T12:38:26.244+05:30 INFO 35473 --- [demo-multipledatabases] [ main] o.f.core.internal.command.DbMigrate : Schema "public" is up to date. No migration necessary. Database migrations completed successfully!
These logs confirm that Flyway detected the migration scripts located under classpath:db/migration/primary and classpath:db/migration/secondary and applied them successfully.
4. Conclusion
Spring Boot with Flyway makes it simple to manage multiple databases with proper version control for schema migrations. By configuring separate datasources and migration paths, you can maintain independent database schemas, reduce conflicts, and ensure reliable deployments. This setup is especially useful in microservices or applications with modular databases.
