Node Database programming

Node Of MySQL drive ：mysql2 yes node The native mysql Driver upgrade .
Installation Library ：npm install mysql2.

Establishing a connection

Explicit connection ：

• Import mysql2 modular

// Introduce modules 
const mysql = require('mysql2')

• call createConnection Method to create the connection object of the database

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

• call connect Method to connect to the database

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

• call query Method execution sql Inquire about

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

Implicit connection ：

• Import mysql2 modular
• call createConnection Method Create a database connection object
• call query Method execution sql Inquire about （query Has implicitly connected to the database ）

Termination and MySQL The connection of

• call end Method

connection.end()

• call destroy Method

connection.destroy()

Perform database operations (CRUD)

call query Method .

Query log ：

• Simple query ：query(sql,callback)

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

• Conditions of the query ：query(sqlString, values, callback)

Parameters values The corresponding is sqlString Placeholder in ?

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

• Parameters are objects ：query(options, callback)

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

Increase record ：
insert into Table name ( 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 Name = value ,… [where Conditions ]

var updatSql = 'update employee set address = ? where name = ?'
var update = [' changan ',' 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 Conditions ]

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 fields ]) 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 these connection objects form a pool , Objects in the application pool are dynamically connected by 、 Use and release .
The connection pool of the database is responsible for allocating 、 Manage and release database connection objects . It allows applications to reuse the connection objects of an existing database , Instead of re creating one .
This avoids frequent connections between applications 、 Disconnect database , Improve the efficiency of database connection objects .

Use of database connection pool

Create a database connection pool ：mysql.createPool(config)

// Import mysql modular 
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： User name to connect to the database
• password： 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 execution is allowed sql sentence , The default value is false

Get a connection from the connection pool ：

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

 Parameters err： Error object . Error message after connection failure 
 Parameters connection： Connection object . If the connection fails , It is undefined

pool.getConnection((err, connection) => {
    
    if (err) {
    
        throw err;
    }
    // Use database connection objects to execute queries 
    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 response to the front end 
        res.end();
    })
    connection.release(); // Release database connection object 
})

Release connection object ( Put the connection object back into the connection pool )：

 connection.release();

Remove the connection object from the connection pool ：

 connection.destory();

Close the connection pool ：

 Connection pool name .end();

In database access ORM

ORM： Object relation mapping , It mainly solves the problem of mismatch between object-oriented programming and relational database .

• class ---- surface
• attribute ---- Column
• object ---- That's ok

It can improve the efficiency of development , You don't have to write directly sql sentence .

Use

ORM The implementation framework of ( modular )：sequelize.

install ：

npm install sequelize

Connect to database ： establish sequelize The object of

// Import sequelize modular 
const Sequelize = require('sequelize');

// establish sequelize 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 // The longest waiting time , In milliseconds 
    }
});

Creating a data model ： The data model is a class , Corresponding to a table in the database

• Use define Method creation
• Use Sequelize.Model.init(attributes, options) function

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

// establish StudentModel Model , The table name of the model pair 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 property , If not defined field, Then 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 Indicates that the given table name is used ,false Represents the model name plus s As the table name 
       timestamps: false //true Indicates the timestamp attribute of the model (createAt、updateAt),false Indicates an attribute without a timestamp 
    }
);

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

module.exports = StudentModel;

Conduct CRUD operation ：

insert data ：

 The model name .create({
    
     Property name 1： value 1,
     Property name 2： value 2,
    ......
}).then((result)=> {
    
     After successful insertion, the code ;  Parameters 'result' In the middle is the data successfully inserted 
}).catch((error)=> {
    
     Code after insertion failure ; Parameters 'error' The information of insertion failure is placed in the 
})

Delete data ：

 The model name .destroy({
    
   where:{
    
       Property name : value 
   }
}).then((result)=> {
    
    Code after successful deletion ;  Parameters 'result' The number of deleted rows is placed in the ( Integers )
}).catch((error)=> {
    
    Delete the failed processing code , Parameters 'error' What is put in is the information of deletion failure 
})

Update data ：

 The model name .findOne({
    
  where: {
    
       Property name ： value 
  }
}).then((result)=> {
     // Parameters 'result' What is put in is the data found 
   result.update({
    
       Property name 1： value 1,
       Property name 2： value 2
      }).then((data)=> {
     // Parameters 'data' In the center is the updated data 
            Processing code 
      }).catch((error)=> {
    
            Processing code 
      })
}).catch((err)=> {
    
    No record processing code found 
})

Query data ：

 The model name .findAll({
    
    where:{
    
        Property name ： value 
    }
}).then((result)=> {
    
     Parameters 'result' The result set of the query 
}).catch((error)=> {
    
     Parameters 'error' What is put in is the information of query failure 
})