Install MySQL2

• Reference: https://expressjs.com/en/guide/database-integration.html

    npm i mysql2    

Create Connection Pool ( config/database.js)

    const mysql = require("mysql2/promise");

    const pool = mysql.createPool({
        host: "localhost",
        port: 3306,
        user: "root",
        password: "root",
        database: "training_db",
        connectionLimit: 10
    });


    module.exports = pool;

Create DAO (dao/user.dao.js)

    const pool = require('../config/database')

    class UserDAO {

        async findAll(){
            const result = await pool.query("select * from users");    
            return result[0];
        }

        async save(user){
            let params = [ user.name, user.email, user.password, user.role];
            const result = await pool.query("insert into users (name,email,password,role) values ( ?,?,?,?)", params);    
            return result[0].insertId;        
        }
    }

    exports.UserDAO = UserDAO;

Test with express -routes (app.js)

    npm i express@next
    npm i nodemon

    const express = require('express')
    const app = express()
    const port = 3000
    app.use(express.json())

    const {UserDAO} = require('./dao/user.dao');

    const userDAO = new UserDAO();
    app.get('/api/users', async (req,res,next)=>{
        
        let result = await userDAO.findAll();
        res.status(200).json(result);    
    });

    app.post('/api/users', async (req,res)=>{
        let user = req.body;
        let result = await userDAO.save(user);
        res.status(201).json({id:result});
    });

    app.use(function (err, req, res, next) {
        console.log("common error handler")
        console.error(err);
        res.json({errorMessage:err.message});
    })


    app.listen(port, () => console.log(`Demo app listening on port port!`))

Testing

    nodemon app.js