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

From Wikiversity
Jump to navigation Jump to search

app.js[edit | edit source]

// 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://github.com/mapbox/node-sqlite3/wiki
//  https://blog.pagesd.info/2019/10/29/use-sqlite-node-async-await/

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

const DATABASE = "temperature.db";
const FORM = `
<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>
`;

app.use(express.urlencoded({
  extended: true
}));

app.get("/", async (request, response) => {
    try {
        await checkDatabase();
        let result = FORM + await getData();
        response.send(result);
    }
    catch(error) {
        response.send(error);
    }
});

app.post("/", async (request, response) => {
    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)
        }

        let result = FORM + await getData();
        response.send(result);
    }
    catch(error) {
        response.send(error);
    }
});

async function checkDatabase() {
    let sql = `
            SELECT COUNT(*) AS Count FROM sqlite_master
            WHERE name = 'Countries';
        `
    let parameters = {};
    let rows = await sqliteAll(sql, parameters);
    if (rows[0].Count > 0) {
        return;
    }

    sql = `
        CREATE TABLE Countries(
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            Country TEXT UNIQUE NOT NULL,
            Temperature REAL NOT NULL);
        `
    parameters = {};
    await sqliteRun(sql, parameters);
}

async function getData() {
    let sql = `
            SELECT ID, Country, Temperature FROM Countries;
        `
    let parameters = {};
    let rows = await sqliteAll(sql, parameters);

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

async function countryExists(country) {
    let sql = `
            SELECT EXISTS(
                SELECT * FROM Countries
                WHERE Country = $country) AS Count;
        `
    let parameters = {
        $country: country
    };
    let rows = await sqliteAll(sql, parameters);
    let result = !!rows[0].Count;
    return result;
}

async function insertCountry(country, temperature) {
    let sql = `
            INSERT INTO Countries (Country, Temperature)
            VALUES($country, $temperature);
        `
    let parameters = {
        $country: country,
        $temperature: temperature
    };
    await sqliteRun(sql, parameters);
}

async function updateCountry(country, temperature) {
    let sql = `
            UPDATE Countries
            SET Temperature = $temperature
            WHERE Country = $country;
        `
    let parameters = {
        $country: country,
        $temperature: temperature
    };
    await sqliteRun(sql, parameters);
}

async function deleteCountry(country) {
    let sql = `
            DELETE FROM Countries
            WHERE Country = $country;
        `
    let parameters = {
        $country: country
    };
    await sqliteRun(sql, parameters);
}

async function sqliteAll(sql, parameters) {
    let promise = new Promise((resolve, reject) => {
        let database = new sqlite3.Database(DATABASE);
        database.serialize();
        database.all(sql, parameters, function(error, rows) {
            if (error)
                reject(error);
            else
                resolve(rows);
        });
        database.close();
    });

    let result = await promise;
    return result;
}

async function sqliteRun(sql, parameters) {
    let promise = new Promise((resolve, reject) => {
        let database = new sqlite3.Database(DATABASE);
        database.serialize();
        database.run(sql, parameters, function(error, rows) {
            if (error)
                reject(error);
            else
                resolve(rows);
        });
        database.close();
    });

    let result = await promise;
    return result;
}

app.listen(3000, () => console.log('server started'));

Try It[edit | edit source]

Copy and paste the code above into the following free online development environment or use your own Node.js (Express) compiler / interpreter / IDE.