In the previous post, an overview of DynamoDB with PartiQL was provided. In this post, you will be understanding how PartiQL works with NodeJS code with the DynamoDB SDK API executeStatement.
executeStatement is used to execute read operation and singleton write operation for the data in DynamoDB.
letpartiqlStmt={Statement:`SELECT * FROM "copa-america" WHERE "pk" = 'TEAM'`,}letresponse=awaitdynamodb.executeStatement(partiqlStmt).promise()returnresponse
executeTransaction is used to execute transaction reads or writes and since it is based on transactional, we can execute multiple statements also.
letpartiqlStmt=[{Statement:`SELECT * FROM "copa-america" WHERE "pk" = 'MATCH'`,}]letresponse=awaitdynamodb.executeTransaction(partiqlStmt).promise()returnresponse
batchExecuteStatement is executeStatement with the feasibility of executing multiple statements.
letpartiqlInsertParams={Statements:[]}for(letteamofevent.teams){letteamParams={pk:"TEAM",sk:team.name+"#"+team.group+"#"+team.ranking,display_name:team.name,team_group:team.group,ranking:team.ranking,matches_played:team.matches_played,matches_won:team.matches_won,matches_drew:team.matches_drew,matches_lost:team.matches_lost,goals_for:team.goals_for,goals_against:team.goals_against,goals_difference:team.goals_difference,team_points:team.team_points}letpartiqlStmt={Statement:`INSERT INTO "testing-partiql" VALUE "{'pk':'${teamParams.pk}','sk':'${teamParams.sk}','display_name':'${teamParams.display_name}','team_group':'${teamParams.team_group}','ranking':${teamParams.ranking},'matches_played':${teamParams.matches_played},'matches_won':${teamParams.matches_won},'matches_drew':${teamParams.matches_drew},'matches_lost':${teamParams.matches_lost},'goals_for':${teamParams.goals_for},'goals_against':${teamParams.goals_against},'goals_difference':${teamParams.goals_difference},'team_points':${teamParams.team_points}}"`,}partiqlInsertParams.Statements.push(partiqlStmt)}letresponse=awaitdynamodb.batchExecuteStatement(partiqlInsertParams).promise()returnresponse
Building and structuring parameters
A SQL based query with a programmatic approach share a lot of similarities with AWS DynamoDB PartiQL statement/transaction execution. Every query executed with executeStatement, executeTransaction and batchExecuteStatement have a structure which has to be followed.
This comprises of the Statement itself which is the SQL statement. Parameters for all the input parameters based on the different DynamoDB supported datatypes - String, Boolean, String sets, Maps, Lists, Number, Number strings and null. For any the statements we can include the parameters in the statement but also able be to send the parameters in Parameters value. DynamoDB supports the concept of pagination of values with NextToken which is returned in the query if the statement has more data to be returned. In case of PartiQL, the limit is set in the statement otherwise by default with the limits of DynamoDB the data are returned.
PartiQL statement without parameters.
letpartiqlStmt={Statement:`SELECT * FROM "copa-america" `,}
PartiQL statement with parameters, and the parameters been set in Statement.
letpartiqlStmt={Statement:`SELECT * FROM "copa-america" WHERE "pk" = 'TEAM' AND contains("display_name",'${params.id}')`,}
PartiQL statement with parameters, and the parameters been set in Parameters attribute.
letpartiqlStmt={Statement:`SELECT * FROM "copa-america" WHERE "pk" = 'TEAM' AND contains("display_name",'?')`,.Parameters:[{"S":params.id}]}
This variants of statement objects are sent in executeStatement. For executeTransaction and batchExecuteStatement it would be an array of these variants of statement objects.
Implementation
For the implementation of PartiQL based SELECT statements, from the previous blog post, I've continued to develop queries for -
Getting all the teams for Copa America
letpartiqlStmt={Statement:`SELECT * FROM "copa-america" WHERE "pk" = 'TEAM'`,}letresponse=awaitdynamodb.executeStatement(partiqlStmt).promise()returnresponse
Response
Getting all the matches for Copa America
letpartiqlStmt={Statement:`SELECT * FROM "copa-america" WHERE "pk" = 'MATCH'`,}letresponse=awaitdynamodb.executeStatement(partiqlStmt).promise()returnresponse
Response
Getting team info
letpartiqlStmt={Statement:`SELECT * FROM "copa-america" WHERE "pk" = 'TEAM' AND contains("display_name",'${params.id}')`,}letresponse=awaitdynamodb.executeStatement(partiqlStmt).promise()returnresponse
Response
Getting all the matches played by a team
letpartiqlStmt={Statement:`SELECT * FROM "copa-america" WHERE "pk" = 'TEAM' AND contains("display_name",'${params.id}')`,}letresponse=awaitdynamodb.executeStatement(partiqlStmt).promise()returnresponse
Response
This has been implemented with AWS SAM application with NodeJS, the sample code is available on GitHub.
This project contains source code and supporting files for a serverless application that you can deploy with the SAM CLI. It includes the following files and folders.
copa-america-partiql-demo - Code for the application's Lambda function.
events - Invocation events that you can use to invoke the function.
copa-america-partiql-demo/tests - Unit tests for the application code.
template.yaml - A template that defines the application's AWS resources.
Deploy the sample application
The Serverless Application Model Command Line Interface (SAM CLI) is an extension of the AWS CLI that adds functionality…