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

//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

//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 };