Server-Side Scripting/SQL Databases/Node.js (Express)
Appearance
routes/lesson9.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 fs = require("fs");
const handlebars = require('handlebars');
const sqlite3 = require("sqlite3")
const router = express.Router();
const DATABASE = "temperature.db";
router.get("/", async (request, response) => {
let result = "";
try {
await checkDatabase();
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 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;
}
module.exports = router;
templates/lesson9.html
[edit | edit source]<!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
[edit | edit source]See Server-Side Scripting/Routes and Templates/Node.js (Express) to create a test environment.