Description

Objective:

Build upon Assignment 4 by refactoring our code to use a Postgres database to manage our National Historic Sites

Data, as well as enable the creation, modification and deletion of Sites in the collection.

If you require a clean version of Assignment 4 to begin this assignment, please email your professor.

 

NOTE: Please reference the sample: https://as5-322-sample1.vercel.app/ when creating your solution.  Once again, the UI does not have to match exactly, but this will help you determine which elements / syntax should be on each page and you must implement the functionalities shown from the sample app. You may copy any HTML / CSS code from here if it helps with your solution.

 

Additionally, since this sample is shared with all students in the class, please do not add any content to the Site collection that may be considered harmful or disrespectful to other students.

Part 1: Connecting to the DB & Adding Existing Sites

 

Since the major focus of this assignment will be refactoring our code to use a Postgres database, let’s begin with this.  Follow the course notes PostgreSQL (Postgres) to set up a database on https://neon.tech and obtain the (pooled) connection string, from which extract the PGHOST, PGDATABASE, PGUSER, PGPASSWORD values based on the following format:

 

“postgresql://PGUSER:PGPASSWORD@PGHOST/PGDATABASE?sslmode=require”

 

Now, with your assignment folder open, add the file “.env” in the root of your solution and add the text:

 

DB_USER=”PGUSER”

DB_DATABASE=”PGDATABASE”

DB_PASSWORD=”PGPASSWORD”

DB_HOST=”PGHOST”

 

Where: PGUSER, PGDATABASE, PGPASSWORD and PGHOST are the values that you recorded from Neon.tech (above)

 

To actually connect to the database and use the .env file, we must install the Sequelize,pg / pg-hstore and dotenv modules from NPM:

 

npm install sequelize pg pg-hstore dotenv

 

Finally, before we write our Sequelize code, open the file: /modules/data-service.js and add the “dotenv” module at the top using the code:

 

require(‘dotenv’).config();

 

This will allow us to access the DB_USER, DB_DATABASE, etc. values from the “.env” file using the “process.env” syntax, ie:  process.env.DB_USER, process.env.DB_DATABASE, etc.

 

Beneath this line, add the code to include the “sequelize” module:

 

const Sequelize = require(‘sequelize’);

 

and create the “sequelize” object only using let sequelize = new Sequelize( … ); – see: “Getting Started” in the Relational Database (Postgres) Notes. Be sure to include all of the correct information using process.env for “database”, “user”, “password” and “host”.

 

With our newly created “sequelize” object, we can create the two “models” required for our Assignment according to the below specification (Column Name / Sequelize Data Type):

 

NOTE: We also wish to disable the createdAt and updatedAt fields – see: Models (Tables) Introduction

 

• ProvinceOrTerritory: const ProvinceOrTerritory = sequelize.define(‘ProvinceOrTerritory’, { … });

Column Name	Sequelize DataType
code	Sequelize.STRING primaryKey (true)
name	Sequelize.STRING
type	Sequelize.STRING
region	Sequelize.STRING
capital	Sequelize.STRING

 

• Site: const Site = sequelize.define(‘Site’, { … });

Column Name	Sequelize DataType
siteId	Sequelize.STRING primaryKey (true)
site	Sequelize.STRING
description	Sequelize.TEXT
date	Sequelize.INTEGER
dateType	Sequelize.STRING
image	Sequelize.STRING
location	Sequelize.STRING
latitude	Sequelize.FLOAT
longitude	Sequelize.FLOAT
designated	Sequelize.INTEGER
provinceOrTerritoryCode	Sequelize.STRING

 

Now that the models are defined, we must create an association between the two:

 

Site.belongsTo(ProvinceOrTerritory, {foreignKey: ‘provinceOrTerritoryCode’})

 

Adding Existing Sites using “BulkCreate”

 

With our models correctly defined, we have everything that we need to start working with the database.  To ensure that our existing data is inserted into our new “ProvincesAndTerritories” and “Sites” tables, copy the code from here:

 

https://seneca-my.sharepoint.com/:t:/g/personal/wei_song_senecapolytechnic_ca/ES1upIlu-QFKlr04iPDHsXQB72ADpAkX3KRnN-e6YqsG6w?e=xLWRGY

 

and insert it at the bottom of the /modules/data-service.js file (beneath all module.exports)

 

(NOTE: this code snippet assumes that you have the below code from Assignment 3 still in place):

const siteData = require(“../data/NHSiteData”);
const provinceAndTerritoryData = require(“../data/provinceAndTerritoryData”);

 

With the code snippet from the above URL in place, open the integrated terminal and execute the command to run it:

 

node modules/data-service.js

 

This should show a big wall of text in the console, followed by “data inserted successfully”!

 

Part 2: Refactoring Existing Code to use Sequelize

 

Now that all of our sites exist on the database, we can refactor our existing code in the data-service.js module to retrieve them.  This can be done by following the below steps:

 

1. Delete the above code snippet to “bulkInsert” (we no longer need it, now that our data is available in the database)
2. Delete the code to read the JSON files / initialize an empty “sites” array:

   const siteData = require(“../data/NHSiteData”);

const provinceAndTerritoryData = require(“../data/provinceAndTerritoryData”);

let sites = [];

3. Delete the JSON files “/data/NHSiteData.json”, “/data/provinceAndTerritoryData.json” as well as the directory “/data”.

 

4. Change your code in “initialize” to instead invoke sync(). If sequelize.sync() resolves successfully, then we can resolve the returned Promise, otherwise reject the returned Promise with the error.
5. Change your code in the “getAllSites()” function to instead use the “Site” model (defined above) to resolve the returned Promise with all returned sites (see: Operations (CRUD) Reference).

   NOTE: Do not forget the option include: [ProvinceOrTerritory] to include ProvinceOrTerritory data when invoking “findAll”.

6. Change your code in the “getSiteById(id)” function to instead use the “Site” model (defined above) to resolve the returned Promise with a single site whose id value matches the “id” parameter. As before, if no site was found, reject the Promise with an error, ie: “Unable to find requested site”

   NOTE: Do not forget the option include: [ProvinceOrTerritory] to include ProvinceOrTerritory data when invoking “findAll”.  Also, remember to resolve with the first element of the returned array ([0]) to return a single object, as “findAll” always returns an array

7. Change your code in the “getSitesByProvinceOrTerritoryName(provinceOrTerritory)” function to instead use the “Site” model (defined above) to resolve the returned Promise with all the returned sites whose “ProvinceOrTerritory.provinceOrTerritory” property contains the string in the “provinceOrTerritory” parameter. This will involve a more complicated “where” clause, ie:

   findAll({include: [ProvinceOrTerritory], where: {

  ‘$ProvinceOrTerritory.provinceOrTerritory$’: {

    [Sequelize.Op.iLike]: `%${provinceOrTerritory}%`

  }

}});

 

As before, if no sites were found, reject the Promise with an error, ie: “Unable to find requested sites”

NOTE: We have once again included the option include: [ProvinceOrTerritory] to include ProvinceOrTerritory Data.

8. Change your code in the “getSitesByRegion(region)” function to instead use the “Site” model (defined above) to resolve the returned Promise with all the returned sites whose “ProvinceOrTerritory.region” property contains the string in the “region” parameter. This will involve a more complicated “where” clause, ie:

   findAll({include: [ProvinceOrTerritory], where: {

  ‘$ProvinceOrTerritory.region$’: region

}});

 

As before, if no sites were found, reject the Promise with an error, ie: “Unable to find requested sites”

NOTE: We have once again included the option include: [ProvinceOrTerritory] to include ProvinceOrTerritory Data.

 

9. Look through your .ejs files for “site.provinceOrTerritoryObj.region” or “site.provinceOrTerritoryObj.provinceOrTerritory” and instead replace them with: “site.ProvinceOrTerritory.region” or  ” site.ProvinceOrTerritory.provinceOrTerritory” correspondingly.  This is because when “including” the ProvinceOrTerritory model in our above functions, we have added a full “ProvinceOrTerritory” object to the result objects (site / sites).

 

10. Test your code by running the usual node server.js – it should work exactly as before!

Part 3: Adding New Sites

 

Since we are now using a database to manage our data, instead of JSON file(s), the next logical step is to enable users to Create / Update and Delete site data.  To begin, we will first create the logic / UI for creating sites and will focus on editing and deleting in the following steps.

 

Creating the Form

 

To begin, we should create a simple UI with a form according to the following specification

 

• This should be in a new file under “views”, ie “/views/addSite.ejs”
• It should have a daysyUI “hero” component, etc. text to match the other views, ie: “Add Site”
• It should render the “navbar” partial with a “page” value of “/addSite”, ie:
  <%- include(‘partials/navbar’, {page: ‘/addSite’ }) %>
• It must have the following form controls and submit using “POST” to “/addSite” (to be created later)
  NOTE: The HTML in the sample code may be used here to help render the form (if you wish)
  • siteId
    • input type=”text”
    • required
  • site
    • input type=”text”
    • required
  • image
    • input type=”url”
    • required
  • date
    • input type=”number”
    • required
  • dateType
    • input type=”text”
    • required
  • designated
    • input type=”number”
    • required
  • location
    • input type=”text”
    • required
  • provinceOrTerritoryCode
    • select
    • required
    • each option must be a “provinceOrTerritory” (added to the view later), ie:

      <% provincesAndTerritories.forEach(provinceOrTerritory=>{ %>

<option value=”<%= provinceOrTerritory.code %>”>

<%= provinceOrTerritory.name %>

</option>

<% }) %>

• description
  • textarea

 

• latitude
  • input type=”number” step=”0.00000001″
  • required

 

• longitude
  • input type=”number” step=”0.00000001″
  • required

 

• Submit Button

 

NOTE: Do not forget to run the command npm run tw:build after creating the form, as new CSS was likely used.

 

Updating the Navbar Partial

 

As you have noticed from the above steps, a small update is required to our navbar to support linking to the view & highlighting the navbar item.  To achieve this, add the following navbar item where appropriate (ie: in the regular & responsive navbar HTML elements)

<li><a class=”<%= (page == ‘/addSite’) ? ‘active’ : ” %>” href=”/addSite”>Add to Collection</a></li>

 

 

Adding a New View: “500.ejs”

 

Since it’s possible that we may encounter database errors, we should have some kind of “500” error message to show the user instead of rendering a regular view.  To get started, make a copy of your “404.ejs” file and update it to show the text “500” as well as any other cosmetic updates you would like to use.

Creating the routes in server.js

 

To correctly serve the “/addSite” view and process the form, two routes are required in your server.js code (below).

Additionally, since our application will be using urlencoded form data, the “express.urlencoded({extended:true})” middleware should be added

 

• GET /addSite

  This route must make a request to a Promise-based “getAllProvincesAndTerritories()” function (to be added later in the data-service.js module):

Once the Promise has resolved with the sites, the “addSite” view must be rendered with them, ie:
res.render(“addSite”, { provincesAndTerritories: provincesAndTerritories });

• POST /addSite

  This route must make a request to a Promise-based “addSite(siteData)” function (to be added later in the data-service.js module), and provide the data in body as the “siteData” parameter.

  Once the Promise has resolved successfully, redirect the user to the “/sites” route.

  If an error was encountered, instead render the new “500” view with an appropriate message, ie:

  res.render(“500”, { message: `I’m sorry, but we have encountered the following error: ${err}` });

  NOTE: we do not explicitly set the “500” status code here, as it causes unexpected behavior on Vercel.

 

Adding new functionality to data-service.js module

 

In our new routes, we made some assumptions about upcoming functionality to be added in the “data-service.js” module, specifically: “addSite(siteData)” and ” getAllProvincesAndTerritories ()”.  To complete the functionality to add new sites, let’s create these now:

NOTE: do not forget to use “module.exports” to make these functions available to server.js

 

• addSite(siteData)

  This function must return a Promise that resolves once a site has been created, or rejects if there was an error.

  It uses the “Site” model to create a new Site with the data from the “siteData” parameter.  Once the ‘create’ function has resolved successfully, resolve the Promise returned without any data by the addSite(siteData) function.

  However, if the function did not resolve successfully, reject the Promise returned with the message by the addSite(siteData) function from the first error, ie: errors[0].message (this will provide a more human-readable error message)

• getAllProvincesAndTerritories()

  This function must return a Promise that resolves with all the provincesAndTerritories in the database. This can be accomplished using the “ProvinceOrTerritory” model to return all of the provincesAndTerritories (ProvinceOrTerritory objects) in the database

 

 

Test the Server

 

We should now be able to add new sites to our collection.  Additionally, if we accidentally create a site with a duplicate id, our users will see the “500” status code.

 

Part 4: Editing Existing Sites

 

In addition to allowing users to add sites, we should also let them edit existing sites. Let’s try to follow the same development methodology used when creating the functionality for adding new sites.  This means starting with the view:

 

• This should be in a new file under “views”, ie “/views/editSite.ejs”
• It should have a daisyUI ‘hero’ component, etc. text to match the other views, ie: “Edit Site” followed by the site name, ie <%= site.site %>
• It should render the “navbar” partial with an empty “page” value “” (since this page will not be represented in the navbar), ie: <%- include(‘partials/navbar’, {page: ” }) %>
• It must have the exact form controls as the “addSite” view, however the values must be populated with data from a “site” object, passed to the view. For each of the controls, this will simply mean setting a “value” attribute, e.g.:

  value=”<%= site.siteId %>” or value=”<%= site.site %>”

However, things get more complicated when we wish to correctly set the “selected” attribute of the “provinceOrTerritoryCode” select control, ie:

<% provincesAndTerritories.forEach(provOrTerr=>{ %>

<option <%= (site.provinceOrTerritoryCode == provOrTerr.code) ? “selected” : “” %> value=”<%= provOrTerr.code %>”>

<%= provOrTerr.name %>

</option>

<% }) %>

(once again this assumes that a “provincesAndTerritories” collection will be added to the view along with a “site” object later)

• Ensure that the “siteId” text field is set to readonly (you may wish to optionally give it the “cursor-not-allowed” class (see: https://tailwindcss.com/docs/cursor) and change the background color using the ‘bg-gray-100’ class

 

• Finally, we should change the “action” attribute on the <form> control to submit the form to “/editSite” as well as change the submit button text to something like “Update Site”

 

Creating the routes in server.js

 

To correctly serve the “/editSite” view and process the form, two routes are required in your server.js code (below).

 

• GET “/editSite/:id”

  This route must make a request to the Promise-based “getSiteById(id)” function with the value from the id route parameter as “siteId” in order to retrieve the correct site.

  It also must make a request to the Promise-based getAllProvincesAndTerritories () function in order to retrieve an array of “provincesAndTerritories” data

  Once the Promises have resolved with the provincesAndTerritories data and the site data the “edit” view must be rendered with them, ie:

  render(“edit”, { provincesAndTerritories: provincesAndTerritories, site: site });

  However, if there was a problem obtaining the site or collection of provincesAndTerritories (ie: the Promises were rejected), instead render the “404” view with an appropriate message, ie:

  res.status(404).render(“404”, { message: err });

• POST “/editSite”

  This route must make a request to a Promise-based “editSite(id, siteData)” function (to be added later in the data-service.js module), and provide the data in body.id as the “id” parameter and req.body as the “siteData” parameter

  Once the Promise has resolved successfully, redirect the user to the “/sites” route.

  If an error was encountered, instead render the “500” view with an appropriate message, ie:

  res.render(“500”, { message: `I’m sorry, but we have encountered the following error: ${err}` });

  NOTE: we do not explicitly set the “500” status code here, as it causes unexpected behavior on Vercel.

 

Adding new functionality to data-service.js module

 

In our new routes, we made some assumptions about upcoming functionality to be added in the “data-service.js” module, specifically: “editSite(id, siteData)”.  To complete the functionality to edit sites, let’s create this now:

NOTE: do not forget to use “module.exports” to make the function available to server.js

 

• editSite(id, siteData)

  This function must return a Promise that resolves once a site has been updated or rejects if there was an error.

  It uses the “Site” model to update an existing site that has an “siteId” property that matches the “id” parameter to the function, with the data from the “siteData” parameter.  Once this function has resolved successfully, resolve the Promise returned by the editSite(id, siteData) function without any data.

  However, if the function did not resolve successfully, reject the Promise returned by the editSite(id,siteData) function with the message from the first error, ie: errors[0].message (this will provide a more human-readable error message)

Adding an Edit button

 

At the moment, we should be able to edit any of our Sites by going directly to the route in the browser, ie: “/editSite/AB001”  However, it makes more sense from a usability perspective to allow users the ability to navigate to this route using the UI.

 

To achieve this, add an “edit” link (rendered using the tailwind button classes, ie: “btn btn-success”, etc) in the site.ejs template that links to: “/editSite/id” where id is the “siteId” value of the current site, ie: <%= site.siteId %>

 

Add text “Is WorldHeritageSite” and the Boolean value of the “worldHeritageSite” field of site object in the site.ejs template. So when the value is true, the “Is WorldHeritageSite” checkbox on the “Edit Site” page should be checked.

 

 

Test the Server

 

We should now be able to edit sites to our collection!

 

Part 5: Deleting Existing Sites

 

The final piece of logic that we will implement in this assignment is to enable users to remove (delete) an existing site from the database.  To achieve this, we must add an additional function on our data-service.js module:

 

NOTE: do not forget to use “module.exports” to make the function available to server.js

 

• deleteSite(id)

  This function must return a Promise that resolves once a site has been deleted, or rejects if there was an error.

  It uses the “Site” model to delete an existing site that has an “siteId” property that matches the “id” parameter to the function.  Once this function has resolved successfully, resolve the Promise returned by the deleteSite(id) function without any data.

  However, if the function did not resolve successfully, reject the Promise returned by the editSite(id,siteData) function with the message from the first error, ie: errors[0].message (this will provide a more human-readable error message)

 

With this function in place, we can now write a new route in server.js :

 

• GET “/deleteSite/:id”

  This route must make a request to the Promise-based “deleteSite(id)” function with the value from the id route parameter as “siteId” in order to delete the correct site.

  Once the Promise has resolved successfully, redirect the user to the “/sites” route.

  If an error was encountered, instead render the “500” view with an appropriate message, ie:

  render(“500”, { message: `I’m sorry, but we have encountered the following error: ${err}` });

  NOTE: we do not explicitly set the “500” status code here, as it causes unexpected behavior on Vercel.

 

Finally, let’s add a button in our UI to enable this functionality by linking to the above route for the correct site.  One place where it makes sense is in our “editSite.ejs” view.  Here, we give the user the choice to either update the site or delete it.

To achieve this, add a “Delete Site” link (rendered using the tailwind button classes, ie: “btn btn-error”, etc) that links to: “/deleteSite/id” where id is the “siteId” value of the current site, ie: <%= site.siteId %>

 

Test the Server

 

We should now be able to remove sites from our collection!

 

Part 6: Updating “Site Collection” Page

 

Update sites.ejs to show the count of sites by adding <span class=”text-base text-primary”>( … )</span> element (which contains the count of sites displayed on the page) to the <h1> element in the daisyUI ‘Hero’ component, e.g.:

 

 

 

Part 7: Updating your Deployment

 

Double check all steps for Configuring your App for Vercel, e.g., Explicitly Requiring the “pg” Module​ in data-service.js:

 

require(‘pg’); // explicitly require the “pg” module

const Sequelize = require(‘sequelize’);

 

 

Finally, once you have tested your site locally and are happy with it, you’re ready to update your deployed site by pushing your latest changes to GitHub.  However, before you do that, you should add .env to your .gitignore file to prevent your environment variables from being included:

 

File: .gitignore

node_modules

.env

 

Additionally, you should add those environment variable values to your app on Vercel.com by logging in and proceeding to the project “Settings” for your app and navigating to the “Environment Variables” tab.  From there you can add or remove environment variables, e.g.:

 

 

 

For more information on setting up environment variables on Vercel, see:

• https://vercel.com/docs/projects/environment-variables

 

Note: An alternative platform for deploying your assignment apps is Render. Please find more about Render from our course website Alternative (Render).

 

Assignment Submission:

• Add the following declaration at the top of your js file:

/********************************************************************************

*  WEB322 – Assignment 05

*

*  I declare that this assignment is my own work in accordance with Seneca’s

*  Academic Integrity Policy:

*

*  https://www.senecacollege.ca/about/policies/academic-integrity-policy.html

*

*  Name: ______________________ Student ID: ______________ Date: ______________

*

*  Published URL:

*

********************************************************************************/

• Compress (.zip) your assignment folder (the whole project) and submit the .zip file to My.Seneca under
  Assignments -> Assignment 5

Important Note:

• NO LATE SUBMISSIONS for assignments. Late assignment submissions will not be accepted and will receive a grade of zero (0).
• Submitted assignments must run locally, ie: start up errors causing the assignment/app to fail on startup will result in a grade of zero (0)for the assignment.