const express = require("express");
const { faker } = require("@faker-js/faker");
const mysql = require("mysql2");
const app = express();
const path = require("path");
const methodOverride = require("method-override");
const { v4: uuidv4 } = require("uuid");
let port = 3000;
app.use(methodOverride("_method"));
app.use(express.urlencoded({ extended: true }));
app.set("view engine", "ejs");
app.set("views", path.join(__dirname, "/views"));
const conn = mysql.createConnection({
host: 'localhost',
user: 'root',
database: 'myapp',
password: 'Sk@123456'
});
// Home route
app.get("/", (req, res) => {
let q = `SELECT count(*) FROM user`;
conn.query(q, (err, result) => {
if (err) throw err;
let count = result[0]["count(*)"];
res.render("home.ejs", { count });
});
});
// Show all users route
app.get("/user", (req, res) => {
let q = `SELECT * FROM user`;
conn.query(q, (err, data) => {
if (err) throw err;
res.render("users.ejs", { data });
});
});
// Edit user route
app.get("/user/:id/edit", (req, res) => {
let { id } = req.params;
let q = `SELECT * FROM user WHERE id='${id}'`;
conn.query(q, (err, result) => {
if (err) throw err;
let user = result[0];
res.render("edit.ejs", { user });
});
});
// Update user route
app.patch("/user/:id", (req, res) => {
let { id } = req.params;
let { username, password } = req.body;
let q = `SELECT * FROM user WHERE id='${id}'`;
conn.query(q, (err, result) => {
if (err) throw err;
let user = result[0];
// Check if the password entered matches
if (user.password !== password) {
res.send("WRONG Password entered!");
} else {
// Update the username
let q2 = `UPDATE user SET username='${username}' WHERE id='${id}'`;
conn.query(q2, (err) => {
if (err) throw err;
console.log("Username updated!");
res.redirect("/user");
});
}
});
});
// Add new user route
app.get("/user/new", (req, res) => {
res.render("new.ejs");
});
app.post("/user/new", (req, res) => {
let { username, email, password } = req.body;
let id = uuidv4();
// Query to insert new user
let q = `INSERT INTO user (id, username, email, password) VALUES ('${id}', '$
{username}', '${email}', '${password}')`;
conn.query(q, (err) => {
if (err) throw err;
console.log("Added new user");
res.redirect("/user");
});
});
// Delete user route
app.delete("/user/:id", (req, res) => {
let { id } = req.params;
let { password } = req.body;
let q = `SELECT * FROM user WHERE id='${id}'`;
conn.query(q, (err, result) => {
if (err) throw err;
let user = result[0];
if (user.password != password) {
res.send("WRONG Password entered!");
} else {
let q2 = `DELETE FROM user WHERE id='${id}'`; // Query to delete
conn.query(q2, (err) => {
if (err) throw err;
console.log("User deleted!");
res.redirect("/user");
});
}
});
});
// Delete confirmation route (optional)
app.get("/user/:id/delete", (req, res) => {
let { id } = req.params;
let q = `SELECT * FROM user WHERE id='${id}'`;
conn.query(q, (err, result) => {
if (err) throw err;
let user = result[0];
res.render("delete.ejs", { user });
});
});
app.listen(port, () => {
console.log('Server is running on http://localhost:' + port);
});
views/home.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Home</title>
</head>
<body>
<h2>The total number of users are: <%= count %></h2>
<a href="/user/new">Join us</a>
</body>
</html>
views/users.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Users</title>
<style>
table, tr, th, td {
border: 1px solid purple;
text-align: center;
}
</style>
</head>
<body>
<form method="GET" action="/user/new">
<button>Add New User</button>
</form>
<h2>List of all users</h2>
<table>
<tr>
<th>Id</th>
<th>Email</th>
<th>Username</th>
<th>Edit</th>
<th>Delete</th>
</tr>
<% for(user of data) { %>
<tr>
<td><%= user.id %></td>
<td><%= user.email %></td>
<td><%= user.username %></td>
<td>
<form method="get" action="/user/<%= user.id %>/edit">
<button>Edit username</button>
</form>
</td>
<td>
<form method="post" action="/user/<%= user.id %>?_method=DELETE">
<input type="password" name="password" placeholder="Enter
password" required />
<button>Delete</button>
</form>
</td>
</tr>
<% } %>
</table>
</body>
</html>
views/edit.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Edit User</title>
</head>
<body>
<h3>Edit your user: <%= user.email %></h3>
<form method="post" action="/user/<%= user.id %>?_method=PATCH">
<textarea name="username"><%= user.username %></textarea>
<input name="password" placeholder="Enter your password" type="password"
required />
<button>Submit</button>
</form>
</body>
</html>
views/new.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Add New User</title>
</head>
<body>
<h3>Create a New User</h3>
<form action="/user/new" method="POST">
<input name="email" type="email" placeholder="Enter email" required />
<input name="username" type="text" placeholder="Enter username" required />
<input name="password" type="password" placeholder="Enter password"
required />
<button>Submit</button>
</form>
</body>
</html>
views/delete.ejs (Optional)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Delete User</title>
</head>
<body>
<h3>To delete this user: <%= user.email %>, enter the password</h3>
<form method="post" action="/user/<%= user.id %>?_method=DELETE">
<input type="password" name="password" placeholder="Enter password to
delete" required />
<button>Delete</button>
</form>
</body>
</html>
npm install express mysql2 @faker-js/faker method-override ejs uuid
