GraphQL Single Table Design with DynamoDB and AWS AppSync

SingleTableAppSync.md

GraphQL

GraphQL Schema

type Customer {
  id: ID!
  email: String!
}

type Invoice {
  id: ID!
  amount: Float!
  date: String!
}

enum Method {
  Express
  Standard
}

type Order {
  id: ID!
  amount: Float!
  date: String!
}

type OrderItem {
  id: ID!
  qty: Int!
  price: Float!
  orderId: String
}

type Query {
  getCustomerById(customerId: ID!): Customer
  getProductById(warehouseItemId: ID!): WarehouseItem
  getWarehouseById(warehouseId: ID!): Warehouse
  getInventoryByProductId(productId: ID!): [WarehouseItem]
  getOrderById(orderId: ID!): Order
  getOrderByIdFromMain(orderId: ID!): Order
  getShipmentsByOrderId(orderId: ID!): [Shipment]
  getOrderHistoryByDateRange(customerId: ID!, beginning: String!, ending: String!): [OrderItem]
}

type ShipItem {
  id: ID!
  qty: Int!
}

type Shipment {
  id: ID!
  address: AWSJSON!
  method: Method!
}

type Warehouse {
  id: ID!
  address: AWSJSON!
}

type WarehouseItem {
  id: ID!
  qty: Int!
  price: Float!
  detail: AWSJSON!
}

GraphQL Queries

query getOrderHistoryByID {
  getOrderHistoryById(orderId: "O#277") {
    date
    id
    amount
  }
}

query getOrderHistoryByDateRange {
  getOrderHistoryByDateRange(
    customerId: "C#71",
    beginning: "2020-08-22T13:30:19",
    ending: "2020-08-26T13:30:19"
  ) {
    id
    qty
    price
    orderId
  }
}

query getShipmentsbyOrderId {
  getShipmentsByOrderId(orderId: "O#135") {
    id
    method
    address
  }
}

query getOrderById {
  getOrderById(orderId: "O#277") {
    id
    amount
    date
  }
}

query getInventoryById {
  getInventoryByProductId(productId: "P#42") {
    id
    price
    qty
    detail
  }
}

query getProductById {
  getProductById(warehouseItemId: "P#42") {
    qty
    price
    detail
  }
}

query getCustomerByCustomerId {
  getCustomerById(customerId: "C#34") {
    id
    email
  }
}

Lambda

index.js

const getCustomerById = require('./getCustomerById')
const getProductById = require('./getProductById')
const getWarehouseById = require('./getWarehouseById')
const getInventoryByProductId = require('./getInventoryByProductId')
const getOrderById = require('./getOrderById')
const getShipmentsByOrderId = require('./getShipmentsByOrderId')
const getOrderHistoryById = require('./getOrderHistoryById')
const getOrderHistoryByDateRange = require('./getOrderHistoryByDateRange')

exports.handler = async (event) => {
    const { arguments, info: { fieldName } }= event
    const { customerId, warehouseItemId, warehouseId, productId, orderId, beginning, ending } = arguments

    switch(fieldName) {
        case "getCustomerById":
            return await getCustomerById(customerId)
        case "getProductById":
            return await getProductById(warehouseItemId)
        case "getWarehouseById":
            return await getWarehouseById(warehouseId)
        case "getInventoryByProductId":
            return await getInventoryByProductId(productId)
        case "getOrderById":
            return await getOrderById(orderId)
        case "getOrderByIdFromMain":
            return await getOrderById(orderId)
        case "getShipmentsByOrderId":
            return await getShipmentsByOrderId(orderId)
        case "getOrderHistoryById":
            return await getOrderHistoryById(orderId)
        case "getOrderHistoryByDateRange":
            return await getOrderHistoryByDateRange(customerId, beginning, ending)
        default:
            return null
    }
    
};

getCustomerById.js

const AWS = require('aws-sdk')
const docClient = new AWS.DynamoDB.DocumentClient()

async function getCustomerById(customerId) {
  var params = {
    TableName : process.env.WH_TABLE,
    Key: { PK: customerId, SK: customerId }
  }
  try {
    const { Item } = await docClient.get(params).promise()
    return { email: Item.email, id: Item.PK }
  } catch (err) {
    return err
  }
}

module.exports = getCustomerById

getInventoryByProductId.js

const AWS = require('aws-sdk')
const docClient = new AWS.DynamoDB.DocumentClient()

async function getInventoryByProductId(productId) {
  const params = {
    TableName : process.env.WH_TABLE,
    KeyConditionExpression: `PK = :productId and begins_with(SK, :sk)`,
    ExpressionAttributeValues: {
      ':productId': productId,
      ':sk': "W#"
    }
  }

  try {
    const data = await docClient.query(params).promise()
    const items = data.Items.map(d => {
        d.id = d.PK
        return d
    })
    return items
  } catch (err) {
    console.log('error from getInventoryByProductId: ', err)
    return err
  }
}

module.exports = getInventoryByProductId

getOrderById.js

const AWS = require('aws-sdk')
const docClient = new AWS.DynamoDB.DocumentClient()

async function getOrderById(orderId) {
  const params = {
    TableName : process.env.WH_TABLE,
    KeyConditionExpression: `PK = :orderId and begins_with(SK, :sk)`,
    ExpressionAttributeValues: {
      ':orderId': orderId,
      ':sk': "C"
    }
  }

  try {
    const data = await docClient.query(params).promise()
    const order = data.Items[0]
    order.id = order.PK
    return order
  } catch (err) {
    console.log('error from getInventoryByProductId: ', err)
    return err
  }
}

module.exports = getOrderById

getProductById.js

const AWS = require('aws-sdk')
const docClient = new AWS.DynamoDB.DocumentClient()

async function getProductById(warehouseItemId) {
  var params = {
    TableName : process.env.WH_TABLE,
    KeyConditionExpression: `PK = :warehouseItemId and begins_with(SK, :sk)`,
    ExpressionAttributeValues: {
      ':warehouseItemId': warehouseItemId,
      ':sk': "W#"
    }
  }
  
  try {
    const { Items } = await docClient.query(params).promise()
    return Items[0]
  } catch (err) {
    console.log('error fetching from DDB: ', err)
    return err
  }
}

module.exports = getProductById

getShipmentsByOrderID.js

const AWS = require('aws-sdk')
const docClient = new AWS.DynamoDB.DocumentClient()

async function getShipmentsByOrderId(orderId) {
  const params = {
    TableName : process.env.WH_TABLE,
    KeyConditionExpression: `PK = :orderId and begins_with(SK, :sk)`,
    ExpressionAttributeValues: {
      ':orderId': orderId,
      ':sk': "S#"
    }
  }

  try {
    const data = await docClient.query(params).promise()
    const items = data.Items.map(d => {
        d.id = d.PK
        return d
    })
    return items
  } catch (err) {
    return err
  }
}

module.exports = getShipmentsByOrderId

getWarehouseByID.js

const AWS = require('aws-sdk')
const docClient = new AWS.DynamoDB.DocumentClient()

async function getWarehouseById(warehouseId) {
  var params = {
    TableName : process.env.WH_TABLE,
    Key: { PK: warehouseId, SK: warehouseId }
  }
  try {
    const { Item } = await docClient.get(params).promise()
    return { address: Item.address, id: Item.PK }
  } catch (err) {
    return err
  }
}

module.exports = getWarehouseById

getOrderHistoryByDateRange.js

const AWS = require('aws-sdk')
const docClient = new AWS.DynamoDB.DocumentClient()

async function getOrderHistoryByDateRange(customerId, beginning, ending) {
  console.log('beginning: ', beginning)
  console.log('ending: ', ending)
  const params = {
    IndexName: "GSI2",
    TableName : process.env.WH_TABLE,
    ExpressionAttributeNames:{
        "#GSI2PK": "GSI2PK",
        "#GSI2SK": "GSI2SK"
    },
    KeyConditionExpression: `#GSI2PK = :customerId and #GSI2SK BETWEEN :beginningValue AND :endingValue`,
    ExpressionAttributeValues: {
      ':customerId': customerId,
      ':beginningValue': beginning,
      ':endingValue': ending
    }
  }

  try {
    const data = await docClient.query(params).promise()
    const Items = data.Items.map(d => {
      d.id = `${d.GSI2PK}_${d.GSI2SK}`
      d.orderId = d.PK
      return d
    }) 
    console.log("ITEMS:::", Items)
    return Items
  } catch (err) {
    console.log('error from getOrderHistoryById: ', err)
    return err
  }
}

module.exports = getOrderHistoryByDateRange

Table

Saw your twitch stream with Rick. Really amazing stuff. One question I had though was, in this example, why is the invoice not part of the customer graphql type even though they are related, is this a limitation of using single table design?

How to enforce non key field to be unique for example some hypothetical unique name to an order ?

how did you set the type column that has (!) beside it, the one with the entity table names data?

how did you set the type column that has (!) beside it, the one with the entity table names data?

I believe this is because that is a dynamoDB reserved word.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ReservedWords.html

I just watched your talk with Rick - great video and presentation!

But tbh, I don't really understand the use of graphql in this example, since your graphql object types are fairly "flat", meaning you only have scalar fields, no relations - maybe you could help me, trying to understand it.

I mean, your graphql queries could also be represented as simple API Gateway endpoints like /product/:id or /customer/:id that you "code out" manually, defining the exact query and result-set that's going to be sent to the client (your ìtemsobject) - what you basically do in your lambdas?

I thought one benefit of graphql is to define the queries on the client-side, like "give me details of order 123 and optionally include all invoices of that order as well as optionally all products - and from the products, only return the id value.

But this would need something like

type Order {
  id: ID!
  amount: Float!
  date: String!
  invoices: [Invoice]
  products: [Product]
}

Could you please provide an example of how to work with nested entities?

Thank you very much!

Could you please provide an example of how to work with nested entities?

You can bind lambda resolvers to entity fields as well. So if you look at Customer type, you can resolve some of its fields to lambdas instead. Then the result from the getCustomerById would be overwritten by the field resolver. And if your Customer type references an Order you can use the same principle to get that.

Could you please provide an example of how to work with nested entities?

You can bind lambda resolvers to entity fields as well. So if you look at Customer type, you can resolve some of its fields to lambdas instead. Then the result from the getCustomerById would be overwritten by the field resolver. And if your Customer type references an Order you can use the same principle to get that.

This comment saved me a lot of time.