aws-rds-spring-boot-integration

仓库: giuseppe-trisciuoglio/developer-kit
安装量: 315
排名: #2929

安装

npx skills add https://github.com/giuseppe-trisciuoglio/developer-kit --skill aws-rds-spring-boot-integration

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: AWS account with RDS access Spring Boot project (3.x) RDS instance created and running (Aurora/MySQL/PostgreSQL) Security group configured for database access Database endpoint information available Database credentials secured (environment variables or Secrets Manager) Instructions Follow these steps to configure AWS RDS with Spring Boot: Add Dependencies - Include Spring Data JPA, database driver (MySQL/PostgreSQL), and Flyway for migrations Configure Datasource - Set up connection properties in application.properties or application.yml Configure Connection Pool - Optimize HikariCP settings for your workload Set Up SSL - Enable encrypted connections to RDS Configure Profiles - Set up environment-specific configurations (dev/prod) Add Migrations - Create Flyway migration scripts for schema management Test Connectivity - Verify database connection and health check endpoint Quick Start Step 1: Add Dependencies Maven (pom.xml): < dependencies

< dependency

< groupId

org.springframework.boot </ groupId

< artifactId

spring-boot-starter-data-jpa </ artifactId

</ dependency

< dependency

< groupId

com.mysql </ groupId

< artifactId

mysql-connector-j </ artifactId

< version

8.2.0 </ version

< scope

runtime </ scope

</ dependency

< dependency

< groupId

org.postgresql </ groupId

< artifactId

postgresql </ artifactId

< scope

runtime </ scope

</ dependency

< dependency

< groupId

org.flywaydb </ groupId

< artifactId

flyway-core </ artifactId

</ dependency

< 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

← 返回排行榜