AWS Database Blog

Build a Spring Boot REST API with Amazon Aurora DSQL

In this post, you learn how to build a Spring Boot REST API that integrates with Aurora DSQL. You’ll configure the Aurora DSQL JDBC Connector for IAM authentication, implement optimistic concurrency control, and run the application across two regional nodes to observe active-active behavior.

Aurora DSQL reduces the operational complexity of managing multi-Region replication while Spring Boot provides the familiar framework for building REST APIs. This combination allows you to focus on application logic rather than database infrastructure management.

This post is intended for developers and solutions architects who are familiar with Java, Spring Boot, and relational databases.

Walkthrough

By the end of this walkthrough, you’ll have a working REST API that demonstrates the following:

  • Setting up Aurora DSQL with the Aurora DSQL JDBC Connector.
  • Handling optimistic concurrency control with retry logic.
  • Building a RESTful product inventory API using Spring Boot.
  • Running the application on two regional nodes and testing concurrent multi-Region writes.

Solution overview

The following diagram illustrates the architecture of the sample application.

Architecture diagram showing two Spring Boot application nodes in us-east-1 and us-west-2 connected through HikariCP and Aurora DSQL JDBC Connector to a multi-Region Aurora DSQL cluster

Figure 1: Architecture diagram showing two Spring Boot application nodes deployed in AWS Regions us-east-1 and us-west-2. Each node connects through HikariCP connection pooling and the Aurora DSQL JDBC Connector with IAM authentication to regional endpoints of an Aurora DSQL multi-region cluster. The diagram illustrates synchronous cross-region replication between the regional endpoints.

The application uses the following components:

  • Spring Boot 3.3 – REST API framework.
  • HikariCP – Connection pooling.
  • Aurora DSQL JDBC Connector – IAM authentication, token refresh, TLS encryption, and database connectivity.
  • Application Load Balancer (ALB) – Distributes incoming traffic across Spring Boot nodes and routes around unhealthy instances.

Prerequisites

Before you begin, make sure you have the following:

  • An AWS account with an Aurora DSQL cluster created (check the Aurora DSQL documentation for current Region availability).
  • AWS Command Line Interface (AWS CLI) configured with credentials.
  • Java 17 or higher installed.
  • Maven 3.6 or higher installed.
  • An AWS Identity and Access Management (AWS IAM) user or role with the minimum permissions shown below.

AWS IAM Policy (minimum permissions):

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["dsql:DbConnectAdmin"],
      "Resource": "arn:aws:dsql:<region>:<account-id>:cluster/<cluster-id>"
    }
  ]
}

Replace <region>, <account-id>, and <cluster-id> with your actual values. For production workloads, follow the principle of least privilege and scope permissions to specific clusters.

Step 1: Create your multi-Region Aurora DSQL cluster

  1. Create the cluster in your primary Region
aws dsql create-cluster \
    --region <PRIMARY_REGION> \
    --multi-region-properties '{"witnessRegion":"<WITNESS_REGION>"}'
  1. Create the cluster in your secondary Region
aws dsql create-cluster \
    --region <SECONDARY_REGION> \
    --multi-region-properties '{"witnessRegion":"<WITNESS_REGION>","clusters":["arn:aws:dsql:<PRIMARY_REGION>:<ACCOUNT_ID>:cluster/<PRIMARY_CLUSTER_ID>"]}'
  1. Peer the primary cluster with the secondary
aws dsql update-cluster \
    --region <PRIMARY_REGION> \
    --identifier <PRIMARY_CLUSTER_ID> \
    --multi-region-properties '{"witnessRegion":"<WITNESS_REGION>","clusters":["arn:aws:dsql:<SECONDARY_REGION>:<ACCOUNT_ID>:cluster/<SECONDARY_CLUSTER_ID>"]}'

Step 2: Set up the project

Clone the sample repository:

git clone https://github.com/aws-samples/aurora-dsql-samples.git
cd aurora-dsql-samples/java/spring_boot

Update src/main/resources/application.properties with your DSQL endpoint:

# The Aurora DSQL JDBC Connector handles IAM auth, token refresh, and SSL automatically
spring.datasource.url=jdbc:aws-dsql:postgresql://<your-endpoint>.dsql.<region>.on.aws
spring.datasource.driver-class-name=software.amazon.dsql.jdbc.DSQLConnector

# AWS IAM user or role name for authentication
spring.datasource.username=<username>

# AWS Region where your DSQL cluster is deployed
spring.cloud.aws.region.static=<region>

The application uses the Aurora DSQL JDBC Connector which automatically handles IAM authentication, token refresh, and TLS encryption.

Aurora DSQL data type support: Aurora DSQL supports a subset of PostgreSQL data types including UUID, VARCHAR, TEXT, INTEGER, BIGINT, DECIMAL, BOOLEAN, TIMESTAMP, DATE, and JSON. The sample application uses these types throughout. For the complete list of supported types and Aurora DSQL-specific limits, see Supported data types in Aurora DSQL.

Step 3: Handle optimistic concurrency

Aurora DSQL uses optimistic concurrency control instead of traditional locking. Optimistic concurrency control allows multiple transactions to proceed without locking resources, checking for conflicts only at commit time. When concurrent transactions conflict, one will receive a 40001 SQL state error. We handle this with retry mechanism.

There are two layers of handling required:

Layer 1: Tell HikariCP not to evict the connection on a 40001 error

public static class DsqlExceptionOverride implements SQLExceptionOverride {

    public SQLExceptionOverride.Override adjudicate(SQLException ex) {
        if ("40001".equals(ex.getSQLState())) {
            return SQLExceptionOverride.Override.DO_NOT_EVICT;
        }
        return SQLExceptionOverride.Override.CONTINUE_EVICT;
    }
}

dataSource.setExceptionOverrideClassName(DsqlExceptionOverride.class.getName());

Layer 2: Retry the transaction with exponential backoff

@Retryable(
    retryFor = OptimisticLockingFailureException.class,
    maxAttemptsExpression = "${dsql.retry.max-attempts:4}",
    backoff = @Backoff(
        delayExpression      = "${dsql.retry.initial-delay-ms:100}",
        multiplierExpression = "${dsql.retry.multiplier:2.0}",
        maxDelayExpression   = "${dsql.retry.max-delay-ms:2000}",
        random = true
    ))

You can tune via application.properties without changing code:

dsql.retry.max-attempts=4
dsql.retry.initial-delay-ms=100
dsql.retry.multiplier=2.0
dsql.retry.max-delay-ms=2000

Step 4: Build the REST API

The sample application includes a product inventory API that provides standard CRUD operations:

@RestController
@RequestMapping("/api/products")
public class ProductController {

    @PostMapping
    public ResponseEntity<Product> createProduct(@RequestBody Product product) {
        Product created = productService.createProduct(product);
        return ResponseEntity.status(HttpStatus.CREATED).body(created);
    }

    @GetMapping
    public ResponseEntity<List<Product>> getAllProducts() {
        return ResponseEntity.ok(productService.getAllProducts());
    }

    @PatchMapping("/{id}/stock")
    public ResponseEntity<Map<String, String>> updateStock(
            @PathVariable UUID id,
            @RequestParam int quantity) {
        productService.updateStock(id, quantity);
        return ResponseEntity.ok(Map.of("message", "Stock updated"));
    }
}

Step 5: Run and test the application

Connect to the EC2 instance running the application:

Make sure its Security Group allows outbound traffic on port 5432 to the Aurora DSQL cluster endpoint:

ssh -i <YOUR_KEY.pem> ec2-user@<EC2_PUBLIC_IP>

For detailed steps on connecting to an EC2 instance, refer to the Connect to your EC2 Instance.

Build and run the application:

mvn clean install
mvn spring-boot:run

Initialize the database schema:

curl -X POST http://<EC2_INSTANCE_IP>:8080/api/products/init

Expected response: HTTP/1.1 200 OK

Create a product:

curl -X POST http://<EC2_INSTANCE_IP>:8080/api/products \
    -H "Content-Type: application/json" \
    -d '{
  "name": "Sample Product",
  "description": "A sample product for testing",
  "price": 29.99,
  "stock": 100
}'

Expected response:

{
  "id": "550e8400-e29b-41d4-a716-446655440000",
  "name": "Sample Product",
  "description": "A sample product for testing",
  "price": 29.99,
  "stock": 100
}

Retrieve the products:

curl http://<EC2_INSTANCE_IP>:8080/api/products

Expected response:

[
  {
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "name": "Sample Product",
    "description": "A sample product for testing",
    "price": 29.99,
    "stock": 100
  }
]

Update stock:

curl -X PATCH "http://<EC2_INSTANCE_IP>:8080/api/products/<product-id>/stock?quantity=50"

Expected response:

{
  "message": "Stock updated"
}

Step 6: Testing concurrent multi-Region writes

  1. Update src/main/resources/application.properties with your regional DSQL endpoints. 
    # Node 1, Region: us-east-1
spring.datasource.url=jdbc:aws-dsql:postgresql://<us-east-1-endpoint>.dsql.us-east-1.on.aws
spring.datasource.driver-class-name=software.amazon.dsql.jdbc.DSQLConnector
spring.datasource.username=<username>
spring.cloud.aws.region.static=us-east-1

# Node 2, Region: us-west-2
spring.datasource.url=jdbc:aws-dsql:postgresql://<us-west-2-endpoint>.dsql.us-west-2.on.aws
spring.datasource.driver-class-name=software.amazon.dsql.jdbc.DSQLConnector
spring.datasource.username=<username>
spring.cloud.aws.region.static=us-west-2
  2. Build the application. 
    mvn clean install
  3. Start the application on each node. 
    mvn spring-boot:run
  4. Update src/main/java/com/example/controller to add the concurrent write endpoint. 
    @RestController
@RequestMapping("/api/products")
public class LoadTestController {

    private final ProductService productService;
    private final ExecutorService executor = Executors.newFixedThreadPool(20);

    public LoadTestController(ProductService productService) {
        this.productService = productService;
    }

    @PostMapping("/load-test")
    public ResponseEntity<LoadTestResult> runLoadTest(
            @RequestParam UUID productId,
            @RequestParam int count,
            @RequestParam int delta) {
        long start = System.currentTimeMillis();
        AtomicInteger succeeded = new AtomicInteger();
        AtomicInteger failed = new AtomicInteger();

        List<CompletableFuture<Void>> futures = IntStream.range(0, count)
            .mapToObj(i -> CompletableFuture.runAsync(() -> {
                try {
                    productService.updateStock(productId, delta);
                    succeeded.incrementAndGet();
                } catch (Exception e) {
                    failed.incrementAndGet();
                }
            }, executor))
            .collect(Collectors.toList());

        CompletableFuture.allOf(futures.toArray(new CompletableFuture[0])).join();
        Product product = productService.getProduct(productId);

        return ResponseEntity.ok(new LoadTestResult(
            productId, count,
            succeeded.get(), failed.get(),
            System.currentTimeMillis() - start,
            product.getStock()
        ));
    }

    @PreDestroy
    public void shutdown() { executor.shutdown(); }
}

public record LoadTestResult(
    UUID productId,
    int requested,
    int succeeded,
    int failed,
    long elapsedMs,
    int finalStock
) {}
  5. Run the multi-Region concurrent write test

    Example 1: Creating Products Concurrently

    # On Node 1: create a product with stock 1000 & note the returned id
curl -X POST http://<node1-host>:8080/api/products \
    -H "Content-Type: application/json" \
    -d '{"name":"Multi-Region Test","description":"Active-active write test","price":9.99,"stock":1000}'

# Trigger both load tests at the same time
# Node 1 (us-east-1): 500 concurrent +1 updates
curl -X POST "http://<node1-host>:8080/api/products/load-test?productId=<id>&count=500&delta=1"

# Node 2 (us-west-2): 500 concurrent -1 updates
curl -X POST "http://<node2-host>:8080/api/products/load-test?productId=<id>&count=500&delta=-1"

# Verify the final stock from either node:
curl http://<node1-host>:8080/api/products/<id>

    Expected results:

    Node 1:

    {
  "productId": "<id>",
  "requested": 500,
  "succeeded": 500,
  "failed": 0,
  "elapsedMs": 3240,
  "finalStock": 1000
}

    Node 2:

    {
  "productId": "<id>",
  "requested": 500,
  "succeeded": 500,
  "failed": 0,
  "elapsedMs": 3180,
  "finalStock": 1000
}

    finalStock returns to 1000 because all 500 increments from us-east-1 and 500 decrements from us-west-2 are accounted.

    Example 2: Single-entry concurrent UPDATE example

    # Node 1 (us-east-1)
curl -X PUT "http://<EC2_INSTANCE_IP>:8080/api/products/$PRODUCT_ID" \
    -H "Content-Type: application/json" \
    -d '{"name":"Updated by Node 1","description":"us-east-1 write","price":19.99,"stock":200}' &

# Node 2 (us-west-2) --- at the same time
curl -X PUT "http://<EC2_INSTANCE_IP>:8081/api/products/$PRODUCT_ID" \
    -H "Content-Type: application/json" \
    -d '{"name":"Updated by Node 2","description":"us-west-2 write","price":29.99,"stock":300}'

    One of the two requests will succeed immediately. The other will receive a 40001 OCC conflict from Aurora DSQL, and @Retryable will transparently retry it. Both requests return HTTP 200.

    Check the Spring Retry debug logs to see the conflict and retry:

    DEBUG RetryTemplate : Retry: count=0; for: 'ProductService.updateProduct'
DEBUG ExponentialRandomBackOffPolicy : Sleeping for 73
DEBUG RetryTemplate : Retry: count=1; for: 'ProductService.updateProduct'

    Verify the final state from either node:

    curl -s http://<EC2_INSTANCE_IP>:8080/api/products/$PRODUCT_ID | jq '{name, stock}'

Clean up

To avoid incurring future charges, delete the resources you created:

  1. Delete the multi-Region Aurora DSQL clusters. 
    # Disable deletion protection for primary cluster
aws dsql update-cluster \
    --region <PRIMARY_REGION> \
    --identifier <PRIMARY_CLUSTER_ID> \
    --no-deletion-protection-enabled

# Delete primary cluster
aws dsql delete-cluster \
    --region <PRIMARY_REGION> \
    --identifier <PRIMARY_CLUSTER_ID>

# Disable deletion protection for secondary cluster
aws dsql update-cluster \
    --region <SECONDARY_REGION> \
    --identifier <SECONDARY_CLUSTER_ID> \
    --no-deletion-protection-enabled

# Delete secondary cluster
aws dsql delete-cluster \
    --region <SECONDARY_REGION> \
    --identifier <SECONDARY_CLUSTER_ID>
  2. Remove the Application Load Balancer. 
    aws elbv2 delete-load-balancer \
    --load-balancer-arn <LOAD_BALANCER_ARN>
  3. Stop and terminate the EC2 Instance running the Spring Boot application. 
    # To stop the instance
aws ec2 stop-instances \
    --instance-ids <INSTANCE_ID>

# To terminate the instance
aws ec2 terminate-instances \
    --instance-ids <INSTANCE_ID>

Key takeaways

The following are key considerations when building applications with Aurora DSQL:

  • Aurora DSQL JDBC Connector – The Aurora DSQL JDBC Connector handles IAM-based authentication, automatic token refresh, and TLS encryption. This can reduce the need for manual password management, token rotation logic, and SSL configuration.
  • Optimistic concurrency – Aurora DSQL uses optimistic concurrency control, which requires implementing retry logic but enables better scalability than pessimistic locking. When two users update the same row simultaneously, one receives a 40001 error. Two layers of handling are required: DsqlExceptionOverride tells HikariCP not to evict the connection (keeping the pool healthy under load), and @Retryable transparently retries the transaction with exponential backoff.
  • Connection pooling – Proper HikariCP configuration supports efficient connection reuse and optimal performance.

Security considerations

When deploying this application in a production environment, consider the following security best practices:

Follow the principle of least privilege by scoping your AWS IAM policy to the specific Aurora DSQL cluster ARN. Use dsql:DbConnect instead of dsql:DbConnectAdmin for application users that do not need administrative access. For secrets management, verify that your AWS credentials are managed through AWS IAM roles for Amazon Elastic Compute Cloud (Amazon EC2), Amazon Elastic Container Service (Amazon ECS) task roles, or AWS IAM Roles Anywhere rather than long-lived access keys.

At the regional level, place an Application Load Balancer in front of your Spring Boot nodes. Configure Amazon Route 53 health checks on the ALB endpoints in each region. If a regional endpoint becomes unavailable and the ALB in that region starts failing health checks, Amazon Route 53 automatically shifts DNS traffic to the ALB in the surviving region. Because each node runs identical application code and connects to its nearest regional endpoint via configuration only, no application code changes are required during this failover.

Production considerations

The patterns in this post provide a foundation for production applications. For a production deployment, also consider the following:

For observability, add Amazon CloudWatch metrics for connection pool utilization, token refresh success and failure rates, and retry counts. Use structured logging with correlation IDs for request tracing. Implement a Spring Boot Actuator health indicator that verifies database connectivity, so your load balancer can detect unhealthy instances. Adjust HikariCP’s maximumPoolSize, minimumIdle, and connectionTimeout based on your expected concurrency, and monitor pool metrics to right-size these values. Be aware that Aurora DSQL uses Distributed Processing Units (DPUs) for pricing, which measure database activity including compute resources, I/O operations, and SQL workload execution, so review the Aurora DSQL pricing page to understand cost implications for your workload.

For regional failover, Aurora DSQL’s multi-Region cluster provides built-in high availability. If a regional endpoint becomes unavailable, the cluster remains operational through the secondary regional endpoint.

Conclusion

In this post, we demonstrated how you can build a Spring Boot REST API that integrates with Amazon Aurora DSQL. By using the Aurora DSQL JDBC Connector for IAM authentication and TLS encryption, and implementing optimistic concurrency control with Spring Retry, you can build scalable, globally distributed applications without the operational overhead of traditional databases.

The sample code provides foundational patterns for authentication, concurrency control, and error handling that you can adapt to your own applications. Whether you’re building a new application or evaluating Aurora DSQL for an existing workload, these patterns help you take advantage of the serverless, multi-Region capabilities of Aurora DSQL.

To provide feedback or contribute, visit the GitHub repository.

About the authors

Mirron Panicker

Mirron Panicker

Mirron is a Technical Account Manager at AWS, where he partners with enterprise customers to optimize and modernize their cloud workloads. Mirron specializes in container technologies, helping teams adopt and scale containerized architectures on AWS.

John Thach

John Thach

John is a Technical Account Manager at AWS. He works with enterprise customers to help them architect and optimize their workloads on AWS. John specializes in cloud operations and infrastructure design, helping teams build resilient, scalable systems.