=> Connect express with mysql
--> Import mysql
let mysql = require("mysql");
--> database connection credentials
let connData = {
host: <host_name>,
user: <user_name>,
password: <password>,
database: <database_name>,
};
--> connect to database
function ConnDb() {
return mysql.createConnection(connData);
}
--> Export connection function
module.exports.ConnDb = ConnDb;
--> In app.js file
let { ConnDb } = require("./modDB.js");
let express = require("express");
const { response } = require("express");
let app = express();
app.use(express.json());
app.use(
function (req, res, next) {
res.header("Access-Control-Allow-Origin", "*");
res.header(
"Access-Control-Allow-Methods",
"GET,POST,OPTIONS,PUT,DELETE,HEAD,PATCH"
);
res.header(
"Access-Control-Allow-Headers",
"Origin, X-Requested-With, Content-Type,Accept"
);
next();
});
const port = 2410;
--> listen to port
app.listen(port, () =>
console.log(`Listening on port
${port}!`));
--> Get API to test
app.get("/svr/test", function (req, res) {
res.send("Test Response");
});
--> Get Api to send student data
app.get("/svr/students", function (req, res) {
res.send(studentsData);
});
--> To send data as per query
app.get("/svr/students", function (req, res) {
console.log(req.query);
let courseStr = req.query.course;
let grade = req.query.grade;
let sort = req.query.sort;
let arr1 = studentsData;
if (courseStr) {
let courseArr = courseStr.split(",");
arr1 = arr1.filter((c1) => courseArr.find((f1) => f1 === c1.course));
}
if (grade) {
arr1 = arr1.filter((c1) => c1.grade === grade);
}
if (sort === "name")
arr1.sort((st1, st2) => st1.name.localeCompare(st2.name));
if (sort === "course") {
arr1.sort((st1, st2) => st1.course.localeCompare(st2.course));
}
res.send(arr1);
});
--> To send response as per id
app.get("/svr/students/:id", function (req, res) {
let id = +req.params.id;
console.log(id);
let student = studentsData.find((s1)
> s1.id === id);
if (student) res.send(student);
else res.status(404).send("No student found");
});
--> To send response as per name
app.get("/svr/students/course/:name",
function (req, res) {
name1 = req.params.name;
studentArr = studentsData.filter((n1) => n1.course === name1);
res.send(studentArr);
});
--> To create new student
app.post("/svr/students", function (req, res) {
body = req.body;
console.log(body);
let maxid = studentsData.reduce(
(acc, curr) => (curr.id >= acc ? curr.id : acc),
0
);
let newid = maxid + 1;
let newStudent = { id: newid, ...body };
studentsData.push(newStudent);
res.send(newStudent);
});
--> Update student
app.put("/svr/students/:id", function (req, res) {
let id = +req.params.id;
let body = req.body;
let index = studentsData.findIndex((st) => st.id === id);
if (index >= 0) {
let updatedStudent = { id: id, ...body };
studentsData[index] = updatedStudent;
res.send(updatedStudent);
} else {
res.status(404).send("No student found");
}
});
--> To delete student data
app.delete("/svr/students/:id", function (req, res) {
let id = +req.params.id;
let index = studentsData.findIndex((st) => st.id === id);
if (index >= 0) {
let deletedStudent = studentsData.splice(index, 1);
res.send(deletedStudent);
} else {
res.status(404).send("No student found");
}
});
--> Get all students
app.get("/svr/students", function (req, res) {
let connection = ConnDb();
let sql = "SELECT * FROM persons";
connection.query(sql, function (err, result) {
if (err) {
console.log(err);
res.status(404).send("Error in fetching data");
} else res.send(result);
});
});
--> Get student data by id
app.get("/svr/students/:id", function (req, res) {
let id = +req.params.id;
let connection = ConnDb();
let sql = "SELECT * FROM persons WHERE id=?";
connection.query(sql, id, function (err, result) {
if (err) {
console.log(err);
res.status(404).send("Error in fetching data");
} else if (result.length === 0) res.status(404).send("No student found");
else res.send(result[0]);
});
});
--> Get student by course name
app.get("/svr/students/course/:name", function (req, res) {
let name = req.params.name;
let connection = ConnDb();
let sql = "SELECT * FROM persons WHERE course=?";
connection.query(sql, name, function (err, result) {
if (err) {
console.log(err);
} else {
res.send(result);
}
});
});
--> Get students by course
app.get("/svr/students", function (req, res) {
let course = req.params.course;
let connection = ConnDb();
let options = "";
let optionArr = [];
if (course) {
options = " WHERE course=?";
optionArr.push(course);
}
let sql = `SELECT * FROM persons${options}`;
connection.query(sql, optionArr, function (err, result) {
if (err) {
console.log(err);
res.status(404).send("Error Fetching data");
} else {
res.send(result);
}
});
});
--> Get student by id
app.get("/svr/students/:id", function (req, res) {
let id = +req.params.id;
connection = ConnDb();
sql = "SELECT * FROM persons WHERE id=?";
connection.query(sql, id, function (err, result) {
if (err) {
console.log(err);
res.status(404).send("Error Fetching Data");
} else if (result.length === 0) {
res.status(404).send("No Student Found");
} else {
res.send(result[0]);
}
});
});
--> get student by course name
app.get("/svr/students/course/:name", function (req, res) {
let name = req.params.name;
connection = ConnDb();
sql = "SELECT * FROM persons WHERE course=?";
connection.query(sql, name, function (err, result) {
if (err) {
console.log(err);
res.status(404).send("Error Fetching Data");
} else {
res.send(result);
}
});
});
--> Get students by course and grade
app.get("/svr/students", function (req, res) {
let course = req.query.course;
let grade = req.query.grade;
connection = ConnDb();
let options = "";
let optionArr = [];
if (course) {
let courseArr = course.split(",");
options = " WHERE course IN (?)";
optionArr.push(courseArr);
}
if (grade) {
options = options ? `${options} AND grade=?` : "WHERE grade=?";
optionArr.push(grade);
}
if (sort) {
options = `${options} ORDER BY ${sort}`;
}
let sql = `SELECT * FROM persons ${options}`;
connection.query(sql, optionArr, function (err, result) {
if (err) {
console.log(err);
res.status(404).send("Error Fetching Data");
} else {
res.send(result[0]);
}
});
});
--> To get all data from database
function showPerson() {
let connection = mysql.createConnection(connData);
let sql = "SELECT * FROM persons";
connection.query(sql, function (err, result) {
if (err) console.log("error in database", err.message);
else console.log(result);
});
}
--> To get data by name
function showPersonByName(name) {
let connection = mysql.createConnection(connData);
let sql = "SELECT * FROM persons WHERE name=?";
connection.query(sql, name, function (err, result) {
if (err) console.log(err);
else console.log(result);
});
}
--> To get data by name and age
function insertPerson(params) {
let connection = mysql.createConnection(connData);
let sql = "INSERT INTO persons(name,age) VALUES(?,?)";
connection.query(sql, params, function (err, result) {
if (err) console.log(err);
else console.log("Id of inserted record", result.insertId);
});
}
--> To add data in database
function insertPersons(params) {
let connection = mysql.createConnection(connData);
let sql = "INSERT INTO persons(name,age) VALUES ?";
connection.query(sql, [params], function (err, result) {
if (err) console.log(err);
else console.log(result);
});
}
--> To update data
function incrementAge(id) {
let connection = mysql.createConnection(connData);
let sql = "UPDATE persons SET age=age+1 WHERE id=?";
connection.query(sql, id, function (err, result) {
if (err) console.log(err);
else console.log(result);
});
}
--> update data by Id
function changeAge(id, newAge) {
let connection = mysql.createConnection(connData);
let sql = "UPDATE persons SET age=? WHERE id=?";
connection.query(sql, [newAge, id], function (err, result) {
if (err) console.log(err);
else console.log(result);
});
}
--> delete data
function resetPerson() {
connection = mysql.createConnection(connData);
sql1 = "DELETE FROM persons";
connection.query(sql1, function (err, result1) {
if (err) {
console.log(err);
} else {
console.log("Successfully Deleted", result1.affectedRows);
let { persons } = require("./students.js");
let arr = persons.map((p1) => [p1.name, p1.age]);
let sql2 = "INSERT INTO persons(name,age) VALUES ?";
connection.query(sql2, [arr], function (err, result) {
if (err) console.log(err);
else console.log("Successfully Inserted", result.affectedRows);
});
}
});
}
--> calling above functions
resetPerson();
changeAge(3, 33);
incrementAge(3);
insertPersons([
["Jim", 30],
["Amy", 30],
["steven", 23],
]);
insertPerson(["Riya", 25]);
showPersonByName("Riya");
showPerson();
