Introduction of Connecting Node to mysql Database

Use Node to do Web development, basically use NoSQL database, the most frequent is to use MongoDB, I did some simple Web development, in order to reduce the learning threshold, 1 straight use MySQL to do database. Here is a brief introduction to the way of connecting MySQL database under 1, hoping to help others.

npm install --save mysql

After using the above command to install the module of MySQL, you can use it directly. A simple example in DOCS in official website can be started as follows.

var mysql = require('mysql');
var connection = mysql.createConnection({
 host: 'localhost',
 user: 'me',
 password : 'secret',
 database : 'my_db'
});
connection.connect();
connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
 if (err) throw err;
 console.log('The solution is: ', rows[0].solution);
});
connection.end();

A very simple example, from the above example can be obtained: use createConnection (option) method to create a connection object, and then the connection object connect () method to create a connection, and finally use query () method to execute SQL statement, return the result as a callback function parameter rows, rows is an array type.

Step 1 Connect

To create a connection object, you need to pass in some connection parameters to connect to the database, that is, option in createConnection (option), and option is an object, which is passed into createConnection () method in the form of key-value pairs. The above example lists the most basic parameters:

host Hostname user users who connect to the database password password database database name

There are other parameters, you can query the official DOCS, which is not listed here. It is enough to learn these parameters at the initial stage.

Step 2 Close

Closing a connection uses the end () method, which provides a callback function as follows:

connect.end(function(err){
  console.log('End a connection');
});

This is the recommended method, and the end () method waits for the connection callback to complete before closing the connection. Another official method, the destroy () method, closes the connection without waiting for the callback to complete.

Give a simple example:

var mysql = require('mysql');
var option = require('./connect.js').option;
var conn = mysql.createConnection(option);
conn.query('select * from message',function(err,rows,fields){
 if(!err){
  console.log(rows);
 }
});
conn.end(function(err){
 console.log('end a connection');
});

The final result will be: Print the SELECT datasheet results first, then print end a connection. And if you change the closing method to conn. destroy (); You don't want to return any results, because the connection is terminated before the callback ends.

3. Connection pooling

The principle of connection pool is to create multiple connection objects for you at the beginning and put them in a "pool", take one when you use it, and put it back in the "pool" when you use it. To a certain extent, it is beneficial to save system overhead, because connection objects are created at the beginning, and system overhead is no longer needed to create database connection objects when you use it. The official DOCS introduces the connection method:

var mysql = require('mysql');
var pool = mysql.createPool({
 connectionLimit : 10,
 host      : 'example.org',
 user      : 'bob',
 password    : 'secret',
 database    : 'my_db'
});
pool.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
 if (err) throw err;
 console.log('The solution is: ', rows[0].solution);
});

The method of creating connection pool is createPool (option). One more parameter connectionLimit in option refers to how many connection objects are created in the connection pool once, and the default is 10. If you want to share 1 connection object, you can use the following methods to connect;

var mysql = require('mysql');
var pool = mysql.createPool({
 host   : 'example.org',
 user   : 'bob',
 password : 'secret',
 database : 'my_db'
});
pool.getConnection(function(err, connection) {
 // Use the connection
 connection.query( 'SELECT something FROM sometable', function(err, rows) {
  // And done with the connection.
  connection.release();
  // Don't use the connection here, it has been returned to the pool.
 });

// Use the connection
 connection.query( 'SELECT something2 FROM sometable2', function(err, rows) {
  // And done with the connection.
  connection.release();
  // Don't use the connection here, it has been returned to the pool.
 });
});

Execute the query () function twice with one connection object.

4. Example 1

Use the basic connection method to connect to the database, defining the data connection and the closed function respectively, as shown in the following example:

// connect.js  Database connection and closure 
var mysql = require('mysql');
var config = require('./config.json'); //  Write database connection parameters to mysql Object, that is config.mysql
var connCount = 0; //  Count the connections that are not closed at present 
exports.getConn = function(){
 connCount ++;
 console.log('............................OPEN a connection, has '+ connCount + ' connection.');
 return mysql.createConnection(config.mysql);
};
exports.endConn = function(conn){
 conn.end(function(err){
  if(!err){
   connCount --;
   console.log('.........................CLOSE a connection, has '+ connCount + ' connection.');
  }
 });
};

Then give an example of using a database,

// db.js  Query user information 
var connect = require('./connect.js'); //  Introducing data connection method 
exports.getUser = function(username, callback){
  var connection = connect.getConn();
  var sql = 'select * from user where username = "' + username + '"';
  connection.query(sql,function(err,rows,fields){
    callback(err,rows,fields);  
  });
  connect.endConn(connection);
}

5. Example 2

Using database connection pooling, the same method of creating database connection pooling first is as follows:

// connect.js  Direct use 
var mysql = require('mysql');
var config = require('./config.json');
var pool = mysql.createPool(config.mysql);

exports.querySQL = function(sql,callback){
  pool.query(sql, function(err,rows,fields){
    callback(err,rows,fields);
  });
}

// connect.js  Use getConnection Method 
var mysql = require('mysql');
var config = require('./config.json');
var pool = mysql.createPool(config.mysql);

exports.querySQL = function(sql, callback){
  pool.getConnection(function(err,conn){
    conn.query(sql,function(err,rows,fields){
      callback(err,rows,fields); 
      conn.release();  //  Don't forget to release 
    });    
  });
}

When using, use querySQL method directly, as follows:

var mysql = require('mysql');
var connection = mysql.createConnection({
 host: 'localhost',
 user: 'me',
 password : 'secret',
 database : 'my_db'
});
connection.connect();
connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
 if (err) throw err;
 console.log('The solution is: ', rows[0].solution);
});
connection.end();

0

Officially, it is recommended to use connection pool to connect. However, whether to connect directly with pool. query () or pool. getConnection (), the official did not introduce its advantages and disadvantages. I simply did a test. It seems that there is not much difference between these two methods, so I didn't study it again. Please tell me if you know, thank you ~