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

Introduction

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

For me, the support for sequences has always been the number one priority among the missing Aurora DSQL features I wished for. I can't always rely on UUIDs, even UUIDv7, which is time-ordered, because, for example, there are IDs (like order id) that I provide to my SAAS customers, and it's difficult to deal with those in case of issues when you need to discuss them on the phone. UUID has a total length of 36 characters (letters and digits) in its canonical string representation, which is very long. I prefer to have much shorter numbers as IDs.

Identity columns and sequence objects

This launch simplifies migrations of existing PostgreSQL applications and supports development of new workloads that rely on database-managed integer identifiers. Developers can 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 available. To learn more, see the Aurora DSQL documentation on sequences and identity columns.

If I have time in the future, I'll update my sample applications introduced in part 1 and part 2 of this series.

Coincidentally, I'm currently working on updating my examples to measure the performance of the AWS Lambda function using Java runtime (the managed version, like 25 or deployed as a GraalVM Native Image as AWS Custom Runtime). I previously always used NoSQL Amazon DynamoDB in my examples, and now I would like to add some examples of using the relational Amazon Aurora DSQL database to compare the results. For this, I use a much simpler application to store and retrieve products, and could incorporate support for sequences into my Aurora DSQL examples from the beginning.

It's a pure SQL standard, so you need to first create a sequence:

CREATE SEQUENCE product_id CACHE 1;     

Now let's look into my sample applications using DSQL:

1. The first application uses Java 25 without any frameworks (like Spring Boot), Hikari database connection pool, no ORM framework like Hibernate (I use a plain JDBC), and AWS SAM. Everything else happens in the ProductDao.java, where I generate the product id using the product_id sequence like this:

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");
       }
      .....
    }
  }

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

1. The second application uses Java 25 without any frameworks (like Spring Boot), Hikari database connection pool, and the Hibernate ORM framework, and AWS SAM. Everything else happens in the Product.java, where I define how to generate the product id using the 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;
...

The product will be saved and retrieved in the ProductDao like this:

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<Product> getProductById(int id) throws Exception {
   var session= sessionFactory.openSession();   
   return Optional.ofNullable(session.find(Product.class, id));
   }
}

Conclusion

AWS finally added much-appreciated support for identity columns and sequence objects .

If you like my content, please follow me on GitHub and give my repositories a star!

Please also check out my website for more technical content and upcoming public speaking activities.