Aws cloudformation task ecs deploy gh

AWS RDS Spring Boot Integration

Overview

Configure AWS RDS databases (Aurora, MySQL, PostgreSQL) with Spring Boot applications for production-ready connectivity. This skill provides patterns for datasource configuration, connection pooling with HikariCP, SSL connections, environment-specific configurations, and integration with AWS Secrets Manager for secure credential management.

When to Use This Skill

Use this skill when:

• Setting up AWS RDS Aurora with Spring Data JPA
• Configuring datasource properties for Aurora, MySQL, or PostgreSQL endpoints
• Implementing HikariCP connection pooling for RDS
• Setting up environment-specific configurations (dev/prod)
• Configuring SSL connections to AWS RDS
• Troubleshooting RDS connection issues
• Setting up database migrations with Flyway
• Integrating with AWS Secrets Manager for credential management
• Optimizing connection pool settings for RDS workloads
• Implementing read/write split with Aurora

Prerequisites

Before starting AWS RDS Spring Boot integration:

1. AWS account with RDS access
2. Spring Boot project (3.x)
3. RDS instance created and running (Aurora/MySQL/PostgreSQL)
4. Security group configured for database access
5. Database endpoint information available
6. Database credentials secured (environment variables or Secrets Manager)

Instructions

Follow these steps to configure AWS RDS with Spring Boot:

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

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

application.properties (Aurora MySQL):

# Aurora MySQL Datasource - Cluster Endpoint
spring.datasource.url=jdbc:mysql://myapp-aurora-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com:3306/devops
spring.datasource.username=admin
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# JPA/Hibernate Configuration
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.open-in-view=false

# HikariCP 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

# Flyway Configuration
spring.flyway.enabled=true
spring.flyway.baseline-on-migrate=true
spring.flyway.locations=classpath:db/migration

application.properties (Aurora PostgreSQL):

# Aurora PostgreSQL Datasource
spring.datasource.url=jdbc:postgresql://myapp-aurora-pg-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com:5432/devops
spring.datasource.username=admin
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=org.postgresql.Driver

# JPA/Hibernate Configuration
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.open-in-view=false

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

Configuration Examples

Simple Aurora Cluster (MySQL)

application.yml:

spring:
  application:
    name: DevOps

  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:
      pool-name: AuroraHikariPool
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 20000
      idle-timeout: 300000
      max-lifetime: 1200000
      leak-detection-threshold: 60000
      connection-test-query: SELECT 1

  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false
    open-in-view: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        format_sql: true
        jdbc:
          batch_size: 20
        order_inserts: true
        order_updates: true

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

logging:
  level:
    org.hibernate.SQL: WARN
    com.zaxxer.hikari: INFO

Read/Write Split Configuration

For read-heavy workloads, use separate writer and reader datasources:

application.properties:

# Aurora MySQL - Writer Endpoint
spring.datasource.writer.jdbc-url=jdbc:mysql://myapp-aurora-cluster.cluster-abc123xyz.us-east-1.rds.amazonaws.com:3306/devops
spring.datasource.writer.username=admin
spring.datasource.writer.password=${DB_PASSWORD}
spring.datasource.writer.driver-class-name=com.mysql.cj.jdbc.Driver

# Aurora MySQL - Reader Endpoint (Read Replicas)
spring.datasource.reader.jdbc-url=jdbc:mysql://myapp-aurora-cluster.cluster-ro-abc123xyz.us-east-1.rds.amazonaws.com:3306/devops
spring.datasource.reader.username=admin
spring.datasource.reader.password=${DB_PASSWORD}
spring.datasource.reader.driver-class-name=com.mysql.cj.jdbc.Driver

# HikariCP for Writer
spring.datasource.writer.hikari.maximum-pool-size=15
spring.datasource.writer.hikari.minimum-idle=5

# HikariCP for Reader
spring.datasource.reader.hikari.maximum-pool-size=25
spring.datasource.reader.hikari.minimum-idle=10

SSL Configuration

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

Environment-Specific Configuration

Development Profile

application-dev.properties:

# Local MySQL for development
spring.datasource.url=jdbc:mysql://localhost:3306/devops_dev
spring.datasource.username=root
spring.datasource.password=root

# Enable DDL auto-update in development
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

# Smaller connection pool for local dev
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.minimum-idle=2

Production Profile

application-prod.properties:

# Aurora Cluster Endpoint (Production)
spring.datasource.url=jdbc:mysql://${AURORA_ENDPOINT}:3306/${DB_NAME}
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}

# Validate schema only in production
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=false
spring.jpa.open-in-view=false

# Production-optimized connection pool
spring.datasource.hikari.maximum-pool-size=30
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000

# Enable Flyway migrations
spring.flyway.enabled=true
spring.flyway.validate-on-migrate=true

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;

Advanced Features

For advanced configuration, see the reference documents:

• Multi-datasource, SSL, Secrets Manager integration
• Common issues and solutions

Examples

Example 1: Basic 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

• Connection Limits: RDS instances have maximum connection limits; configure pool size accordingly
• Connection Timeout: Set appropriate timeout values to prevent hanging connections
• SSL Certificates: Aurora SSL certificates rotate automatically; ensure your JDBC driver handles this
• Failover Handling: Aurora failover may cause brief connection interruptions; implement retry logic
• Cost Monitoring: Aurora costs scale with instance size and storage; monitor usage regularly
• Security Groups: Ensure security groups allow traffic from your application's IP range
• Credential Rotation: Use AWS Secrets Manager for automated credential rotation
• Multi-AZ Costs: Enabling Multi-AZ increases costs approximately double
• Storage Autoscaling: Enable storage autoscaling to prevent running out of storage space

Best Practices

Connection Pool Optimization

• Use HikariCP with Aurora-optimized settings
• Set appropriate pool sizes based on Aurora instance capacity
• Configure connection timeouts for failover handling
• Enable leak detection

Security Best Practices

• Never hardcode credentials in configuration files
• Use environment variables or AWS Secrets Manager
• Enable SSL/TLS connections
• Configure proper security group rules
• Use IAM Database Authentication when possible

Performance Optimization

• Enable batch operations for bulk data operations
• Disable open-in-view pattern to prevent lazy loading issues
• Use appropriate indexing for Aurora queries
• Configure connection pooling for high availability

Monitoring

• Enable Spring Boot Actuator for database metrics
• Monitor connection pool metrics
• Set up proper logging for debugging
• Configure health checks for database connectivity

Testing

Create a health check endpoint to test database connectivity:

@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

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