Node Express JS CRUD (Create, Read, Update and Delete) Mysql

Node Express JS CRUD (Create, Read, Update and Delete) Mysql

https://expressjs.com/
Express JS
Fast, unopinionated, minimalist web framework for Node.js

Install
$ npm install express --savev PS C:\nodeproject> npm install express --save
https://expressjs.com/en/starter/hello-world.html

mysql
https://github.com/mysqljs/mysql
$ npm i mysql2
PS C:\nodeproject>npm i mysql2

run PS C:\nodeproject> node index.js

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78

//index.js const express = require("express"); const { executeQuery } = require("./db"); const port = 9000; const app = express(); app.use(express.json()); app.use(express.urlencoded({ extended: true }));   app.get("/api/employee", async (req, res) => {     try {         let employeeData = await executeQuery("select * from employee");         res.status(200).json(employeeData);     } catch (err) {         res.status(500).json(err);         console.log("eror query");     } });   app.get("/api/employee/:id", async (req, res) => {     let id = req.params.id;     try {         let employeeData = await executeQuery(             "select * from employee where emp_id=?",             [id]         );         res.status(200).json(employeeData);     } catch (err) {         res.status(500).json(err);     } });   app.post("/api/saveEmployee", async (req, res) => {     try {         const { emp_name, emp_email, emp_address, emp_phone } = req.body;         let employeeData = await executeQuery(             "insert into employee(emp_name,emp_email,emp_address,emp_phone) values(?,?,?,?)",             [emp_name, emp_email, emp_address, emp_phone]         );         res.status(201).json(employeeData);     } catch (err) {         res.status(400).json(err);     } });   app.put("/api/update/:id", async (req, res) => {     try {         const userId = req.params.id;         const q = "UPDATE employee SET `emp_name`= ?, `emp_email`= ?, `emp_address`= ?, `emp_phone`= ? WHERE emp_id = ?";           const values = [             req.body.emp_name,             req.body.emp_email,             req.body.emp_address,             req.body.emp_phone,         ];           executeQuery(q, [...values, userId], () => {             res.status(201).json(values);         });     } catch (err) {         res.status(400).json(err);     } });   app.delete("/api/delete/:id", async (req, res) => {     let id = req.params.id;     try {         const sql = "Delete FROM employee WHERE emp_id = ?";         executeQuery(sql, [id], (err, result) => {             if (err) return res.json({ Error: "delete employee error in sql" });             return res.json({ Status: "Success" })         })     } catch (err) {         res.status(500).json(err);     } });   app.listen(port, () => console.log(`server is running on port ${port}`));

db.js

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

//db.js const util = require('util'); const mysql = require('mysql2'); //npm i mysql2 https://www.npmjs.com/package/mysql2   const pool = mysql.createPool({     socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock',     connectionLimit: 10,     host: 'localhost',     user: 'root',     password: 'root',     database: 'nextjsdb' });   // Ping database to check for common exception errors. pool.getConnection((err, connection) => {     if (err) {         if (err.code === 'PROTOCOL_CONNECTION_LOST') {             console.error('Database connection was closed.');         }         if (err.code === 'ER_CON_COUNT_ERROR') {             console.error('Database has too many connections.');         }         if (err.code === 'ECONNREFUSED') {             console.error('Database connection was refused.');         }     }       if (connection) connection.release();       return; });   // Promisify for Node.js async/await. pool.query = util.promisify(pool.query);   const executeQuery = (query, arraParms) => {     return new Promise((resolve, reject) => {         try {             pool.query(query, arraParms, (err, data) => {                 if (err) {                     console.log("error in executing the query");                     reject(err);                 }                 resolve(data);             });         } catch (err) {             reject(err);         }     }); };   module.exports = { executeQuery };