How to implement DynamoDB single table designs with AWS Amplify (AppSync, Lambda)

In this article, we will explore the implementation of single table designs in an AWS Amplify application using AppSync and Lambda.

You should know some concepts beforehand since this article only focuses on the implementation of single table design. For example:

• how to design single table
• multiple tables in Amplify
• single table vs multiple tables

While Amplify currently only supports multiple table designs, it is still possible to implement single table designs with some modifications as below structure:

Step 1: Define Access Patterns

Let’s assume a use case in which customers can buy products and place orders on an e-commerce website. To structure our partition key and sort key in the early stages, we need to define our access patterns. Let’s create three simple access patterns:

1. Retrieve customer information using the customer ID.
2. Retrieve product information using the product ID.
3. Retrieve order information, including customer and product information, using the order ID.

Step 2: Create a Table in DynamoDB

In the AWS DynamoDB console, we’ll create a table named “ecommerce” and name the partition key as “PK” and the sort key as “SK”. Since we’re using a single table design, we won’t be able to take advantage of the @model directive in Amplify GraphQL that automatically generates tables in DynamoDB.

Step 3: Create graphQL API in Amplify

If you have not initialized the amplify project yet, please run this command:

amplify init

Otherwise, we can start to create a new GraphQL API in our Amplify project by running the following command:

amplify add api
? Select from one of the below mentioned services: GraphQL
? Here is the GraphQL API that we will create. Select a setting to edit or continue Continue
? Choose a schema template: One-to-many relationship (e.g., "Blogs" with "Posts" and "Comments")

The schema template is not important as we will override it by our e-commerce use case. Now let’s override the code in schema.graphql as below. For the sake of the query test, we also create its mutation correspondingly.

# This "input" configures a global authorization rule to enable public access to
# all models in this schema. Learn more about authorization rules here: https://docs.amplify.aws/cli/graphql/authorization-rules
input AMPLIFY { globalAuthRule: AuthRule = { allow: public } } # FOR TESTING ONLY!

type Query {
  getCustomerById(id: ID!): Customer @function(name: "ecommerce-${env}")
  getProductById(id: ID!): Product @function(name: "ecommerce-${env}")
  getOrderById(id: ID!): Order @function(name: "ecommerce-${env}")
}
type Mutation {
  createCustomer(input: createCustomerInput!): Customer @function(name: "ecommerce-${env}")
  createProduct(input: createProductInput!): Product @function(name: "ecommerce-${env}")
  createOrder(input: createOrderInput!): Order @function(name: "ecommerce-${env}")
}
type Customer {
  PK: ID!
  email: String!
}
type Product {
  PK: ID!
  qty: Int!
  price: Float!
  name: String!
  detail: AWSJSON!
}
type Order {
  PK: ID!
  SK: ID!
  type: String!
  amount: Float!
  orderItems: [OrderItem]
}
type OrderItem {
  PK: ID!
  SK: ID!
  price: Float!
  name: String!
  qty: Int!
  type: String!
}
input createCustomerInput {
  email: String!
}
input createProductInput {
  qty: Int!
  price: Float!
  name: String!
  detail: AWSJSON!
}
input createOrderInput {
  amount: Float!
  products: [createOrderItemInput!]!
  customerID: ID!
}
input createOrderItemInput {
  qty: Int!
  price: Float!
  name: String!
  productID: ID!
}

Step 4: Create Lambda function resolver

You may notice the type of Query and Mutation above have @funciton directive which helps to resolve the request and respond between AppSync and DynamoDB. So let’s create this function in Amplify:

amplify add function
? Select which capability you want to add: Lambda function (serverless function)
? Provide an AWS Lambda function name: ecommerce
? Choose the runtime that you want to use: NodeJS
? Choose the function template that you want to use: Hello World
? Do you want to configure advanced settings? No
? Do you want to edit the local lambda function now? No
Successfully added resource ecommerce locally.

Now let’s write some code for this lambda resolve under amplify/backend/function/ecommerce/src directory:

index.js

The index.js file serves as the root for a Lambda function that handles API requests based on the field name and responds with the correct data structure, as defined in the schema.graphql file.

const AWS = require('aws-sdk')
const {v4: uuid} = require('uuid')
const getCustomerById = require('./getCustomerById')
const getProductById = require('./getProductById')
const getOrderById = require('./getOrderById')
const createCustomer = require('./createCustomer')
const createProduct = require('./createProduct')
const createOrder = require('./createOrder')
const docClient = new AWS.DynamoDB.DocumentClient()

exports.handler = async (event) => {
  const {fieldName, arguments} = event
  switch (fieldName) {
    case "getCustomerById":
      return await getCustomerById(arguments, docClient)
    case "getProductById":
      return await getProductById(arguments, docClient)
    case "getOrderById":
      return await getOrderById(arguments, docClient)
    case "createCustomer":
      return await createCustomer(arguments, docClient, uuid)
    case "createProduct":
      return await createProduct(arguments, docClient, uuid)
    case "createOrder":
      return await createOrder(arguments, docClient, uuid)
    default:
      return null
  }
};

getCustomerById.js

Retrieve customer information by its id.

async function getCustomerById({id}, docClient) {
  const params = {
    TableName: "ecommerce",
    Key: {PK: id, SK: id}
  }
  try {
    const {Item} = await docClient.get(params).promise()
    if (Item !== null) {
      return Item
    }
  } catch (err) {
    return err
  }
}
module.exports = getCustomerById

GetproductById.js

Retrieve product information by its id.

async function getProductById({id}, docClient) {
  const params = {
    TableName: 'ecommerce',
    Key: {PK: id, SK: id}
  }  
  try {
    const {Item} = await docClient.get(params).promise()
    if (Item !== null) {
      return Item
    }
  } catch (err) {
    return err
  }
}
module.exports = getProductById

getOrderById.js:

To retrieve order information that includes products and customer information, we will use the docClient.query() method. This method will retrieve all items in the table with the same partition key. The result will be an array of items, so we have written a function called formatOrderResponseData to format the data into the order type schema defined in schema.graphql.

async function getOrderById({id}, docClient) {
  const params = {
    TableName: "ecommerce",
    KeyConditionExpression: `PK = :id`,
    ExpressionAttributeValues: {
      ":id": id
    }
  }
  try {
    const {Items} = await docClient.query(params).promise()
    if (Items.length) {
      return formatOrderResponseData(Items)
    }
  } catch (err) {
    return err
  }
}

function formatOrderResponseData(orderData) {
  let order;
  orderData.forEach(({PK, SK, type, amount, price, name, qty}) => {
    if (type === 'order') {
      order =
        {
          PK,
          SK,
          type,
          amount,
          orderItems: []
        };
    }
    if (type === 'orderItem') {
      order.orderItems.push({
        PK,
        SK,
        price,
        name,
        qty,
        type
      });
    }
  });
  return order;
}

module.exports = getOrderById

createCustomer.js

To create a customer, we need to first define its partition and sort key. The partition and sort key should start with “C#” to indicate that it’s a customer.

async function createCustomer({input:{email}}, docClient, uuid) {
  const customerID = `C#${uuid()}`
  const params = {
    TableName: "ecommerce",
    Item: {
      PK: customerID,
      SK: customerID,
      email,
      type: 'customer',
    },
  }
  const item =  await docClient.put(params).promise()
  if (item !== null){
    return params.Item;
  }
}

module.exports = createCustomer

createProduct.js

To create a product, we can set its partition and sort key that start with “P#” to indicate that it’s a product.

async function createProduct({input: {qty, price, name, detail}}, docClient, uuid) {
  const productID = `P#${uuid()}`;
  const params = {
    TableName : "ecommerce",
    Item: {
      PK: productID,
      SK: productID,
      qty,
      price,
      name,
      detail,
      type: 'product',
    },
  }
  try {
    const item =  await docClient.put(params).promise()
    if (item !== null){
      return params.Item;
    }
  } catch (err) {
    return err
  }
}

module.exports = createProduct

createOrder.js

To create an order, there are three write requests involved:

1. creating the order
2. creating the order item
3. updating the product quantity

The docClient.transactWrite function has atomicity, meaning that if any of the requests fail, all write transactions will be abandoned.

async function createOrder({input: {amount, products, customerID}}, docClient, uuid) {
    const PK = `O#${uuid()}`
    const TableName = 'ecommerce'
    const params = {
      TransactItems: [{
        Put: {
          TableName,
          Item: {
            PK,
            SK: customerID,
            amount,
            type: 'order'
          }
        }
      }].concat(products.map(({productID, qty, price, name}) => [
        {
          Put: {
            TableName,
            Item: {
              PK,
              SK: productID,
              type: 'orderItem',
              qty,
              price,
              name
            }
          }
        },
        {
          Update: {
            TableName,
            Key: {
              PK: productID,
              SK: productID
            },
            UpdateExpression: 'SET qty = qty - :qty',
            ExpressionAttributeValues: {
              ':qty': qty
            },
            ConditionExpression: 'qty >= :qty'
          }
        }
      ])).flat()
    }
  try {
    const item =  await docClient.transactWrite(params).promise()
    if (item !== null){
      return {
        PK,
        SK: customerID,
        amount,
        type: 'order',
        orderItems: products.map(({productID, qty, price, name}) => ({
          PK,
          SK: productID,
          price,
          name,
          qty,
          type: 'orderItem',
        }
        ))
      };
    }
  } catch (err) {
    return err
  }
  }

module.exports = createOrder

Step 5: Amplify push and test the API

We’re almost done! To deploy our code, let’s run the command amplify push in the terminal. Upon successful deployment, we should see our schema in the AppSync console.

The final step in testing our API is to grant the Lambda function permission to read and write to DynamoDB. We need to locate the Lambda function name in the IAM role and add read and write policies for DynamoDB.

Now, we can test our API on the AppSync query page by first creating a customer and a product.

In our DynamoDB, we should be able to find those two new items

Next, let’s test the getCustomerById and getProductById query:

Next, let’s create an order using the newly created customer and product:

The createOrder function will create two items in the database: one for the order itself and another for the order item. Besides the two new items, the quantity in P#ca11cf9c-17ea-4845–8566-fbb9a0961da7 was updated as well.

Finaaly, lets run getOrderById query:

Conclusion

In conclusion, the use of a single table design in DynamoDB requires careful consideration of partition and sort key assignments, as well as extra effort in writing Lambda resolver. However, it can offer outstanding query performance. Therefore, the decision to use single table design depends on whether its benefits outweigh its drawbacks.

If you have any questions, please let me know in the comments!