Data access patterns

You are currently viewing the legacy GraphQL Transformer documentation. View latest documentation

In the DynamoDB documentation for modeling relational data in a NoSQL database, there is an in depth example of 17 access patterns from the First Steps for Modeling Relational Data in DynamoDB page.

	Most common/import access patterns in our organization
1	Look up employee details by employee ID
2	Query employee details by employee name
3	Find an employee's phone number(s)
4	Find a customer's phone number(s)
5	Get orders for a given customer within a given date range
6	Show all open orders within a given date range across all customers
7	See all employees recently hired
8	Find all employees working in a given warehouse
9	Get all items on order for a given product
10	Get current inventories for a given product at all warehouses
11	Get customers by account representative
12	Get orders by account representative and date
13	Get all items on order for a given product
14	Get all employees with a given job title
15	Get inventory by product and warehouse
16	Get total product inventory
17	Get account representatives ranked by order total and sales period

In this example, you will learn how to support these data access patterns using GraphQL, AWS Amplify, and the GraphQL Transform library. This example has the following types:

Warehouse
Product
Inventory
Employee
AccountRepresentative
Customer

The following schema introduces the required keys and connections so that you can support these access patterns:

1type Order
2  @model
3  @key(
4    name: "byCustomerByStatusByDate"
5    fields: ["customerID", "status", "date"]
6  )
7  @key(name: "byCustomerByDate", fields: ["customerID", "date"])
8  @key(
9    name: "byRepresentativebyDate"
10    fields: ["accountRepresentativeID", "date"]
11  )
12  @key(name: "byProduct", fields: ["productID", "id"]) {
13  id: ID!
14  customerID: ID!
15  accountRepresentativeID: ID!
16  productID: ID!
17  status: String!
18  amount: Int!
19  date: String!
20}
21
22type Customer
23  @model
24  @key(name: "byRepresentative", fields: ["accountRepresentativeID", "id"]) {
25  id: ID!
26  name: String!
27  phoneNumber: String
28  accountRepresentativeID: ID!
29  ordersByDate: [Order] @connection(keyName: "byCustomerByDate", fields: ["id"])
30  ordersByStatusDate: [Order]
31    @connection(keyName: "byCustomerByStatusByDate", fields: ["id"])
32}
33
34type Employee
35  @model
36  @key(
37    name: "newHire"
38    fields: ["newHire", "id"]
39    queryField: "employeesNewHire"
40  )
41  @key(
42    name: "newHireByStartDate"
43    fields: ["newHire", "startDate"]
44    queryField: "employeesNewHireByStartDate"
45  )
46  @key(name: "byName", fields: ["name", "id"], queryField: "employeeByName")
47  @key(
48    name: "byTitle"
49    fields: ["jobTitle", "id"]
50    queryField: "employeesByJobTitle"
51  )
52  @key(name: "byWarehouse", fields: ["warehouseID", "id"]) {
53  id: ID!
54  name: String!
55  startDate: String!
56  phoneNumber: String!
57  warehouseID: ID!
58  jobTitle: String!
59  newHire: String! # You have to use String type, because Boolean types cannot be sort keys
60}
61
62type Warehouse @model {
63  id: ID!
64  employees: [Employee] @connection(keyName: "byWarehouse", fields: ["id"])
65}
66
67type AccountRepresentative
68  @model
69  @key(
70    name: "bySalesPeriodByOrderTotal"
71    fields: ["salesPeriod", "orderTotal"]
72    queryField: "repsByPeriodAndTotal"
73  ) {
74  id: ID!
75  customers: [Customer] @connection(keyName: "byRepresentative", fields: ["id"])
76  orders: [Order] @connection(keyName: "byRepresentativebyDate", fields: ["id"])
77  orderTotal: Int
78  salesPeriod: String
79}
80
81type Inventory
82  @model
83  @key(
84    name: "byWarehouseID"
85    fields: ["warehouseID"]
86    queryField: "itemsByWarehouseID"
87  )
88  @key(fields: ["productID", "warehouseID"]) {
89  productID: ID!
90  warehouseID: ID!
91  inventoryAmount: Int!
92}
93
94type Product @model {
95  id: ID!
96  name: String!
97  orders: [Order] @connection(keyName: "byProduct", fields: ["id"])
98  inventories: [Inventory] @connection(fields: ["id"])
99}

Now that you have the schema created, let's create the items in the database that you will be operating against:

1# first
2mutation createWarehouse {
3  createWarehouse(input: { id: "1" }) {
4    id
5  }
6}
7
8# second
9mutation createEmployee {
10  createEmployee(
11    input: {
12      id: "amanda"
13      name: "Amanda"
14      startDate: "2018-05-22"
15      phoneNumber: "6015555555"
16      warehouseID: "1"
17      jobTitle: "Manager"
18      newHire: "true"
19    }
20  ) {
21    id
22    jobTitle
23    name
24    newHire
25    phoneNumber
26    startDate
27    warehouseID
28  }
29}
30
31# third
32mutation createAccountRepresentative {
33  createAccountRepresentative(
34    input: { id: "dabit", orderTotal: 400000, salesPeriod: "January 2019" }
35  ) {
36    id
37    orderTotal
38    salesPeriod
39  }
40}
41
42# fourth
43mutation createCustomer {
44  createCustomer(
45    input: {
46      id: "jennifer_thomas"
47      accountRepresentativeID: "dabit"
48      name: "Jennifer Thomas"
49      phoneNumber: "+16015555555"
50    }
51  ) {
52    id
53    name
54    accountRepresentativeID
55    phoneNumber
56  }
57}
58
59# fifth
60mutation createProduct {
61  createProduct(input: { id: "yeezyboost", name: "Yeezy Boost" }) {
62    id
63    name
64  }
65}
66
67# sixth
68mutation createInventory {
69  createInventory(
70    input: { productID: "yeezyboost", warehouseID: "1", inventoryAmount: 300 }
71  ) {
72    productID
73    inventoryAmount
74    warehouseID
75  }
76}
77
78# seventh
79mutation createOrder {
80  createOrder(
81    input: {
82      amount: 300
83      date: "2018-07-12"
84      status: "pending"
85      accountRepresentativeID: "dabit"
86      customerID: "jennifer_thomas"
87      productID: "yeezyboost"
88    }
89  ) {
90    id
91    customerID
92    accountRepresentativeID
93    amount
94    date
95    customerID
96    productID
97  }
98}

1. Look up employee details by employee ID

This can simply be done by querying the employee model with an employee ID, no @key or @connection is needed to make this work.

1query getEmployee($id: ID!) {
2  getEmployee(id: $id) {
3    id
4    name
5    phoneNumber
6    startDate
7    jobTitle
8  }
9}

2. Query employee details by employee name

The @key byName on the Employee type makes this access-pattern feasible because under the covers an index is created and a query is used to match against the name field. You can use this query:

1query employeeByName($name: String!) {
2  employeeByName(name: $name) {
3    items {
4      id
5      name
6      phoneNumber
7      startDate
8      jobTitle
9    }
10  }
11}

3. Find an Employee’s phone number

Either one of the previous queries would work to find an employee’s phone number as long as one has their ID or name.

4. Find a customer’s phone number

A similar query to those given above but on the Customer model would give you a customer’s phone number.

1query getCustomer($customerID: ID!) {
2  getCustomer(id: $customerID) {
3    phoneNumber
4  }
5}

5. Get orders for a given customer within a given date range

There is a one-to-many relation that lets all the orders of a customer be queried.

This relationship is created by having the @key name byCustomerByDate on the Order model that is queried by the connection on the orders field of the Customer model.

A sort key with the date is used. What this means is that the GraphQL resolver can use predicates like Between to efficiently search the date range rather than scanning all records in the database and then filtering them out.

The query one would need to get the orders to a customer within a date range would be:

1query getCustomerWithOrdersByDate($customerID: ID!) {
2  getCustomer(id: $customerID) {
3    ordersByDate(date: { between: ["2018-01-22", "2020-10-11"] }) {
4      items {
5        id
6        amount
7        productID
8      }
9    }
10  }
11}

6. Show all open orders within a given date range across all customers

The @key byCustomerByStatusByDate enables you to run a query that would work for this access pattern.

In this example, a composite sort key (combination of two or more keys) with the status and date is used. What this means is that the unique identifier of a record in the database is created by concatenating these two fields (status and date) together, and then the GraphQL resolver can use predicates like Between or Contains to efficiently search the unique identifier for matches rather than scanning all records in the database and then filtering them out.

1query getCustomerWithOrdersByStatusDate($customerID: ID!) {
2  getCustomer(id: $customerID) {
3    ordersByStatusDate(
4      statusDate: {
5        between: [
6          { status: "pending", date: "2018-01-22" }
7          { status: "pending", date: "2020-10-11" }
8        ]
9      }
10    ) {
11      items {
12        id
13        amount
14        date
15      }
16    }
17  }
18}

7. See all employees hired recently

Having @key(name: "newHire", fields: ["newHire", "id"]) on the Employee model allows one to query by whether an employee has been hired recently.

1query employeesNewHire {
2  employeesNewHire(newHire: "true") {
3    items {
4      id
5      name
6      phoneNumber
7      startDate
8      jobTitle
9    }
10  }
11}

You can also query and have the results returned by start date by using the employeesNewHireByStartDate query:

1query employeesNewHireByDate {
2  employeesNewHireByStartDate(newHire: "true") {
3    items {
4      id
5      name
6      phoneNumber
7      startDate
8      jobTitle
9    }
10  }
11}

8. Find all employees working in a given warehouse

This needs a one to many relationship from warehouses to employees. As can be seen from the @connection in the Warehouse model, this connection uses the byWarehouse key on the Employee model. The relevant query would look like this:

1query getWarehouse($warehouseID: ID!) {
2  getWarehouse(id: $warehouseID) {
3    id
4    employees {
5      items {
6        id
7        name
8        startDate
9        phoneNumber
10        jobTitle
11      }
12    }
13  }
14}

9. Get all items on order for a given product

This access-pattern would use a one-to-many relation from products to orders. With this query you can get all orders of a given product:

1query getProductOrders($productID: ID!) {
2  getProduct(id: $productID) {
3    id
4    orders {
5      items {
6        id
7        status
8        amount
9        date
10      }
11    }
12  }
13}

10. Get current inventories for a product at all warehouses

The query needed to get the inventories of a product in all warehouses would be:

1query getProductInventoryInfo($productID: ID!) {
2  getProduct(id: $productID) {
3    id
4    inventories {
5      items {
6        warehouseID
7        inventoryAmount
8      }
9    }
10  }
11}

11. Get customers by account representative

This uses a one-to-many connection between account representatives and customers:

The query needed would look like this:

1query getCustomersForAccountRepresentative($representativeId: ID!) {
2  getAccountRepresentative(id: $representativeId) {
3    customers {
4      items {
5        id
6        name
7        phoneNumber
8      }
9    }
10  }
11}

12. Get orders by account representative and date

As can be seen in the AccountRepresentative model this connection uses the byRepresentativebyDate field on the Order model to create the connection needed. The query needed would look like this:

1query getOrdersForAccountRepresentative($representativeId: ID!) {
2  getAccountRepresentative(id: $representativeId) {
3    id
4    orders(date: { between: ["2010-01-22", "2020-10-11"] }) {
5      items {
6        id
7        status
8        amount
9        date
10      }
11    }
12  }
13}

13. Get all items on order for a given product

This is the same as number 9.

14. Get all employees with a given job title

Using the byTitle @key makes this access pattern quite easy.

1query employeesByJobTitle {
2  employeesByJobTitle(jobTitle: "Manager") {
3    items {
4      id
5      name
6      phoneNumber
7      jobTitle
8    }
9  }
10}

15. Get inventory by product by warehouse

Here having the inventories be held in a separate model is particularly useful since this model can have its own partition key and sort key such that the inventories themselves can be queried as is needed for this access-pattern.

A query on this model would look like this:

1query inventoryByProductAndWarehouse($productID: ID!, $warehouseID: ID!) {
2  getInventory(productID: $productID, warehouseID: $warehouseID) {
3    productID
4    warehouseID
5    inventoryAmount
6  }
7}

You can also get all inventory from an individual warehouse by using the itemsByWarehouseID query created by the byWarehouseID key:

1query byWarehouseId($warehouseID: ID!) {
2  itemsByWarehouseID(warehouseID: $warehouseID) {
3    items {
4      inventoryAmount
5      productID
6    }
7  }
8}

16. Get total product inventory

How this would be done depends on the use case. If one just wants a list of all inventories in all warehouses, one could just run a list inventories on the Inventory model:

1query listInventorys {
2  listInventorys {
3    items {
4      productID
5      warehouseID
6      inventoryAmount
7    }
8  }
9}

17. Get sales representatives ranked by order total and sales period

The sales period is either a date range or maybe even a month or week. Therefore you can set the sales period as a string and query using the combination of salesPeriod and orderTotal. You can also set the sortDirection in order to get the return values from largest to smallest:

1query repsByPeriodAndTotal {
2  repsByPeriodAndTotal(
3    sortDirection: DESC
4    salesPeriod: "January 2019"
5    orderTotal: { ge: 1000 }
6  ) {
7    items {
8      id
9      orderTotal
10    }
11  }
12}