Data API for Amazon Aurora Serverless v2 with AWS SDK for Java - Part 2 Executing SQL statements

Introduction

In the first part of the series we set up the sample application which has API Gateway in front of Lambda functions which communicate with Aurora Serverless v2 PostgreSQL database via Data API to create the products and retrieve them (by id). In this part we'll dive deeper into the new Data API for Aurora Serverless v2 itself and its capabilities like executing SQL Statements and will use AWS SDK for Java (of course) for it. Here you can find the sample project.

Data API for Aurora Serverless v2 executing SQL Statements

In order to use the new Data API we need include the following dependency to the pom.xml

<dependency>
  <groupId>software.amazon.awssdk</groupId>
  <artifactId>rdsdata</artifactId>
</dependency>

In this example we retrieve the product by its id, see the definition of the Lambda function GetProductByIdViaAuroraServerlessV2DataApi
Here is the complete source code of the Lambda function GetProductByIdViaAuroraServerlessV2DataApiHandler.

As the first step we need to build RdsDataClient like this:

RdsDataClient rdsDataClient = RdsDataClient.builder().build();

Then we need to build ExecuteStatementRequest, for example like this

final String id = event.getPathParameters().get("id");
final String dbClusterArn=System.getenv("DB_CLUSTER_ARN");
final String dbSecretStoreArn=System.getenv("DB_CRED_SECRETS_STORE_ARN");
final String sql="select id, name, price from products where id="+id;

final ExecuteStatementRequest request= ExecuteStatementRequest.builder().database("").
                        resourceArn(dbClusterArn).
                        secretArn(dbSecretStoreArn).
                        sql(sql).
                        build();

We pass Aurora DB Cluster ARN and Secret Manager ARN which we received through the environment variables via the SAM template and we first don't use any prepared statement.

To execute the statement we need to invoke

final ExecuteStatementResponse response= rdsDataClient.executeStatement(request);

And then we need to work through the response records, each of the them in case execute statement response isn't empty consists of the List of fields from which we get the individual typed (String, Integer) values by index which matches the field position in the SQL query starting from 0. In our example as we select product by id we know that records can have maximal 1 element.

final List<List<Field>> records=response.records();
if (records.isEmpty()) return Optional.empty();

final List<Field> fields= records.get(0);
final String name= fields.get(1).stringValue(); 
final BigDecimal price= new BigDecimal(fields.get(2).stringValue());

final Product product = new Product(id, name, price);
return Optional.of(product);

If we construct ExecuteStatementRequest with additionally formatRecordsAs(RecordsFormatType.JSON)

final ExecuteStatementRequest request= ExecuteStatementRequest.builder().database("").
                        resourceArn(dbClusterArn).
                        secretArn(dbSecretStoreArn).
                        sql(sql).
   formatRecordsAs(RecordsFormatType.JSON).
                        build();

and then execute the statement and then invoke response.formattedRecords() we get a string value that represents the result set of a SELECT statement in JSON format.

final ExecuteStatementResponse response= rdsDataClient.executeStatement(request);
Final String responseAsJson=response.formattedRecords();

We can then easily convert this string value into the array of Products using librares like Jackson.

Now let's look into how to construct the Prepared Statement using Data API to be protected agains SQL injection attacks. For this we need to adjust the ExecuteStatementRequest and use SqlParameter abstraction.

final String sql="select id, name, price from products where id=:id";

final SqlParameter sqlParam= SqlParameter.builder().name("id").value(Field.builder().longValue(Long.valueOf(id)).build()).build();

final ExecuteStatementRequest request= ExecuteStatementRequest.builder().database("").
                        resourceArn(dbClusterArn).
                        secretArn(dbSecretStoreArn).
                        sql(sql).
                        parameters(sqlParam).
                        build();

in the WHERE section of the SQL Statement we define our parameter where id=:id and build Sql Parameter object with the field with the name id (which matches the parameter name in SQL statement) of the type Long passing the dynamically its value like this :

SqlParameter.builder().name("id").value(Field.builder().
longValue(Long.valueOf(id)).build()).build(); 

Finally we pass the created SqlParameter object (or multiple sql parameters) to the ExecuteStatementRequest builder by invoking additionally the parameter method like this .parameters(sqlParam). Dealing with the ExecuteStatementResponse remains the same.

Conclusion

In this article we introduced the new Data API for Aurora Serverless v2 itself and its capabilities using normal and prepared SQL statements and getting response in different formats as record of field or JSON array. Of course, Data API offers more, like running a batch SQL statement over an array of data for bulk update and insert operations. A batch SQL statement can provide a significant performance improvement over individual insert and update statements. We'll look at this capability of the Data API in the next part of the series.