Serverless applications with Java and Aurora DSQL - Part 5 Support for identity columns and sequence objects added

Source: Dev.to

Introduction

Recently, AWS added support for identity columns and sequence objects in Aurora DSQL. This enables developers to generate auto‑incrementing, integer‑based IDs directly in the database using familiar SQL patterns.

For many workloads, short numeric IDs are preferable to UUIDs (even time‑ordered UUIDv7). Numeric IDs are easier to communicate verbally (e.g., order numbers) and are more compact than the 36‑character UUID string representation.

The new feature simplifies migration of existing PostgreSQL applications and supports new workloads that rely on database‑managed integer identifiers. Developers can now create compact, human‑readable IDs such as order numbers, account IDs, or operational references without custom ID‑generation logic in application code or middleware.

Aurora DSQL support for identity columns and sequence objects is available in all regions where Aurora DSQL is offered. See the Aurora DSQL documentation on sequences and identity columns for more details.

Using Sequences in Sample Applications

Below are two minimal Java 25 examples that demonstrate how to use a PostgreSQL sequence (product_id) with Aurora DSQL. Both applications use AWS SAM for deployment and HikariCP for connection pooling.

Create the Sequence

CREATE SEQUENCE product_id CACHE 1;

1️⃣ Plain JDBC Example (No Frameworks)

This example uses plain JDBC (no Spring, no Hibernate). The product ID is generated by selecting the next value from the product_id sequence.

public int createProduct(Product product) throws Exception {
    try (var pst1 = this.generateProductId(con);
         var rs = pst1.executeQuery()) {
        if (rs.next()) {
            var id = rs.getInt("nextval");
            // assign id to product, insert product row, etc.
        }
        // ...
    }
}

private PreparedStatement generateProductId(Connection con) throws SQLException {
    return con.prepareStatement("SELECT nextval('product_id')");
}

2️⃣ Hibernate ORM Example

In this version the entity is mapped with JPA annotations to use the same product_id sequence.

@Entity
@Table(name = "products")
public class Product implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(sequenceName = "product_id", allocationSize = 1)
    private int id;

    // other fields, getters, setters...
}

The DAO uses a SessionFactory to persist and retrieve Product entities:

public class ProductDao {

    private static final SessionFactory sessionFactory = HibernateUtils.getSessionFactory();

    public int createProduct(Product product) throws Exception {
        var session = sessionFactory.openSession();
        var transaction = session.beginTransaction();
        session.persist(product);
        transaction.commit();
        return product.getId();
    }

    public Optional getProductById(int id) throws Exception {
        var session = sessionFactory.openSession();
        return Optional.ofNullable(session.find(Product.class, id));
    }
}

Next Steps

• Update the sample applications from earlier parts of this series to incorporate sequence support.
• Benchmark the performance of the Lambda functions using the Java runtime (managed) versus a GraalVM native image.
• Compare results against the original DynamoDB‑based examples.