3.E.3: Bigfoot SQL 1-M

Learning Objectives

  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.

Introduction

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.

Setup

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.

Base: Add Comments to Sightings

Create Comment model, associate with Sighting

Create model and migration files

Create a new model Comment to store comments with npx sequelize model:generate, which will also create a corresponding migration. Comment should have content and SightingId attributes, where content can be text data type and SightingId can be integer. No need to input references attribute 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 underscored: true. 

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 .

Update model and migration files to include associations and foreign key attributes

Add associations and foreign key declarations to Sighting and Comment models. Review Update models and migrations section of Sequelize 1-M Relationships submodule for a refresher.

Add foreign key declaration to comment migration by adding the references attribute to sighting_id.

Run migrations and verify schema correctness

Run migrations to create the comments table in our database as we specified in the migration. Verify that our migrations succeeded by viewing the new comments table in either DBeaver, pgAdmin or Postico and verifying that our content and sighting_id attributes were included successfully.

Congrats! We're now ready to use our new database structure in our application!

Write backend routes for comments

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.

Add routes to retrieve and create comments

In /controllers/sightingsController.js add 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.

In /routers/sightingsRouter.js, add 2 new routes for retrieving and creating comments for a given sighting respectively.

Consider using the /sightings/:sightingId/comments path and GET method to retrieve comments, and the /sightings/:sightingId/comments path 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 sighting_id in findAll and create methods to perform each route's logic in a single query instead of multiple.

Test new routes with Thunder Client

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 /sightings/:sightingId/comments.

Almost there! Time to hook up the frontend!

Add comment list and input interfaces to sighting-specific pages

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.

Render comment list

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 ListGroup in our reference solution.

Render comment composer

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/comments API to save the comment in the database. After successfully creating the new comment, retrieve all comments with the /sightings/:sightingId/comments GET API to refresh the local comment list. Feel free to use any UI elements you prefer; Rocket uses a React Bootstrap Form in 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!!!

Comfortable: Edit and Delete Comments

  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 SightingsController class, controllers/sightingsController.js

    3. Create new routes within the routes() method within the SightingsRouter class, 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.

More Comfortable: Add Likes on Sightings

  1. Create a new model Likes with 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 Like model.

  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.

Submission

Submit pull requests to the main branches 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.

Reference Solution

Here is reference code for the frontend and the backend for this exercise. You can do better!

Previous3.E.2: Bigfoot SQLNext3.E.4: Bigfoot SQL M-M

Last updated