DEV Community

loading...
Cover image for DynamoDB with PartiQL (Part - 2)
AWS Community ASEAN

DynamoDB with PartiQL (Part - 2)

Jones Zachariah Noel
AWS Serverless | VueJS | NodeJS | Software Engineer | Full-stack developer | Project manager
・4 min read

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.

Key takeaways from this blog post

  • Understanding of DynamoDB SDK for PartiQL execution.
  • Building and structuring parameters.

Understanding of DynamoDB SDK for PartiQL execution

DynamoDB SDK provides support for PartiQL with the APIs - executeStatement, executeTransaction and for batch statements batchExecuteStatement.

executeStatement is used to execute read operation and singleton write operation for the data in DynamoDB.

let partiqlStmt = {
        Statement: `SELECT * FROM "copa-america" WHERE "pk" = 'TEAM'`,
    }
let response = await dynamodb.executeStatement(partiqlStmt).promise()
return response
Enter fullscreen mode Exit fullscreen mode

executeTransaction is used to execute transaction reads or writes and since it is based on transactional, we can execute multiple statements also.

let partiqlStmt = [{
        Statement: `SELECT * FROM "copa-america" WHERE "pk" = 'MATCH'`,
    }]
let response = await dynamodb.executeTransaction(partiqlStmt).promise()
return response
Enter fullscreen mode Exit fullscreen mode

batchExecuteStatement is executeStatement with the feasibility of executing multiple statements.

    let partiqlInsertParams = {
        Statements: []
    }
    for (let team of event.teams) {
        let teamParams = {
            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
        }
        let partiqlStmt = {
            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)
    }
    let response = await dynamodb.batchExecuteStatement(partiqlInsertParams).promise()
    return response
Enter fullscreen mode Exit fullscreen mode

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.

JSON structure

{
      Statement: 'STRING_VALUE', /* required */
      Parameters: [
        { /* AttributeValue */
          B: Buffer.from('...') || 'STRING_VALUE' /* Strings will be Base-64 encoded on your behalf */,
          BOOL: true || false,
          BS: [
            Buffer.from('...') || 'STRING_VALUE' /* Strings will be Base-64 encoded on your behalf */,
            /* more items */
          ],
          L: [
            /* recursive AttributeValue */,
            /* more items */
          ],
          M: {
            '<AttributeName>': /* recursive AttributeValue */,
            /* '<AttributeName>': ... */
          },
          N: 'STRING_VALUE',
          NS: [
            'STRING_VALUE',
            /* more items */
          ],
          NULL: true || false,
          S: 'STRING_VALUE',
          SS: [
            'STRING_VALUE',
            /* more items */
          ]
        },
        /* more items */
      ]
    }
Enter fullscreen mode Exit fullscreen mode

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.

    let partiqlStmt = {
        Statement: `SELECT * FROM "copa-america" `,
    }
Enter fullscreen mode Exit fullscreen mode

PartiQL statement with parameters, and the parameters been set in Statement.

    let partiqlStmt = {
        Statement: `SELECT * FROM "copa-america" WHERE "pk" = 'TEAM' AND contains("display_name",'${params.id}')`,
    }
Enter fullscreen mode Exit fullscreen mode

PartiQL statement with parameters, and the parameters been set in Parameters attribute.

    let partiqlStmt = {
        Statement: `SELECT * FROM "copa-america" WHERE "pk" = 'TEAM' AND contains("display_name",'?')`,.
        Parameters: [{
                  "S":  params.id
             }
        ]
    }
Enter fullscreen mode Exit fullscreen mode

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
let partiqlStmt = {
        Statement: `SELECT * FROM "copa-america" WHERE "pk" = 'TEAM'`,
    }
let response = await dynamodb.executeStatement(partiqlStmt).promise()
return response
Enter fullscreen mode Exit fullscreen mode

Response
all the teams for Copa America

  • Getting all the matches for Copa America
let partiqlStmt = {
        Statement: `SELECT * FROM "copa-america" WHERE "pk" = 'MATCH'`,
    }
let response = await dynamodb.executeStatement(partiqlStmt).promise()
return response
Enter fullscreen mode Exit fullscreen mode

Response
all the matches for Copa America

  • Getting team info
let partiqlStmt = {
        Statement: `SELECT * FROM "copa-america" WHERE "pk" = 'TEAM' AND contains("display_name",'${params.id}')`,
    }
let response = await dynamodb.executeStatement(partiqlStmt).promise()
return response
Enter fullscreen mode Exit fullscreen mode

Response
team info

  • Getting all the matches played by a team
let partiqlStmt = {
        Statement: `SELECT * FROM "copa-america" WHERE "pk" = 'TEAM' AND contains("display_name",'${params.id}')`,
    }
let response = await dynamodb.executeStatement(partiqlStmt).promise()
return response
Enter fullscreen mode Exit fullscreen mode

Response
all the matches played by a team

This has been implemented with AWS SAM application with NodeJS, the sample code is available on GitHub.

GitHub logo zachjonesnoel / copa-america-partiql-demo

Examples of PartiQL with DynamoDB on NodeJS SDK.

copa-america-partiql-demo

Know about PartiQL and AWS DynamoDB (https://dev.to/awscommunity-asean/dynamodb-with-partiql-2693)

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
  • Getting team info
  • Getting all the matches for Copa America
  • Getting all the matches played by a team

A detailed implementation is available on https://dev.to/awscommunity-asean/dynamodb-with-partiql-part-2-47fe

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…

Discussion (0)