Spring Boot REST API with Amazon Aurora DSQL
Build a Spring Boot REST API with Amazon Aurora DSQL (Complete Tutorial)
Amazon Aurora DSQL is AWS's serverless, distributed SQL database: PostgreSQL-compatible, active-active across regions, and built for scale-out transactional workloads. It exposes the PostgreSQL wire protocol, which means your existing Java tooling — the PostgreSQL JDBC driver, Hibernate, Spring Data JPA — works out of the box.
In this tutorial, you'll build a complete REST API on Spring Boot backed by Aurora DSQL: schema, entities, CRUD endpoints, IAM-authenticated connections, retry handling for distributed transaction conflicts, and tests.
Table of Contents
- What Is Aurora DSQL (and Why Java Devs Should Care)
- Prerequisites & Cluster Setup
- Dependencies
- IAM Authentication: Generating Auth Tokens
- Datasource Configuration
- Retrying Serializable Transactions
- Entity & Repository
- REST Controller
- Run & Test with cURL
- Tests
- Production Checklist
- FAQ
- Conclusion
What Is Aurora DSQL (and Why Java Devs Should Care)
Aurora DSQL (Distributed SQL) is designed for transactional workloads that need what traditional single-writer databases can't offer:
- Serverless: no instances, no storage provisioning, no failover scripts
- PostgreSQL compatibility: your Spring Data JPA, Flyway, and PostgreSQL driver skills transfer directly
- Active-active multi-Region: read and write in multiple Regions with strong consistency
- Horizontal scale-out: shards automatically; no read replica management
Because it speaks PostgreSQL, the Java side is refreshingly boring — and boring is good. The two things that are different: authentication (IAM tokens instead of passwords) and transaction retries (distributed systems occasionally ask you to retry a conflicting transaction).
Prerequisites & Cluster Setup
You need:
- JDK 21+ and Spring Boot 3.x/4.x
- An AWS account with permissions for Aurora DSQL
- AWS CLI configured
Create a DSQL cluster:
aws dsql create-cluster --region us-east-1 \
--tags Key=env,Value=dev
# Save the cluster endpoint from the response:
# {"id": "abc123...", "arn": "...", ...}
Create a database (optional — DSQL creates a default one) and grab the endpoint:
aws dsql get-cluster --identifier abc123... --region us-east-1 # Endpoint looks like: # abc123....dsql.us-east-1.on.aws
Dependencies
Add the PostgreSQL driver and the AWS SDK v2 DSQL utilities (for IAM auth tokens) to your pom.xml:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<!-- IAM auth token generation for DSQL -->
<dependency>
<groupId>software.amazon.awssdk</groupId>
<artifactId>dsql</artifactId>
</dependency>
Properties in application.yml:
spring:
application:
name: dsql-api
datasource:
url: jdbc:postgresql://${DSQL_HOST}:5432/postgres
username: admin
hikari:
maximum-pool-size: 10
data-source-properties:
ssl: true
sslmode: require
app:
dsql:
region: us-east-1
cluster-id: ${DSQL_CLUSTER_ID}
IAM Authentication: Generating Auth Tokens
Aurora DSQL doesn't use static passwords. Every connection authenticates with a short-lived IAM token generated via the AWS SDK:
@Component
public class DsqlAuthTokenProvider {
private final DsqlUtilities dsql;
public DsqlAuthTokenProvider(DsqlUtilities dsql) {
this.dsql = dsql;
}
public String token(String clusterId, String region) {
return dsql.generateAuthToken(GenerateAuthTokenRequest.builder()
.clusterId(clusterId)
.region(Region.of(region))
.expiresIn(Duration.ofMinutes(15))
.build());
}
}
DSQL rotates/expires these tokens, so wire token generation into a DataSource that refreshes credentials per connection (or on expiry events) rather than a one-time startup password.
Datasource Configuration
A HikariCP setup that supplies the IAM token as the password for every new connection:
@Configuration
public class DsqlDataSourceConfig {
@Bean
DataSource dataSource(DsqlAuthTokenProvider auth,
@Value("${app.dsql.cluster-id}") String clusterId,
@Value("${app.dsql.region}") String region,
@Value("${spring.datasource.url}") String url) {
HikariConfig config = new HikariConfig();
config.setJdbcUrl(url);
config.setUsername("admin");
config.setMaximumPoolSize(10);
config.addDataSourceProperty("sslmode", "require");
// Supply a fresh IAM token whenever a connection is created
config.setPassword(auth.token(clusterId, region));
return new HikariDataSource(config);
}
}
Important: DSQL auth tokens expire, so for long-running services, refresh the token on credential-expiry errors (Hikari will drop and recreate connections — just regenerate the token in the password supplier).
Retrying Serializable Transactions
In a distributed SQL system, concurrent transactions can conflict. DSQL signals this with a retryable 40001 (serialization failure). Wrap your service methods in a small retry aspect/helper:
@Service
public class OrderService {
@Transactional
public Order placeOrder(OrderRequest req) {
// read stock, write order, decrement inventory...
return orderRepository.save(new Order(req.item(), req.qty()));
}
}
public interface RetryableTx {
⟨T⟩ T execute(Supplier⟨T⟩ tx);
}
@Component
public class DsqlRetry implements RetryableTx {
private static final Set⟨String⟩ RETRYABLE =
Set.of("40001", "40P01"); // serialization / deadlock
@Override
public ⟨T⟩ T execute(Supplier⟨T⟩ tx) {
int attempts = 0;
while (true) {
try {
return tx.get();
} catch (PersistenceException | DataIntegrityViolationException e) {
if (++attempts >= 5 || !isRetryable(e)) throw e;
backoff(attempts); // 10ms, 20ms, 40ms...
}
}
}
private boolean isRetryable(RuntimeException e) {
Throwable t = e;
while (t != null) {
if (t instanceof PSQLException p
&& p.getServerErrorMessage() != null
&& RETRYABLE.contains(
p.getServerErrorMessage().getSQLState())) {
return true;
}
t = t.getCause();
}
return false;
}
}
This is the single most important production detail for DSQL — treat 40001 like you treat optimistic-locking failures in JPA: expected, and retried with backoff + jitter.
Entity & Repository
Plain JPA — nothing DSQL-specific:
@Entity
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.UUID)
private UUID id;
@Column(nullable = false)
private String item;
@Column(nullable = false)
private int quantity;
protected Order() {} // JPA
public Order(String item, int quantity) {
this.item = item;
this.quantity = quantity;
}
// getters...
public UUID getId() { return id; }
public String getItem() { return item; }
public int getQuantity() { return quantity; }
}
public interface OrderRepository
extends JpaRepository⟨Order, UUID⟩ {
List⟨Order⟩ findByItem(String item);
}
REST Controller
@RestController
@RequestMapping("/api/orders")
public class OrderController {
private final OrderRepository repo;
private final DsqlRetry retry;
public OrderController(OrderRepository repo, DsqlRetry retry) {
this.repo = repo;
this.retry = retry;
}
@PostMapping
public ResponseEntity⟨Order⟩ create(@RequestBody OrderRequest req) {
Order saved = retry.execute(
() -> repo.save(new Order(req.item(), req.quantity())));
return ResponseEntity.status(HttpStatus.CREATED).body(saved);
}
@GetMapping("/{id}")
public Order get(@PathVariable UUID id) {
return repo.findById(id)
.orElseThrow(() -> new ResponseStatusException(
HttpStatus.NOT_FOUND, "order not found"));
}
@GetMapping
public List⟨Order⟩ list() {
return repo.findAll();
}
@DeleteMapping("/{id}")
public ResponseEntity⟨Void⟩ delete(@PathVariable UUID id) {
repo.deleteById(id);
return ResponseEntity.noContent().build();
}
}
public record OrderRequest(String item, int quantity) {}
Run & Test with cURL
# Create an order
curl -X POST http://localhost:8080/api/orders \
-H "Content-Type: application/json" \
-d '{"item":"keyboard","quantity":2}'
# Response
{"id":"7f3a...","item":"keyboard","quantity":2}
# Fetch it
curl http://localhost:8080/api/orders/7f3a...
# List all
curl http://localhost:8080/api/orders
Tests
Use @DataJpaTest with Testcontainers' PostgreSQL container for repository tests — DSQL's PostgreSQL compatibility means your tests validate the same code paths:
@DataJpaTest
@Testcontainers
class OrderRepositoryTest {
@Container
@ServiceConnection // Spring Boot 3.1+ auto-config
static PostgreSQLContainer⟨?⟩ postgres =
new PostgreSQLContainer⟨⟩("postgres:16");
@Autowired OrderRepository repo;
@Test
void savesAndFinds() {
Order o = repo.save(new Order("mouse", 5));
assertThat(repo.findById(o.getId()))
.isPresent()
.get()
.extracting(Order::getItem)
.isEqualTo("mouse");
}
@Test
void findsByItem() {
repo.save(new Order("cable", 1));
assertThat(repo.findByItem("cable")).hasSize(1);
}
}
Add @SpringBootTest + MockMvc web-layer tests for the controller, and a retry test that simulates a 40001 failure and verifies the service retries instead of failing the request.
Production Checklist
| Concern | What to Do |
|---|---|
| Auth tokens | Refresh IAM tokens on expiry; never hardcode; use IRSA/EC2 instance roles, never access keys |
| Retry storms | Exponential backoff with jitter; cap retries (3–5); monitor 40001 rates in CloudWatch |
| Schema migrations | Flyway works against DSQL's PostgreSQL compatibility — pin versions, test against staging clusters |
| Connection pooling | Size pools conservatively; DSQL scales the backend, but per-connection costs still exist |
| Observability | Enable Hibernate query metrics + Micrometer; trace slow queries with AWS X-Ray or OTel |
| Multi-Region | Point your app at the nearest DSQL endpoint; the database handles cross-Region consistency |
Frequently Asked Questions (FAQ)
Is Aurora DSQL a PostgreSQL fork?
It's PostgreSQL-compatible (wire protocol, driver, and most SQL), but a separate distributed implementation. Most Spring/JPA code works unchanged; check the DSQL documentation for any unsupported PostgreSQL features.
Which JDBC driver do I use?
The standard PostgreSQL JDBC driver — same one you'd use for RDS PostgreSQL. The only difference is authentication: IAM-generated auth tokens instead of a password.
Do I need to change my Spring Data JPA code?
No. Entities, repositories, and @Transactional all work as-is. The only additions are IAM token generation and retrying serialization failures (40001).
How do schema migrations work?
Flyway and Liquibase both work over the PostgreSQL compatibility layer. Run them as you normally would against the DSQL endpoint with IAM auth.
How does DSQL compare to Aurora PostgreSQL?
Classic Aurora PostgreSQL is a great managed single-Region relational database. Aurora DSQL is for workloads needing active-active multi-Region writes and elastic scale-out — choose based on consistency/Region requirements, not features alone.
What happens if my transaction fails with 40001?
It's a retryable conflict, not an error in your code. Roll back the attempt, back off briefly, and retry — the service code in this post shows exactly how.
Conclusion
Amazon Aurora DSQL gives you distributed, multi-Region SQL with almost zero changes to your Spring Boot stack: same PostgreSQL driver, same JPA, same Flyway. The two adjustments — IAM auth tokens and serialization retries — are small, and now you have working code for both. You've got a CRUD API, conflict-safe transactions, and Testcontainers-backed tests — a solid foundation for whatever you build next.
Want the multi-Region failover or SQS-outbox pattern next? Drop a comment below.
Comments
Post a Comment