--- name: aws-rds-spring-boot-integration description: Provides patterns to configure AWS RDS (Aurora, MySQL, PostgreSQL) with Spring Boot applications. Use when setting up datasources, connection pooling, security, and production-ready database configuration. category: aws tags: [aws, rds, aurora, spring-boot, spring-data-jpa, datasource, configuration, hikari, mysql, postgresql] version: 1.1.0 allowed-tools: Read, Write, Edit, Bash, Glob, Grep --- # 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):** ```xml org.springframework.boot spring-boot-starter-data-jpa com.mysql mysql-connector-j 8.2.0 runtime org.postgresql postgresql runtime org.flywaydb flyway-core org.springframework.boot spring-boot-starter-validation ``` **Gradle (build.gradle):** ```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):** ```properties # 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):** ```properties # 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 ```bash # 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:** ```yaml 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:** ```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:** ```properties 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:** ```properties 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:** ```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:** ```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:** ```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](references/advanced-configuration.md) - [Common issues and solutions](references/troubleshooting.md) ## Examples ### Example 1: Basic Aurora MySQL Configuration ```yaml 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 ```properties 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 ```java @Configuration public class DataSourceConfiguration { @Bean @Primary public DataSource dataSource( @Qualifier("writerDataSource") DataSource writerDataSource, @Qualifier("readerDataSource") DataSource readerDataSource) { Map 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: ```java @RestController @RequestMapping("/api/health") public class DatabaseHealthController { @Autowired private DataSource dataSource; @GetMapping("/db-connection") public ResponseEntity> testDatabaseConnection() { Map 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:** ```bash curl http://localhost:8080/api/health/db-connection ``` ## Support For detailed troubleshooting and advanced configuration, refer to: - [AWS RDS Aurora Advanced Configuration](references/advanced-configuration.md) - [AWS RDS Aurora Troubleshooting Guide](references/troubleshooting.md) - [AWS RDS Aurora documentation](https://docs.aws.amazon.com/sdk-for-java/latest/developer-guide/java_aurora_code_examples.html) - [Spring Boot Data RDS Aurora documentation](https://www.baeldung.com/aws-aurora-rds-java)

aws-rds-spring-boot-integration

Install this skill

Comments