Database programming of Node

MySQL driver of node: MySQL 2 is an upgraded version of node's native MySQL driver.
Installation Library: npm install mysql2.

Establish connection

Explicit connection:

• Import mysql2 module

//Introduction module
const mysql = require('mysql2')

• Call the createConnection method to create a connection object for the database

//Create database connection object
const connection = mysql.createConnection({
    host:'localhost',
    port:3306,
    user:'root',
    password:'111111',
    database:'project'
})

• Call the connect method to connect to the database

//Call the connect method to connect to the database
connection.connect((err)=> {
    if(err) {
        throw err
    }
    console.log(connection.threadId)
})

• Call the query method to execute sql query

//Query data
connection.query('select * from employee',(err,result)=> {
    if(err) {
        console.log(err)
        return
    }
    console.log(result)
})

Implicit connection:

• Import mysql2 module
• Call the createConnection method to create a connection object for the database
• Call the query method to execute sql query (query has implicitly connected to the database)

Terminate the connection to MySQL

• Call the end method

connection.end()

• Call destroy method

connection.destroy()

Perform database operations (CRUD)

Call the query method.

Query record:

• Simple query: query(sql,callback)

connection.query('select * from employee',(err,result)=> {
    if(err) {
        console.log(err)
        return
    }
    console.log(result)
})

• Condition query: query(sqlString, values, callback)

The parameter values corresponds to the placeholder in sqlString?

var sqlString = 'select * from employee where address = ?'
connection.query(sqlString,['Jinling'],(err,result)=> {
    if(err) {
        console.log(err)
        return
    }
    console.log(result)
})

• The parameter is the object: query(options, callback)

connection.query({
    sql:'select * from employee where address=?',
    values:['Jinling']
},(err,result)=> {
    if(err) {
        console.log(err)
        return
    }
    console.log(result)
})

Add record:
insert into table name (column name...) values (value...)

var add = 'insert into employee(name,gender,birthday,phone,address) values(?,?,?,?,?)'
var addSql = ['Deng Ying','male','1990-10-31','11111111','Daming']

connection.query({
    sql:add,
    values:addSql
},(err,result)=> {
    if(err) {
        console.log(err)
        return
    }
    console.log(result.insertId)
    console.log(result)
})

Update record:
update table name set column name = value,... [where condition]

var updatSql = 'update employee set address = ? where name = ?'
var update = ['Chang'an','Deng Ying']

connection.query({
    sql:updatSql,
    values:update
},(err,data)=> {
    if(err) {
        console.log(err)
        return
    }
    console.log(data.affectedRows)
    console.log(data.changedRows)
})

Delete record:
delete from table name [where condition]

var delSql = 'delete from employee where name=?'
connection.query({
    sql:delSql,
    values:'Deng Ying'
},(err,result)=> {
    if(err) {
        console.log(err)
        return
    }
    console.log(result.affectedRows)
})

Prevent injection attacks
Use placeholders?
Use connection.escape([parameter field]) to escape the value

Database connection pool technology

Database connection pool is to establish a sufficient number of database connection objects when the program starts, and form these connection objects into a pool. The program dynamically applies, uses and releases the connection objects in the pool.
The connection pool of the database is responsible for allocating, managing and releasing database connection objects. It allows applications to reuse an existing database connection object instead of re creating one.
This can avoid the frequent connection and disconnection of the application to the database, and improve the use efficiency of the database connection object.

Use of database connection pool

Create database connection pool: mysql.createPool(config)

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

const pool = mysql.createPool({
    connectionLimit:20,
    host:'localhost',
    port:3306,
    user:'root',
    password:'200173',
    database:'project',
    multipleStatements:true
})

• host: the address of the database server
• Port: port number
• User: the user name to connect to the database
• Password: the password to connect to the database
• Database: database name
• connectionLimit: used to specify the maximum number of links in the connection pool. The default property value is 10
• multipleStatements: whether multiple sql statements are allowed to be executed. The default value is false

Get a connection from the connection pool:

Connection pool name.getConnection(function(err,connection){		
       Executed code
})

parameter err: Wrong object. Error message after connection failure
 parameter connection: Connect objects. If the connection fails,It is undefined

pool.getConnection((err, connection) => {
    if (err) {
        throw err;
    }
    //Executing queries using database connection objects
    connection.query('select * from employee', (e, result) => {
        if (e) {
            throw e;
        }
        res.write(JSON.stringify(result)); //Convert the query results of the database into JSON format and respond to the front end
        res.end();
    })
    connection.release(); //Release database connection object
})

Release the connection object (put the connection object back into the connection pool):

 connection.release();

To remove a connection object from a connection pool:

 connection.destory();

Close the connection pool:

Connection pool name.end();

ORM in database access

ORM: object relational mapping, which mainly solves the mismatch between object-oriented programming and relational database.

• Category - table
• Attribute - column
• Object - line

It can improve the efficiency of development without directly writing sql statements.

use

Implementation framework (module) of ORM: serialize.

Installation:

npm install sequelize

Connecting to the database: creating sequenced objects

//Import serialize module
const Sequelize = require('sequelize');

//Create a serialize object
var MySequelize = new Sequelize('dbms','root','123456',{
    host: 'localhost',
    port: 3306,
    dialect: 'mysql', //Database type
    pool: { //Database connection pool
        max: 20, //Maximum number of connected objects
        min: 5, //Minimum number of connected objects
        idle: 10000 //Maximum wait time in milliseconds
    }
});

Create data model: a data model is a class that corresponds to a table in the database

• Create using the define method
• Use the serialize. Model. Init (attributes, options) function

const Sequelize = require('sequelize');
const MySequelize = require('../config/dbconfig');

//Create a StudentModel model whose table name is student

var StudentModel = MySequelize.define('student',{
    sid: {
        type: Sequelize.INTEGER, //Represents the data type of the property
        field: 's_id', //The column name corresponding to the attribute. If no field is defined, the column name in the table is the attribute name
        primaryKey: true, //Represents the primary key
        autoIncrement: true //Indicates that the primary key is automatically incremented
    },
    sname: {
        type: Sequelize.STRING(50),
        field: 's_name',
        allowNull: false, //Indicates that the column cannot be empty
        //unique: true / / indicates that the value of this column must be unique
    },
    sgender: {
        type: Sequelize.STRING(4),
        field: 's_gender',
        allowNull: false
    },
    sbirthday: {
        type: Sequelize.DATE,
        field: 's_birthday',
        allowNull: false
    },
    saddress: {
        type: Sequelize.STRING(100),
        field: 's_address',
        allowNull: false
    }
},
    {
       freezeTableName: true, //true means to use the given table name, false means the model name plus s as the table name
       timestamps: false //true indicates that the model has time stamped attributes (createAt and updateat), and false indicates that the model does not have time stamped attributes
    }
);

// var student = StudentModel.sync({force: false}); // Synchronize the database. The value of force is false. If the table exists, it will be deleted and then created. The value of force is true, which means that if the table exists, it will not be created

module.exports = StudentModel;

CRUD operation:

Insert data:

Model name.create({
    Property name 1: value 1,
    Property name 2: value 2,
    ......
}).then((result)=> {
    Code after successful insertion; parameter'result'The data inserted successfully is placed in the
}).catch((error)=> {
    Code after insertion failure;parameter'error'The information of insertion failure is placed in the
})

Delete data:

Model name.destroy({
   where:{
      Attribute name:value
   }
}).then((result)=> {
   Code after deletion; parameter'result'The number of deleted rows is placed in the(integer)
}).catch((error)=> {
   Delete failed processing code,parameter'error'The information that failed to delete is placed in the
})

Update data:

Model name.findOne({
  where: {
      Attribute name: value
  }
}).then((result)=> { //The parameter 'result' contains the found data
   result.update({
      Property name 1: value 1,
      Property name 2: value 2
      }).then((data)=> { //The parameter 'data' contains the updated data
           Processing code
      }).catch((error)=> {
           Processing code
      })
}).catch((err)=> {
   No record processing code found
})

Query data:

Model name.findAll({
    where:{
       Attribute name: value
    }
}).then((result)=> {
    parameter'result'The result set of the query is placed in the
}).catch((error)=> {
    parameter'error'The information of failed query is placed in the
})