CSE 340: Web Backend Development

W02 Learning Activity: Displaying Database results in EJS

Overview

In previous activities, you have created a database, connected to it from your Node.js application, and created tables with sample data. In this activity, you will learn how to query the database from your Node.js application and render it using an EJS template.

Activity Instructions

Follow these steps to complete the activity:

Create a model file for Organizations

1. Create a new file src/models/organizations.js .
2. Add a function to query the database and get all the organizations, by adding the following code to the src/models/organizations.js file.

   import db from './db.js'
   
   const getAllOrganizations = async() => {
       const query = `
           SELECT organization_id, name, description, contact_email, logo_filename
         FROM public.organization;
       `;
   
       const result = await db.query(query);
   
       return result.rows;
   }
   
   export {getAllOrganizations}  
   

   This code does the following:

   • Imports the database connection from the db.js file.
   • Defines an asynchronous function getAllOrganizations that queries the database for all organizations.
   • Returns the rows of the result if successful.
   • Exports the getAllOrganizations function so it can be used in other parts of the application.

Use the model function in server.js

1. In src/server.js, import the getAllOrganizations function, aft the other imports at the top of the file:

   import { getAllOrganizations } from './src/models/organizations.js';

2. Next, use the getAllOrganizations function to get the list of organizations.
   • Find the event handler for the organizations route.
   • Make sure the event handler is asynchronous, by ensuring the async keyword is present before the function parameters.
   • Inside the event handler, call the getAllOrganizations function using the await keyword and store the result in a variable named organizations.
   • For now, add a console.log statement to print the organizations variable to the console.

   The route handler should now look as follows:

   app.get('/organizations', async (req, res) => {
       const organizations = await getAllOrganizations();
       console.log(organizations);
         
       const title = 'Our Partner Organizations';
       res.render('organizations', { title });
   });
   

Test the query

1. Start your Node.js application by running npm run dev in your terminal.
2. Open a web browser and navigate to http://127.0.0.1:3000/organizations to trigger the event handler.
3. Check the terminal where your Node.js application is running. You should see the list of organizations printed to the console.
4. If you see the list of organizations, congratulations! You have successfully queried the database from your Node.js application.

Update server.js to pass the organizations to the EJS template

Now that you have tested the query and verified that you can get the information from the database and display it to the console, you can update your server.js file so that instead of displaying it to the console, you can pass the data to your EJS template to render it.

In the event handler for the organizations route in src/server.js, remove the line of code that displayed it to the console. Instead, pass it to the EJS template by updating the res.render call.

The route handler should now look as follows:

app.get('/organizations', async (req, res) => {
    const organizations = await getAllOrganizations();
    const title = 'Our Partner Organizations';

    res.render('organizations', { title, organizations });
});

Notice that you are passing the organizations list to the template so it has access to it.

Display the results in EJS

Now it is time to display the results in your EJS template.

1. Open the EJS template file for the organizations page. This file should be named src/views/organizations.ejs
2. In the EJS template file, use a loop to iterate through the organizations variable and display the organization names and contact emails.
3. Make sure to display all the appropriate information for the organization. You will need to update the image src location to insert the logo file from the database.

<%- include('partials/header') %>
    <main>
        <h1><%= title %></h1>
        
        <p>Here are a few of our partner organizations:</p>
        <ul>

            <% organizations.forEach(organization => { %>
                <li><img src="/images/<%= organization.logo_filename %>" alt="<%= organization.name %> logo"><strong><%= organization.name %></strong>: <%= organization.contact_email %></li>
            <% }); %>
        </ul>
    </main>

<%- include('partials/footer') %>

Test the EJS rendering

First test the EJS rendering in your development environment.

1. Start your Node.js application by running npm run dev in your terminal (if it is not already running).
2. Open a web browser and navigate to http://127.0.0.1:3000/organizations to view the organizations page.
3. You should see the list of organizations displayed on the page, including their names, descriptions, contact emails, and logos.

Deploy and test

Finally, deploy your updated code to Render.com and test it in the deployed environment.

1. Push your changes to GitHub.
2. Render.com should automatically deploy your updated code.
3. Once the deployment is complete, open a web browser and navigate to your organizations page at the deployed, render site.
4. You should see the list of organizations displayed on the page, just like in your development environment.

Submission

Congratulations! You have now completed all of the learning activities for this week.

To submit this activity, return to Canvas and submit the associated quiz there.

Other Links:

• Return to: Week Overview | Course Home