Bootcamp
Search…
3.ICE.7: Many to Many

Base

Create a command-line app to record workouts.

App Setup

  1. 1.
    Begin with the base Node repo.​
  2. 2.
    Install the pg library with NPM.
  3. 3.
    Use the code from 3.4.2: PostgreSQL Node App as a template for index.js. Remember to update pgConnectionConfigs to reference the new DB name.

Set Up Database Schema

Create a new database and the following tables inside that database.

Exercise Table (exercises)

column name
data type
description
example
id
SERIAL PRIMARY KEY
ID
1
name
TEXT
Name of the exercise
Pull up

Workout Table (workouts)

column name
data type
description
example
id
SERIAL PRIMARY KEY
ID
1
name
TEXT
Name of the workout
Leg Tuesday
date
TEXT
Date of the workout
3/3/2020

Join Table (exercise_workouts)

column name
data type
description
example
id
SERIAL PRIMARY KEY
ID
1
exercise_id
INTEGER
ID of the exercise
1
workout_id
INTEGER
ID of the workout
1

Insert Seed Data

Insert exercises into the exercises table. Find a list of exercises here.

Output Exercises

Create a command to view all exercises in the DB.
1
node index.js exercises
Copied!

Sample Output

1
1. deadlift
2
2. squat
Copied!

Create Workouts (With Existing Exercises)

Create a command to log workouts. When the user enters a new workout (with exercises) on the command line, our app creates a new entry in the workouts table AND adds the corresponding rows in the exercise_workouts table to associate the new workout with exercises.
When the following command runs it will take each input after the 5th input (after <DATE>) and assume that it is an ID of an exercise. For each of these IDs it will create a row in the exercise_workouts table.

Command Template

Square brackets in the command template reference optional values. In this case, the 2nd and onward exercise IDs are optional.
1
node index.js add-workout <WORKOUT_NAME> <DATE> <EXERCISE_ID> [<EXERCISE_ID> ...]
Copied!

Sample Command

1
node index.js add-workout 'leg tuesday' '3/2/2020' 1 2
Copied!
Because there is no limit to the exercise IDs that could be included in the command, we may wish to consider using Rest Parameters to parse process.argv, something like the following. In the following example, workoutExercises will be an array of all exercises input on the command line. We can then iterate over workoutExercises to insert the relevant records to our database.
1
const [
2
appName,
3
scriptName,
4
cmdName,
5
workoutName,
6
workoutDate,
7
...workoutExercises
8
] = process.argv;
Copied!

Get Workouts with Specific Exercise with Exercise ID

Create a command to get workouts by exercise. Use exercise_id to get all workouts with that exercise. Make an INNER JOIN query to list workout names with this exercise. Join on the workouts and exercise_workouts tables.

Command Template

1
node index.js get-workouts-by-exercise <EXERCISE_ID>
Copied!

Sample Command

1
node index.js get-workouts-by-exercise 2
Copied!

Comfortable

Get Workouts with Specific Exercise with Exercise Name

Use exercise name to get workouts instead of exercise ID. First query the exercises table to get the relevant exercise ID. Then follow the same logic as before to get workouts.

Command Template

1
node index.js get-workouts-by-exercise <EXERCISE_NAME>
Copied!

Sample Command

1
node index.js get-workouts-by-exercise squat
Copied!

Save Workouts with Exercise Name

Do a nested query so that the user can save workout exercises by exercise name.

Command Template

Square brackets in the command template reference optional values. In this case, the 2nd and onward exercise names are optional.
1
node index.js add-workout <WORKOUT_NAME> <DATE> <EXERCISE_NAME> [<EXERCISE_NAME> ...]
Copied!

Sample Command

1
node index.js add-workout 'leg tuesday' '3/2/2020' deadlift squat
Copied!

More Comfortable

Add Muscles Table

Add another table muscles that gives body part categories to each exercise, like in the table here. An exercise will be associated with 1 or more muscles. Seed the muscles table with some muscles.

Get Exercise by Muscle

Add a command that lists exercises by muscle name.

Command Template

1
node index.js get-exercise <MUSCLE_NAME>
Copied!

Sample Command

1
node index.js get-exercise quads
Copied!

Get Workout by Muscle

Add a command that lists workouts by muscle name.

Command Template

1
node index.js get-workout-by-muscle <MUSCLE_NAME>
Copied!

Sample Command

1
node index.js get-workout-by-muscle quads
Copied!