# 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](https://dbeaver.io/) as a universal database management tool, [pgAdmin](https://www.pgadmin.org/) for Windows specific software and [Postico](https://eggerapps.at/postico/) for Mac specific software, all of which are free. You are free to use any other SQL clients you may prefer. 1. Universal tool: [DBeaver downloader](https://dbeaver.io/download/) 1. For further instructions on DBeaver please refer to this [section](/3-backend/3.2-sql/3.2.6-dbeaver.md). 2. Windows: [pgAdmin installation instructions](https://stackoverflow.com/questions/45707319/pgadmin-on-windows-10-with-postgres-when-installed-via-bash-on-ubuntu-on-windows/54192456#54192456) 3. Mac: [Postico installation instructions](https://eggerapps.at/postico/) ## \[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. {% code title=".sequelizerc" %} ```javascript 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"), }; ``` {% endcode %} 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. ```bash 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. {% code title="config/database.js" %} ```javascript module.exports = { "development": { "username": "my_unix_username", "password": null, "database": "my_app_development", "host": "127.0.0.1", "dialect": "postgres" } } ``` {% endcode %} 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](https://bootcamp.rocketacademy.co/2-full-stack/2.2-advanced-react/2.2.7-environmental-variables). **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. {% code title="20220601140735-sightings.js" %} ```javascript "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, {}); */ }, }; ``` {% endcode %} #### 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`](https://sequelize.org/api/v6/class/src/dialects/abstract/query-interface.js~queryinterface#instance-method-bulkInsert) and [`queryInterface.bulkDelete`](https://sequelize.org/api/v6/class/src/dialects/abstract/query-interface.js~queryinterface#instance-method-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. {% code title="20220601140735-sightings.js" overflow="wrap" %} ```javascript "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').", createdAt: new Date(), updatedAt: 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.", createdAt: new Date(), updatedAt: 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.", createdAt: new Date(), updatedAt: new Date(), }, ], {} ); }, async down(queryInterface, Sequelize) { await queryInterface.bulkDelete("sightings", null, {}); }, }; ``` {% endcode %} #### 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](https://github.com/rocketacademy/bigfoot-sql-backend-bootcamp) 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` {% code title="config/database.js" %} ```json 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 } } ``` {% endcode %} #### Sample view of `sightings` table in Postico ![Sample view of Sightings table in Postico after running migrations and seeders](/files/WZ8LjCvG6Otp4NAtThf7) ## 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 ![Sample request to localhost:3000/sightings with Thunder Client](/files/LpRNlFw3Du8pv9gZV1vc) #### 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 {% hint style="warning" %} **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. {% endhint %} ### Create backend route to create new sighting 1. Add [Express JSON middleware](https://expressjs.com/en/api.html#express.json) 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](https://stackoverflow.blog/2020/03/02/best-practices-for-rest-api-design/#h-use-nouns-instead-of-verbs-in-endpoint-paths) 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. ![POST request to our API server at localhost:3000/sightings with Thunder Client](/files/ds96J41C2hWcRKHHIZp4) ### 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`](https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/datetime-local) to input dates and times compatible with our Sequelize DATE data type 3. [Here is Axios POST request syntax](https://axios-http.com/docs/post_example) 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](https://reactrouter.com/docs/en/v6/getting-started/overview#navigation) 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](https://reactrouter.com/docs/en/v6/getting-started/overview#navigation) 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](https://sequelize.org/api/v6/class/src/dialects/abstract/query-interface.js~queryinterface) 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](https://github.com/rocketacademy/bigfoot-frontend-3.2/tree/solution-sql-base) and the [backend](https://github.com/rocketacademy/bigfoot-sql-backend-bootcamp/blob/solution-sql-base/index.js) for this exercise. You can do better! --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://bc.rocketacademy.co/3-backend/3.e-exercises/3.e.2-bigfoot-sql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.