Aws Rds Spring Boot Integration

AWS RDS Spring Boot Integration

Overview

Configure AWS RDS databases (Aurora, MySQL, PostgreSQL) with Spring Boot applications. Provides patterns for datasource configuration, HikariCP connection pooling, SSL connections, environment-specific configurations, and AWS Secrets Manager integration.

When to Use

Use when configuring HikariCP connection pools for RDS workloads, implementing read/write split with Aurora replicas, setting up IAM database authentication, enabling SSL/TLS connections, managing database migrations with Flyway, or troubleshooting RDS connectivity issues.

Instructions

Follow these steps to configure AWS RDS with Spring Boot:

1. Add Dependencies — Include Spring Data JPA, database driver (MySQL/PostgreSQL), and Flyway 2. Configure Datasource — Set connection properties in application.yml 3. Configure HikariCP — Optimize pool settings for your RDS workload 4. Set Up SSL — Enable encrypted connections to RDS 5. Configure Profiles — Set environment-specific configurations (dev/prod) 6. Add Migrations — Create Flyway scripts for schema management 7. Validate Connectivity — Run health check to verify database connection

If validation fails: Check security group rules, verify credentials, ensure RDS is accessible from your network, and confirm SSL certificate configuration.

8. Run Migrations — Apply Flyway migrations only after connectivity validation passes

Quick Start

Step 1: Add Dependencies

Maven (pom.xml):

<dependencies>
    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- Aurora MySQL Driver -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.2.0</version>
        <scope>runtime</scope>
    </dependency>

    <!-- Aurora PostgreSQL Driver (alternative) -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- Flyway for database migrations -->
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>

    <!-- Validation -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-validation</artifactId>
    </dependency>
</dependencies>

Gradle (build.gradle):

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-validation'

    // Aurora MySQL
    runtimeOnly 'com.mysql:mysql-connector-j:8.2.0'

    // Aurora PostgreSQL (alternative)
    runtimeOnly 'org.postgresql:postgresql'

    // Flyway
    implementation 'org.flywaydb:flyway-core'
}

Step 2: Basic Datasource Configuration

Use the configuration in the Examples section below. For PostgreSQL, change:

• Driver: org.postgresql.Driver
• URL: jdbc:postgresql://... with ?ssl=true&sslmode=require
• Dialect: org.hibernate.dialect.PostgreSQLDialect

Step 3: Set Up Environment Variables

# Production environment variables
export DB_PASSWORD=YourStrongPassword123!
export SPRING_PROFILES_ACTIVE=prod

# For development
export SPRING_PROFILES_ACTIVE=dev

Database Migration Setup

Create migration files for Flyway:

src/main/resources/db/migration/
├── V1__create_users_table.sql
├── V2__add_phone_column.sql
└── V3__create_orders_table.sql

V1__create_users_table.sql:

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Examples

Example 1: Aurora MySQL Configuration

spring:
  datasource:
    url: jdbc:mysql://myapp-aurora-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com:3306/devops
    username: admin
    password: ${DB_PASSWORD}
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 20000
  jpa:
    hibernate:
      ddl-auto: validate
    open-in-view: false

Example 2: Aurora PostgreSQL with SSL

spring.datasource.url=jdbc:postgresql://myapp-aurora-pg-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com:5432/devops?ssl=true&sslmode=require
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}
spring.datasource.hikari.maximum-pool-size=30
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

Example 3: Read/Write Split Configuration

@Configuration
public class DataSourceConfiguration {

    @Bean
    @Primary
    public DataSource dataSource(
            @Qualifier("writerDataSource") DataSource writerDataSource,
            @Qualifier("readerDataSource") DataSource readerDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("writer", writerDataSource);
        targetDataSources.put("reader", readerDataSource);

        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(writerDataSource);

        return routingDataSource;
    }
}

Constraints and Warnings

• HikariCP pool size must respect RDS instance connection limits
• Security groups must allow traffic from your application's IP range
• Use AWS Secrets Manager instead of hardcoding credentials
• Enable storage autoscaling to prevent storage exhaustion

Best Practices

• HikariCP: Enable leak detection and configure timeouts for failover scenarios
• Security: Enable SSL/TLS; use IAM Database Authentication when possible
• Performance: Disable open-in-view; use appropriate indexing and batch operations
• Monitoring: Enable Spring Boot Actuator with database health checks

Testing

Verify connectivity with this health check endpoint:

@RestController
@RequestMapping("/api/health")
public class DatabaseHealthController {
    @Autowired
    private DataSource dataSource;

    @GetMapping("/db-connection")
    public ResponseEntity<Map<String, Object>> testDatabaseConnection() {
        Map<String, Object> response = new HashMap<>();
        try (Connection connection = dataSource.getConnection()) {
            response.put("status", "success");
            response.put("database", connection.getCatalog());
            response.put("connected", true);
            return ResponseEntity.ok(response);
        } catch (Exception e) {
            response.put("status", "failed");
            response.put("error", e.getMessage());
            response.put("connected", false);
            return ResponseEntity.status(HttpStatus.SERVICE_UNAVAILABLE).body(response);
        }
    }
}

curl http://localhost:8080/api/health/db-connection

Support

For detailed troubleshooting and advanced configuration, refer to:

• AWS RDS Aurora Advanced Configuration
• AWS RDS Aurora Troubleshooting Guide
• AWS RDS Aurora documentation
• Spring Boot Data RDS Aurora documentation

AWS RDS Aurora Advanced Configuration

Read/Write Split Configuration

For applications with heavy read operations, configure separate datasources:

Multi-Datasource Configuration Class:

@Configuration
public class AuroraDataSourceConfig {

    @Primary
    @Bean(name = "writerDataSource")
    @ConfigurationProperties("spring.datasource.writer")
    public DataSource writerDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "readerDataSource")
    @ConfigurationProperties("spring.datasource.reader")
    public DataSource readerDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "writerEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean writerEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("writerDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.domain")
                .persistenceUnit("writer")
                .build();
    }

    @Bean(name = "readerEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean readerEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("readerDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.domain")
                .persistenceUnit("reader")
                .build();
    }

    @Primary
    @Bean(name = "writerTransactionManager")
    public PlatformTransactionManager writerTransactionManager(
            @Qualifier("writerEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

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

Usage in Repository:

@Repository
public interface UserReadRepository extends JpaRepository<User, Long> {
    // Read operations automatically use reader endpoint
}

@Repository
public interface UserWriteRepository extends JpaRepository<User, Long> {
    // Write operations use writer endpoint
}

SSL/TLS Configuration

Enable SSL for secure connections to Aurora:

Aurora MySQL with SSL:

spring.datasource.url=jdbc:mysql://myapp-aurora-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com:3306/devops?useSSL=true&requireSSL=true&verifyServerCertificate=true

Aurora PostgreSQL with SSL:

spring.datasource.url=jdbc:postgresql://myapp-aurora-pg-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com:5432/devops?ssl=true&sslmode=require

Download RDS Certificate:

# Download RDS CA certificate
wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

# Configure in application
spring.datasource.url=jdbc:mysql://...?useSSL=true&trustCertificateKeyStoreUrl=file:///path/to/global-bundle.pem

AWS Secrets Manager Integration

Add AWS SDK Dependency:

<dependency>
    <groupId>software.amazon.awssdk</groupId>
    <artifactId>secretsmanager</artifactId>
    <version>2.20.0</version>
</dependency>

Secrets Manager Configuration:

@Configuration
public class AuroraDataSourceConfig {

    @Value("${aws.secretsmanager.secret-name}")
    private String secretName;

    @Value("${aws.region}")
    private String region;

    @Bean
    public DataSource dataSource() {
        Map<String, String> credentials = getAuroraCredentials();

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(credentials.get("url"));
        config.setUsername(credentials.get("username"));
        config.setPassword(credentials.get("password"));
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(20000);

        return new HikariDataSource(config);
    }

    private Map<String, String> getAuroraCredentials() {
        SecretsManagerClient client = SecretsManagerClient.builder()
            .region(Region.of(region))
            .build();

        GetSecretValueRequest request = GetSecretValueRequest.builder()
            .secretId(secretName)
            .build();

        GetSecretValueResponse response = client.getSecretValue(request);
        String secretString = response.secretString();

        // Parse JSON secret
        ObjectMapper mapper = new ObjectMapper();
        try {
            return mapper.readValue(secretString, Map.class);
        } catch (Exception e) {
            throw new RuntimeException("Failed to parse secret", e);
        }
    }
}

application.properties (Secrets Manager):

aws.secretsmanager.secret-name=prod/aurora/credentials
aws.region=us-east-1

Database Migration with Flyway

Setup Flyway

Create Migration Directory:

src/main/resources/db/migration/
├── V1__create_users_table.sql
├── V2__add_phone_column.sql
└── V3__create_orders_table.sql

V1__create_users_table.sql:

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

V2__add_phone_column.sql:

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

Flyway Configuration:

spring.jpa.hibernate.ddl-auto=validate
spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true
spring.flyway.locations=classpath:db/migration
spring.flyway.validate-on-migrate=true

Connection Pool Optimization for Aurora

Recommended HikariCP Settings:

# Aurora-optimized connection pool
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.leak-detection-threshold=60000
spring.datasource.hikari.connection-test-query=SELECT 1

Formula for Pool Size:

connections = ((core_count * 2) + effective_spindle_count)
For Aurora: Use 20-30 connections per application instance

Failover Handling

Aurora automatically handles failover between instances. Configure connection retry:

# Connection retry configuration
spring.datasource.hikari.connection-timeout=30000
spring.datasource.url=jdbc:mysql://cluster-endpoint:3306/db?failOverReadOnly=false&maxReconnects=3&connectTimeout=30000

Read Replica Load Balancing

Use reader endpoint for distributing read traffic across replicas:

# Reader endpoint for read-heavy workloads
spring.datasource.reader.url=jdbc:mysql://cluster-ro-endpoint:3306/db

Performance Optimization

Enable batch operations:

spring.jpa.properties.hibernate.jdbc.batch_size=20
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true

Disable open-in-view pattern:

spring.jpa.open-in-view=false

Production logging configuration:

# Disable SQL logging in production
logging.level.org.hibernate.SQL=WARN
logging.level.org.springframework.jdbc=WARN

# Enable HikariCP metrics
logging.level.com.zaxxer.hikari=INFO
logging.level.com.zaxxer.hikari.pool=DEBUG

Enable Spring Boot Actuator for metrics:

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

management.endpoints.web.exposure.include=health,metrics,info
management.endpoint.health.show-details=always

AWS RDS Aurora Troubleshooting Guide

Common Issues and Solutions

Connection Timeout to Aurora Cluster

Error: Communications link failure or Connection timed out

Solutions:

• Verify security group inbound rules allow traffic on port 3306 (MySQL) or 5432 (PostgreSQL)
• Check Aurora cluster endpoint is correct (cluster vs instance endpoint)
• Ensure your IP/CIDR is whitelisted in security group
• Verify VPC and subnet configuration
• Check if Aurora cluster is in the same VPC or VPC peering is configured

# Test connection from EC2/local machine
telnet myapp-aurora-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com 3306

Access Denied for User

Error: Access denied for user 'admin'@'...'

Solutions:

• Verify master username and password are correct
• Check if IAM authentication is required but not configured
• Reset master password in Aurora console if needed
• Verify user permissions in database

-- Check user permissions
SHOW GRANTS FOR 'admin'@'%';

Database Not Found

Error: Unknown database 'devops'

Solutions:

• Verify initial database name was created with cluster
• Create database manually using MySQL/PostgreSQL client
• Check database name in JDBC URL matches existing database

-- Connect to Aurora and create database
CREATE DATABASE devops CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SSL Connection Issues

Error: SSL connection error or Certificate validation failed

Solutions:

# Option 1: Disable SSL verification (NOT recommended for production)
spring.datasource.url=jdbc:mysql://...?useSSL=false

# Option 2: Properly configure SSL with RDS certificate
spring.datasource.url=jdbc:mysql://...?useSSL=true&requireSSL=true&verifyServerCertificate=true&trustCertificateKeyStoreUrl=file:///path/to/global-bundle.pem

# Option 3: Trust all certificates (NOT recommended for production)
spring.datasource.url=jdbc:mysql://...?useSSL=true&requireSSL=true&verifyServerCertificate=false

Too Many Connections

Error: Too many connections or Connection pool exhausted

Solutions:

• Review Aurora instance max_connections parameter
• Optimize HikariCP pool size
• Check for connection leaks in application code

# Reduce pool size
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.minimum-idle=5

# Enable leak detection
spring.datasource.hikari.leak-detection-threshold=60000

Check Aurora max_connections:

SHOW VARIABLES LIKE 'max_connections';
-- Default for Aurora: depends on instance class
-- db.r6g.large: ~1000 connections

Slow Query Performance

Error: Queries taking longer than expected

Solutions:

• Enable slow query log in Aurora parameter group
• Review connection pool settings
• Check Aurora instance metrics in CloudWatch
• Optimize queries and add indexes

# Enable query logging (development only)
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Failover Delays

Error: Application freezes during Aurora failover

Solutions:

• Configure connection timeout appropriately
• Use cluster endpoint (not instance endpoint)
• Implement connection retry logic

spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.validation-timeout=5000
spring.datasource.url=jdbc:mysql://...?failOverReadOnly=false&maxReconnects=3

Testing Aurora Connection

Connection Test with Spring Boot Application

Create a Simple Test Endpoint:

@RestController
@RequestMapping("/api/health")
public class DatabaseHealthController {

    @Autowired
    private DataSource dataSource;

    @GetMapping("/db-connection")
    public ResponseEntity<Map<String, Object>> testDatabaseConnection() {
        Map<String, Object> response = new HashMap<>();

        try (Connection connection = dataSource.getConnection()) {
            response.put("status", "success");
            response.put("database", connection.getCatalog());
            response.put("url", connection.getMetaData().getURL());
            response.put("connected", true);
            return ResponseEntity.ok(response);
        } catch (Exception e) {
            response.put("status", "failed");
            response.put("error", e.getMessage());
            response.put("connected", false);
            return ResponseEntity.status(HttpStatus.SERVICE_UNAVAILABLE).body(response);
        }
    }
}

Test with cURL:

curl http://localhost:8080/api/health/db-connection

Verify Aurora Connection with MySQL/PostgreSQL Client

MySQL Client Connection:

# Connect to Aurora MySQL cluster
mysql -h myapp-aurora-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com \
      -P 3306 \
      -u admin \
      -p devops

# Verify connection
SHOW DATABASES;
SELECT @@version;
SHOW VARIABLES LIKE 'aurora_version';

PostgreSQL Client Connection:

# Connect to Aurora PostgreSQL
psql -h myapp-aurora-pg-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com \
     -p 5432 \
     -U admin \
     -d devops

# Verify connection
\l
SELECT version();