Learn to operate mysql database with nodejs in three minutes

Keywords: node.js MySQL Big Data

Step 1: - Preparation--

  • Create (projiect) folder
  • Open the terminal under the current file -- enter a command -- create a package management file
npm i 
npm init -y or npm init  // Generate package.json source file; If the name of the project folder is in Chinese, use npm init. If it is in English, use npm init directly 
npm init -y
npm i mysql // Download mysql
npm i express // Installing the express module

  After creation, it is displayed as follows:

  •   Create app.js file

  Step 2: open app.js through vs code - query

  • Import mysql
  • Connect the mysql database to the server through the createPool method and declare a db variable
  • Test whether the connection is successful through the db.query method
  • Return data to client
    • Import express
    • Create server
    • Start the server
    • Register routing
      • Execute sql statements through db.query (query database)
      • If the execution is successful, respond the data to the client

The code is as follows:

// Import mysql
const mysql = require('mysql')
// Import express
const express = require('express')
// Create server
const app = express()
// Connect to the server through the createPool method
const db = mysql.createPool({
    host: '127.0.0.1', // Indicates connecting to a mysql database on a server
    user: 'root', // User name of the database (root by default)
    password: 'root', // Password of the database (root by default)
    database: 'web67',// Name of local database created
})
// Get the data in the database through nodejs and return it to the client------------------ 
app.get('/user', (req, res) => {
    // Execute mysql through the db.query method to test whether the connection is successful
    // The query statement data gets an array, and the addition, deletion and modification get the number of affected rows
    db.query('select * from user', (err, data) => {
        if (err) return console.log(err.message); // connection failed
        if (data.length === 0) return console.log('Data is empty'); // If the data length is 0, no data is obtained
        // Otherwise, the result will be returned to the client res.send
        res.send({
            status: 0,
            msg: 'Data acquisition succeeded',
            data
        })
    })
})

// Start the server
app.listen(80, () => {
    console.log('running...');
})

Code validation:

Open the terminal under the current file -- enter the command -- nodemon file name. js

  If nodemon is not installed, you can also run it with the node file name. js

  Open Postmon to send a get request and enter http://127.0.0.1/user To verify whether the data is successfully printed on the client. The following page is displayed:

Step 3: add a user name and password to the database (user) -

  • Create the corresponding route in app.js
  • Get the data submitted by the client through req.body
  • Configure the middleware express.urlencoded to parse the data submitted by the form in the form of post
  • Building sql statements
  • Execute sql statement
  • If it fails, the user will be prompted to judge err. If it is an object, the execution fails
  • How do I know that data has been written to the database? Judge the number of affected rows to see if its value is 1   If it is not 1, the update fails. If the value is equal to 1, the update succeeds
  • Response data to client

The code is as follows:

 

  Complete code (including step 2):

// Import mysql
const mysql = require('mysql')
// Import express
const express = require('express')
// Create server
const app = express()
// Configuration Middleware
app.use(express.urlencoded({ extended: false }))
// Connect to the server through the createPool method
const db = mysql.createPool({
    host: '127.0.0.1', // Indicates connecting to a mysql database on a server
    user: 'root', // User name of the database (root by default)
    password: 'root', // Password of the database (root by default)
    database: 'web67',// Name of local database created
})
// Get the data in the database through nodejs and return it to the client------------------ 
app.get('/user', (req, res) => {
    // Execute mysql through the db.query method to test whether the connection is successful
    // The query statement data gets an array, and the addition, deletion and modification get the number of affected rows
    db.query('select * from user', (err, data) => {
        if (err) return console.log(err.message); // connection failed
        if (data.length === 0) return console.log('Data is empty'); // If the data length is 0, no data is obtained
        // Otherwise, the result will be returned to the client res.send
        res.send({
            status: 0,
            msg: 'Data acquisition succeeded',
            data
        })
    })
})

// Add user name and password to user
app.post('/addUser', (req, res) => {
    const data = req.body // Get data (configure middleware to parse data, otherwise undefind will be displayed)
    const sql = 'insert into user set ?' // Building sql statements
    // Execute sql statement
    db.query(sql, data, (err, data) => {
        if (err) return console.log(err.message); // Judge whether sql execution fails
        // Judge whether the data is successfully inserted and whether the value of affectedRows is 1. If it is not 1, the write fails
        if (data.affectedRows !== 1) return console.log('Data write failed');  
        // Otherwise, the client will be returned after successful writing
        res.send({
            status: 0,
            msg: 'Data written successfully'
        })
    })
})

// Start the server
app.listen(80, () => {
    console.log('running...');
})

Code validation:

Open the terminal under the current file -- enter the command -- nodemon file name. js

Open Postmon to send a post request and enter http://127.0.0.1/addUser To verify whether the data is successfully printed on the client. The following page is displayed:

Step 4: update the user name according to the id ----- change

  • Create the corresponding route in app.js
  • Get the data submitted by the client through req.body
  • Configure the middleware express.urlencoded to parse the data submitted by the form in the form of post
  • Building sql statements
  • Execute sql statement
  • If it fails, the user will be prompted to judge err. If it is an object, the execution fails
  • How do I know that data has been written to the database? Judge the number of affected rows to see whether the value is 1. If it is not 1, it means that the update fails. If the value is equal to 1, it means that the update is successful
  • Response data to client

The code is as follows:

 

   Complete code (including steps 2 and 3):

// Import mysql
const mysql = require('mysql')
// Import express
const express = require('express')
// Create server
const app = express()
// Configuration Middleware
app.use(express.urlencoded({ extended: false }))
// Connect to the server through the createPool method
const db = mysql.createPool({
    host: '127.0.0.1', // Indicates connecting to a mysql database on a server
    user: 'root', // User name of the database (root by default)
    password: 'root', // Password of the database (root by default)
    database: 'web67',// Name of local database created
})
// Get the data in the database through nodejs and return it to the client------------------ 
app.get('/user', (req, res) => {
    // Execute mysql through the db.query method to test whether the connection is successful
    // The query statement data gets an array, and the addition, deletion and modification get the number of affected rows
    db.query('select * from user', (err, data) => {
        if (err) return console.log(err.message); // connection failed
        if (data.length === 0) return console.log('Data is empty'); // If the data length is 0, no data is obtained
        // Otherwise, the result will be returned to the client res.send
        res.send({
            status: 0,
            msg: 'Data acquisition succeeded',
            data
        })
    })
})

// Add user name and password to user------------------
app.post('/addUser', (req, res) => {
    const data = req.body // Get data (configure middleware to parse data, otherwise undefind will be displayed)
    const sql = 'insert into user set ?' // Building sql statements
    // Execute sql statement
    db.query(sql, data, (err, data) => {
        if (err) return console.log(err.message); // Judge whether sql execution fails
        // Judge whether the data is successfully inserted and whether the value of affectedRows is 1. If it is not 1, the write fails
        if (data.affectedRows !== 1) return console.log('Data write failed');
        // Otherwise, the client will be returned after successful writing
        res.send({
            status: 0,
            msg: 'Data written successfully'
        })
    })
})

// Update user name based on id------------------
app.post('/updateUser', (req, res) => {
    const data = req.body
    const sql = 'update user set ? where id = ?'
    db.query(sql, [data, data.id], (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Data update failed');
        // The data is updated successfully, and the client result is returned
        res.send({
            status: 0,
            msg: 'Data update succeeded'
        })
    })
})

// Start the server
app.listen(80, () => {
    console.log('running...');
})

Code validation:

Open the terminal under the current file -- enter the command -- nodemon file name. js

   Open Postmon to send a post request and enter http://127.0.0.1/updateUser To verify whether the data is successfully printed on the client. The following page is displayed:

Step 5: failed to delete according to id ----- delete

  • Create the corresponding route in app.js
  • Get dynamic parameters through req.params
  • Building sql statements
  • Execute sql statement
  • If it fails, the user will be prompted to judge err. If it is an object, the execution fails
  • How to know that the data has been deleted successfully? Judge the number of affected rows to see if its value is 1. If it is not 1, the deletion fails. If it is 1, the deletion succeeds
  • Response data to client

The code is as follows:

  Complete code (including steps 2, 3 and 4):

// Import mysql
const mysql = require('mysql')
// Import express
const express = require('express')
// Create server
const app = express()
// Configuration Middleware
app.use(express.urlencoded({ extended: false }))
// Connect to the server through the createPool method
const db = mysql.createPool({
    host: '127.0.0.1', // Indicates connecting to a mysql database on a server
    user: 'root', // User name of the database (root by default)
    password: 'root', // Password of the database (root by default)
    database: 'web67',// Name of local database created
})
// Get the data in the database through nodejs and return it to the client------------------ 
app.get('/user', (req, res) => {
    // Execute mysql through the db.query method to test whether the connection is successful
    // The query statement data gets an array, and the addition, deletion and modification get the number of affected rows
    db.query('select * from user', (err, data) => {
        if (err) return console.log(err.message); // connection failed
        if (data.length === 0) return console.log('Data is empty'); // If the data length is 0, no data is obtained
        // Otherwise, the result will be returned to the client res.send
        res.send({
            status: 0,
            msg: 'Data acquisition succeeded',
            data
        })
    })
})

// Add user name and password to user------------------
app.post('/addUser', (req, res) => {
    const data = req.body // Get data (configure middleware to parse data, otherwise undefind will be displayed)
    const sql = 'insert into user set ?' // Building sql statements
    // Execute sql statement
    db.query(sql, data, (err, data) => {
        if (err) return console.log(err.message); // Judge whether sql execution fails
        // Judge whether the data is successfully inserted and whether the value of affectedRows is 1. If it is not 1, the write fails
        if (data.affectedRows !== 1) return console.log('Data write failed');
        // Otherwise, the client will be returned after successful writing
        res.send({
            status: 0,
            msg: 'Data written successfully'
        })
    })
})

// Update user name based on id------------------
app.post('/updateUser', (req, res) => {
    const data = req.body
    const sql = 'update user set ? where id = ?'
    db.query(sql, [data, data.id], (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Data update failed');
        // The data is updated successfully, and the client result is returned
        res.send({
            status: 0,
            msg: 'Data update succeeded'
        })
    })
})

// Delete data by id---------------------
app.get('/deleteData/:id', (req, res) => {
    const data = req.params.id // Get dynamic id parameter
    const sql = 'delete from user where id = ?' // Building sql statements
    db.query(sql, data, (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Data deletion failed');
        res.send({
            status: 0,
            msg: 'Data deleted successfully'
        })
    })
})

// Start the server
app.listen(80, () => {
    console.log('running...');
})

Code validation:

Open the terminal under the current file -- enter the command -- nodemon file name. js

Open Postmon to send a post request and enter http://127.0.0.1/updateUser To verify whether the data is successfully printed on the client. The following page is displayed:

Step 6: optimize and delete some codes ----- simulate deletion

Simulated deletion - false deletion: change the status status of the data to 0 and disable it;

Purpose: to prevent data from being retrieved after complete deletion;

  The code is as follows:

 

  Finally show the complete code (including all the above steps):

// Import mysql
const mysql = require('mysql')
// Import express
const express = require('express')
// Create server
const app = express()
// Configuration Middleware
app.use(express.urlencoded({ extended: false }))
// Connect to the server through the createPool method
const db = mysql.createPool({
    host: '127.0.0.1', // Indicates connecting to a mysql database on a server
    user: 'root', // User name of the database (root by default)
    password: 'root', // Password of the database (root by default)
    database: 'web67',// Name of local database created
})
// Get the data in the database through nodejs and return it to the client------------------ 
app.get('/user', (req, res) => {
    // Execute mysql through the db.query method to test whether the connection is successful
    // The query statement data gets an array, and the addition, deletion and modification get the number of affected rows
    db.query('select * from user', (err, data) => {
        if (err) return console.log(err.message); // connection failed
        if (data.length === 0) return console.log('Data is empty'); // If the data length is 0, no data is obtained
        // Otherwise, the result will be returned to the client res.send
        res.send({
            status: 0,
            msg: 'Data acquisition succeeded',
            data
        })
    })
})

// Add user name and password to user------------------
app.post('/addUser', (req, res) => {
    const data = req.body // Get data (configure middleware to parse data, otherwise undefind will be displayed)
    const sql = 'insert into user set ?' // Building sql statements
    // Execute sql statement
    db.query(sql, data, (err, data) => {
        if (err) return console.log(err.message); // Judge whether sql execution fails
        // Judge whether the data is successfully inserted and whether the value of affectedRows is 1. If it is not 1, the write fails
        if (data.affectedRows !== 1) return console.log('Data write failed');
        // Otherwise, the client will be returned after successful writing
        res.send({
            status: 0,
            msg: 'Data written successfully'
        })
    })
})

// Update user name based on id------------------
app.post('/updateUser', (req, res) => {
    const data = req.body
    const sql = 'update user set ? where id = ?'
    db.query(sql, [data, data.id], (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Data update failed');
        // The data is updated successfully, and the client result is returned
        res.send({
            status: 0,
            msg: 'Data update succeeded'
        })
    })
})

// Delete data by id---------------------
app.get('/deleteData/:id', (req, res) => {
    const data = req.params.id // Get dynamic id parameter
    const sql = 'update user set status = 0 where id = ?' // Building sql statements
    db.query(sql, data, (err, data) => {
        if (err) return console.log(err.message);
        if (data.affectedRows !== 1) return console.log('Data deletion failed');
        res.send({
            status: 0,
            msg: 'Data deleted successfully'
        })
    })
})

// Start the server
app.listen(80, () => {
    console.log('running...');
})

Code validation:

Open the terminal under the current file -- enter the command -- nodemon file name. js

Open Postmon to send a post request and enter http://127.0.0.1/updateUser To verify whether the data is successfully printed on the client. The following page is displayed:

Posted by bad_gui on Mon, 11 Oct 2021 13:36:45 -0700

Hot Keywords

©2024 Programmer Group