Page updated Jan 16, 2024

Preview: AWS Amplify's new code-first DX (Gen 2)

The next generation of Amplify's backend building experience with a TypeScript-first DX.

Get started

Warehouse Management System

In this "Warehouse management system" example, you will learn how to configure common access patterns for your app. This example has the following types:

  • Warehouse
  • Product
  • Inventory
  • Employee
  • AccountRepresentative
  • Customer

These types have the following common access patterns:

  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

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

# 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/auth input AMPLIFY { globalAuthRule: AuthRule = { allow: public } } # FOR TESTING ONLY! type Order @model { id: ID! customerID: ID! @index(name: "byCustomerByStatusByDate", sortKeyFields: ["status", "date"]) @index(name: "byCustomerByDate", sortKeyFields: ["date"]) accountRepresentativeID: ID! @index(name: "byRepresentativebyDate", sortKeyFields: ["date"]) productID: ID! @index(name: "byProduct", sortKeyFields: ["id"]) status: String! amount: Int! date: String! } type Customer @model { id: ID! name: String! phoneNumber: String accountRepresentativeID: ID! @index(name: "byRepresentative", sortKeyFields: ["id"]) ordersByDate: [Order] @hasMany(indexName: "byCustomerByDate", fields: ["id"]) ordersByStatusDate: [Order] @hasMany(indexName: "byCustomerByStatusByDate", fields: ["id"]) } type Employee @model { id: ID! name: String! @index(name: "byName", queryField: "employeeByName", sortKeyFields: ["id"]) startDate: String! phoneNumber: String! warehouseID: ID! @index(name: "byWarehouse", sortKeyFields: ["id"]) jobTitle: String! @index(name: "byTitle", queryField: "employeesByJobTitle", sortKeyFields: ["id"]) newHire: String! @index(name: "newHire", queryField: "employeesNewHire", sortKeyFields: ["id"]) @index(name: "newHireByStartDate", queryField: "employeesNewHireByStartDate", sortKeyFields: ["startDate"]) } type Warehouse @model { id: ID! employees: [Employee] @hasMany(indexName: "byWarehouse", fields: ["id"]) } type AccountRepresentative @model { id: ID! customers: [Customer] @hasMany(indexName: "byRepresentative", fields: ["id"]) orders: [Order] @hasMany(indexName: "byRepresentativebyDate", fields: ["id"]) orderTotal: Int salesPeriod: String @index(name: "bySalesPeriodByOrderTotal", queryField: "repsByPeriodAndTotal", sortKeyFields: ["orderTotal"]) } type Inventory @model { productID: ID! @primaryKey(sortKeyFields: ["warehouseID"]) warehouseID: ID! @index(name: "byWarehouseID", queryField: "itemsByWarehouseID") inventoryAmount: Int! } type Product @model { id: ID! name: String! orders: [Order] @hasMany(indexName: "byProduct", fields: ["id"]) inventories: [Inventory] @hasMany(fields: ["id"]) }
1# This "input" configures a global authorization rule to enable public access to
2# all models in this schema. Learn more about authorization rules here: https://docs.amplify.aws/cli/graphql/auth
3input AMPLIFY { globalAuthRule: AuthRule = { allow: public } } # FOR TESTING ONLY!
4
5type Order @model {
6 id: ID!
7 customerID: ID! @index(name: "byCustomerByStatusByDate", sortKeyFields: ["status", "date"]) @index(name: "byCustomerByDate", sortKeyFields: ["date"])
8 accountRepresentativeID: ID! @index(name: "byRepresentativebyDate", sortKeyFields: ["date"])
9 productID: ID! @index(name: "byProduct", sortKeyFields: ["id"])
10 status: String!
11 amount: Int!
12 date: String!
13}
14
15type Customer @model {
16 id: ID!
17 name: String!
18 phoneNumber: String
19 accountRepresentativeID: ID! @index(name: "byRepresentative", sortKeyFields: ["id"])
20 ordersByDate: [Order] @hasMany(indexName: "byCustomerByDate", fields: ["id"])
21 ordersByStatusDate: [Order] @hasMany(indexName: "byCustomerByStatusByDate", fields: ["id"])
22}
23
24type Employee @model {
25 id: ID!
26 name: String! @index(name: "byName", queryField: "employeeByName", sortKeyFields: ["id"])
27 startDate: String!
28 phoneNumber: String!
29 warehouseID: ID! @index(name: "byWarehouse", sortKeyFields: ["id"])
30 jobTitle: String! @index(name: "byTitle", queryField: "employeesByJobTitle", sortKeyFields: ["id"])
31 newHire: String! @index(name: "newHire", queryField: "employeesNewHire", sortKeyFields: ["id"]) @index(name: "newHireByStartDate", queryField: "employeesNewHireByStartDate", sortKeyFields: ["startDate"])
32}
33
34type Warehouse @model {
35 id: ID!
36 employees: [Employee] @hasMany(indexName: "byWarehouse", fields: ["id"])
37}
38
39type AccountRepresentative @model {
40 id: ID!
41 customers: [Customer] @hasMany(indexName: "byRepresentative", fields: ["id"])
42 orders: [Order] @hasMany(indexName: "byRepresentativebyDate", fields: ["id"])
43 orderTotal: Int
44 salesPeriod: String @index(name: "bySalesPeriodByOrderTotal", queryField: "repsByPeriodAndTotal", sortKeyFields: ["orderTotal"])
45}
46
47type Inventory @model {
48 productID: ID! @primaryKey(sortKeyFields: ["warehouseID"])
49 warehouseID: ID! @index(name: "byWarehouseID", queryField: "itemsByWarehouseID")
50 inventoryAmount: Int!
51}
52
53type Product @model {
54 id: ID!
55 name: String!
56 orders: [Order] @hasMany(indexName: "byProduct", fields: ["id"])
57 inventories: [Inventory] @hasMany(fields: ["id"])
58}

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

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

1. Look up employee details by employee ID

This can simply be done by querying the employee model with an employee ID, no @primaryKey or @index need to be explicitly specified to make this work.

query getEmployee($id: ID!) { getEmployee(id: $id) { id name phoneNumber startDate jobTitle } }
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 @index byName on the Employee type makes this access-pattern feasible because under the hood an index is created and a query is used to match against the name field. You can use this query:

query employeeByName($name: String!) { employeeByName(name: $name) { items { id name phoneNumber startDate jobTitle } } }
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.

query getCustomer($customerID: ID!) { getCustomer(id: $customerID) { phoneNumber } }
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 @index name byCustomerByDate on the Order model that is queried by the @hasMany relationship 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:

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

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

The @index 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.

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

7. See all employees hired recently

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

query employeesNewHire { employeesNewHire(newHire: "true") { items { id name phoneNumber startDate jobTitle } } }
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:

query employeesNewHireByDate { employeesNewHireByStartDate(newHire: "true") { items { id name phoneNumber startDate jobTitle } } }
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 @hasMany relationship in the Warehouse model, this relationship uses the byWarehouse index on the Employee model. The relevant query would look like this:

query getWarehouse($warehouseID: ID!) { getWarehouse(id: $warehouseID) { id employees{ items { id name startDate phoneNumber jobTitle } } } }
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:

query getProductOrders($productID: ID!) { getProduct(id: $productID) { id orders { items { id status amount date } } } }
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:

query getProductInventoryInfo($productID: ID!) { getProduct(id: $productID) { id inventories { items { warehouseID inventoryAmount } } } }
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 has-many relationship between account representatives and customers:

The query needed would look like this:

query getCustomersForAccountRepresentative($representativeId: ID!) { getAccountRepresentative(id: $representativeId) { customers { items { id name phoneNumber } } } }
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 relationship uses the byRepresentativebyDate field on the Order model to create the connection needed. The query needed would look like this:

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

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 @index makes this access pattern quite easy.

query employeesByJobTitle { employeesByJobTitle(jobTitle: "Manager") { items { id name phoneNumber jobTitle } } }
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:

query inventoryByProductAndWarehouse($productID: ID!, $warehouseID: ID!) { getInventory(productID: $productID, warehouseID: $warehouseID) { productID warehouseID inventoryAmount } }
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:

query byWarehouseId($warehouseID: ID!) { itemsByWarehouseID(warehouseID: $warehouseID) { items { inventoryAmount productID } } }
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:

query listInventorys { listInventorys { items { productID warehouseID inventoryAmount } } }
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:

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