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 }
]
},
]
}