IK.AM

I tried using Amazon Aurora DSQL from Spring Boot, so here are some tips I’ve gathered.

You can find the sample app code here.
For instructions on running the sample app, please refer to the README.

Adding Dependencies

To access DSQL, you’ll need the AWS SDK in addition to dependencies like PostgreSQL JDBC and Spring JDBC. This is because you need to dynamically obtain the DSQL password (token) from the AWS SDK.

To simplify AWS Credentials management, I use Spring Cloud AWS. The core feature io.awspring.cloud:spring-cloud-aws-starter is sufficient. To obtain the DSQL token, add software.amazon.awssdk:dsql as well.

It’s not required, but since I use aws sso login to get credentials, I also add software.amazon.awssdk:sso for SSO support.

<dependencies>
  <dependency>
    <groupId>io.awspring.cloud</groupId>
    <artifactId>spring-cloud-aws-starter</artifactId>
  </dependency>
  <dependency>
    <groupId>software.amazon.awssdk</groupId>
    <artifactId>sso</artifactId>
    <exclusions>
      <exclusion>
        <groupId>commons-logging</groupId>
        <artifactId>commons-logging</artifactId>
      </exclusion>
    </exclusions>
  </dependency>
  <dependency>
    <groupId>software.amazon.awssdk</groupId>
    <artifactId>dsql</artifactId>
    <exclusions>
      <exclusion>
        <groupId>commons-logging</groupId>
        <artifactId>commons-logging</artifactId>
      </exclusion>
    </exclusions>
  </dependency>
</dependencies>

Spring Cloud AWS uses the following BOM:

  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>io.awspring.cloud</groupId>
        <artifactId>spring-cloud-aws-dependencies</artifactId>
        <version>3.3.1</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

With this setup, you can dynamically obtain the DSQL password using the credentials for the aws CLI.

DataSource Configuration

Assume that DSQL has already been created in the console. At the time of writing, only single region is available in Tokyo (ap-northeast-1).

This time, I assume you are accessing the public endpoint from your local environment, using the admin user.

Get the public endpoint from the console and set it in application.properties as follows:

spring.datasource.url=jdbc:postgresql://<public_endpoint>/postgres?sslmode=verify-full&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory
spring.datasource.username=admin
# If the region is set in ~/.aws/config or you are running on AWS, the following setting is not required.
spring.cloud.aws.region.static=ap-northeast-1

To obtain the DSQL token and set it in the DataSource, create a DataSourceConfig like the following. This class also schedules a task to periodically update the DSQL password and registers a SQLExceptionTranslator to convert optimistic concurrency control errors in DSQL to OptimisticLockingFailureException.

/*
 * Copyright (C) 2025 Toshiaki Maki <makingx@gmail.com>
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *         http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.example.config;

import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.SQLExceptionOverride;
import java.sql.SQLException;
import java.time.Duration;
import java.time.Instant;
import java.util.function.Consumer;
import java.util.function.Supplier;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.task.SimpleAsyncTaskSchedulerBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.OptimisticLockingFailureException;
import org.springframework.jdbc.support.JdbcTransactionManager;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;
import org.springframework.scheduling.concurrent.SimpleAsyncTaskScheduler;
import org.springframework.util.StringUtils;
import software.amazon.awssdk.auth.credentials.AwsCredentialsProvider;
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.regions.providers.AwsRegionProvider;
import software.amazon.awssdk.services.dsql.DsqlUtilities;
import software.amazon.awssdk.services.dsql.model.GenerateAuthTokenRequest;

@Configuration(proxyBeanMethods = false)
@Profile("!testcontainers")
public class DsqlDataSourceConfig {

    private final Logger logger = LoggerFactory.getLogger(DsqlDataSourceConfig.class);

    private final Duration tokenTtl = Duration.ofMinutes(60);

    @Bean
    @ConfigurationProperties("spring.datasource")
    DataSourceProperties dsqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    Supplier<String> dsqlTokenSupplier(DataSourceProperties dsqlDataSourceProperties,
            AwsRegionProvider awsRegionProvider, AwsCredentialsProvider credentialsProvider) {
        Region region = awsRegionProvider.getRegion();
        DsqlUtilities utilities = DsqlUtilities.builder()
            .region(region)
            .credentialsProvider(credentialsProvider)
            .build();
        String username = dsqlDataSourceProperties.getUsername();
        String hostname = dsqlDataSourceProperties.getUrl().split("/")[2];
        return () -> {
            Consumer<GenerateAuthTokenRequest.Builder> request = builder -> builder.hostname(hostname)
                .region(region)
                .expiresIn(tokenTtl);
            return "admin".equals(username) ? utilities.generateDbConnectAdminAuthToken(request)
                    : utilities.generateDbConnectAuthToken(request);
        };
    }

    @Bean
    @ConfigurationProperties("spring.datasource.hikari")
    HikariDataSource dsqlDataSource(DataSourceProperties dsqlDataSourceProperties, Supplier<String> dsqlTokenSupplier) {
        HikariDataSource dataSource = dsqlDataSourceProperties.initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
        String token = dsqlTokenSupplier.get();
        if (StringUtils.hasText(dataSource.getPassword())) {
            logger.warn("Overriding existing password for the datasource with DSQL token.");
        }
        dataSource.setPassword(token);
        dataSource.setExceptionOverrideClassName(DsqlExceptionOverride.class.getName());
        return dataSource;
    }

    @Bean
    DsqlSQLExceptionTranslator dsqlSQLExceptionTranslator() {
        return new DsqlSQLExceptionTranslator();
    }

    @Bean
    JdbcTransactionManager transactionManager(DataSource dataSource,
            DsqlSQLExceptionTranslator dsqlSQLExceptionTranslator) {
        JdbcTransactionManager jdbcTransactionManager = new JdbcTransactionManager(dataSource);
        jdbcTransactionManager.setExceptionTranslator(dsqlSQLExceptionTranslator);
        return jdbcTransactionManager;
    }

    @Bean
    SimpleAsyncTaskScheduler taskScheduler(SimpleAsyncTaskSchedulerBuilder builder) {
        return builder.build();
    }

    @Bean
    InitializingBean tokenRefresher(DataSource dataSource, Supplier<String> dsqlTokenSupplier,
            SimpleAsyncTaskScheduler taskScheduler) throws Exception {
        HikariDataSource hikariDataSource = dataSource.unwrap(HikariDataSource.class);
        Duration interval = tokenTtl.dividedBy(2);
        return () -> taskScheduler.scheduleWithFixedDelay(() -> {
            try {
                String token = dsqlTokenSupplier.get();
                hikariDataSource.getHikariConfigMXBean().setPassword(token);
                hikariDataSource.getHikariPoolMXBean().softEvictConnections();
            }
            catch (RuntimeException e) {
                logger.error("Failed to refresh DSQL token", e);
            }
        }, Instant.now().plusSeconds(interval.toSeconds()), interval);
    }

    // https://catalog.workshops.aws/aurora-dsql/en-US/04-programming-with-aurora-dsql/02-handling-concurrency-conflicts
    private static final String DSQL_OPTIMISTIC_CONCURRENCY_ERROR_STATE = "40001";

    static class DsqlSQLExceptionTranslator implements SQLExceptionTranslator {

        SQLStateSQLExceptionTranslator delegate = new SQLStateSQLExceptionTranslator();

        @Override
        public DataAccessException translate(String task, String sql, SQLException ex) {
            if (DSQL_OPTIMISTIC_CONCURRENCY_ERROR_STATE.equals(ex.getSQLState())) {
                throw new OptimisticLockingFailureException(ex.getMessage(), ex);
            }
            return delegate.translate(task, sql, ex);
        }

    }

    public static class DsqlExceptionOverride implements SQLExceptionOverride {

        @java.lang.Override
        public Override adjudicate(SQLException ex) {
            if (DSQL_OPTIMISTIC_CONCURRENCY_ERROR_STATE.equals(ex.getSQLState())) {
                return Override.DO_NOT_EVICT;
            }
            return Override.CONTINUE_EVICT;
        }

    }

}

If you try to create a connection using a token that has expired, you’ll get an authentication error, so for resident applications, you need to rotate the token periodically. With HikariCP, you can change the password at runtime using HikariConfigMXBean. Also, by using softEvictConnections of HikariPoolMXBean, idle connections are discarded, and active connections are discarded when they return to the pool.

If you use the default SQLExceptionTranslator, an optimistic concurrency control error will throw a CannotAcquireLockException. You can handle this exception as is, but CannotAcquireLockException inherits from PessimisticLockingFailureException, which is intended for pessimistic locking errors (such as SELECT FOR UPDATE). Therefore, I created a DSQL-specific SQLExceptionTranslator to throw a more appropriate OptimisticLockingFailureException.

Retrying Optimistic Concurrency Control Errors

If an optimistic concurrency control error occurs, you need to retry on the application side. Retry processing can be easily implemented using Spring Retry.

With the above configuration, an OptimisticLockingFailureException will be thrown when an optimistic concurrency control error occurs. You can configure retry for OptimisticLockingFailureException using the @Retryable annotation.

@Service
@Transactional
@Retryable(retryFor = OptimisticLockingFailureException.class, maxAttempts = 4,
        backoff = @Backoff(delay = 100, multiplier = 2, random = true))
public class CartService {
  // ...
}

One thing to note is that this OptimisticLockingFailureException occurs at transaction commit time. Simply combining @Transactional and @Retryable is not enough; if the method with the @Transactional annotation is nested, you need to set the retry on the outer @Transactional method.

As described in the README, you can use the sample app to trigger an optimistic concurrency control error with the following steps. For load testing, use the vegeta command.

# Create a cart if not exists
curl -s "http://localhost:8080/api/v1/carts?userId=user123" | jq .
# Clear the cart
curl -s -X DELETE "http://localhost:8080/api/v1/carts/items?userId=user123" | jq .
# Add an item to the cart
curl -s -X POST "http://localhost:8080/api/v1/carts/items?userId=user123" \
  --json '{
    "productId": "product-001",
    "productName": "iPhone 15",
    "price": 999.99,
    "quantity": 1
  }' | jq .
ITEM_ID=$(curl -s "http://localhost:8080/api/v1/carts?userId=user123" | jq -r ".items[0].id")

cat <<EOF > body.json
{
  "quantity": 3
}
EOF

# Run the attack
echo "PATCH http://localhost:8080/api/v1/carts/items/${ITEM_ID}?userId=user123" | vegeta attack -duration=10s -rate=30 -body=body.json -header='Content-Type: application/json' | vegeta report

Other Notes

This isn’t directly related to Spring Boot, but here are some current limitations of DSQL that I noticed when implementing the application:

• Foreign key constraints cannot be used
• Sequences cannot be used
• Extensions cannot be used

It’s probably best to use UUIDs for primary keys.

It seems difficult to migrate an existing PostgreSQL application to DSQL as-is.

That said, the free tier is quite generous, so you can try out various features.