Spring Boot Multi-Database Configuration with JPA & Flyway: Seamless MySQL & PostgreSQL Migration Guide

Introduction

In modern enterprise applications, the need to support multiple databases simultaneously is becoming increasingly common. Whether you're migrating from one database to another, supporting different databases for different clients, or implementing a multi-tenant architecture with database-per-tenant, configuring Spring Boot to work with multiple databases is a valuable skill.

This comprehensive guide will walk you through setting up a Spring Boot application with:

Multiple database configurations (MySQL and PostgreSQL)
JPA/Hibernate for each database
Flyway migration support for both databases
Proper transaction management
Best practices for production environments

Prerequisites

Before starting, ensure you have:

Java JDK 8 or later
Maven or Gradle
Spring Boot 2.5.x or later
MySQL and PostgreSQL installed locally or accessible
Basic knowledge of Spring Data JPA

Step 1: Project Setup

Create a new Spring Boot project with the following dependencies:

<dependencies>
    <!-- Spring Boot Starters -->
    <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>
    
    <!-- Database Drivers -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <!-- Flyway -->
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
    
    <!-- Other Utilities -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

Step 2: Database Configuration

Create a configuration class to handle multiple data sources:

@Configuration
public class DatabaseConfig {

    // MySQL Configuration
    @Bean
    @ConfigurationProperties("spring.mysql.datasource")
    public DataSourceProperties mysqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties("spring.mysql.datasource.hikari")
    public DataSource mysqlDataSource() {
        return mysqlDataSourceProperties()
            .initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
    }

    @Bean(name = "mysqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(mysqlDataSource())
            .packages("com.example.multidb.mysql.entities")
            .persistenceUnit("mysql")
            .properties(jpaProperties())
            .build();
    }

    @Bean(name = "mysqlTransactionManager")
    public PlatformTransactionManager mysqlTransactionManager(
            @Qualifier("mysqlEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

    // PostgreSQL Configuration
    @Bean
    @ConfigurationProperties("spring.postgresql.datasource")
    public DataSourceProperties postgresqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "postgresqlDataSource")
    @ConfigurationProperties("spring.postgresql.datasource.hikari")
    public DataSource postgresqlDataSource() {
        return postgresqlDataSourceProperties()
            .initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
    }

    @Bean(name = "postgresqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        return builder
            .dataSource(postgresqlDataSource())
            .packages("com.example.multidb.postgresql.entities")
            .persistenceUnit("postgresql")
            .properties(jpaProperties())
            .build();
    }

    @Bean(name = "postgresqlTransactionManager")
    public PlatformTransactionManager postgresqlTransactionManager(
            @Qualifier("postgresqlEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

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

Step 3: Application Properties

Configure your application.yml (or application.properties):

spring:
  jpa:
    open-in-view: false
    show-sql: true
    properties:
      hibernate:
        format_sql: true

  mysql:
    datasource:
      url: jdbc:mysql://localhost:3306/mysql_db?useSSL=false&serverTimezone=UTC
      username: mysql_user
      password: mysql_password
      driver-class-name: com.mysql.cj.jdbc.Driver
      hikari:
        pool-name: MySQL-Hikari-Pool
        maximum-pool-size: 10
        minimum-idle: 2
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000

  postgresql:
    datasource:
      url: jdbc:postgresql://localhost:5432/postgres_db
      username: postgres_user
      password: postgres_password
      driver-class-name: org.postgresql.Driver
      hikari:
        pool-name: PostgreSQL-Hikari-Pool
        maximum-pool-size: 10
        minimum-idle: 2
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000

flyway:
  mysql:
    enabled: true
    locations: classpath:db/migration/mysql
    baseline-on-migrate: true
  postgresql:
    enabled: true
    locations: classpath:db/migration/postgresql
    baseline-on-migrate: true

Step 4: Flyway Configuration

Create a custom Flyway configuration to handle multiple databases:

@Configuration
public class FlywayConfig {

    @Bean(initMethod = "migrate")
    public Flyway flywayMySQL(@Qualifier("mysqlDataSource") DataSource dataSource) {
        return Flyway.configure()
            .dataSource(dataSource)
            .locations("classpath:db/migration/mysql")
            .baselineOnMigrate(true)
            .load();
    }

    @Bean(initMethod = "migrate")
    public Flyway flywayPostgreSQL(@Qualifier("postgresqlDataSource") DataSource dataSource) {
        return Flyway.configure()
            .dataSource(dataSource)
            .locations("classpath:db/migration/postgresql")
            .baselineOnMigrate(true)
            .load();
    }
}

Step 5: Entity Classes

Create separate packages for each database's entities:

MySQL Entity Example

package com.example.multidb.mysql.entities;

import jakarta.persistence.*;
import lombok.Data;

@Data
@Entity
@Table(name = "users")
public class MySQLUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false)
    private String username;
    
    @Column(nullable = false)
    private String email;
    
    // Other fields, getters, setters
}

PostgreSQL Entity Example

package com.example.multidb.postgresql.entities;

import jakarta.persistence.*;
import lombok.Data;

@Data
@Entity
@Table(name = "customers")
public class PostgreSQLCustomer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false)
    private String firstName;
    
    @Column(nullable = false)
    private String lastName;
    
    // Other fields, getters, setters
}

Step 6: Repository Interfaces

Create separate repositories for each database:

MySQL Repository

package com.example.multidb.mysql.repositories;

import com.example.multidb.mysql.entities.MySQLUser;
import org.springframework.data.jpa.repository.JpaRepository;

public interface MySQLUserRepository extends JpaRepository<MySQLUser, Long> {
    MySQLUser findByUsername(String username);
}

PostgreSQL Repository

package com.example.multidb.postgresql.repositories;

import com.example.multidb.postgresql.entities.PostgreSQLCustomer;
import org.springframework.data.jpa.repository.JpaRepository;

public interface PostgreSQLCustomerRepository extends JpaRepository<PostgreSQLCustomer, Long> {
    PostgreSQLCustomer findByFirstNameAndLastName(String firstName, String lastName);
}

Step 7: Service Layer

Implement services that use the appropriate repositories:

@Service
@Transactional
public class UserService {
    
    private final MySQLUserRepository mysqlUserRepository;
    private final PostgreSQLCustomerRepository postgresqlCustomerRepository;
    
    public UserService(MySQLUserRepository mysqlUserRepository,
                     PostgreSQLCustomerRepository postgresqlCustomerRepository) {
        this.mysqlUserRepository = mysqlUserRepository;
        this.postgresqlCustomerRepository = postgresqlCustomerRepository;
    }
    
    @Transactional("mysqlTransactionManager")
    public MySQLUser createMySQLUser(MySQLUser user) {
        return mysqlUserRepository.save(user);
    }
    
    @Transactional("postgresqlTransactionManager")
    public PostgreSQLCustomer createPostgreSQLCustomer(PostgreSQLCustomer customer) {
        return postgresqlCustomerRepository.save(customer);
    }
    
    // Other service methods
}

Step 8: Flyway Migrations

Create separate migration folders for each database:

MySQL Migration Example

src/main/resources/db/migration/mysql/V1__Create_users_table.sql

CREATE TABLE IF NOT EXISTS users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_username ON users(username);

PostgreSQL Migration Example

src/main/resources/db/migration/postgresql/V1__Create_customers_table.sql

CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_customers_name ON customers(first_name, last_name);

Step 9: Transaction Management

For cross-database transactions, consider using JTA (Java Transaction API) with Atomikos:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>

Then update your configuration:

@Bean(name = "mysqlDataSource")
@ConfigurationProperties("spring.mysql.datasource")
public DataSource mysqlDataSource() {
    return new AtomikosDataSourceBean();
}

@Bean(name = "postgresqlDataSource")
@ConfigurationProperties("spring.postgresql.datasource")
public DataSource postgresqlDataSource() {
    return new AtomikosDataSourceBean();
}

Step 10: Testing

Create integration tests to verify your configuration:

@SpringBootTest
@ActiveProfiles("test")
public class MultiDatabaseIntegrationTest {
    
    @Autowired
    private MySQLUserRepository mysqlUserRepository;
    
    @Autowired
    private PostgreSQLCustomerRepository postgresqlCustomerRepository;
    
    @Test
    @Transactional("mysqlTransactionManager")
    public void testMySQLOperations() {
        MySQLUser user = new MySQLUser();
        user.setUsername("testuser");
        user.setEmail("test@example.com");
        
        mysqlUserRepository.save(user);
        
        Optional<MySQLUser> found = mysqlUserRepository.findByUsername("testuser");
        assertTrue(found.isPresent());
        assertEquals("test@example.com", found.get().getEmail());
    }
    
    @Test
    @Transactional("postgresqlTransactionManager")
    public void testPostgreSQLOperations() {
        PostgreSQLCustomer customer = new PostgreSQLCustomer();
        customer.setFirstName("John");
        customer.setLastName("Doe");
        
        postgresqlCustomerRepository.save(customer);
        
        Optional<PostgreSQLCustomer> found = postgresqlCustomerRepository
            .findByFirstNameAndLastName("John", "Doe");
        assertTrue(found.isPresent());
    }
}

Best Practices

Clear Package Structure: Keep entities, repositories, and services for each database in separate packages.
Transaction Boundaries: Be explicit about transaction managers when working with multiple databases.
Connection Pooling: Use HikariCP or another connection pool with appropriate settings for each database.
Migration Management: Keep Flyway migrations separate and database-specific.
Monitoring: Implement monitoring for both database connections and query performance.
Error Handling: Implement proper error handling for database-specific exceptions.
Testing: Write comprehensive tests for both databases and cross-database operations.

Migration Strategy

When migrating from one database to another:

Dual-Write Pattern: Write to both databases during migration period.
Data Synchronization: Implement a synchronization mechanism.
Feature Flags: Use feature flags to switch between databases.
Validation: Validate data consistency between both databases.
Cutover: Plan a cutover strategy with minimal downtime.

Conclusion

Configuring Spring Boot to work with multiple databases using JPA and Flyway requires careful setup but provides tremendous flexibility. This guide has shown you how to:

Configure multiple data sources with proper connection pooling
Set up separate JPA entity managers and transaction managers
Implement Flyway migrations for each database
Organize your codebase for clarity and maintainability
Handle transactions across multiple databases

By following these patterns, you can build robust applications that work seamlessly with multiple database systems, whether for migration purposes, multi-tenancy, or other architectural requirements.

CODE WITH SIBIN

Solving Real Problems with Real Code