3.E.3: Bigfoot SQL 1-M
- 1.How to create and update models and migrations to add models with 1-M relationships to our applications
- 2.Understand the development process of creating and updating models and migrations, creating and testing backend routes, and creating and testing frontend functionality. Always develop with user stories in mind.
We will add a comments feature to Bigfoot so users can leave comments on each sighting. There will be a 1-M relationship between sightings and comments respectively.
If you haven't already, complete Exercise Setup for the previous Bigfoot SQL exercise. This exercise will use the same frontend and backend repos and the same setup. Remember to follow the MVC set up, so create Controllers and Routers within your backend repository.
Create a new model
Commentto store comments with
npx sequelize model:generate, which will also create a corresponding migration.
textdata type and
integer. No need to input
referencesattribute for the foreign key yet; we can edit our model and migration files after they are created. You may need to add in the model constraint
npx sequelize model:generate --name comment --attributes content:string,sighting_id:integer
This should generate output like the following.
Sequelize CLI [Node: 16.14.2, CLI: 6.4.1, ORM: 6.20.1]
New model was created at /Users/kai/rocket-code/bootcamp/examples/bigfoot-sql-backend-bootcamp/db/models/comment.js .
New migration was created at /Users/kai/rocket-code/bootcamp/examples/bigfoot-sql-backend-bootcamp/db/migrations/20220604082051-create-comment.js .
Add associations and foreign key declarations to
Commentmodels. Review Update models and migrations section of Sequelize 1-M Relationships submodule for a refresher.
Add foreign key declaration to
commentmigration by adding the
Run migrations to create the
commentstable in our database as we specified in the migration. Verify that our migrations succeeded by viewing the new
commentstable in either DBeaver, pgAdmin or Postico and verifying that our
sighting_idattributes were included successfully.
Commentstable in Postico after running migrations
Congrats! We're now ready to use our new database structure in our application!
Now that we've updated our models and database schema to include comments, let's write routes to retrieve and create comments so that our frontend can store and retrieve comment data in the backend.
/controllers/sightingsController.jsadd two new methods to the class
SightingController, one with the logic that will be used to retrieve all comments from a sighting, and the other to create comments on sighting.
/routers/sightingsRouter.js, add 2 new routes for retrieving and creating comments for a given sighting respectively.
Consider using the
/sightings/:sightingId/commentspath and GET method to retrieve comments, and the
/sightings/:sightingId/commentspath and POST method to create a comment. Notice we are using REST API best practices to name route paths, aiming to name paths with nouns and use HTTP methods to communicate actions.
There are multiple ways to query for all comments or create a new comment for a given sighting. Rocket recommends specifying
createmethods to perform each route's logic in a single query instead of multiple.
After creating the 2 routes, test them with Thunder Client to make sure they are working before moving on. The following screenshot demonstrates a successful POST request to
Sample POST request to
/sightings/:sightingId/commentsfrom Thunder Client
Almost there! Time to hook up the frontend!
We will edit our sighting-specific page in our frontend to render a list of comments for that sighting and render a composer above that list to leave new comments.
Update the component we are using for our sighting-specific page to retrieve all comments for that sighting on component mount (likely in
useEffect), save those comments in state and render those comments below sighting details. Feel free to use any UI elements you prefer; Rocket uses a React Bootstrap
ListGroupin our reference solution.
Create a form below sighting details and above the comment list to create new comments. On submit, this form should send an AJAX POST request to our
/sightings/:sightingId/commentsAPI to save the comment in the database. After successfully creating the new comment, retrieve all comments with the
/sightings/:sightingId/commentsGET API to refresh the local comment list. Feel free to use any UI elements you prefer; Rocket uses a React Bootstrap
Formin our reference solution.
Creating new comment is similar to creating new sighting
The pattern for creating a new comment should be similar to creating a new sighting. Feel free to review your old code for reference!
Congratulations on making it this far!!!
- 1.Create edit and delete comment routes in the backend that accept PUT and DELETE requests respectively. Requests to the edit route should respond with the edited comment instance, and requests to the delete route should respond with no data.
- 1.Follow the MVC setup within the boilerplate
- 2.Create new methods within the
- 3.Create new routes within the routes() method within the
SightingsRouterclass, bind the new method that you created in the previous step with the class itself.
- 4.Test the new routes with Thunder Client before moving on.
- 2.Create an edit comment form in the frontend that auto-populates with the relevant comment's content and on submit sends a PUT request to our edit comment route. We may wish to create this form in its own component for decomposition.
- 3.Create edit and delete buttons next to each comment that allow users to edit and delete comments. The edit button should toggle the edit comment form to appear and disappear. The delete button should send a request to our delete route and on successful delete, remove the relevant comment from the frontend. After edit or delete, consider querying for all comments again like after we created a new comment to refresh the comment list with most updated data.
- 1.Create a new model
Likeswith a 1-M relationship with
Sightings, where each sighting can have many likes, but each like belongs to a single sighting. Update models and migrations like we did with comments such that our Express app can use Sequelize relationship methods with the
- 2.Create routes & controller methods to retrieve and create likes for a given sighting, similar to what we did with comments. Link the new methods to the router within sightingRouter.js Test these routes with Thunder Client before moving on.
- 3.Update our frontend to display the number of likes each sighting has and enable users to like sightings. Because we haven't implemented authentication yet, users can like sightings unlimited times.
Submit pull requests to the
mainbranches of Rocket's Bigfoot Frontend and Bigfoot SQL Backend repos respectively, and share your PR links in your section Slack channel.
There is no need to deploy this exercise for now. We will build on it in upcoming Bigfoot exercises and deploy at the end of Bigfoot SQL M-M exercise after we have a firmer grasp of Sequelize.