Server-Side Scripting/SQL Databases/Node.js (Express MySQL)

routes/lesson9.js

// This program creates and displays a temperature database
// with options to insert, update, and delete records.
//
// References:
//  https://en.wikibooks.org/wiki/JavaScript
//  https://zellwk.com/blog/async-await-express/
//  https://www.mysqltutorial.org/mysql-nodejs/select/


const express = require("express");
const fs = require("fs");
const handlebars = require('handlebars');
const mysql = require("mysql")
const router = express.Router();

const HOST = "localhost";
const USER = "root";
const PASSWORD = "password";
const DATABASE = "Temperature";

router.get("/", async (request, response) => {
    let result = "";

    try {
        await checkDatabase();
        await checkTable();
        result = await getData();
    }
    catch(error) {
        result = error;
    }

    let source = fs.readFileSync("./templates/lesson9.html");
    let template = handlebars.compile(source.toString());
    let data = {
        table: result
    }
    result = template(data);
    response.send(result);
});

router.post("/", async (request, response) => {
    let result = "";

    try {
        let country = request.body.country.trim();
        let temperature = request.body.temperature.trim();

        if (!await countryExists(country)) {
            await insertCountry(country, temperature)
        } else if (temperature != "") {
            await updateCountry(country, temperature)
        } else {
            await deleteCountry(country)
        }

        result = await getData();
    }
    catch(error) {
        result = error;
    }

    let source = fs.readFileSync("./templates/lesson9.html");
    let template = handlebars.compile(source.toString());
    let data = {
        table: result
    }
    result = template(data);
    response.send(result);
});

async function checkDatabase() {
    let database = "INFORMATION_SCHEMA";
    let sql = `
        SELECT COUNT(*) AS Count 
        FROM INFORMATION_SCHEMA.SCHEMATA
        WHERE SCHEMA_NAME = 'Temperature';
    `;
    let parameters = [];
    let data = await mysqlQuery(database, sql, parameters);

    if (data[0].Count > 0) {
        return;
    }

    sql = "CREATE DATABASE Temperature;";
    parameters = [];
    await mysqlQuery(null, sql, parameters);
}

async function checkTable() {
    let database = "INFORMATION_SCHEMA";
    let sql = `
        SELECT COUNT(*) AS Count
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'Temperature'
        AND TABLE_NAME = 'Countries';
        `
    let parameters = [];
    let data = await mysqlQuery(database, sql, parameters);
    if (data[0].Count > 0) {
        return;
    }

    sql = `
        CREATE TABLE Countries(
        ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
        Country VARCHAR(35) UNIQUE NOT NULL,
        Temperature REAL NOT NULL);
        `
    parameters = [];
    await mysqlQuery(DATABASE, sql, parameters);
}

async function getData() {
    let database = DATABASE;
    let sql = `
        SELECT ID, Country, Temperature FROM Countries;
        `
    let parameters = [];
    let data = await mysqlQuery(database, sql, parameters);

    let result = "<table><tr><th>ID</th>";
    result += "<th>Country</th>";
    result += "<th>Temperature</th></tr>";
    for (i = 0; i < data.length; i++) {
        result += "<tr><td>" + data[i].ID + "</td>"
        result += "<td>" + data[i].Country + "</td>"
        result += "<td>"+ data[i].Temperature + "</td></tr>"
    }
    result += "</table>"    
    return result;
}

async function countryExists(country) {
    let database = DATABASE;
    let sql = `
        SELECT EXISTS(
        SELECT * FROM Countries
        WHERE Country = ?) AS Count;
        `
    let parameters = [country];
    let data = await mysqlQuery(database, sql, parameters);
    let result = !!data[0].Count;
    return result;
}

async function insertCountry(country, temperature) {
    let database = DATABASE;
    let sql = `
        INSERT INTO Countries (Country, Temperature)
        VALUES(?, ?);
        `
    let parameters = [country, temperature];
    await mysqlQuery(database, sql, parameters);
}

async function updateCountry(country, temperature) {
    let database = DATABASE;
    let sql = `
        UPDATE Countries
        SET Temperature = ?
        WHERE Country = ?;
        `
    let parameters = [temperature, country];
    await mysqlQuery(database, sql, parameters);
}

async function deleteCountry(country) {
    let database = DATABASE;
    let sql = `
        DELETE FROM Countries
        WHERE Country = ?;
        `
    let parameters = [country];
    await mysqlQuery(database, sql, parameters);
}

async function mysqlConnection(database) {
    return new Promise((resolve, reject) => {
        let connection = mysql.createConnection({
          host: HOST,
          user: USER,
          password: PASSWORD,
          database: database,
        });

        connection.connect( (error) => {
            if (error) {
                reject(error);
            } else {
                resolve(connection);
            }
        });
    });
}

async function mysqlQuery(database, sql, parameters) {
    let connection = await mysqlConnection(database);

    return new Promise((resolve, reject) => {
        connection.query(sql, parameters, function(error, rows) {
            if (error) {
                reject(error);
            } else {
                resolve(rows);
            }
        });
        
        connection.end();
    });
}

module.exports = router;

templates/lesson9.html

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

<head>
    <meta charset="UTF-8">
    <title>Lesson 9</title>
    <link rel="stylesheet" href="styles.css">
</head>

<body>
    <h1>Temperature Data Entry</h1>
    <p>Enter country and temperature. Enter again to update.</p>
    <p>Enter country without temperature to delete.</p>
    <form method="POST">
        <p><label for="country">Country:</label>
            <input type="text" id="country" name="country" required>
        </p>
        <p><label for="stop">Temperature:</label>
            <input type="text" id="temperature" name="temperature">
        </p>
        <p><input type="submit" name="submit" value="Submit"></p>
    </form>
    <hr>
    {{{table}}}
</body>
</html>

Try It

See Server-Side Scripting/Routes and Templates/Node.js (Express) to create a test environment.