node. js How to operate an MySQL database

The MySQL database is the most popular open source database. Basically, it is one of the database programs that every web developer must master.

Basic use

On node. js, the most popular mysql package is the mysql module.

npm install mysql

Then refer to it directly in js script

var mysql   = require('mysql');

Configure the database connection for mysql.

var connection = mysql.createConnection({
 host   : 'ip',
 user   : ' User name ',
 password : ' Password ',
 database : 'dbname'
});
connection.connect();

In this way, you get a connection.

Then you can happily perform various curd operations.

node. js curd for the database are all in the query method. This is quite different from ado. net.

All your operations get results from the callback function of query

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
 if (error) throw error;
 console.log('The solution is: ', results[0].solution);
});

Connection pool operation

In stand-alone software, we use simple to get a connection between us, and then we will do it.

However, in the Internet-oriented web service, frequent creation and closure of connections consumes server performance.

So our predecessors invented all kinds of pools. For example, thread pool in multithreaded operation, object pool in game development, and connection pool in database operation.

Create a connection pool:

var mysql = require('mysql');
var pool = mysql.createPool({
 connectionLimit :  Number of connection pools ,
 host      : 'ip Address ',
 user      : ' Account number ',
 password    : ' Password ',
 database    : ' Database name '
});

Then there is the curd operation like the above 1

// Get from the connection pool 1 Connections 
pool.getConnection(function(err, connection) {
  if (err) throw err; // not connected!
 
  //  Use this connection curd
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    //  After using it, remember to put this connection in the connection pool 
    connection.release();
 
    // Handle error after the release.
    if (error) throw error;
 
  });
});

If your program wants to exit, please call the end () method of the connection pool. Otherwise, the program will be stuck in the background, and 1 straight exit fails.

Packaged as Promise

In ES6, asynchronous functions of js can be called directly using the syntax similar to await1 of C #.

But this function must be an async declaration and the return value must be an Promise object.

query = function (sql, arr, callback) {
  console.log(' Get 1 Connections ');
  return new Promise(function (resolve, reject) {
    pool.getConnection(function (err, connection) {
      if (err) {
        reject(err);// not connected!
      } else {
        console.log(' Start a query ');
        connection.query(sql, arr, function (error, results, fields) {
          connection.release();
          console.log(' The connection has been released, and the result is returned ');

          if (error) reject(error);
          // callback && callback(results, fields)
          resolve({
            rows: results,
            fields: fields
          })
        });
      }


    });
  })


}

That's the end of the simple usage. Of course, there are more advanced uses, such as Cluster operation of MySQL. If you are interested, you can study it, because I don't need this function at present, so I won't delve into it later.

The above is node. js how to operate MySQL database details, more about node. js operating database information please pay attention to other related articles on this site!