@[TOC] nodeJS express creates a server and operates the mysql database (personal study notes)

1, Initialization

1. Create project

1.1 create folders and initialize package management files

npm init -y

1.2 installing express

npm i express@4.17.1

1.3 create the app.js file in the root directory file as the entry file of the whole project

// Import express
const express = require('express')
// Create server instance object
const app = express()


// Start the server
app.listen(8888, ()=> {
    console.log('The server has started and is running on port 8888')
})

2. Configure cors cross domain

2.1 installing cors Middleware

npm i cors

2.2 import cors middleware into app.js file

// Import and configure cors Middleware
const cors = require('cors')
// Register as global
app.use(cors())

2.3 configure middleware for parsing form data (application/x-www-form-urlencoded)

app.use(express.urlencoded({ extended: false }))

3. Create routing related files (one storage path and one processing related configuration) and initialize them

3.1. The code for initializing the user routing module (creating the user.js file in the router folder as the user's routing module) is as follows:

const express = require('express')
// Create routing object
const router = express.Router()


// Register new users
router.post('/resgiter', (req, res) => {
    res.send('resgiter ok')
})
// Sign in
router.post('/login', (req, res) => {
    res.send('login ok')
})



module.exports = router

• Import and use the user routing module (in app.js)

const userRouter = require('./router/user')
app.use('/api', userRouter)

The postman tool is used for testing, and the results are as follows:

4. Extract the processing function in the user routing module

router file (user.js)

const express = require('express')
// Create routing object
const router = express.Router()

// Import the processing function module corresponding to the user route
const user_handle = require('../rotuter_handle/user')
// Register new users
router.post('/resgiter', user_handle.regiter)
// Sign in
router.post('/login', user_handle.login)



module.exports = router

router_handle file (user.js)

// Register handler
exports.regiter = (req, res) => {
    res.send('resgiter ok')
}
// Login handler
exports.login = (req, res) => {
    res.send('login ok')
}

2, Application of connection with mysql database

1. Create mysql data table (Navicat database visualization tool can be used)

2. Install and configure mysql module

2.1. Install mysql command

npm i mysql

2.2. Create a new file and create a database connection object

// Import mysql module
const mysql = require('mysql')

// Create database connection object
const db = mysql.createPool({
    host: '127.0.0.1',//Host IP
    user: 'root', //Database user name
    password: '', //Database password
    database: 'nodedb'//Database name
})

// Shared db database connection object
module.exports = db

2.3 user registration practice

1. Check whether the form is legal
2. Check whether the user name is occupied
3. Encrypt the password
4. Insert new user

2.3.1 check whether the form is legal

// Register handler
exports.resgiter = (req, res) => {
    // Get the user information submitted by the client to the server
    const userInfo = req.body
    // Judge whether the data is legal
    if (!userInfo.username || !userInfo.password) {
        return res.send({ status: 1, message: 'Illegal user name or password' })
    }
}

test result

2.3.2 check whether the user name is repeated (in the processing function)

• Import database operation module

const db = require('../db/index')

• Define sql statements to query whether the user name is occupied

    const sqlStr = 'select * from ev_users where username = ?'
    db.query(sqlStr, userInfo.username, (err,result) => {
        // Failed to execute mysql statement
        if (err) {
            return res.send({ status: 1, message: err.message })
        }
        // Determine whether the user name is occupied
        if (result.length > 0) {
            return res.send({ status: 1, message: 'The user name has been occupied, please change it' })
        }
        // User name can be used
        
    })

2.3.3 encrypt the password (in the processing function)

• Advantages of using bcryptjs:
  1. The encrypted password cannot be cracked reversely
  2. The same plaintext password is encrypted for many times, and the encryption results are different, which ensures the security

2.3.3.1 installation command

npm i bcryptjs

Using bcryptjs

// Import bcryptjs
const bcrypt = require('bcryptjs')

Call the hashSync() method in bcryptjs to encrypt and copy the encrypted value to the form password

        userInfo.password = bcrypt.hashSync(userInfo.password, 10)

Define sql statements to insert new users

         const sqlInsert = 'insert into ev_users set ?'
        db.query(sqlInsert, { username: userInfo.username, password: userInfo.password }, (err, result) => {
            if (err) {
                return res.send({ status: 1, message: err.message })
            }
            if (result.affectedRows !== 1) {
                return res.send({ status: 1, message: 'Failed to register user, please try again later' })
            }
            // login was successful
            res.send({ status:0, message: 'login was successful' })
        })

2.4. Complete code of router_handle (user.js):

// Import database operation module
const db = require('../db/index')
// Import bcryptjs
const bcrypt = require('bcryptjs')
// Register handler
exports.resgiter = (req, res) => {
    // Get the user information submitted by the client to the server
    const userInfo = req.body
    // Judge whether the data is legal
    if (!userInfo.username || !userInfo.password) {
        return res.send({ status: 1, message: 'Illegal user name or password' })
    }

    // Define sql statements to query whether the user name is occupied
    const sqlStr = 'select * from ev_users where username = ?'
    db.query(sqlStr, userInfo.username, (err,result) => {
        // Failed to execute mysql statement
        if (err) {
            return res.send({ status: 1, message: err.message })
        }
        // Determine whether the user name is occupied
        if (result.length > 0) {
            return res.send({ status: 1, message: 'The user name has been occupied, please change it' })
        }
        // d call the hashSync() method in bcryptjs for encryption
        userInfo.password = bcrypt.hashSync(userInfo.password, 10)

        // Define sql statements to insert new users
        const sqlInsert = 'insert into ev_users set ?'
        db.query(sqlInsert, { username: userInfo.username, password: userInfo.password }, (err, result) => {
            if (err) {
                return res.send({ status: 1, message: err.message })
            }
            if (result.affectedRows !== 1) {
                return res.send({ status: 1, message: 'Failed to register user, please try again later' })
            }
            // login was successful
            res.send({ status:0, message: 'login was successful' })
        })

    })
}
// Login handler
exports.login = (req, res) => {
    res.send('login ok')
}

The results are shown in the figure below