3.E.2: Bigfoot SQL
Last updated
Last updated
How to create a backend API server with Express, PostgreSQL and Sequelize
Connect a frontend app with a backend API server that uses SQL to store and retrieve data
Create a backend that supports the MVC structure
We will use forms and POST requests to save new Bigfoot sightings to our database.
SQL clients help us visualise our databases with clickable interfaces without repeatedly typing SQL queries. Rocket recommends using as a universal database management tool, for Windows specific software and for Mac specific software, all of which are free. You are free to use any other SQL clients you may prefer.
Universal tool:
For further instructions on DBeaver please refer to this .
Windows:
Mac:
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.
Install sequelize
and pg
to use Sequelize with Postgres in our app.
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.
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.
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.
Verify our Sequelize config file is in config/database.js
and our models
, seeders
and migrations
folders are in a folder called db
.
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.
Create DB based on config
Create the database specified in the config using Sequelize CLI's db:create
command.
We should see output like the following after creating our database on the command line.
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.
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
.
This should generate output like the following.
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.
db:migrate
will perform the following.
Create a table called SequelizeMeta in database if it doesn't exist yet that records which migrations have run on the database so far
Run migration files that have not been run yet, generating relevant tables and columns
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.
Run Sequelize CLI's seed:generate
command to generate a new seed file. Use kebab-case for the seed file name by convention.
This should generate output like the following.
Notice we have a new seed file in our db/seeders
folder. The file should have the following contents.
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.
After collecting data from sightings.json
in the Bigfoot JSON exercise, our new seeder file now looks like the following.
Run our seed file with Sequelize CLI's db:seed:all
command to populate our database with our seed data.
This should generate output like the following.
Continue with your forked copy of the Rocket Academy Bigfoot Frontend repo from the Bigfoot JSON exercise
While developing within the Backend repo follow the MVC pattern.
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.
Run npm i
from the root of the Bigfoot SQL Backend repo to install relevant packages
Create your .env based off the sample provided from the exercise repo.
Update config/database.js
to use your Unix username and name the database bigfoot_sql_development
. Kai's looks like the one below.
Run npx sequelize db:create
to create the bigfoot_app_development
database
Run npx sequelize db:migrate
to set up the database schema
Run npx sequelize db:seed:all
to seed data in the database
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.
database.js
sightings
table in PosticoAllow 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.
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.
Start the API server with npm start
from the root of the Bigfoot SQL Backend repo
Open Thunder Client in VS Code and send a request to localhost:3000/sightings
Verify that we receive the 3 sightings we seeded in our database
Start the API server with npm start
from the root of the Bigfoot SQL Backend repo
Send a request to localhost:3000/sightings/1
in Thunder Client
Verify we receive the data for the first sighting in our database
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 the backend we just set up with our frontend from Bigfoot JSON to achieve the same functionality we had before.
Start the backend server with npm start
Start the frontend server with npm start
from the root of the Bigfoot Frontend repo
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 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
.
Follow the MVC setup within the boilerplate
Create a new method within the SightingsController
class, controllers/sightingsController.js
that contains the logic to add a new sighting into the sighting
table.
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.
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 frontend route /new
in index.js
in the Bigfoot Frontend repo where we will render our new sighting form component
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.
Reminder to use controlled form inputs to manage forms in React
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.
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.
Follow the MVC setup within the boilerplate
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
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.
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.
Create a link on sighting-specific pages to edit that sighting. That link should navigate to the edit sighting form for that sighting.
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
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
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.
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 .
Notice the example seed code in the auto-generated comments references and commands. We will be using these methods to insert and delete our seeders respectively. All queryInterface
methods have detailed documentation in Sequelize API docs.
Fork and clone the
Add using app.use(express.json());
above our routes in index.js
to enable Express to parse JSON bodies of incoming POST requests
Note we design our API to send a POST request to /sightings
instead of paths like /createSighting
because suggest using nouns instead of verbs in paths, using request method to communicate type of action
Consider using the to input dates and times compatible with our Sequelize DATE data type
for reference
Consider using 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
.
Create a link at the root route /
that navigates users to /new
to submit a new sighting. Consider using 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
.
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 helpful.
Here is reference code for the and the for this exercise. You can do better!
Sightings
table in Postico after running migrations and seederslocalhost:3000/sightings
with Thunder Clientlocalhost:3000/sightings
with Thunder Client