การเชื่อมต่อและใช้งานฐานข้อมูล MySQL ใน Express เบื้องต้น

npm install mysql --save

--
-- Table structure for table `tbl_users`
--

CREATE TABLE `tbl_users` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `email` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for table `tbl_users`
--
ALTER TABLE `tbl_users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `tbl_users`
--
ALTER TABLE `tbl_users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

const express = require('express')
const router = express.Router()

router.get('/', function(req, res, next) {
    res.send('Users Page')
})

module.exports = router

const express = require('express')
const router = express.Router()
const { validation, schema } = require('../validator/users')
 
router.route('/users?')
    .get((req, res, next) => { 
		// แสดงข้อมูลทั้งหมด
        return res.json({})
    })
    .post(validation(schema),(req, res, next) => {   
		// เพิ่มข้อมูลใหม่เข้าไปในฐานข้อมูล และแสดงข้อมูลใหม่ที่เพิ่งเพิ่ม
        return res.json({})
    })
 
router.route('/user/:id')
    .all((req, res, next) => { 
		// ตรวจสอบว่า  id ข้อมูลที่ส่งมา หรืออยู่ในฐานข้อมูลหรือไม่ 
		// ถ้ามีส่งต่อไปดึงมาแสดง / แก้ไข / ลบ
        next()
    })
    .get((req, res, next) => { 
		// แสดงรายการข้อมูลจากฐานข้อมูลของ id ข้อมูลที่อต้กงาร
        return res.json({})
    })
    .put(validation(schema),(req, res, next) => {   
		// ทำการแก้ไขรายการข้อมูลของ id ข้อมูลที่ต้องการ จากฐานข้อมูล แล้วแสดงรายการข้อมูลที่แก้ไข
        return res.json({})
    })
    .delete((req, res, next) => { 
		// ทำการลบช้อมูลของ id ข้อมูลที่ต้องการ จากฐานข้อมูล แล้วแสดงข้อมูลที่เพิ่งลบ
        return res.json({})
    })
 
module.exports = router

const express = require('express')  // ใช้งาน module express
const app = express()  // สร้างตัวแปร app เป็น instance ของ express
const path = require('path') // เรียกใช้งาน path module
const createError = require('http-errors') // เรียกใช้งาน http-errors module
const port = 3000  // port 
 
// ส่วนของการใช้งาน router module ต่างๆ 
const indexRouter = require('./routes/index')
const userRouter = require('./routes/users')
const userApi = require('./api/users')
 
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.set('view options', {delimiter: '?'});
// app.set('env','production')
 
app.use(express.json())
app.use(express.urlencoded({ extended: false }))
app.use(express.static(path.join(__dirname, 'public')))
 
// เรียกใช้งาน indexRouter
app.use('/', indexRouter)
app.use('/user', userRouter)
app.use('/api', [userApi]) 

// ทำงานทุก request ที่เข้ามา 
app.use(function(req, res, next) {
    var err = createError(404)
    next(err)
})
 
// ส่วนจัดการ error
app.use(function (err, req, res, next) {
    // กำหนด response local variables 
    res.locals.message = err.message
    res.locals.error = req.app.get('env') === 'development' ? err : {}
 
    // กำหนด status และ render หน้า error page
    res.status(err.status || 500) // ถ้ามี status หรือถ้าไม่มีใช้เป็น 500
    res.render('error') 
})
 
app.listen(port, function() {
    console.log(`Example app listening on port ${port}!`)
})

const userRouter = require('./routes/users')
const userApi = require('./api/users')

app.use('/user', userRouter)  // http://localhost:3000/user
app.use('/api', [userApi])   // http://localhost:3000/api/users

const mysql = require('mysql') // เรียกใช้งาน MySQL module

// กำหนดการเชื่อมต่อฐานข้อมูล
const db = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'testdb'
  })

// ทำการเชื่อมต่อกับฐานข้อมูล 
db.connect((err) =>{
    if(err){ // กรณีเกิด error
        console.error('error connecting: ' + err.stack)
        return
    }
    console.log('connected as id ' + db.threadId)
})
// ปิดการเชื่อมต่อฐานข้อมูล MySQL ในที่นี้เราจะไม่ให้ทำงาน
// db.end() 
module.exports = db  

// กำหนดการเชื่อมต่อฐานข้อมูล
const db = mysql.createConnection({
    host     : 'localhost',
    user     : 'root',
    password : '',
    database : 'testdb'
  })

let sql = ' SELECT * FROM tbl_users '
db.query(sql, (error, results, fields) =>{
	// error คือ ข้อผิดพลาดของคำสั่ง sql นั้นๆ  if (error) throw error;
	// results เป็น array ของข้อมูลผลลัพธ์
	// fields เป็นรายละเอียดของฟิลด์ของตาราง tbl_users ปกติเราจะไม่ได้ใช้ค่านี้เท่าไหร่
})

let sql = ' SELECT * FROM tbl_users '
db.query(sql,(error, results, fields)=>{
	if(error) {
		throw error
	}   
	res.json(results)
})

let sql = ' SELECT * FROM tbl_users WHERE id = 1 '  // แบบกำหนดค่าโดยตรง
let sql = ' SELECT * FROM tbl_users WHERE id = '+ req.params.id  // แบบกำหนดค่าจากตัวแปร
// แบบป้องกัน SQL Injection
let sql = ' SELECT * FROM tbl_users WHERE id = '+ db.escape(req.params.id)

let sql = ' SELECT * FROM tbl_users WHERE id = ? '
db.query(sql, [req.params.id], (error, results, fields) =>{
	// error คือ ข้อผิดพลาดของคำสั่ง sql นั้นๆ  if (error) throw error;
	// results เป็น array ของข้อมูลผลลัพธ์
	// fields เป็นรายละเอียดของฟิลด์ของตาราง tbl_users ปกติเราจะไม่ได้ใช้ค่านี้เท่าไหร่
})

let table = 'tbl_users'
let columns = ['name','email']
let id = 1
let sql = ' SELECT ?? FROM ?? WHERE id = ? '
db.query(sql, [columns, table, id],(error, results, fields)=>{
	console.log(error)
	if(error) {
		throw error
	}   
	console.log(results)
	console.log(fields)
	res.json(results)
})

SELECT `name`, `email` FROM `tbl_users` WHERE id = 1

let user = {
	"name": 'William Smith', 
	"email": 'will.smith@gmail.com' 
}
let sql = ' INSERT INTO tbl_users SET ? '
db.query(sql, user, (error, results, fields)=>{
	console.log(error)
	if(error) {
		throw error
	}   
	console.log(results.insertId)
	console.log(results)
	console.log(fields)
	res.json(results)
})

let user = {
	"name": 'Jennifer Lee', 
	"email": 'jennifer.lee@gmail.com' 
}
let id = 2
let sql = ' UPDATE tbl_users SET ? WHERE id = ? '
db.query(sql, [user ,id], (error, results, fields)=>{
	console.log(error)
	if(error) {
		throw error
	}   
	if(results.affectedRows > 0) {
			console.log('updaed new value')
	}
	console.log(results)
	console.log(fields)
	res.json(results)
})

let id = 2
let sql = ' DELETE FROM tbl_users WHERE id = ? '
db.query(sql, [id], (error, results, fields)=>{
	console.log(error)
	if(error) {
		throw error
	}   
	if(results.affectedRows > 0) {
			console.log('deleted')
	}
	console.log(results)
	console.log(fields)
	res.json(results)
})

db.beginTransaction((err) => {
  	if (err) { throw err }
	let user = {
		"name": 'William Smith', 
		"email": 'will.smith@gmail.com' 
	}
	let sql = ' INSERT INTO tbl_users SET ? '
	db.query(sql, user, (error, results, fields)=>{
		if (error) {
		  return db.rollback(function() {
			throw error
		  })
		}
		let user = {
			"name": 'Jennifer Lee', 
			"email": 'jennifer.lee@gmail.com' 
		}
		let id = results.insertId
		let sql = ' UPDATE tbl_users SET ? WHERE id = ? '
		db.query(sql, [user ,id], (error, results, fields)=>{
			  if (error) {
				return db.rollback(function() {
				  throw error
				})
			  }
			  db.commit((err) => {
					if (err) {
					  return db.rollback(function() {
						throw err
					  })
					}
					console.log('success!')
					res.json(results)
			  }) // end commit
		}) // end update
	 }) // end insert
}) // end beginTransaction

const express = require('express')
const router = express.Router()
const { validation, schema } = require('../validator/users')
const db = require('../config/db') // เรียกใช้งานเชื่อมกับ MySQL

router.route('/users?')
    .get((req, res, next) => { 
		// ทำการแสดงข้อมูลทั้งหมด
        let sql = ' SELECT * FROM tbl_users '
        db.query(sql,(error, results, fields)=>{
			// เกิด error ในคำสั่ง sql
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
			// แสดงข้อมูลกร๊ไม่เกิด error
            const result = {
                "status": 200,
                "data": results
            }
            return res.json(result)        
        })
    })
    .post(validation(schema),(req, res, next) => {   
		// เตรียมฟิดล์และค่าของข้อมูลที่จะเพิ่ม
        let user = {
            "name": req.body.name, 
            "email": req.body.email 
        }
        let sql = ' INSERT INTO tbl_users SET ? '
        db.query(sql, user, (error, results, fields)=>{
			// เกิด error ในคำสั่ง sql
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
			// เพื่อไม่ต้องไปดึงข้อมูลที่เพิ่งเพิม มาแสดง ให้เราใช้เฉพาะ id ข้อมูลใหม่ที่เพิ่งเพิม
			// รวมกับชุดข้อมูลที่เพิ่งเพิ่ม เป็น ข้อมูลที่ส่งกลับออกมา
            user = [{'id':results.insertId, ...user}]
            const result = {
                "status": 200,
                "data": user
            }
            return res.json(result)        
        })
    })

router.route('/user/:id')
    .all((req, res, next) => { 
		// ตรวจสอบว่า id user ที่ส่งเข้ามาเพื่อดูข้อมูล / แก้ไข / ลบ มีอยู่ในฐานข้อมูลหรือไม่
        let sql = ' SELECT * FROM tbl_users WHERE id = ? '
        db.query(sql, [req.params.id], (error, results, fields)=>{
			// เกิด error ในคำสั่ง sql
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
			// กรณีไม่พบข้อมูล 
            if(results.length ===0) return res.status(400).json({
                "status": 400,
                "message": "Not found user with the given ID" 
            }) 
            res.user = results // ถ้ามี id นั้นอยู่ในฐานข้อมูล ส่งข้อมูลผู้ใช้นั้นไปทำงานต่อ
            next()  
        })        
    })
    .get((req, res, next) => { 
		// ถ้าเป็นการแสดงข้อมูลของ ผู้ใช้ id ใด ก็นำค่าที่ถูกส่งมาไปแสดง
        const result = {
            "status": 200,
            "data": res.user
        }
        return res.json(result)
    })
    .put(validation(schema),(req, res, next) => {   
		// ถ้าเป็นการแก้ไขข้อมูล ก็เตรียมรูปแบบข้อมูลที่รับมาสำหรับแก้ไข
        let user = {
            "name": req.body.name, 
            "email": req.body.email 
        }        
		// ทำการอัพเดท โดยอิง id ที่พบ
        let sql = ' UPDATE tbl_users SET ? WHERE id = ? '
        db.query(sql, [user, req.params.id], (error, results, fields)=>{
			// เกิด error ในคำสั่ง sql
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
			// ถ้ามีการแก้ไขค่าใหม่ 
            if(results.affectedRows > 0) {
				// เอาค่าฟิลด์ทีได้ทำการอัพเดท ไปอัพเดทกับข้อมูลทั้งหมด
                user = Object.assign(res.user[0], user)
            }else{ // มีการอัพเดท แต่เป็นค่าเดิม
                user = res.user
            }
			// ส่งรายการข้อมูลที่อัพเทกลับไป
            const result = {
                "status": 200,
                "data": user
            }
            return res.json(result)        
        })
    })
    .delete((req, res, next) => { 
		// ถ้าเป็นการลบข้อมูล จาก id ใดๆ 
        let sql = ' DELETE FROM tbl_users WHERE id = ? '
        db.query(sql, [req.params.id],(error, results, fields)=>{
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
			// ใช้ค่าข้อมูลถ้าค้นเจอ ก่อนลบ ส่งออกไป เป็นการบอกว่า รายการนี้คือรายการที่ถูกลบไปแล้ว
            const result = {
                "status": 200,
                "data": res.user
            }
            return res.json(result)        
        })
    })

module.exports = router

// เกิด error ในคำสั่ง sql
if(error) return res.status(500).json({
	"status": 500,
	"message": "Internal Server Error" // error.sqlMessage
})

const Joi = require('@hapi/joi')

const validation = (schema) =>{
    return ((req, res, next) => {
        // ทำการตรวจสอบความถูกต้องของข้อมูล req.body ที่ส่งมา
        Joi.validate(req.body, schema, function (error, value) {
            // กรณีเกิด error ข้อมูลไม่ผ่านการตรวจสอบ 
            if(error) return res.status(400).json({
                "status": 400,
                "message": error.details[0].message
            })
            if(!error) next()
        })  
    })
}

// กำหนดชุดรูปแบบ schema
const schema = Joi.object().keys({
    province_name: Joi.string().min(3).max(30).required(),
    province_name_eng: Joi.string().min(3).max(30).required()
})

module.exports = { validation, schema }

const express = require('express')
const router = express.Router()
const { validation, schema } = require('../validator/provinces')
const db = require('../config/db')

router.route('/provinces?')
    .get((req, res, next) => { 
		// เราจะดึงข้อมูลท้งหมดตามเงื่อนไขแบ่งหน้า กับดึงข้อมูลจำนวนรายการทั้งหมด ในฟิลด์ total 
        let sql = ' SELECT a.*, (SELECT COUNT(b.province_id) FROM tbl_provinces b) AS total FROM tbl_provinces a '
        let per_page = 10  // กำหนดรายการที่จะแสดงในแต่ละหน้า
        let page = 1  // หน้าแรก
        let offset = 0 // เริ่มแสดงที่ offset 
        let total = 0 // จำนวนรายการถั้งหมด
        if(req.query.page){ // ถ้ามีการแสดงหน้าที่จะแสดงเข้ามา เช่น ?page=1
            page = req.query.page  // หน้า ที่จะแสดง
            offset = (page-1) * per_page  // กำหนด offset เริ่มต้นกรณีแบ่งหน้า
            sql += ' LIMIT '+ offset +','+ per_page +' ' // ต่อคำสั่ง sql เพิ่มการแบ่งหน้า
        }
        db.query(sql,(error, results, fields)=>{
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
            total = results.length  // ถ้าไม่มีการแบ่งหน้า จำนวนทั้งหมด จะนับจากจำนวนของรายการ
			// ถ้ามีการแบ่งหน้า จำนวนทั้งหมดจะใช้จากค่าฟิลด์ total ที่เราคิวรี่เริ่มด้ววคำสั่ง COUNT()
            if(req.query.page && total>0) total = results[0].total 
			// รูปแบบข้อมูลที่ส่งกลับ กรณีรองรับการแบ่างหน้า
            const result = {
                "status": 200,
                "total":total,  // จำนวนรายการทั้งหมด
                "current_page":page, // หน้าที่กำลังแสดงอยู่
                "total_page":Math.ceil(total/per_page), // จำนวนหน้าทั้งหมด
                "data": results  // รายการข้อมูล
            }
            return res.json(result)        
        })
    })
    .post(validation(schema),(req, res, next) => {   
        let province = {
            "province_name": req.body.province_name, 
            "province_name_eng": req.body.province_name_eng 
        }
        let sql = ' INSERT INTO tbl_provinces SET ? '
        db.query(sql, province, (error, results, fields)=>{
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
            province = [{'province_id':results.insertId, ...province}]
            const result = {
                "status": 200,
                "data": province
            }
            return res.json(result)        
        })
    })

router.route('/province/:id')
    .all((req, res, next) => { 
        let sql = ' SELECT * FROM tbl_provinces WHERE province_id = ? '
        db.query(sql, [req.params.id], (error, results, fields)=>{
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
            if(results.length ===0) return res.status(400).json({
                "status": 400,
                "message": "Not found user with the given ID" 
            }) 
            res.province = results 
            next()  
        })        
    })
    .get((req, res, next) => { 
        const result = {
            "status": 200,
            "data": res.province
        }
        return res.json(result)
    })
    .put(validation(schema),(req, res, next) => {   
        let province = {
            "province_name": req.body.province_name, 
            "province_name_eng": req.body.province_name_eng 
        }        
        let sql = ' UPDATE tbl_provinces SET ? WHERE province_id = ? '
        db.query(sql, [province, req.params.id], (error, results, fields)=>{
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
            if(results.affectedRows > 0) {
                province = Object.assign(res.province[0], province)
            }else{
                province = res.province
            }
            const result = {
                "status": 200,
                "data": province
            }
            return res.json(result)        
        })
    })
    .delete((req, res, next) => { 
        let sql = ' DELETE FROM tbl_provinces WHERE province_id = ? '
        db.query(sql, [req.params.id],(error, results, fields)=>{
            if(error) return res.status(500).json({
                "status": 500,
                "message": "Internal Server Error" // error.sqlMessage
            })
            const result = {
                "status": 200,
                "data": res.province
            }
            return res.json(result)        
        })
    })

module.exports = router

SELECT a.*, (SELECT COUNT(b.province_id) FROM tbl_provinces b) AS total FROM tbl_provinces a LIMIT 70,10

const provinceApi = require('./api/provinces')

app.use('/api', [userApi, provinceApi])