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

  1. What Is Aurora DSQL (and Why Java Devs Should Care)
  2. Prerequisites & Cluster Setup
  3. Dependencies
  4. IAM Authentication: Generating Auth Tokens
  5. Datasource Configuration
  6. Retrying Serializable Transactions
  7. Entity & Repository
  8. REST Controller
  9. Run & Test with cURL
  10. Tests
  11. Production Checklist
  12. FAQ
  13. 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

ConcernWhat to Do
Auth tokensRefresh IAM tokens on expiry; never hardcode; use IRSA/EC2 instance roles, never access keys
Retry stormsExponential backoff with jitter; cap retries (3–5); monitor 40001 rates in CloudWatch
Schema migrationsFlyway works against DSQL's PostgreSQL compatibility — pin versions, test against staging clusters
Connection poolingSize pools conservatively; DSQL scales the backend, but per-connection costs still exist
ObservabilityEnable Hibernate query metrics + Micrometer; trace slow queries with AWS X-Ray or OTel
Multi-RegionPoint 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