Node.js MySQL driver

• Node. The native MySQL driver library of JS is called mysql

• MySQL L2 project is an upgraded version of the native MySQL driver project. It is compatible with MySQL and supports its main features. It provides new features:

  • Faster and better performance
  • Preprocessing statement
  • Extended support for encoding and collation
  • Promise wrapper
  • SSL and authentication switch
  • Custom flow

• Install mysql2 driver (the library name is mysql2): npm install mysql2

Operating MySQL database

Connect to MySQL database

1. Example of explicitly establishing a connection:

const mysql = require('mysql2');
//Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password : 'abc123',
  database: 'testmydb'
});
connection.connect(function(err) {
  if (err) {
    console.error('Connection error: ' + err.stack);
    return;
  }
  console.log('connect ID: ' + connection.threadId);
});

2. Example of implicitly establishing a connection:

var mysql = require('mysql2');
var connection = mysql.createConnection(...);//The connection option code is omitted here
connection.query('SELECT * FROM `bookinfo`',function(err, results) {
    console.log(results); // The result includes the rows returned by the MySQL server
});

Set connection options:

attribute	describe
host	The database address of the connection. The default is localhost
port	The port corresponding to the connection address is 3306 by default
user	MySQL user name for connection
password	User's password
database	Name of the database to which you want to connect (optional)
charset	The coding form of the connection (utf8_general_ci by default) determines the collation sorting rules
timezone	Time zone configured on MySQL server (default local)
dateStrings	Returns the mandatory DATE type (TIMESTAMP, DATETIME, or DATE) as a string
connectTimeout	When setting the connection, the unresponsive waiting time before failure is returned

Terminate connection:

• connection.end(function(err) {/ / connection termination});
• connection.destroy();

Add, delete, modify query

1. Query record:

The first form: query(sqlString, callback)

const mysql = require('mysql2');
//Establish connection
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password : 'abc123',
  database: 'testmydb'
});
connection.connect();
//Execute query operation
connection.query('SELECT * FROM `bookinfo` WHERE `press` = "People's Posts and Telecommunications Publishing House"', function 
(err, results, fields) {
    if (err) throw err;
    console.log('-------Query record----------');
    console.log('Query results:',results);  
    console.log('Query result field:',fields);  
});
connection.end();   //abort connection

The second form: query(sqlString, values, callback)

connection.query('SELECT * FROM `bookinfo` WHERE `press` = ?', ['People's Posts and Telecommunications Publishing House'],
function (err, results, fields) {
});

The third form: query(options, callback)

connection.query({
 	 'SELECT * FROM `bookinfo` WHERE `press` = ?',
 	  timeout: 40000, // 40 seconds
  	  values: ['People's Posts and Telecommunications Publishing House']
	}, function (error, results, fields) {
});

Combination 2 and 3:

connection.query({
    'SELECT * FROM `bookinfo` WHERE `press` = ?',
     timeout: 40000, // 40s
   },
   ['People's Posts and Telecommunications Publishing House'],
   function (err, results, fields) {
);

Query with a single placeholder:

connection.query('SELECT * FROM `bookinfo` WHERE `press` = ?', 'People's Posts and Telecommunications Publishing House',
	function (err, results, fields) {
});

2. Add record:

//The connection establishment code is omitted here
//Define SQL statements and parameters for adding records
var  addSql = ' INSERT INTO `bookinfo`(`isbn`, `name`, `author`, `press`, `price`, `pubdate`) VALUES(?,?,?,?,?,?)';
var  addSql_Params = ['9787115488435', 'Artificial intelligence (2nd Edition)','Steven•Lucci','People's Posts and Telecommunications Publishing House',108.00,'2018-09-01'];
//Perform the add operation through the query command
connection.query(addSql,addSql_Params,function (err, results) {
    if (err) throw err;
    console.log('-------insert record----------');
    console.log('Insert record ID:',results.insertId);
    console.log('Insert results:',results);  
});
connection.end();

3. Modification record:

//The connection establishment code is omitted here
//Define SQL statements and parameters for modifying records
var updateSql = 'UPDATE bookinfo SET author = ?,price = ? WHERE id = ?';
var updateSql_Params = ['[day]Jiechenghao',87.5,9];
//Perform modification through query command
connection.query(updateSql,updateSql_Params,function (err, result) {
    if (err) throw err;
    console.log('-------Modify record----------');
    console.log('Number of rows affected by modification:',result.affectedRows);  
    console.log('Modify the number of rows changed:',result.changedRows);  
});
connection.end();

4. Delete record:

//The connection establishment code is omitted here
//Define SQL statements to delete records
var delSql = 'DELETE FROM bookinfo WHERE id = 11';
//Delete through query command
connection.query(delSql,function (err, result) {
    if (err) throw err;
    console.log('-------Delete record----------');
    console.log('Number of rows deleted:',result.affectedRows);  
});
connection.end(); 

Prevent SQL injection attacks

1. Escape can be done through mysql escape( ),connection.escape() or pool Escape () method.

var userId = 'some user provided value';
var sql  = 'SELECT * FROM users WHERE id = ' + connection.escape(userId);
connection.query(sql, function(err, results) {
  // ...
});

2. Can the symbol be? As a placeholder in the query string to replace the value to escape.

connection.query('SELECT * FROM users  WHERE id = ?', [userId], function(err, results) {
  // ...
});

Streaming query:

var query = connection.query('SELECT * FROM posts');
query
  .on('error', function(err) {
    //Handle the error, after which the 'end' event will be triggered
  })
  .on('fields', function(fields) {
    // Process field data
  })
  .on('result', function(row) {
    connection.pause();//Pausing a connection is useful if the process involves I/O operations
    processRow(row, function() {
      connection.resume();
    });
  })
  .on('end', function() {
    //All lines have been received
  });

Use preprocessing statements:

connection.execute(
  'SELECT * FROM 'table' WHERE 'name' = ? AND 'age' > ?',
  ['Rick C-137', 53],
  function(err, results, fields) {
    console.log(results); 
  }
);

Database connection pool operation

Connection pool is a very important concept when developing web applications. The performance cost of establishing a database connection is very high. In server applications, if one or more database connections are established for each received client request, the performance of the application will be seriously reduced.

In server applications, it is usually necessary to create and maintain a connection pool for multiple database connections. When the connections are no longer needed, these connections can be cached in the connection pool. When the next client request is received, the connections will be taken out of the connection pool and reused without re establishing the connection

Comparison of usage methods of database object and database connection pool:

1. Create database connection object:

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

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

2. Method of saving data to database:

function save(params) {
    connection.query('insert into student set?',{
        s_id:params.id,
        s_name:params.name,
        s_birthday:params.birthday,
        s_gender:params.gender
    },(err,result) => {
        if (err){
            console.log(err);
            return;
        }
        console.log('Insert data succeeded');
        console.log(result);
        //Disconnect database
        connection.end();
    })
}

1. Create database connection pool:

//Import mysql module
const mysql = require('mysql2');
//Create database connection pool
const pool=mysql.createPool({
        connectionLimit:20,
        host : 'localhost',
        port : 3306,
        user : 'root',
        password : '123',
        database : 'school',
        multipleStatements:true  //Allow multiple MySQL statements in a query (default: false)
    }
);

2. Save data to database connection pool:

function save(params) {
    pool.getConnection((err, connection) => {
        if (err) {
            console.log('Failed to connect to the database', err);
            return;
        }
        connection.query('insert into student set?', {
            s_id: params.id,
            s_name: params.name,
            s_birthday: params.birthday,
            s_gender: params.gender
        }, (err, result) => {
            if (err) {
                console.log(err);
                return;
            }
            console.log('Insert data succeeded');
            //Release database connection object
            connection.release();
        })
    })
}