Share
Explore
f23 MAD 6135 Project Template (Lab test 2)
For Lab Test 2, it is only required to submit the solution for connect to CES.db and display the record the records in it to the Console. Update to part 2. Backend Setup with Express and SQLite:
Lab test 2 Learning outcomes:
Connecting html form to routes in your backend node JS program for the purpose of allowing user to enter information into the HTML form which can be conveyed via Ajax and Microservers to the controller partition, and used for sql (or mongodb) queries.
Once you have done this Lab:
Note: Send either a Zip file or a Word Document.
What more do you need to do for your project?
Present your html front end and sqlite database to present a theme.
CSS: more visually appealing
Pre-requisite: The Lab Worksheet for using sqlite in Node.js
Learning Outcomes:
Make a full stack javaScript application with completely documented code.
Recall for your hand in project: I want to see very complete documentation in Markdown and GitBook: Some UML diagrams.
Your Project FS application will use a backend database which could be:
sqlite
MongoDB
Firebase
in the backend and presents an html form which user enters data into the form which is stored in the sqlite database.
Outline:
1. Setting Up the Environment:
Installing Node.js:
Setting up a new Node project:
bashCopy code
mkdir fullstack-js-app
cd fullstack-js-app
npm init -y
Installing necessary libraries:
bashCopy code
npm install express sqlite3 body-parser
2. Backend Setup with Express and SQLite:
For the initial walk-through of this, I will be using the CES.db college enrollment system database we developed with dbbrowserforsql.
Setting up the Express server (server.js):
Note: This code shows you an in-memory database. For your Project, use a persistent file (file on the file system of the server).
const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();
const app = express();
const PORT = 3000;
// Middleware to parse POST data
app.use(bodyParser.urlencoded({ extended: true }));
// Database setup
let db = new sqlite3.Database(':memory:', (err) => {
if (err) {
return console.error(err.message);
}
console.log('Connected to the in-memory SQlite database.');
});
// Create table
db.run('CREATE TABLE user_data (name TEXT, email TEXT)', (err) => {
if (err) {
return console.error(err.message);
}
});
app.get('/', (req, res) => {
res.sendFile(__dirname + '/index.html');
});
app.post('/submit', (req, res) => {
const { name, email } = req.body;
db.run(`INSERT INTO user_data(name, email) VALUES(?, ?)`, [name, email], (err) => {
if (err) {
return console.error(err.message);
}
res.send('Data saved successfully!');
});
});
app.listen(PORT, () => {
console.log(`Server is running on http://localhost:${PORT}`);
});
CES Database:
3. Frontend Setup:
Creating an HTML form (index.html):
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Full Stack JS App</title>
</head>
<body>
<form action="/submit" method="post">
<label for="name">Name:</label>
<input type="text" id="name" name="name" required>
<br>
<label for="email">Email:</label>
<input type="email" id="email" name="email" required>
<br>
<input type="submit" value="Submit">
</form>
</body>
</html>
Version E:
You have three tables - classes, enrollments, and students.
For the context of the application you provided, we'd like to modify the existing behavior of saving the user (probably a student in this context) into the database, and allow a student to enroll in a class.
Let's create some basic routes to:
Here's how you can modify your index.js to handle these:
const express = require('express');
const bodyParser = require('body-parser');
const sqlite3 = require('sqlite3').verbose();
const app = express();
const PORT = 3000;
console.log("Starting server...");
// Middleware to parse POST data
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json()); // to parse JSON data
// Middleware to serve static files from the 'public' directory
app.use(express.static('public'));
// Database setup
const db = new sqlite3.Database('./ces.db', (err) => {
if (err) {
console.error("Error connecting to the ces SQLite database:", err.message);
return;
}
console.log('Connected to the ces SQLite database.');
});
// Route to serve index.html
app.get('/', (req, res) => {
res.sendFile(__dirname + '/public/index.html');
});
// Route to register a new student
app.post('/register', (req, res) => {
const { studentname } = req.body;
db.run(`INSERT INTO students(studentname) VALUES(?)`, [studentname], (err) => {
if (err) {
console.error("Error registering student:", err.message);
res.status(500).send('An error occurred while registering student.');
return;
}
res.send('Student registered successfully!');
});
});
// Route to list all classes
app.get('/classes', (req, res) => {
db.all(`SELECT * FROM classes`, [], (err, rows) => {
if (err) {
console.error("Error fetching classes:", err.message);
res.status(500).send('An error occurred while fetching classes.');
return;
}
res.json(rows);
});
});
// Route to enroll a student in a class
app.post('/enroll', (req, res) => {
const { studentid, classid } = req.body;
db.run(`INSERT INTO enrollments(studentid, classid) VALUES(?, ?)`, [studentid, classid], (err) => {
if (err) {
console.error("Error enrolling student:", err.message);
res.status(500).send('An error occurred while enrolling the student.');
return;
}
res.send('Student enrolled successfully!');
});
});
app.listen(PORT, () => {
console.log(`Server is running on http://localhost:${PORT}`);
});
With these changes, the application can now handle registering students, enrolling them in classes, and listing available classes.
The routes and methods (GET/POST) indicate the kind of operations that can be performed, and each route is associated with the respective table in the ces.db database.
To make the system work end-to-end, you'll need to modify or create the index.html file to handle these actions, by sending requests to the server when the user interacts with the page (like submitting forms).
Updatedindex.html with interactions for registering students, enrolling them in classes, and listing available classes.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>CES Party</title>
<style>
body {
font-family: Arial, sans-serif;
background-color: #FFE082;
background-image: linear-gradient(45deg, #FFD54F, #FF6E40);
animation: gradientBG 5s infinite linear alternate;
}
@keyframes gradientBG {
0% {
background-image: linear-gradient(45deg, #FFD54F, #FF6E40);
}
100% {
background-image: linear-gradient(45deg, #FF6E40, #FFD54F);
}
}
.container {
max-width: 600px;
margin: 2rem auto;
background-color: rgba(255, 255, 255, 0.9);
padding: 2rem;
border-radius: 15px;
}
h2 {
text-align: center;
color: #3949AB;
}
button {
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.