Your assignment page on Canvas should contain instructions for submitting this project. If you are still unsure, reach out to School Staff.
Design the data model for a recipe book application and use Knex migrations and seeding functionality to build a SQLite database based on the model and seed it with test data. Then, build an endpoint to fetch a recipe by its id.
The requirements for the system as stated by the client are:
- Recipes have a name that must be unique (e.g. "Spaghetti Bolognese").
- Recipes contain an ordered list of steps (e.g. "Preheat the oven", "Roast the squash").
- Each step contains some instructions (e.g. "Preheat the oven") and belongs to a single recipe.
- Steps might involve any number of ingredients (zero, one or more).
- If a step involves one or more ingredients, each ingredient is used in a certain quantity.
- Ingredients can be used in different recipes, in different quantities.
After brainstorming with the team it is suggested that a JSON representation of a recipe could look like the following:
{
"recipe_id" : 1,
"recipe_name": "Spaghetti Bolognese",
"created_at": "2021-01-01 08:23:19.120",
"steps": [
{
"step_id": 11,
"step_number": 1,
"step_instructions": "Put a large saucepan on a medium heat",
"ingredients": []
},
{
"step_id": 12,
"step_number": 2,
"step_instructions": "Add 1 tbsp olive oil",
"ingredients": [
{ "ingredient_id": 27, "ingredient_name": "olive oil", "quantity": 0.014 }
]
},
]
}The JSON representation above is the result of querying data from several tables using SQL joins, and then using JavaScript to hammer the data into that particular shape.
Note that it's unlikely all the fields { "ingredient_id": 27, "ingredient_name": "olive oil", "quantity": 0.014 } come from the same table. Otherwise an ingredient could only ever be used in a fixed quantity!
Before writing any code, write out all desired tables in the data model and determine the relationships between tables.
Try to keep your design to FOUR tables. With three tables it will be hard to meet all requirements, and more than 5 is likely overkill.
- Put an Express application together starting with the
package.jsonand aknexfile.js. Use existing projects as reference if needed.
- Write a migration file that creates all tables necessary to model this data
- Write seed files to populate the tables with test data. Hint: Keep your recipes simple or this step could become extremely time consuming.
Write a data access file that exports an object with the following function:
getRecipeById(recipe_id)- Should resolve a representation of the recipe similar to the one shown in the Data Model above.
- The function will pull information from several tables using Knex and then create a response object using loops, objects, array methods etc.
- There are many ways to solve this, but from a performance standpoint the fewer trips to the database the better!
Write an endpoint to fetch a recipe by its id, using the getRecipeById(recipe_id) function.
- Write an endpoint to create a new recipe using ingredients that already exist in the database.
- Build a form in React that allows to create a new recipe selecting ingredients that already exist in the database.
- Research transactions in SQL and Knex: POSTing a recipe involves inserts to several tables, and the operation needs to completely succeed or be rolled back if any of the inserts fail.
The representation sent to the server could look like the following:
{
"recipe_name": "Spaghetti Bolognese",
"steps": [
{
"step_number": 1,
"step_instructions": "Put a large saucepan on a medium heat",
},
{
"step_number": 2,
"step_instructions": "Mix eggs and ham",
"ingredients": [
{ "ingredient_id": 27, "quantity": 2 },
{ "ingredient_id": 48, "quantity": 0.1 }
]
},
]
}=======================================================================
| Status Code | Description |
|---|---|
| 200 | request successfully processed |
| 201 | successfully created the new record |
| 400 | the request could not be understood by the server |
| 401 | unauthorized request from an unknown user |
| 403 | unauthorized request from a known user |
| 404 | the request resource is not found on the server |
| 500 | unknown and/or unexpected error occured |
| 503 | the server is not ready to handle this request |
=======================================================================
- the migration and seeds are configured correctly
- the db-config file is configured correctly to retrieve data from database
=======================================================================
The sample query, GET http://localhost:9000/api/users/
Body : none;
Response :
[ {"username":"user12", "password":"password1"}, ... ]
The sample query, GET http://localhost:9000/api/users/5
Body : none;
Response :
[{"username":"user12", "password":"password1"}]
The sample query, POST http://localhost:9000/api/users/
Body :
{"username":"[email protected]", "password":"tricord!!22"}
Response :
[{"username":"[email protected]", "password":"tricord!!22"}]
The sample query, PUT http://localhost:9000/api/users/10
Body :
{"username":"[email protected]", "password":"tricord!!22"}
Response :
[{"username":"[email protected]", "password":"tricord!!22"}]
The sample query, DELETE http://localhost:9000/api/users/10
Body : none;
Response :
[{"result":"successfully deleted user id 10}]
The middleware verify {id} in req.params to have below properties:
_type of integer
_not null
_not undefined
_value is greater than zero
This middleware verify {user_id} to have below properties:
_type of string
_not null
_not undefined
_not empty string
_length between 5 and 20
This middleware verify {username} to have below properties:
_type of string
_not null
_not undeefined
_not empty string
_length between 5 and 20
This middleware verify {password} to have below properties:
_type of string
_not null
_not undeefined
_not empty string
_length between 5 and 20
This middleware verify the {username} to be unique inside the ""users"" table.
This middleware verify the {password} to have below properties:
_not null
_not undefined
_not empty string
_alphanumeric
_length between 5 and 20
===================================================================================
The sample query, GET http://localhost:9000/api/profiles/
Body : none;
Response :
[
{
"email": "[email protected]",
"first_name": "mason",
"id": 3,
"last_name": "john",
"middle_name": "",
"user_id": 3,
"user_type": "user"
},
{
"email": "[email protected]",
"first_name": "noob",
"id": 4,
"last_name": "player",
"middle_name": "",
"user_id": 4,
"user_type": "user"
},
...
]
The sample query, GET http://localhost:9000/api/profiles/3
Body : none;
Response :
[
{
"email": "[email protected]",
"first_name": "mason",
"id": 3,
"last_name": "john",
"middle_name": "",
"user_id": 3,
"user_type": "user"
}
]
The sample query, POST http://localhost:9000/api/profiles/
Body :
{
"email": "[email protected]",
"first_name": "mason3",
"last_name": "mason3",
"middle_name": "c",
"user_id": 4,
"user_type": "user"
}
Response :
{
"newProfile": {
"email": "[email protected]",
"first_name": "mason3",
"id": 11,
"last_name": "mason3",
"middle_name": "c",
"user_id": 4,
"user_type": "user"
},
"result": 1
}
The sample query, PUT http://localhost:9000/api/profiles/
Body :
{
"email": "[email protected]",
"first_name": "mason3",
"id": 10,
"last_name": "mason3",
"middle_name": "c",
"user_id": 4,
"user_type": "user"
}
Response:
{
"modifiedProfile": {
"email": "[email protected]",
"first_name": "mason3",
"id": 10,
"last_name": "mason3",
"middle_name": "c",
"user_id": 4,
"user_type": "user"
},
"result": 1
}
The sample query, DELETE http://localhost:9000/api/profiles/11
Body : none;
Reponse :
{
"deletedProfile": {
"email": "[email protected]",
"first_name": "mason3",
"id": 11,
"last_name": "mason3",
"middle_name": "c",
"user_id": 4,
"user_type": "user"
},
"result": 1
}
verify the {id} exists in the **profiles** table.
verify the {first_name, last_name, middle_name, email, user_type, user_id} is valid
verify the {user_id} exist in the the **users** table.
===================================================================================
The sample query, GET http://localhost:9000/api/products/
Body : none;
Response :
[
{
"description": "a very colorful shirt",
"id": 1,
"name": "color shirt",
"price": 11.99
},
{
"description": "a very strong case",
"id": 2,
"name": "pencil case",
"price": 11.99
},
{
"description": "a long lasting bottle",
"id": 3,
"name": "water bottle",
"price": 11.99
},
{
"description": "a wood box",
"id": 4,
"name": "container box",
"price": 11.99
},
{
"description": "new",
"id": 6,
"name": "new",
"price": 10.99
}
]
The sample query, GET http://localhost:9000/api/products/4
Body : none;
Response :
[
{
"description": "a wood box",
"id": 4,
"name": "container box",
"price": 11.99
}
]
The sample query, POST http://localhost:9000/api/products/
Body :
{
"description": "description",
"name": "bookmark",
"price": 2.1
}
Response :
{
"newProduct": {
"description": "description",
"id": 7,
"name": "bookmark",
"price": 2.1
},
"result": 1
}
The sample query, PUT http://localhost:9000/api/products/7
Body :
{
"description": "description",
"id": 7,
"name": "new bookmark",
"price": 2.1
}
Response :
{
"modifiedProduct": {
"description": "description",
"id": 7,
"name": "new bookmark",
"price": 2.1
},
"result": 1
}
The sample query, DELETE http://localhost:9000/api/profiles/7
Body : none;
Response :
{
"deletedProduct": {
"description": "description",
"id": 7,
"name": "new bookmark",
"price": 2.1
},
"result": 1
}
check if the product id exists in the **products** table
verify {name, descriptoin, prices} values
===================================================================================
The sample query, GET http://localhost:9000/api/orders/
Body : none;
Response :
[
{
"id": 4,
"order_id": 2,
"product_id": 4,
"quantity": 5,
"status": "pending",
"user_id": 4
},
{
"id": 5,
"order_id": 3,
"product_id": 5,
"quantity": 5,
"status": "pending",
"user_id": 5
},
...
]
The sample query, GET http://localhost:9000/api/orders/4
Body : none;
Response :
[
{
"id": 4,
"order_id": 2,
"product_id": 4,
"quantity": 5,
"status": "pending",
"user_id": 4
}
]
The sample query, POST http://localhost:9000/api/orders/
Body :
{
"order_number": 3124,
"product_id": 1,
"quantity": 100,
"status": "pending",
"user_id": 2
}
Response :
{
"newOrder": {
"id": 7,
"order_number": 3124,
"product_id": 1,
"quantity": 100,
"status": "pending",
"user_id": 2
},
"result": 1
}
The sample query, PUT http://localhost:9000/api/orders/7
Body :
{
"order_number": 3124,
"product_id": 1,
"quantity": 1000,
"status": "pending",
"user_id": 2
}
Response :
{
"modifiedOrder": {
"id": 7,
"order_number": 3124,
"product_id": 1,
"quantity": 1000,
"status": "pending",
"user_id": 2
},
"result": 1
}
The sample query, DELETE http://localhost:9000/api/orders/5
Body : none;
Response :
{
"deletedOrder": {
"id": 7,
"order_number": 3124,
"product_id": 1,
"quantity": 1000,
"status": "pending",
"user_id": 2
},
"result": 1
}
ensure the {id} exists in **orders** table
ensure {order_number, product_id, quantity, status, user_id} are valid inputs.
===================================================================================
Created tests using Jest and Supertest for the following:
- middleware-verify.js, middleware-verify.test.js
- server.js, server.test.js
- orders-router.js, orders-routers.test.js
- products-router.js, products-router.test.js
- profiles-router.js, profiles-router.test.js
- users-router.js, users-router.test.js
===================================================================================