Building a CRUD API with SAM, PartiQL & DynamoDB

February 7, 2023

Introduction

In this tutorial, we are going to see how to build a CRUD (Create, Read, Update, & Delete) API with node js using an SQL-compatible query language known as PartiQL to communicate with DynamoDB and AWS SAM as the framework. We are going to be looking at a simple case of an appointment crud API letting one create, update, view, and delete appointments from a DynamoDB table.

Prerequisite

In order to properly follow through successfully with this tutorial, you’ll need to have the following:

  1. You need to have an AWS account with administrative access. If you don’t have an AWS account, kindly use the link to create a free trial account for AWS.
  2. An IDE (e.g. VS CODE)
  3. Set up AWS credentials in your IDE
  4. AWS toolkit for VS code
  5. AWS SAM CLI - Install the AWS SAM CLI
  6. Node.js - Install Node.js 18, including the npm package management tool
  7. Postman for testing the API

Create DynamoDB table

Log into the AWS Management Console

While on the console, select Services, next select Database, and then select DynamoDB

In the next screen that displays, click on Tables, then click on Create table

On the screen that follows, fill in the fields as seen below and click Create table

Now we have our DynamoDB table fully setup and can move to the creation of our project.

Setup  Project

Create a new SAM application

Launch VS code and navigate to your project folder (e.g. Appointment)

Then Launch the terminal

In your terminal type ‘sam init’  and hit the enter/return key. You should have a similar output as shown below:

Insert ‘1‘ into the terminal and hit the enter/return key. Your output should be as follows:

Select any template of your choice by inserting the template number into the terminal, in this case, we will be selecting the first template ‘1’ as seen above.

You will be asked to use the most popular runtime and package as shown below, insert N and hit the enter/return key to continue.

In the list of presented environments as seen above, insert the number corresponding to nodejs14.x in our case that will be 14. Hit the enter/return key then you will be asked to insert the package type; insert 1 for zip, and hit the enter/return key. Your output should be similar to the following:

For the starter template, insert ‘1’ then hit the enter/return key. You will be asked a couple of questions as shown below simply enter N and hit the enter/return key for each question till you reach the project name.

For the project name, enter ‘Appointment’ and then hit the enter/return key. Your output should be as follows:

Now we have a sample SAM sample project ready for us to use.

Building the Appointment CRUD API

We are now going to restructure our sample app as follows:

  1. Delete everything inside the sample appointment folder EXCEPT the template.yaml file
  2. Create a new folder called Functions
  3. Create the following files insertAppointment.js, getAppointments.js, updateAppointment.js, deleteAppointment.js inside the Functions folder

Your output should look as follows:

Insert the following code in the insertAppointment.js file:

  
//get table name from the environment virable as in the template.yaml file
const Appointment = process.env.APPOINTMENT_TABLE;
//adding the needful packages
const AWS = require('aws-sdk');
const dynamoDbClient = new AWS.DynamoDB();
const uuid = require('node-uuid');
//function to insert data into the DynamoDB table
exports.insertAppointment = async (event) => {
    const { body } = event;   // It destructures the body payload from event. 
    let parsedBody = JSON.parse(body); // It parses the JSON payload to java script object 
    // The item contains fully order Item. 
    let bookAppointment = {
        id: uuid.v4(),
        name: parsedBody.name,
        appointment_date: parsedBody.appointment_date,
    }
    // We use 'insert' statement to put items into Dynamodb.
    try {
        // stringify object to what PartiQL will accept
        var item = JSON.stringify(bookAppointment);
// replace double quotes with sngle quotes as PartiQL will reject a query with double quotes
        item = item.replaceAll("\"", "'");
        await dynamoDbClient.executeStatement(
            {
                Statement: `INSERT INTO ${Appointment} VALUE ${item}`
            }).promise();
    } catch (err) {
        console.log("Error inserting data", err.message)
    }
    const response = {
        statusCode: 200,
        body: JSON.stringify(bookAppointment)

    };
    return response;
}
  

Insert the following code in the getAppointments.js file:

  
const Appointment = process.env.APPOINTMENT_TABLE;
const AWS = require('aws-sdk'); 
const dynamoDbClient = new AWS.DynamoDB();

async function executeExecuteStatements() {
    // Call DynamoDB's executeStatement API
    try {
        const statement = `select * from  ${Appointment}`
        const executeStatementOutput = await dynamoDbClient.executeStatement({Statement: statement}).promise();
        console.log('Results', executeStatementOutput);
        return executeStatementOutput;
    } catch (err) {
        console.log('Failure', err.message);
    }
}

exports.getAppointments = async (event) => {
    if (event.httpMethod !== 'GET') {
        throw new Error(`getAllItems only accept GET method, you tried: ${event.httpMethod}`);
    }

    console.info('received:', event);
    let items = {};

    // It calls the fetchAllOrders method above
    try {
        items = await executeExecuteStatements();
    } catch (err) {
        console.log('Failure', err.message);
    }

    // It returns the items to client with status code: 200
    const response = {
        statusCode: 200,
        body: JSON.stringify(items),
    };
    return response;
};
  

Insert the following code in the updateAppointment.js file:

  
const Appointment = process.env.APPOINTMENT_TABLE;
const AWS = require('aws-sdk');
const dynamoDbClient = new AWS.DynamoDB();

exports.updateAppointment = async (event) => {
    if (event.httpMethod !== 'PUT') {
        throw new Error(`updateItem only accept PUT method, you tried: ${event.httpMethod}`);
    }
    let appointmentId = JSON.stringify(event.pathParameters.appointmentId); // It gets the OrderId from parameter.
    appointmentId =appointmentId.replaceAll("\"", "'"); 
    const { body} = event;    // It destructures the 'body' payload from event
    let parsedBody = JSON.parse(body);  // It parses the JSON payload to java script object
    console.log('Date parsed', JSON.stringify(parsedBody.appointment_date));
    let appointment_date = JSON.stringify(parsedBody.appointment_date)
    appointment_date = appointment_date.replaceAll("\"", "'"); 

    // Calls the update expression to update the item
    try {
        await dynamoDbClient.executeStatement(
            {
                Statement: `UPDATE ${Appointment} SET appointment_date = ${appointment_date} WHERE id = ${appointmentId}`  
            }).promise();
        console.log("Success for updating Item")
    } catch (err) {
        console.log("Error during update", err.message)
    }

    const response = {
        statusCode: 200,
        body: JSON.stringify(parsedBody)
    };
    return response;
}
  

Insert the following code in the deleteAppointment.js file:

  
const Appointment = process.env.APPOINTMENT_TABLE;
const AWS = require('aws-sdk');
const dynamoDbClient = new AWS.DynamoDB();

exports.deleteAppointment = async (event) => {
    if (event.httpMethod !== 'DELETE') {
        throw new Error(`DELETEItem only accept DELETE method, you tried: ${event.httpMethod}`);
    }
    let appointmentId = JSON.stringify(event.pathParameters.appointmentId); // It gets the OrderId from parameter.
    appointmentId =appointmentId.replaceAll("\"", "'"); 
    const { body} = event;    // It destructures the 'body' payload from event
    let parsedBody = JSON.parse(body);  // It parses the JSON payload to java script object

    // Calls the delete expression to update the item
    try {
        await dynamoDbClient.executeStatement(
            {
               Statement: `DELETE FROM ${Appointment} WHERE id = ${appointmentd}`  
            }).promise();
        console.log('ITEM DELETED');
    } catch (err) {
        console.log("Error during delete", err.message)
    }

    const response = {
        statusCode: 200,
        body: ('You have canceled your Appointment',appointmentId)
    };
    return response;
}
  

Update the template.yaml file so it looks exactly as below:

  
AWSTemplateFormatVersion: '2010-09-09'
Transform: AWS::Serverless-2016-10-31
Description: >
  CRUD-DYNAMODB-PARTIQL

  Sample SAM Template for CRUD-DYNAMODB-PARTIQL
#More info about Globals: https://github.com/awslabs/serverless-application-model/blob/master/docs/globals.rst
Globals:
  Function:
    Timeout: 3
    Tracing: Active
  Api:
    TracingEnabled: true

Resources:
# DynamoDB Table
  AppointmentTable:
    Type: AWS::DynamoDB::Table
    Properties: 
      TableName: Appointment
      AttributeDefinitions: 
        - AttributeName: id
          AttributeType: S
      KeySchema: 
        - AttributeName: id
          KeyType: HASH
      ProvisionedThroughput: 
        ReadCapacityUnits: 5
        WriteCapacityUnits: 5
# Appointment API
  AppointmentApi:
    Type: AWS::Serverless::Api
    Properties:
      StageName: Dev
      Cors:
        AllowMethods: "'POST, GET, UPDATE, DELETE'"
        AllowHeaders: "'X-Forwarded-For'"
        AllowOrigin: "'*'"
        MaxAge: "'600'"
      DefinitionBody:
        'Fn::Transform':
          Name: 'AWS::Include'
          Parameters:
            Location: './api.yaml'

  ApiGwExecutionRole: 
    Type: "AWS::IAM::Role"
    Properties: 
      AssumeRolePolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - 
            Effect: "Allow"
            Principal: 
              Service: 
                - "apigateway.amazonaws.com"
                - "lambda.amazonaws.com"
            Action: 
              - "sts:AssumeRole"
      Path: "/"

  ApiGwExecutionPolicy: 
    Type: "AWS::IAM::Policy"
    Properties: 
      PolicyName: "apigw-execution-policy"
      PolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          Action:
            - lambda:*
          Effect: Allow
          Resource:
            - !GetAtt GetAppointments.Arn
            - !GetAtt InsertAppointment.Arn
            - !GetAtt UpdateAppointment.Arn
            - !GetAtt DeleteAppointment.Arn
            
      Roles: 
        - Ref: "ApiGwExecutionRole"

  GetAppointments:
    Type: AWS::Serverless::Function 
    Properties:
      CodeUri: functions/
      Handler: getAppointments.getAppointments
      Runtime: nodejs18.x
      Environment:
        Variables:
          APPOINTMENT_TABLE: !Ref AppointmentTable
      Policies:
        Statement:
          - Effect: Allow
            Action:
              - dynamodb:PartiQLSelect
              - dynamodb:PartiQLInsert 
              - dynamodb:PartiQLUpdate
              - dynamodb:PartiQLDelete
            Resource:
              - !Sub 
                - 'arn:aws:dynamodb:*:*:table/${Table}'
                - { Table: !Ref AppointmentTable }
          - Effect: Allow
            Action:
              - logs:*
            Resource:
              - "*"    
  InsertAppointment:
    Type: AWS::Serverless::Function 
    Properties:
      CodeUri: functions/
      Handler: insertAppointment.insertAppointment
      Runtime: nodejs18.x
      Environment:
        Variables:
          APPOINTMENT_TABLE: !Ref AppointmentTable
      Policies:
        Statement:
          - Effect: Allow
            Action:
              - dynamodb:PartiQLInsert
            Resource:
              - !Sub 
                - 'arn:aws:dynamodb:*:*:table/${Table}'
                - { Table: !Ref AppointmentTable }
          - Effect: Allow
            Action:
              - logs:*
            Resource:
              - "*"    
  UpdateAppointment:
    Type: AWS::Serverless::Function 
    Properties:
      CodeUri: functions/
      Handler: updateAppointment.updateAppointment
      Runtime: nodejs18.x
      Environment:
        Variables:
          APPOINTMENT_TABLE: !Ref AppointmentTable
      Policies:
        Statement:
          - Effect: Allow
            Action:
              - dynamodb:PartiQLUpdate
            Resource:
              - !Sub 
                - 'arn:aws:dynamodb:*:*:table/${Table}'
                - { Table: !Ref AppointmentTable }
          - Effect: Allow
            Action:
              - logs:*
            Resource:
              - "*"    

  DeleteAppointment:
    Type: AWS::Serverless::Function 
    Properties:
      CodeUri: functions/
      Handler: deleteAppointment.deleteAppointment
      Runtime: nodejs18.x
      Environment:
        Variables:
          APPOINTMENT_TABLE: !Ref AppointmentTable
      Policies:
        Statement:
          - Effect: Allow
            Action:
              - dynamodb:PartiQLDelete
            Resource:
              - !Sub 
                - 'arn:aws:dynamodb:*:*:table/${Table}'
                - { Table: !Ref AppointmentTable }
          - Effect: Allow
            Action:
              - logs:*
            Resource:
              - "*"    
Outputs:

  DynamoDBTableName:
    Description: "DynamoDB Table Name"
    Value: !Ref AppointmentTable

  GetAppointments:
    Description: "GetAppointments Lambda Function ARN"
    Value: !GetAtt GetAppointments.Arn

  InsertAppointment:
    Description: "InsertAppointment Lambda Function ARN"
    Value: !GetAtt InsertAppointment.Arn

  UpdateAppointment:
    Description: "UpdateAppointment Lambda Function ARN"
    Value: !GetAtt UpdateAppointment.Arn

  DeleteAppointment:
    Description: "DeleteAppointment Lambda Function ARN"
    Value: !GetAtt DeleteAppointment.Arn

  AppointmentApiEndpoint:
    Description: "API Gateway endpoint URL for Dev stage for Appointment Get function"
    Value: !Sub "https://${AppointmentApi}.execute-api.${AWS::Region}.amazonaws.com/Dev/appointment/"
  

Create a file in the root application called api.yaml. Your app structure should now look as follows:

Insert the following code  in the api.yaml file you recently created:

  
---
swagger: "2.0"
info:
  version: "1.0"
  title: "Appointments"
basePath: "/Dev"
schemes:
- "https"
paths:
  /appointment:
    get:
      responses: {}
      x-amazon-apigateway-integration:
        credentials: 
          Fn::GetAtt: [ ApiGwExecutionRole, Arn ]
        type: "aws_proxy"
        httpMethod: "POST"
        uri:
          Fn::Sub: arn:aws:apigateway:${AWS::Region}:lambda:path/2015-03-31/functions/${GetAppointments.Arn}/invocations
        passthroughBehavior: "when_no_match"

    post:
      consumes:
      - "application/json"
      produces:
      - "application/json"
      responses: {}
      x-amazon-apigateway-integration:
        type: "aws_proxy"
        credentials: 
          Fn::GetAtt: [ ApiGwExecutionRole, Arn ]
        httpMethod: "POST"
        uri:
          Fn::Sub: arn:aws:apigateway:${AWS::Region}:lambda:path/2015-03-31/functions/${InsertAppointment.Arn}/invocations
        responses:
          default:
            statusCode: "200"
        passthroughBehavior: "when_no_match"

  /appointment/{appointmentId+}:
    put:
      produces:
      - "application/json"
      parameters:
      - name: "appointmentId"
        in: "path"
        required: true
        type: "string"
      responses: {}
      x-amazon-apigateway-integration:
        credentials: 
          Fn::GetAtt: [ ApiGwExecutionRole, Arn ]
        httpMethod: "POST"
        uri:
          Fn::Sub: arn:aws:apigateway:${AWS::Region}:lambda:path/2015-03-31/functions/${UpdateAppointment.Arn}/invocations
        responses:
          default:
            statusCode: "200"
        passthroughBehavior: "when_no_match"
        type: "aws_proxy"

    delete:
      responses: {}
      x-amazon-apigateway-integration:
        credentials: 
          Fn::GetAtt: [ ApiGwExecutionRole, Arn ]
        type: "aws_proxy"
        httpMethod: "POST"
        uri:
          Fn::Sub: arn:aws:apigateway:${AWS::Region}:lambda:path/2015-03-31/functions/${DeleteAppointment.Arn}/invocations
        passthroughBehavior: "when_no_match"
  

Deploy to AWS

Now that we have everything ready, we will move over to the deployment phase.

Navigate to the project folder using the cd command and install the uui dependency that we used in the insertAppointment.js file using npm install as shown below:

Navigate to the Functions folder and create a package.json file as shown below, validating each field by hitting the return/enter key.

Update the newly created package.json as follows to include dependencies:

  
{
  "name": "functions",
  "version": "1.0.0",
  "description": "",
  "main": "insertAppointment.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "aws-sdk": "^2.0.9",
    "node-uuid": "^1.4.8"
    }
}
  

Navigate back to the root folder using the command cd

Build the app using sam build, your output should be similar to the one below:

Next, we use the command sam deploy —guided and follow the steps as seen below. You will be presented with a couple of questions; feel free to modify your response, then hit the enter/return key to proceed to the next question.

To finalize the deployment, insert y, then hit the enter/return key to proceed.

Your output should be as follows:

deployment output

Verify resources in AWS

To verify our functions were created, go to the AWS console, click on services, then click on compute, and then click on Lambda.

You should be able to see the four functions we created as seen below:

To check the gateway, go to services in the AWS console, click on Networking & content delivery, then click on API Gateway.

You should find the created API as shown below:

Test your API

Now that we have it all in place, its time to see if our API works.

First, we retrieve the API URL. You can do this via the following methods:

Through the output you got after deployment on step 7 of Deploy to AWS

Go to your API Gateway as you did in step 2 to verify resources in AWS. Click on the Appointment API; select Stages, select Dev, and then select Get, Post, Delete, or Put, depending on which API you want to retrieve.

Note: All API URLs will be the same except for Delete and Put, which will require a parameter (id) when calling the API.

Launch postman and sign in. Click on the + symbol next to the overview tab to open a new tab.

To test the GET API, select GET from the dropdown menu, enter the get URL retrieved earlier then click Send.

You should see a similar screen to the one below once you are done. Notice we have an empty list of items (4) returned because our Appointment table is empty.

To test the POST API, select POST from the dropdown list, do not change the API URL, click on Body, select Raw and insert the following code:

  
{
"name": "mandela",
"appointment_date": "2023-01-26"
}
  

Click Send to process the request. Your output should be as follows:

As seen in the image above (5) you will observe we have an auto-generated id, save the id as we will be needing it in the next step.

Repeat this step to insert multiple data or records into the Appointment DynamoDb.

To check the newly added record, repeat Step 3 and this time your output should be as follows:

You could also check the DynamoDB Table by going to the AWS console and then Services. Then click Databases, and then click on DynamoDB.

Click on Tables to the left, select the Appointment table to the right, then click on Actions, and then select Explore items.

Your output should be as seen below:

To test the PUT API, select PUT from the dropdown list, add the saved id in Step 4 to the API URL, click on Body, select Raw, and update the code (we will be updating the date) as seen below:

  
{
"appointment_date": "2023-02-26"
}
  

Click on Send to process the request. Your output should be as follows:

If you verify that the update was successful using Step 3 then your output should literally be as follows:

To test the DELETE API, select DELETE from the dropdown list, add the saved id in Step 4 to the API URL, and then click on Send to process the request. Your results should be as follows:

You can verify what you have with the results shown in  Step 4; they should be exactly the same.

Now we got a fully working API to interact with our DynamoDB table using PartiQL, you could grab the complete code and try it out here.

Conclusion

In this tutorial, we looked at how to create and test a CRUD API using:

  • The AWS Console
  • VS code as our IDE
  • SAM for Infrastructure as Code
  • PartiQL and DynamoDB
  • Postman for API testing

Feel free to download a copy of the code and reach out via Linkedin or Twitter in case you have any questions.

References

Access free book

The dream team

At Serverless Guru, we're a collective of proactive solution finders. We prioritize genuineness, forward-thinking vision, and above all, we commit to diligently serving our members each and every day.

See open positions
Akah Mandela
Akah Mandela
Serverless Developer
An IT enthusiast who uses innovation, creativity, thinking “out of the box” to provide software-based solutions to solve business problems.

Looking for skilled architects & developers?

Join businesses around the globe that trust our services. Let's start your serverless journey. Get in touch today!
Ryan Jones
Founder
Speak to a Guru
Mason Toberny
Head of Enterprise Accounts
Speak to a Guru

More from Serverless Guru

How to Build a Locally Hosted Chatbot with Amazon Bedrock, LangChain, and Streamlit

January 29, 2024
Learn More

Cloud Network Innovations from AWS in 2023

December 29, 2023
Learn More

Serverless Cost Insights - Part 2: Expert Tips to Maximize Serverless Cost Savings

December 29, 2023
Learn More

Join the Community

Gather, share, and learn about AWS and serverless with enthusiasts worldwide in our open and free community.