Description
Objective:
Assignment 5 is a designed as an opportunity to work with a Postgres data source on the server and practice refactoring an application. You will practice the use of pg, sequelize modules. You’ll learn how to use pgadmin GUI tool for PostgreSQL. You will also learn to connect to a provisioned PostgreSQL on Heroku cloud platform and manipulate the data therein.
Have fun!
Specification:
NOTE: If you are unable to start this assignment because Assignment 4 was incomplete – email your professor for a clean version of the Assignment 4 files to start from (effectively removing any custom CSS or text added to your solution). Remember, you must successfully complete ALL assignments to pass this course.
SAFE TO DO: create and safely store a backup copy of your Assignment 4 in case you need it later.
Getting Started:
Before we get started, we must add a new Postgres instance on our web-322 app in Heroku:
• Navigate to the application from your Heroku dashboard
• Click the “Resources” header and type “Postgres” in the bottom text box labeled: “Add-ons”
• Click the “Heroku Postgres” link that comes up
• This should cause a modal window to appear:
• Keep the settings the way they are “Hobby Dev –Free” and click “Provision”
• Click on the new Add-on “Heroku Postgres :: Database” link
• Scroll down to “Administration” and click the “View Credentials”
• Record all of the credentials (ie: Host, Database, User, Port, etc.) – we will be using them to connect to the database:
Getting Started – Cleaning the solution
• To begin: open your Assignment 4 “web322-app” folder in Visual Studio Code
• In this assignment, we will no longer be reading the files from the “data” folder, so remove this folder from the solution
• Inside your data-server.js module, delete any code that is not a module.exports function (ie: global variables, & “require” statements)
• Inside every single module.exports function (ie: module.exports.initialize(), module.exports.getAllEmployees, module.exports.getEmployeesByStatus, etc.), remove all of the code and replace it with a return call to an “empty” promise that invokes reject() – (Note: we will be updating these later), ie:
return new Promise(function (resolve, reject) { reject();
});
Installing “sequelize”
• Open the “integrated terminal” in Visual Studio Code and enter the commands:
o npm install sequelize –save o npm install pg pg-hstore –save
• At the top of your data-service.js module, add the lines:
o const Sequelize = require(‘sequelize’);
o var sequelize = new Sequelize(‘database’, ‘user’, ‘password’, {
host: ‘host’, dialect: ‘postgres’, port: 5432,
dialectOptions: { ssl: true
}
});
o NOTE: for the above code to work, replace ‘database’, ‘user’, ‘password’ and ‘host’ with the credentials that you saved when creating your new Heroku Postgres Database (above)
Creating Data Models
• Inside your data-service.js module (before your module.exports functions), define the following 2 data models (HINT: See “Models (Tables) Introduction” in the Week 7 Notes for examples) • Employee
Column Name Sequelize DataType
employeeNum Sequelize.INTEGER
primaryKey autoIncrement
firstName Sequelize.STRING
last_name Sequelize.STRING
email Sequelize.STRING
SSN Sequelize.STRING
addressStreet Sequelize.STRING
addresCity Sequelize.STRING
addressState Sequelize.STRING
addressPostal Sequelize.STRING
maritalStatus Sequelize.STRING
isManager Sequelize.BOOLEAN
employeeManagerNum Sequelize.INTEGER
status Sequelize.STRING
department Sequelize.INTEGER
hireDate Sequelize.STRING
• Department
Column Name Sequelize DataType
departmentId Sequelize.INTEGER
primaryKey autoIncrement
departmentName Sequelize.STRING
Update Existing data-service.js functions
Now that we have Sequelize set up properly, and our “Employee” and “Department” models defined, we can use all of the Sequelize operations, discussed in the Week 7 Notes to update our data-service.js to work with the database:
initialize()
• This function will invoke the sequelize.sync() function, which will ensure that we can connected to the DB and that our Employee and Department models are represented in the database as tables.
• If the sync() operation resolved successfully, invoke the resolve method for the promise to communicate back to server.js that the operation was a success.
• If there was an error at any time during this process, invoke the reject method for the promise and pass an appropriate message, ie: reject(“unable to sync the database”).
getAllEmployees()
• This function will invoke the Employee.findAll() function
• If the Employee.findAll() operation resolved successfully, invoke the resolve method for the promise (with the data) to communicate back to server.js that the operation was a success and to provide the data.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “no results returned”.
getEmployeesByStatus(status)
• This function will invoke the Employee.findAll() function and filter the results by “status” (using the value passed to the function – ie: “Full Time” or “Part Time”
• If the Employee.findAll() operation resolved successfully, invoke the resolve method for the promise (with the data) to communicate back to server.js that the operation was a success and to provide the data.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “no results returned”.
getEmployeesByDepartment(department)
• This function will invoke the Employee.findAll() function and filter the results by “department” (using the value passed to the function – ie: 1 or 2 or 3 … etc
• If the Employee.findAll() operation resolved successfully, invoke the resolve method for the promise (with the data) to communicate back to server.js that the operation was a success and to provide the data.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “no results returned”.
getEmployeesByManager(manager)
• This function will invoke the Employee.findAll() function and filter the results by “employeeManagerNum” (using the value passed to the function – ie: 1 or 2 or 3 … etc
• If the Employee.findAll() operation resolved successfully, invoke the resolve method for the promise (with the data) to communicate back to server.js that the operation was a success and to provide the data.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “no results returned”.
getEmployeeByNum(num)
• This function will invoke the Employee.findAll() function and filter the results by “employeeNum” (using the value passed to the function – ie: 1 or 2 or 3 … etc
• If the Employee.findAll() operation resolved successfully, invoke the resolve method for the promise (with the data[0], ie: only provide the first object) to communicate back to server.js that the operation was a success and to provide the data.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “no results returned”.
getManagers()
• This function will invoke the Employee.findAll() function and filter the results using “isManager: true”
• If the Employee.findAll() operation resolved successfully, invoke the resolve method for the promise (with the data) to communicate back to server.js that the operation was a success and to provide the data.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “no results returned”.
getDepartments()
• This function will invoke the Department.findAll() function
• If the Department.findAll() operation resolved successfully, invoke the resolve method for the promise (with the data) to communicate back to server.js that the operation was a success and to provide the data.
• If there was an error at any time during this process (or no results were returned), invoke the reject method and pass a meaningful message, ie: “no results returned”.
addEmployee(employeeData)
• Before we can work with employeeData correctly, we must make sure the isManager property is set properly.
To ensure that this value is set correctly, before you start working with the employeeData object, add the line: o employeeData.isManager = (employeeData.isManager) ? true : false;
• Additionally, we must ensure that any blank values (“”) for properties are set to null. For example, if the user didn’t enter a Manager Number (causing employeeData.employeeManagerNum to be “”), this needs to be set instead to null (ie: employeeData.employeeManagerNum = null). You can iterate over every property in an object (to check for empty values and replace them with null) using a for…in loop.
• Now that the isManager is explicitly set (true or false), and all of the remaining “” are replaced with null, we can invoke the Employee.create() function
• If the Employee.create() operation resolved successfully, invoke the resolve method for the promise to communicate back to server.js that the operation was a success.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “unable to create employee”.
updateEmployee(employeeData)
• Like addEmployee(employeeData) we must ensure that the isManager value is explicitly set to true/false and any blank values in employeeData are set to null (follow the same procedure)
• Now that all of the “” are replaced with null, we can invoke the Employee.update() function and filter the operation by “employeeNum” (ie employeeData.employeeNum)
• If the Employee.update() operation resolved successfully, invoke the resolve method for the promise to communicate back to server.js that the operation was a success.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “unable to create employee”.
Adding new data-service.js functions
So far, all our data-service functions have focused primarily on fetching data and only adding/updating Employee data. If we want to allow our users to fully manipulate the data, we must add some additional promise-based data-service functions to add/update Departments:
addDepartment(departmentData)
• Like addEmployee(employeeData) function we must ensure that any blank values in departmentData are set to null (follow the same procedure)
• Now that all of the “” are replaced with null, we can invoke the Department.create() function
• If the Department.create() operation resolved successfully, invoke the resolve method for the promise to communicate back to server.js that the operation was a success.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “unable to create employee”
updateDepartment(departmentData)
• Like addDepartment(departmentData) function we must ensure that any blank values in departmentData are set to null (follow the same procedure)
• Now that all of the “” are replaced with null, we can invoke the Department.update() function and filter the operation by “departmentId” (ie departmentData.departmentId)
• If the Department.update() operation resolved successfully, invoke the resolve method for the promise to communicate back to server.js that the operation was a success.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “unable to create employee”.
getDepartmentById(id)
• Similar to the getEmployeeByNum(num) function, this function will invoke the Department.findAll() function
(instead of Employee.findAll()) and filter the results by “id” (using the value passed to the function – ie: 1 or 2 or
3 … etc
• If the Department.findAll() operation resolved successfully, invoke the resolve method for the promise (with the data[0], ie: only provide the first object) to communicate back to server.js that the operation was a success and to provide the data.
• If there was an error at any time during this process, invoke the reject method and pass a meaningful message, ie: “no results returned”.
Updating Routes (server.js) to Add / Update Departments
/departments/add
• This GET route is very similar to your current “/employees/add” route – only instead of “rendering” the
“addEmployee” view, we will instead set up the route to “render” an “addDepartment” view (added later)
/departments/add
• This POST route is very similar to your current “/employees/add” POST route – only instead of calling the addEmployee() data-service function, you will instead call your newly created addDepartment() function with the POST data.
• Instead of redirecting to /employees when the promise has resolved (using .then()), we will instead redirect to /departments
/department/update
• This POST route is very similar to your current “/employee/update” route – only instead of calling the updateEmployee() data-service function, you will instead call your newly created updateDepartment() function with the POST data.
• Instead of redirecting to /employees when the promise has resolved (using .then()), we will instead redirect to /departments
/department/:departmentId
• This GET route is very similar to your current “/employee/:empNum” route – only instead of calling the getEmployeeByNum() data-service function, you will instead call your newly created getDepartmentById() function with the departmentId parameter value.
• Once the getDepartmentById(id) operation has resolved, we then “render” a “department” view (added later) and pass in the data from the promise
• If the getDepartmentById(id) promise is rejected (using .catch), send a 404 error back to the client using:
res.status(404).send(“Department Not Found”);
Updating Views to Add / Update Departments
In order to provide user interfaces to all of our new “Department” functionality, we need to add / modify some views within the “views” directory of our app:
addDepartment.hbs
Fundamentally, this view is nearly identical to the addEmployee.hbs view, however there are a few key changes:
o The header (<h1>…</h1>) must read “Department” o The form must submit to “/departments/add” o There must be only one input field (type: “text”, name: “departmentName”, label: “Department Name”) o The submit button must read “Add Department • When complete, your view should appear as:
department.hbs
• Like addDepartment.hbs, the department.hbs view is very similar to it’s employee counterpart: employee.hbs, only with a few key differences:
o The header (<h1>…</h1>) must read “departmentName – Department: departmentId” where departmentName and departmentId represent the departmentName and departmentId of the current department.
o The form must submit to “/departments/update”
o The hidden field must have the properties: name=”departmentId” and value=”{{data.departmentId}}” in order to keep track of exactly which department the user is editing
o There must be only one visible input field (type: “text”, name: “departmentName”, label: “Department Name”) it’s value must be preset to the current department’s departmentName (hint: value=”{{data.departmentName}}”) o The submit button must read “Update Department”
o When complete, your view should appear as the following (if we’re currently looking at “Department Three”, for example):
departmentList.hbs
• Lastly, to enable users to access all of this new functionality, we need to make some changes to our current departmentList.hbs file:
o Replace the <h1>{{title}}<h1> element with an updated <h1>…</h1> that also includes a link to “create” a new Department using the code: <h1>{{title}}<a href=”/departments/add” class=”btn btn-success pull-right” style=”margin-top:5px;”>Add New Department</a><h1>
o Replace the code:
<td><a href=”/employees?department={{departmentId}}”>{{departmentId}}</a></td> with
<td><a href=”/department/{{departmentId}}”>{{departmentId}}</a></td>
Now, users can click “Add New Department”, if they wish to add a department; or the Department Number if they wish to edit an existing department!
Updating the “Department” List in the Employee Views
Now that users can add new Departments, it makes sense that all of the Departments available to an employee (either when adding a new employee or editing an existing one), should consist of all the current departments in the database (instead of just 1…7). To support this new functionality, we must make a few key changes to our routes and views:
“/employees/add” route
• Since the “addEmployee” view will now be working with actual Departments, we need to update the route to make a call to our data-service module to “getDepartments”.
• Once the getDepartments() operation has resolved, we then “render” the ” addEmployee view (as before), however this time we will and pass in the data from the promise, as “departments”, ie: res.render(“addEmployee”, {departments: data});
• If the getDepartments() promise is rejected (using .catch), “render” the ” addEmployee view anyway (as before), however instead of sending the data from the promise, send an empty array for “departments, ie: res.render(“addEmployee”, {departments: []}); “addEmployee.hbs” view
• Update the: <select class=”form-control” name=”department” id=”department”>…</select> element to use the new handlebars code:
{{#if departments}}
<select class=”form-control” name=”department” id=”department”>
{{#each departments}}
<option value=”{{departmentId}}”>{{departmentName}}</option>
{{/each}}
</select>
{{else}}
<div>No Departments</div>
{{/if}}
Now, if we have any departments in the system, they will show up in our view – otherwise we will show a div element that states “No Departments” “/employee/:empNum” route
• If we want to do the same thing for existing employees, the task is more complicated: In addition to sending the current Employee to the “employee” view, we must also send all of the Departments. This requires two separate calls to our data-service module ( “dataService” in the below code) that return data that needs to be sent to the view. Not only that, but we must ensure that the right department is selected for the employee and respond to any errors that might occur during the operations. To ensure that this all works correctly, use the following code for the route:
app.get(“/employee/:empNum”, (req, res) => {
// initialize an empty object to store the values let viewData = {};
dataService.getEmployeeByNum(req.params.empNum)
.then((data) => {
viewData.data = data; //store employee data in the “viewData” object as “data”
}).catch(()=>{
viewData.data = null; // set employee to null if there was an error
}).then(dataService.getDepartments)
.then((data) => {
viewData.departments = data; // store department data in the “viewData” object as “departments”
// loop through viewData.departments and once we have found the departmentId that matches
// the employee’s “department” value, add a “selected” property to the matching // viewData.departments object
for (let i = 0; i < viewData.departments.length; i++) {
if (viewData.departments[i].departmentId == viewData.data.department) { viewData.departments[i].selected = true;
}
}
}).catch(()=>{
viewData.departments=[]; // set departments to empty if there was an error
}).then(()=>{
if(viewData.data == null){ // if no employee – return an error res.status(404).send(“Employee Not Found”);
}else{
res.render(“employee”, { viewData: viewData }); // render the “employee” view
}
});
});
“employee.hbs” view
• Now that we have all of the data for the employee inside “viewData.data” (instead of just “data”), we must update every handlebars reference to data, from data.propertyName to viewData.data.propertyName. For example: {{data.firstName}} would become: {{viewData.data.firstName}}
Once this is complete, we need to update the <select class=”form-control” name=”department” id=”department”>…</select> element as well:
{{#if viewData.departments}}
<select class=”form-control” name=”department” id=”department”>
{{#each viewData.departments}}
<option value=”{{departmentId}}” {{#if selected}} selected {{/if}} >{{departmentName}} </option> {{/each}}
</select>
{{else}}
<div>No Departments</div>
{{/if}}
Updating server.js, data-service.js & employeeList.hbs to Delete Employees
To make the user-interface more usable, we should allow users to remove (delete) employees that they no longer wish to be in the system. This will involve:
• Creating a new function (ie: deleteEmployeeByNum(empNum)) in data-service.js to “delete” employees using the Employee.destroy() for a specific employee. Ensure that this function returns a promise and only “resolves” if the Employee was deleted (“destroyed”). “Reject” the promise if the “destroy” method encountered an error (was rejected).
• Create a new GET route (ie: “/employee/delete/:empNum”) that will invoke your newly created deleteEmployeeByNum(empNum) data-service method. If the function resolved successfully, redirect the user to the “/employees” view. If the operation encountered an error, return a status code of 500 and the plain text: “Unable to Remove Employee / Employee not found)”
• Lastly, update the employeeList.hbs view to include a “remove” link for every employee within in a new column of the table (at the end) – Note: The header for the column should not contain any text. The links in every row should be styled as a button (ie: class=”btn btn-danger”) with the text “remove” and link to the newly created GET route “employee/delete/empNum” where empNum is the employee number of the employee in the current row. Once this button is clicked, the employee will be deleted and the user will be redirected back to the “/employees” list.
Sample Solution
To see a completed version of this app running, visit: https://tranquil-bastion-88914.herokuapp.com/
Assignment Submission:
• Add the following declaration at the top of your server.js file:
/********************************************************************************* * WEB322 – Assignment 05
*
*
* Online (Heroku) Link: ________________________________________________________
*
********************************************************************************/
Publish your application on Heroku & test to ensure correctness
• Paste Heroku link for your web application on the Comments Section of the Submissions page. Missing link will attract a penalty of -2. The link should also be pasted in the declaration section near the top of the server.js code.
• Submitted assignments must run locally, i.e. start up errors causing the assignment/app to fail on startup will result in a grade of zero (0) for the assignment.
