3.E.2: Bigfoot SQL

Learning Objectives

  1. How to create a backend API server with Express, PostgreSQL and Sequelize

  2. Connect a frontend app with a backend API server that uses SQL to store and retrieve data

  3. Create a backend that supports the MVC structure

Introduction

We will use forms and POST requests to save new Bigfoot sightings to our database.

New Tooling: SQL Clients

SQL clients help us visualise our databases with clickable interfaces without repeatedly typing SQL queries. Rocket recommends using DBeaver as a universal database management tool, pgAdmin for Windows specific software and Postico for Mac specific software, all of which are free. We are free to use any other SQL clients we may prefer.

  1. Universal tool: DBeaver downloader

    1. For further instructions on DBeaver please refer to this section.

  2. Windows: pgAdmin installation instructions

  3. Mac: Postico installation instructions

[Reference Only] Sequelize Setup

Rocket has performed the following Sequelize setup for this Bigfoot exercise, but you may wish to refer back to these instructions for future projects where you need to set up Sequelize on your own.

Setup Packages

Install Sequelize NPM Packages

Install sequelize and pg to use Sequelize with Postgres in our app.

npm i sequelize pg

Install sequelize-cli in dev dependencies to generate Sequelize model and migration files and run Sequelize migrations that will set up our database schema. We install to dev dependencies because we only need sequelize-cli for database setup and not when the app is live; dev dependencies are typically not installed in production. The -D flag is for dev dependencies.

npm i -D sequelize-cli

Create Sequelize Folders

Create a .sequelizerc file in the root of our repo to tell Sequelize where to store its files. We will use a suggested configuration from the official Sequelize docs.

.sequelizerc
const path = require("path");

module.exports = {
  config: path.resolve("config", "database.js"),
  "models-path": path.resolve("db", "models"),
  "seeders-path": path.resolve("db", "seeders"),
  "migrations-path": path.resolve("db", "migrations"),
};

Create Sequelize folders and starter files in the locations specified in .sequelizerc using npx sequelize init. These will store Sequelize files and configurations that we will need for our application. We will explore these files in more detail below.

npx sequelize init

Verify our Sequelize config file is in config/database.js and our models, seeders and migrations folders are in a folder called db.

Configure Database

Populate DB config

Update config/database.js to contain database settings for the "development" environment, i.e. when we are developing locally. We can delete "test" and "production" settings for now until we need them.

config/database.js
module.exports = {
  "development": {
    "username": "my_unix_username",
    "password": null,
    "database": "my_app_development",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }
}

Replace my_unix_username with your local Unix username (use whoami to find it) and replace my_app in my_app_development with the name of your app in snake_case. To mask this sensitive data we suggest using a .env within your application for a refresher have a look here.

Create DB based on config

Create the database specified in the config using Sequelize CLI's db:create command.

npx sequelize db:create

We should see output like the following after creating our database on the command line.

Sequelize CLI [Node: 16.14.2, CLI: 6.4.1, ORM: 6.20.1]

Loaded configuration file "config/database.js".
Using environment "development".
Database my_app_development created.

Create Models and Migrations

Recall from Rocket's Sequelize submodule that models tell our apps what data they can access, and migrations control our database schema. Our models may change as our apps evolve, and we will need to create new migrations to update our databases with those changes without affecting live data.

Create model and migration files

Use Sequelize's model:generate command to generate a model and initial migration for the Sighting model (and sightings table in our DB). We use data type text for the notes field because notes can be longer than 255 chars, which is the max length for data type string.

npx sequelize model:generate --name sighting --attributes date:date,location:string,notes:text --underscored

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/sighting.js .
New migration was created at /Users/kai/rocket-code/bootcamp/examples/bigfoot-sql-backend-bootcamp/db/migrations/20220531155824-create-sighting.js .\

Run Migration to Create Table

Migration files specify what changes Sequelize should make to our DB schema. To execute all unexecuted migration files, run Sequelize CLI's db:migrate command.

npx sequelize db:migrate

db:migrate will perform the following.

  1. Create a table called SequelizeMeta in database if it doesn't exist yet that records which migrations have run on the database so far

  2. Run migration files that have not been run yet, generating relevant tables and columns

Create and Run Seeder to Populate Database

Seed files specify what seed data to include in our database before any user input. In our case we will populate dummy sighting data so the app does not look empty when users open it.

Generate seed file

Run Sequelize CLI's seed:generate command to generate a new seed file. Use kebab-case for the seed file name by convention.

npx sequelize seed:generate --name seed-sightings

This should generate output like the following.

Sequelize CLI [Node: 16.14.2, CLI: 6.4.1, ORM: 6.20.1]

seeders folder at "/Users/kai/rocket-code/bootcamp/examples/bigfoot-sql-backend-bootcamp/db/seeders" already exists.
New seed was created at /Users/kai/rocket-code/bootcamp/examples/bigfoot-sql-backend-bootcamp/db/seeders/20220601140735-seed-sightings.js .

Notice we have a new seed file in our db/seeders folder. The file should have the following contents.

20220601140735-sightings.js
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    /**
     * Add seed commands here.
     *
     * Example:
     * await queryInterface.bulkInsert('People', [{
     *   name: 'John Doe',
     *   isBetaMember: false
     * }], {});
     */
  },

  async down(queryInterface, Sequelize) {
    /**
     * Add commands to revert seed here.
     *
     * Example:
     * await queryInterface.bulkDelete('People', null, {});
     */
  },
};

Populate seed file

We will edit this file to insert sightings. Notice the structure of the file is similar to migration files, where Sequelize runs up to apply the seeder and down to undo the seeder. We will rarely use down but we should still include the relevant code to undo our up logic in case needed.

Notice the example seed code in the auto-generated comments references queryInterface.bulkInsert and queryInterface.bulkDelete commands. We will be using these methods to insert and delete our seeders respectively. All queryInterface methods have detailed documentation in Sequelize API docs.

After collecting data from sightings.json in the Bigfoot JSON exercise, our new seeder file now looks like the following.

20220601140735-sightings.js
"use strict";

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.bulkInsert(
      "sightings",
      [
        {
          date: new Date(1990, 9),
          location: "East side of Prince William Sound, Alaska, USA",
          notes:
            "Ed L. was salmon fishing with a companion in Prince William Sound. After anchoring off shore, his companion took a small boat up a river to check on the state of the salmon run. As the day wore on toward evening and he didn't come back at the expected time, Ed scanned upriver and across the adjacent land with binoculars. There he saw a sasquatch walking across the tundra, with long, smooth steps and with dark hair flowing from its shoulders, bouncing behind \"like a cape\" at every step. The sasquatch paid no attention to the boat (distance about 1,000').",
          created_at: new Date(),
          updated_at: new Date(),
        },
        {
          date: new Date(2000, 9, 2),
          location: "Warren County, New Jersey, USA",
          notes:
            "a month ago i was with a friend and her son, we were hanging out at a camp ground by Allamuchy state park, in Warren county NJ. i think the camp ground was part of Stephens state park either way it was on the Musconetcong river. it was late in the day and we were just walking on the trials by the river. then i heard a sound like someone was lightly banging a fork or something against a plate. i thought maybe a bear was snooping around looking for a free meal, this is bear country and we have a very healthy black bear population, but maybe 30 seconds after i heard the metalic sound i heard someone or something bang a branch against a tree 4 or 5 times in a row, i thought that the bear maybe was getting a little to close to someones camp site. i didnt smell anything but i think i was up wind anyway and we were burning wood in the fire and all i could really smell was the fire anyway. it also bothers me that something waited until it was sure that i was asleep before coming into camp. i looked around before we left to see if i could find any tracks or anything but the ground is was just to hard, there was a few game trails but i didnt look very far or spend alot of time looking. i looked on the internet and found this sight and was surprized to see other sitings in new jersey.",
          created_at: new Date(),
          updated_at: new Date(),
        },
        {
          date: new Date(2016, 6, 7),
          location: "Sullivan County, New Hampshire, USA",
          notes:
            "I was on my way to Claremont from Lebanon on Rte 120 and was passing by some reeds in a marshy area next to the road that sounded like a huge flock of birds. The creature came out of the reeds and was crouched down looking at me through them. Surprisingly it got out of the reeds from crouched to standing turned around and ran towards the wooded area up a hill and was moving with it's arms brush and tree branches out of the way. It was incredibly fast runner and I got a good look at its coconut shaped head as it ran away. It was light brown/brown. It had incredibly broad shoulders. I went back the same day. The marsh went up to my knees and I could smell a bad odor like garbage. Myself and a friend went back to the sighting. We found a footprint and a hair sample. I could hear wood knocks when my friend James did a call and we also found a large bed of branches and some tree structures. The following month I went back to Claremont and I heard screeching in the woods while I was traveling Rte. 120 on a bicycle.",
          created_at: new Date(),
          updated_at: new Date(),
        },
      ],
      {}
    );
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete("sightings", null, {});
  },
};

Run seed file to populate database

Run our seed file with Sequelize CLI's db:seed:all command to populate our database with our seed data.

npx sequelize db:seed:all

This should generate output like the following.

Sequelize CLI [Node: 16.14.2, CLI: 6.4.1, ORM: 6.20.1]

Loaded configuration file "config/database.js".
Using environment "development".
== 20220601140735-sightings: migrating =======
== 20220601140735-sightings: migrated (0.006s)

Exercise Setup

Fork and clone repos

  1. Fork and clone the Rocket Academy Bigfoot SQL Backend repo

  2. Continue with your forked copy of the Rocket Academy Bigfoot Frontend repo from the Bigfoot JSON exercise

  3. While developing within the Backend repo follow the MVC pattern.

Familiarise yourself with starter code

Rocket has set up Sequelize for us using the steps documented above. Familiarise yourself with those steps and the files in the starter code for when you need to set up Sequelize for yourself in your projects.

Setup backend

Instructions

  1. Run npm i from the root of the Bigfoot SQL Backend repo to install relevant packages

  2. Create your .env based off the sample provided from the exercise repo.

  3. Update config/database.js to use your Unix username and name the database bigfoot_sql_development. Kai's looks like the one below.

  4. Run npx sequelize db:create to create the bigfoot_app_development database

  5. Run npx sequelize db:migrate to set up the database schema

  6. Run npx sequelize db:seed:all to seed data in the database

  7. Verify the seed data was added by viewing the sightings table in your chosen SQL client. You can use the information in config/database.json to connect to the database in the SQL client.

Sample database.js

config/database.js
require('dotenv').config()

module.exports = {
  "development": {
    "username": process.env.DB_USERNAME,
    "password": process.env.DB_PASSWORD,
    "database": process.env.DB_NAME,
    "host": process.env.DB_HOST,
    "dialect": process.env.DB_DIALECT
  }
}

Sample view of sightings table in Postico

Base: Report New Sightings

Allow users to report Bigfoot sightings that are saved to our SQL database. When users refresh the sightings page they should be able to see the sightings they added.

Verify with Thunder Client that backend routes work

Rocket provided starter routes in index.js that import the Sighting Sequelize model and query it for data. We will first verify those routes work with Thunder Client before attempting to connect the backend to the frontend.

Verify root route returns all sightings

  1. Start the API server with npm start from the root of the Bigfoot SQL Backend repo

  2. Open Thunder Client in VS Code and send a request to localhost:3000/sightings

  3. Verify that we receive the 3 sightings we seeded in our database

Verify sighting-specific route returns data for single sighting

  1. Start the API server with npm start from the root of the Bigfoot SQL Backend repo

  2. Send a request to localhost:3000/sightings/1 in Thunder Client

  3. Verify we receive the data for the first sighting in our database

Understand the backend architecture

The index.js is the entry point to our application and any request made from the browser to our backend server will go through this file. Within the boilerplate code a SightingRouter and SightingController are used to send responses to frontend API requests that you can test as above. These files help to maintain the applications structure as we develop further. The routers are used to define the url endpoint the frontend can consume, while the controllers query the Sequelize data for reference or alter it, if requested. The request is handled by the index, which is passes the request to the appropriate router, before the correct controller method is chosen and an output is used as a response.

Connect backend to frontend to view sighting data

Connect the backend we just set up with our frontend from Bigfoot JSON to achieve the same functionality we had before.

  1. Start the backend server with npm start

  2. Start the frontend server with npm start from the root of the Bigfoot Frontend repo

  3. Tweak the logic in the frontend to show only the date and location properties of each sighting on the homepage and date, location and notes properties of each sighting on sighting-specific pages

Using sighting ID instead of array index to reference individual sightings

In Bigfoot JSON we may have used array index to reference specific sightings, e.g. when we navigate to a sighting-specific page. Now that we store our sightings in SQL, we will want to use the SQL id property of each sighting instead.

Rocket recommends renaming all mentions of sighting index to sighting ID instead for clarity.

Create backend route to create new sighting

  1. Add Express JSON middleware using app.use(express.json()); above our routes in index.js to enable Express to parse JSON bodies of incoming POST requests

  2. Create a route for a POST request to /sightings to create a new sighting in our database using Sequelize and return the new sighting with res.json. We can access request body attributes using req.body.

    1. Follow the MVC setup within the boilerplate

    2. Create a new method within the SightingsController class, controllers/sightingsController.js that contains the logic to add a new sighting into the sighting table.

    3. Create a new route within the routes() method within the SightingsRouter class, bind the new method that you created in the previous step with the class itself.

    4. Note we design our API to send a POST request to /sightings instead of paths like /createSighting because REST API best practices suggest using nouns instead of verbs in paths, using request method to communicate type of action

  3. Verify our route works by sending a POST request with Thunder Client to localhost:3000/sightings with the relevant new sighting data in a JSON body

Your Thunder Client output may look something like the following.

Create new page on frontend to input new sighting

  1. Create new frontend route /new in index.js in the Bigfoot Frontend repo where we will render our new sighting form component

  2. Create new component for new sighting form in the components folder and import it in index.js where we can render it for the /new frontend route. This component should render a sighting form that sends a POST request to our API server on submit and redirect to the sighting-specific route for the new sighting after submit.

    1. Reminder to use controlled form inputs to manage forms in React

    2. Consider using the HTML input type datetime-local to input dates and times compatible with our Sequelize DATE data type

    3. Here is Axios POST request syntax for reference

    4. Reminder to use the BACKEND_URL constant we defined in Bigfoot JSON to access our backend, instead of hard-coding the backend URL, which would make our code more prone to bugs.

    5. Consider using React Router's useNavigate hook and navigate function to navigate to the sighting-specific page after form submit. Navigate after the axios promise resolves so that when we reach the sighting-specific page our app will be able to retrieve the new sighting from the database. Retrieve the sighting ID of the new sighting from the response to our POST request at res.data.

  3. Create a link at the root route / that navigates users to /new to submit a new sighting. Consider using React Router's Link component for this, and consider creating a new component to encapsulate both the new sighting button and sighting list to render at the / route in index.js.

Comfortable: Edit Sightings

  1. Create an edit sighting route in the backend that accepts PUT requests to /sightings/:sightingId. Requests to this route should update the relevant sighting's data in the database and return the full sighting data to the client, similar to the POST route to create a new sighting. Test the new route in Thunder Client before moving on.

    1. Follow the MVC setup within the boilerplate

    2. Create a new method within the SightingsController, controllers/sightingsController.js that contains the logic to edit a particular sighting within the sighting table. Use the put not post

    3. Create a new route within the routes() method within the SightingsRouter class, bind the new method that you created in the previous step with the class itself.

  2. Create an edit sighting form in the frontend at /sightings/:sightingId/edit that automatically pre-populates with that sighting's data in the backend. Submitting the form should save the sighting's new data in the backend and redirect to the sighting-specific page, similar to functionality of the new sighting form.

  3. Create a link on sighting-specific pages to edit that sighting. That link should navigate to the edit sighting form for that sighting.

More Comfortable: Add Location Details

  1. Update our Sighting model in the backend to include location properties such as city and country, and rename the location property to locationDescription for clarity

  2. Create a new migration using Sequelize CLI's migration:generate function to add city and country columns to the Sightings table and update the location column name to locationDescription. Remember to add a down function in the migration to undo the migration. Use the existing migration as a reference; you may find the Sequelize queryInterface API docs helpful.

  3. Update new sighting form and edit sighting form in the frontend to accept city and country data and send location description data correctly, updating field names where relevant

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.1: Bigfoot JSONNext3.E.3: Bigfoot SQL 1-M

Last updated