CODE PAGE

NODE WITH DATABASE

Here is how you can access a database (MySQL) with NodeJS. We will use cPanel to set things up. cPanel is a very common server admin system. We will use FileZilla for our FTP app to upload files to the server.

cPanel

1. You will not have to do some steps if you already have an account
2. Go to: http://tech.fast.sheridanc.on.ca/
3. If a warning shows choose Advanced and press the Proceed link
4. In the Services Pulldown at top select LAMP/GIT




5. Click the request button
6. You will get an email with your password
7. This is the Microsoft Outlook e-mail
8. at Sheridan Central > e-mail icon top right


9. After recieving the e-mail, login to cPanel:
10. If at Sheridan, go to http://dev.fast.sheridanc.on.ca:2083/
11. If at home, go to http://cpanel.USERNAME.dev.fast.sheridanc.on.ca
12. Login with your Sheridan username and password from the e-mail




DataBase

13. Go to Databases and firstly choose MySQL Databases




14. Add a New Database called wp (your username is a prefix)
15. Secondly, Back on the cPanel front choose phpMyAdmin
16. Select your database username_wp at the top left
17. Under Create New Table
18. Call the table node (lowercase) with 4 columns
19. Press the Create button






Table Fields

20. Add id (INT), name, city, color (TEXT)
21. Make the id A_I (Auto Incrementing) and PRIMARY
22. Press Save




23. Select Insert from the top Tabs
24. Add a record to your table (do NOT fill in id)
25. Choose Browse to see your data in the table




FTP SETUP

26. You can FTP with FileZilla
27. From the FILE menu choose Site Manager
28. Make NEW SITE - call it WP or Sheridan
29. Protocol: SFTP (port 22)
30. Host: dev.fast.sheridanc.on.ca
31. User: Sheridan username
32. Password: password from email






NODEJS APP

33. We will make an app that stores a name, city and color
34. It will show a table of results
35. And make it so we can delete any result




36. Make a folder called database/ on in your Web Programming directory
37. Add the following folders and files by copying from the Database ZIP
38. Here is what the folder structure looks like
39. We will not npm install here - but rather do that on the server later
40. Get the files from this DATABASE ZIP




41. public/database.css



body {
    background-color:#999;
    font-family:verdana;
    color:#ddd;
    text-align: center;
    margin:0px;
}
header {
    margin:0px;
    padding:5px;
    background-color: #222;
    box-shadow: rgba(0,0,0,.3) 0px 10px 10px;
}
form {
    margin:10px;
    padding:30px;
    display:inline-block;
    border-radius: 20px;
    border:thick solid #333;
    background-color: #ddd;
    font-size:30px;
    color:#333;
    width:90%;
    max-width:400px;
    box-shadow: rgba(0,0,0,.3) 10px 10px 10px;
}
input {
    margin-top:20px;
    font-size:30px;
}
input[type=text], input[type=color] {
    margin-bottom:15px;
}
input[type=submit] {
    box-shadow: rgba(0,0,0,.3) 5px 5px 5px;
    padding:10px 20px;
    border:none;
    cursor:pointer;
    border-radius: 10px;
    background-color: rgb(129, 192, 255);
    margin-bottom:15px;
}
input[type=submit]:hover {
    background-color: rgb(181, 208, 255);    
}
footer {
    margin:30px;
    font-size:24px            
}
#welcome {
    font-size:24px;
    font-variant: small-caps;
    font-style: italic;
    margin:30px 0px -10px 0px;
}
table {
    margin:20px;
    display:inline-table;
    border-spacing:5px;
    color:#444
}
th {
    font-size:30px;
    padding:10px 20px;
    background-color: white;
}
td {
    font-size:20px;
    padding:10px 20px;
    background-color: #ccc;
}


/* CONFIRMATION PANEL */
#confirm {
    display:none;
    position: fixed;
    top: 50%;
    left: 50%;
    transform: translate(-50%, -50%);
    width:290px;
    padding:30px;
    text-align: center;
    background-color: yellow;
    border:medium solid #222;
    border-radius: 15px;
    box-shadow: rgb(0,0,0,.2) 10px 10px 10px;
}
#confirm button {
    padding:10px 20px;
    font-size:20px;
    border-radius:10px;
    cursor:pointer;
}
#delete {
    width:125px;
    text-align: center;
    margin-right:15px;
    background-color: red;
    color:white;
}
#delete:hover {
    background-color: rgb(255, 74, 74);
}
#cancel:hover {
    background-color: white;
}



42. public/index.html



<!doctype html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <title>NodeJS with Database</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="database.css">    
</head>

<body>

    <header>
        <h1>Database</h1>        
    </header>

    <br><br>
    <form action="/database" method="post">

        <label for="name">Name
            <br>
            <input type="text" id="name" name="name" required>
        </label>
        <br>
        <label for="city">City
            <br>
            <input type="text" id="city" name="city" list="list" required>
        </label>
        <br>
        <label for="username">Color
            <br>
            <input type="color" id="color" name="color" required>
        </label>
        <br>
        <input type="submit">

        <datalist id="list">
            <option>Oakville</option>
            <option>Toronto</option>
            <option>Hamilton</option>
            <option>Brampton</option>
            <option>Mississauga</option>
            <option>Burlington</option>
        </datalist>

    </form>

</body>

</html>
                



43. views/result.ejs



<!doctype html>
<html lang="en">

<head>
    <meta charset="utf-8">
    <title>Results - NodeJS with Database</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="/database/database.css">      
</head>

<body>

    <header>
        <h1>Database</h1>        
    </header>

    <table>
        <tr>
            <th>Name</th><th>City</th><th>Color</th><th></th>         
        </tr>
        <%
        results.forEach(row => { 
        %>

        <tr>
            <td><%= row.name %></td>
            <td><%= row.city %></td>
            <td style="background-color:<%= row.color %>"></td>
            <td><button class="x" id=<%= row.id %>>X</button></td>
        </tr>
        <%
        });
        %>
    </table>

    <div id="confirm">
        <button id="delete">DELETE</button> 
        <button id="cancel">CANCEL</button>
    </div>

    <script>

        // an html collection of all the delete buttons
        const buttons = document.getElementsByClassName("x"); 

        // add an event to each delete button
        let currentX;
        for (tag of buttons) {
            tag.addEventListener("click", e=>{
                confirm.style.display = "block";
                currentX = e.target; 
            });
        }

        // confirmation pane
        const confirm = document.getElementById("confirm");
        const deleteBut = document.getElementById("delete");
        const cancelBut = document.getElementById("cancel");

        // the delete button on the confirm pane
        deleteBut.addEventListener("click", ()=>{

            // send the delete request to the server
            fetch("/database/delete", {
                method:"POST",
                headers: {"Content-Type": "application/json"},
                body: JSON.stringify({id:currentX.id})
            }).then(response=>{
                return response.json();
            }).then(data=>{
                if (data.error == "none") {
                    // hide the x button's tr tag
                    currentX.parentElement.parentElement.style.display = "none";
                    confirm.style.display = "none";
                } else {                   
                    showError();
                }               
            }).catch(showError);
            function showError() {
                deleteBut.innerText = "ERROR";
                setTimeout(()=>{deleteBut.innerText = "DELETE"}, 1500);
            }
            
        });

        // the cancel button on the confirm pane
        cancelBut.addEventListener("click", ()=>{
            confirm.style.display = "none";
        });

    </script>


</body>

</html>



44. app.js



"use strict";

const express = require("express");
const app = express();
const path = require("path"); 

app.set("port", process.env.PORT || 3000);

// set the template engine
app.set("view engine", "ejs");

// import mysql - could use other database drivers like mongodb
var mysql = require('mysql');

// parse any post data
app.use(express.urlencoded({ extended: false }));

// parse any JSON data
app.use(express.json());


// COULD USE CONTROLLERS FOR THE CODE INSIDE POSTS

// LOGIN
// You can make one database for all your apps 
// and make a table (or tables with prefixes) for each app
// then keep your user, pass and database in a login module
// that has the following code - this is all that is in login.js

// exports.user = "username";
// exports.pass = "password";
// exports.database = "database";

const login = require("./login");

app.post("/database", (req, res)=>{ 

    const connection = mysql.createConnection({
        host     : '127.0.0.1',
        user     : login.user,
        password : login.pass,
        database : login.database
    });

    connection.connect((error)=>{
        if (error) {res.send(error);}
    });

    // INSERT THE DATA
    connection.query(
        "INSERT INTO node(name, city, color) VALUES(?,?,?)", 
        [req.body.name, req.body.city, req.body.color],  
        (error, results, fields)=>{if (error) res.send(error);}
    );
    
    // SHOW THE DATA
    connection.query("SELECT * FROM node", (error, results, fields)=>{

        // // used this in testing
        // results.forEach((row)=>{
        //     output += row.name + " - " + row.color + " - " + row.city + "<br>";
        // });
        // res.send(output);

        if (error) {res.send(error);}

        res.render("results", { results: results }); // or in ES6 just { results }

    });

    // EDITING DATA 
    // We are not editing data in this app (aside from deleting below)
    // You would edit with an SQL UPDATE query: 
    // connection.query(
    //     "UPDATE node SET name=?, city=?, color=? WHERE id=?", 
    //     [req.body.name, req.body.city, req.body.color, req.body.id],
    //     (error, results, fields)=>{if (error) res.send(error);}
    // );

    connection.end();
    
}); // end get database


// DELETING 

app.post("/database/delete", (req, res)=>{ 
    
    const connection = mysql.createConnection({
        host     : '127.0.0.1',
        user     : login.user,
        password : login.pass,
        database : login.database
    });

    connection.connect((error)=>{
        if (error) res.json({"error":error});
    });

    // DELETE THE DATA
    connection.query(
        "DELETE FROM node WHERE id=?", [req.body.id], (error, results, fields)=>{
            if (error) res.json({"error":error});
            else res.json({"error":"none"});
        }
    );

    connection.end();

});

// this will load the index and css as if in database root
app.use("/database", express.static(path.join(__dirname, "public")));


app.listen(app.get("port"), ()=>{
    console.log("running on port " + app.get("port"));
});

                    



45. login.js



exports.user = "sheridan_username";
exports.pass = "password_from_email";
exports.database = "username_database";



46. package.json



{
    "name": "database",
    "version": "1.0.0",
    "description": "",
    "main": "index.js",
    "scripts": {
        "test": "echo \"Error: no test specified\" && exit 1"
    },
    "author": "Dr Abstract",
    "license": "ISC",
    "dependencies": {
        "ejs": "^3.1.9",
        "express": "^4.19.0",
        "mysql": "^2.18.1"
    }
}





Upload Files

47. Start your FileZilla and use the little pulldown arrow at top left
48. Choose your site - whatever you called it - Sheridan or WP, etc.
49. This will show your files on the left and server files on the right




50. Browse to your dababase/ folder on the left (local files)
51. On the right, make an apps/ folder directly in your username folder
52. This should be /data/home/[username]/apps/
53. Never put server files with passwords, etc. in the public_html
54. On the right, open the apps/ folder
55. drag your dababase/ from the left into the apps/ on the right




RUNNING APP IN CPANEL

56. Open cPanel
57. If at Sheridan, go to http://dev.fast.sheridanc.on.ca:2083/
58. If at home, go to http://cpanel.USERNAME.dev.fast.sheridanc.on.ca
59. Scroll to Software, and select Application Manager






60. Press Register Application
61. Set the Application Name to Database
62. Set the Deployment Domain pulldown to Your Domain
63. Set the Base Application URL to database
64. Set the Application Path to apps/database
65. Choose Development while testing and Production when launching
66. Ignore Environment Variables and press DEPLOY


67. Press Ensure Dependencies to run npm install on the server
68. This will do an npm install based on the package.json


69. Make sure the App is Enabled so that it is running
70. To make changes to the app you MUST upload the changes to the server
71. And then Disable and Enable the app
72. This will take about a minute to actually process before the change is live
73. During that time, the app may hang or show errors




TEST YOUR APP

74. Go to http://[username].dev.fast.sheridanc.on.ca/database
75. Type in a name, city and choose a color
76. Press Submit
77. Try that a couple times
78. Press the X next to an entry to delete
79. A confirmation panel will show - press CONFIRM or CANCEL







DEBUGGING

80. Do the database part last when building an app like this
81. Get everything else working locally first
82. Test with node init, npm install, nodemon, etc.


83. Then upload to the server and hook the database up
84. Watch out for typos in field names - keep them lowercase
85. Remember to Disable and Enable the app to see changes
86. Wendi has notes on how to see logs from the server

FULL Sheridan Node documentation can be found in Wendi Jollymore's Lessons.