Node.js connects to MySql database to realize addition, deletion, modification and query

First, you need to prepare some environments and development tools

Then create an empty folder (I created demo), enter the command line where the empty folder is located, and then use the npm init -y command for initialization

// The initialization results are as follows
{
  "name": "demo",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Then install two modules under this command line: express and mysql. Express is more convenient to realize routing

npm install(i) express
npm install(i) mysql

// You can also install two modules at a time
npm i express mysql

The query method is directly used to add, delete, modify and query without processing

Then, create index.js and api.http in the root directory of the folder (the file name can be named casually and you can recognize it). The api.http file is used to test whether the route is written successfully. You can test the get, post, delete and put request methods at the same time. You need to install the REST Client plug-in to use it

  • be careful
    The get and delete request methods take values in req.query, and the parameter transfer form is directly spliced after the path
    The post and pur request modes are taken in req.body, and the parameter transfer form must comply with the strict json mode

Write code in the index.js file to build a simple server

const express = require('express')
const mysql = require('mysql') // Introduce two modules for download

const app = express() // Instantiate the express module

app.use(express.json()) // The express.json() function is a built-in middleware function in express. It uses body parser to parse incoming requests with json payload

// Test whether the server is set up successfully
app.get('/', (req, res) => {
    res.send('You have succeeded~~')
})

app.listen(3030, () => {
    console.log('The server is turned on at port 3030...')
})

When the following results appear, the server has been set up successfully

Then encapsulate a function to connect to the database and return the connection method

// Method of encapsulating connection data
function Db() {
    let conn = mysql.createConnection({
        host: 'localhost', // Host name. The default is localhost
        port: '3306', // MySql connection port, 3306 is the default port number
        user: 'root', // User name to connect to the database
        password: '123456', // password
        database: 'demo' // Database name
    })
    conn.connect((err) => {
        if (err) throw err
    })
    return conn // Return the encapsulated connection method
}

Write the specific data of the operation database in the api.http file. The complete api.http code is as follows

// api.http
@url = http://localhost:3030 / / the default port number is 3030

POST {{url}}/addData
Content-Type: application/json

{
    "username": "root",
    "password": "123456",
    "sex": "female",
    "age": "23"
}

### This is a comment that must be written to separate different routes
DELETE {{url}}/deleteData?username=bbb

###
PUT {{url}}/updateData
Content-Type: application/json

{
    "username": "root",
    "age": "27"
}

###
GET {{url}}/selectData?username=root

After the above preparation work is completed, the addition, deletion, modification and query of the database can be realized

Add data

// Add data
app.post('/addData', (req, res) => {
    let { username, password, sex, age } = req.body
    let conn = Db() // Establish connection
    let sql = `insert into users(username, password, sex, age) values('${username}', '${password}', '${sex}', '${age}')`
    conn.query(sql, (err, result) => {
        if(err){
            console.log(err)
        } else{
            res.send(result)
        }
    })
    conn.end() // Close connection
})

The data table before adding data is not executed is as follows

Click Send Request to send the request. The request result is as follows:

The data table after successful request is as follows

Delete data

// Delete data
app.delete('/deleteData', (req, res) => {
    let { username } = req.query![Insert picture description here](https://img-blog.csdnimg.cn/a78c353372d540dfa6a465d2565d5945.jpg#pic_center)

    let conn = Db()
    let sql = `delete from users where username = '${username}'`
    conn.query(sql, (err, result) => {
        if(err){
            console.log(err)
        } else{
            res.send(result)
        }
    })
    conn.end()
})

The data table before deletion is as follows

The operation in the api.http file is the same as when adding data, but a delete request is sent
The deleted data table is as follows

Modify data

// Modify data
app.put('/updateData', (req, res) => {
    let { username, age } = req.body
    let conn = Db()
    let sql = `update users set age = '${age}' where username = '${username}'`
    conn.query(sql, (err, result) => {
        if(err){
            console.log(err)
        } else{
            res.send(result)
        }
    })
    conn.end()
})

Before modifying the data, the data table is as follows

The operation in the api.http file is the same as when adding data, but a put request is sent
After modifying the data, the data table is as follows. It can be seen that the age has changed

Query data

// Find data
app.get('/selectData', (req, res) => {
    let { username } = req.query
    let conn = Db()
    let sql = `select * from users where username = '${username}'`
    conn.query(sql, (err, result) => {
        if(err){
            console.log(err)
        } else{
            res.send(result)
        }
    })
    conn.end()
})

The operation in the api.http file is the same as when adding data, but you need to send a get request
The data in the data sheet are as follows:

The query results are as follows

The above implements the addition, deletion, modification and query of Node operation MySql. The complete index.js code is as follows, and the complete api.http code is above

// index.js
const express = require('express')
const mysql = require('mysql') // Introduce two modules for download

const app = express() // Instantiate the express module

app.use(express.json()) // The express.json() function is a built-in middleware function in express. It uses body parser to parse incoming requests with json payload

// Method of encapsulating connection data
function Db() {
    let conn = mysql.createConnection({
        host: 'localhost', // Host name. The default is localhost
        port: '3306', // MySql connection port, 3306 is the default port number
        user: 'root', // User name to connect to the database
        password: '123456', // password
        database: 'demo' // Database name
    })
    conn.connect((err) => {
        if (err) throw err
    })
    return conn // Return the encapsulated connection method
}

// Test whether the server is set up successfully
app.get('/', (req, res) => {
    res.send('You have succeeded~~')
})

// Add data
app.post('/addData', (req, res) => {
    let { username, password, sex, age } = req.body
    let conn = Db() // Establish connection
    let sql = `insert into users(username, password, sex, age) values('${username}', '${password}', '${sex}', '${age}')`
    conn.query(sql, (err, result) => {
        if(err){
            console.log(err)
        } else{
            res.send(result)
        }
    })
    conn.end() // Close connection
})

// Delete data
app.delete('/deleteData', (req, res) => {
    let { username } = req.query
    let conn = Db()
    let sql = `delete from users where username = '${username}'`
    conn.query(sql, (err, result) => {
        if(err){
            console.log(err)
        } else{
            res.send(result)
        }
    })
    conn.end()
})

// Modify data
app.put('/updateData', (req, res) => {
    let { username, age } = req.body
    let conn = Db()
    let sql = `update users set age = '${age}' where username = '${username}'`
    conn.query(sql, (err, result) => {
        if(err){
            console.log(err)
        } else{
            res.send(result)
        }
    })
    conn.end()
})

// Find data
app.get('/selectData', (req, res) => {
    let { username } = req.query
    let conn = Db()
    let sql = `select * from users where username = '${username}'`
    conn.query(sql, (err, result) => {
        if(err){
            console.log(err)
        } else{
            res.send(result)
        }
    })
    conn.end()
})

app.listen(3030, () => {
    console.log('The server is turned on at port 3030...')
})

Use Promise, async and await to add, delete, modify and query

Peomise is an asynchronous loading container, which can combine async and await to convert asynchronous operations into synchronous operations, and then solve the callback problem
As above, in index_ Write code in promise.js file to create a simple server

const express = require('express')
const mysql = require('mysql')

const app = express()

app.use(express.json())

// test
app.get('/', (req, res) => {
    res.send('You succeeded!!')
})

app.listen(4040, () => {
    console.log('The server starts on port 4040...')
})

When the following results appear, the server has been set up successfully

Then start connecting to the database

// Connect to database
const Db = mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'123456',
    database:'demo',
    port:3306
})
Db.connect((err)=> {
    if(err) throw err
})

Encapsulate Promise function, realize the operation of database, and return the result through one-dimensional data

// Encapsulate Promise function
function Query(sql){
    return new Promise((resolve, reject) => {
        Db.query(sql, (err, result) => {
            if(!err){
                resolve([null, result])
            } else{
                resolve([err])
            }
        })
    })
}

Encapsulate the error information processing function to uniformly process the format of error information

// Encapsulating error message handling functions
function Error(options){
    let { status, message, result = [] } = options
    return {
        status,
        message,
        result
    }
}

Encapsulates the correct result function and uniformly processes the format of the returned correct result information

// Encapsulate the correct result function
function Success(options){
    let { status = 200, message, result} = options
    return {
        status,
        message,
        result
    }
}

In api_Promise.http file to write the specific data of the operation database and complete api_Promise.http code is as follows

@url = http://localhost:4040 / / port 4040 is set this time

POST {{url}}/addData
Content-Type: application/json

{
    "username": "Zhang San",
    "password": "123456",
    "sex": "male",
    "age": "23"
}

###
DELETE {{url}}/deleteData?username=Li Si

###
PUT {{url}}/updateData
Content-Type: application/json

{
    "username": "Zhang San",
    "age": "25"
}

###
GET {{url}}/selectData?username=Zhang San

After the above preparation work is completed, the addition, deletion, modification and query of the database can be realized

Add data

// Add data
app.post('/addData', async (req, res, next) => {
    let { username, password, sex, age } = req.body
    let sql = `insert into users(username, password, sex, age) values('${username}', '${password}', '${sex}',' ${age}')`
    let [err, result] = await Query(sql)
    if(err){
        res.send(Error({
            status: 500,
            message: 'Add failed'
        }))
        return ;
    } 
    res.send(Success({
        message: 'Added successfully',
        result
    }))
})

The data table before adding is as follows

The added results are as follows

The data table after adding is as follows

Delete data

// Delete data
app.delete('/deleteData', async(req, res, next) => {
    let { username } = req.query
    let sql = `delete from users where username = '${username}'`
    let [err, result] = await Query(sql)
    if(err){
        res.send(Error({
            status: 500,
            message: 'Deletion failed'
        }))
        return ;
    } 
    res.send(Success({
        message: 'Delete succeeded',
        result
    }))
})

The contents of the data table before deletion are as follows

The data table after deletion is as follows:

Modify data

// Modify data
app.put('/updateData', async(req, res, next) => {
    let { username, age } = req.body
    let sql = `update users set age = '${age}' where username = '${username}'`
    let [err, result] = await Query(sql)
    if(err){
        res.send(Error({
            status: 500,
            message: 'Modification failed'
        }))
        return ;
    } 
    res.send(Success({
        message: 'Modified successfully',
        result
    }))
})

The data sheet before modification is as follows:

The contents of the modified data table are as follows. It can be seen that the age has changed

Find data

// Find data
app.get('/selectData', async(req, res, next) => {
    let { username } = req.query
    let sql = `select * from users where username = '${username}'`
    // let sql = `select * from users`
    let [err, result] = await Query(sql)
    if(err){
        res.send(Error({
            status: 500,
            message: 'Search failed'
        }))
        return ;
    } 
    res.send(Success({
        message: 'Search succeeded',
        result
    }))
})

The contents in the data sheet are as follows:

The results of searching data are as follows

In the above, promise combines async and await operations to add, delete, modify and query MySql, and complete index_Promise.js code is as follows, api_Promise.http the complete code is above

// index_Promise.js
const express = require('express')
const mysql = require('mysql')

const app = express()

app.use(express.json())

// Connect to database
const Db = mysql.createConnection({
    host:'localhost',
    user:'root',
    password:'123456',
    database:'demo',
    port:3306
})
Db.connect((err)=> {
    if(err) throw err
})

// Encapsulate Promise function
function Query(sql){
    return new Promise((resolve, reject) => {
        Db.query(sql, (err, result) => {
            if(!err){
                resolve([null, result])
            } else{
                resolve([err])
            }
        })
    })
}

// Encapsulating error message handling functions
function Error(options){
    let { status, message, result = [] } = options
    return {
        status,
        message,
        result
    }
}

// Encapsulate the correct result function
function Success(options){
    let { status = 200, message, result} = options
    return {
        status,
        message,
        result
    }
}

// test
app.get('/', (req, res) => {
    res.send('You succeeded!!')
})

// Add data
app.post('/addData', async (req, res, next) => {
    let { username, password, sex, age } = req.body
    let sql = `insert into users(username, password, sex, age) values('${username}', '${password}', '${sex}',' ${age}')`
    let [err, result] = await Query(sql)
    if(err){
        res.send(Error({
            status: 500,
            message: 'Add failed'
        }))
        return ;
    } 
    res.send(Success({
        message: 'Added successfully',
        result
    }))
})

// Delete data
app.delete('/deleteData', async(req, res, next) => {
    let { username } = req.query
    let sql = `delete from users where username = '${username}'`
    let [err, result] = await Query(sql)
    if(err){
        res.send(Error({
            status: 500,
            message: 'Deletion failed'
        }))
        return ;
    } 
    res.send(Success({
        message: 'Delete succeeded',
        result
    }))
})

// Modify data
app.put('/updateData', async(req, res, next) => {
    let { username, age } = req.body
    let sql = `update users set age = '${age}' where username = '${username}'`
    let [err, result] = await Query(sql)
    if(err){
        res.send(Error({
            status: 500,
            message: 'Modification failed'
        }))
        return ;
    } 
    res.send(Success({
        message: 'Modified successfully',
        result
    }))
})

// Find data
app.get('/selectData', async(req, res, next) => {
    let { username } = req.query
    let sql = `select * from users where username = '${username}'`
    // let sql = `select * from users`
    let [err, result] = await Query(sql)
    if(err){
        res.send(Error({
            status: 500,
            message: 'Search failed'
        }))
        return ;
    } 
    res.send(Success({
        message: 'Search succeeded',
        result
    }))
})

app.listen(4040, () => {
    console.log('The server starts on port 4040...')
})

The above are two ways to implement Node operation MySql for data addition, deletion, modification and query. If you don't understand anything, you are welcome to leave a message~~

Tags: Javascript node.js MySQL Visual Studio Code

Posted on Sat, 11 Sep 2021 19:22:23 -0400 by balkar