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:
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.
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:
Delete everything inside the sample appointment folder EXCEPT the template.yaml file
Create a new folder called Functions
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:
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:
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 toverify 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:
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.
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.
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:
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.
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:
Delete everything inside the sample appointment folder EXCEPT the template.yaml file
Create a new folder called Functions
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:
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:
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 toverify 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:
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.
%20(1).svg)
.webp)
.svg)
