[SOLVED] Web322 assignment 5 build upon assignment 4 by refactoring our code to use a postgres database to manage our national historic sites

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.

 

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

 

 

 

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

 

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

 

 

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”!

 

 

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:

 

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

let sites = [];

 

  ‘$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.

  ‘$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.

 

 

 

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.

 

 

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

 

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

<%= provinceOrTerritory.name %>

</option>

<% }) %>

 

 

 

 

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

 

 

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>

 

 

 

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.

 

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

 

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

 

 

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

 

 

 

 

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.

 

 

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:

 

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)

 

 

 

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

 

 

 

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

 

 

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.

 

 

 

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

 

 

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

 

 

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

 

 

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 %>

 

 

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

 

 

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.:

 

 

 

 

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:

 

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

 

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

*  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:

*

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