node.js + mysql 连接池

node.js + mysql connection pooling

提问by kasztelan

I'm trying to figure out how to structure my application to use MySQL most efficent way. I'm using node-mysql module. Other threads here suggested to use connection pooling so i set up a little module mysql.js

我试图弄清楚如何构建我的应用程序以最有效地使用 MySQL。我正在使用 node-mysql 模块。这里的其他线程建议使用连接池，所以我设置了一个小模块 mysql.js

var mysql = require('mysql');

var pool  = mysql.createPool({
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'guess'
});

exports.pool = pool;

Now whenever I want to query mysql I require this module and then query the databse

现在每当我想查询 mysql 我需要这个模块然后查询数据库

var mysql = require('../db/mysql').pool;

var test = function(req, res) {
     mysql.getConnection(function(err, conn){
         conn.query("select * from users", function(err, rows) {
              res.json(rows);
         })
     })
}

Is this good approach? I couldn't really find too much examples of using mysql connections besides very simple one where everything is done in main app.js script so I don't really know what the convention / best practices are.

这是好方法吗？我真的找不到太多使用 mysql 连接的例子，除了非常简单的一个，其中一切都在主 app.js 脚本中完成，所以我真的不知道什么是约定/最佳实践。

Should I always use connection.end() after each query? What if I forget about it somewhere?

我应该在每次查询后始终使用 connection.end() 吗？如果我在某处忘记了它怎么办？

How to rewrite the exports part of my mysql module to return just a connection so I don't have to write getConnection() every time?

如何重写我的 mysql 模块的导出部分以仅返回一个连接，这样我就不必每次都编写 getConnection()？

采纳答案by Klaasvaak

It's a good approach.

这是一个很好的方法。

If you just want to get a connection add the following code to your module where the pool is in:

如果您只想获得连接，请将以下代码添加到池所在的模块中：

var getConnection = function(callback) {
    pool.getConnection(function(err, connection) {
        callback(err, connection);
    });
};

module.exports = getConnection;

You still have to write getConnection every time. But you could save the connection in the module the first time you get it.

您仍然必须每次都编写 getConnection 。但是您可以在第一次获得连接时将其保存在模块中。

Don't forget to end the connection when you are done using it:

使用完毕后不要忘记结束连接：

connection.release();

回答by binki

You should avoid using pool.getConnection()if you can. If you call pool.getConnection(), you mustcall connection.release()when you are done using the connection. Otherwise, you application will get stuck waiting forever for connections to be returned to the pool once you hit the connection limit.

pool.getConnection()如果可以，您应该避免使用。如果调用pool.getConnection()，则必须connection.release()在使用完连接后调用。否则，一旦达到连接限制，您的应用程序将永远等待连接返回到池中。

For simple queries, you can use pool.query(). This shorthand will automatically call connection.release()for you—even in error conditions.

对于简单的查询，您可以使用pool.query(). connection.release()即使在错误情况下，此速记也会自动调用。

function doSomething(cb) {
  pool.query('SELECT 2*2 "value"', (ex, rows) => {
    if (ex) {
      cb(ex);
    } else {
      cb(null, rows[0].value);
    }
  });
}

However, in some cases you must use pool.getConnection(). These cases include:

但是，在某些情况下，您必须使用pool.getConnection(). 这些案例包括：

1. Making multiple queries within a transaction.
2. Sharing data objects such as temporary tables between subsequent queries.

1. 在事务中进行多个查询。
2. 在后续查询之间共享数据对象，例如临时表。

If you must use pool.getConnection(), ensure you call connection.release()using a pattern similar to below:

如果必须使用pool.getConnection()，请确保connection.release()使用类似于以下的模式进行调用：

function doSomething(cb) {
  pool.getConnection((ex, connection) => {
    if (ex) {
      cb(ex);
    } else {
      // Ensure that any call to cb releases the connection
      // by wrapping it.
      cb = (cb => {
        return function () {
          connection.release();
          cb.apply(this, arguments);
        };
      })(cb);
      connection.beginTransaction(ex => {
        if (ex) {
          cb(ex);
        } else {
          connection.query('INSERT INTO table1 ("value") VALUES (\'my value\');', ex => {
            if (ex) {
              cb(ex);
            } else {
              connection.query('INSERT INTO table2 ("value") VALUES (\'my other value\')', ex => {
                if (ex) {
                  cb(ex);
                } else {
                  connection.commit(ex => {
                    cb(ex);
                  });
                }
              });
            }
          });
        }
      });
    }
  });
}

I personally prefer to use Promises and the useAsync()pattern. This pattern combined with async/awaitmakes it a lot harder to accidentally forget to release()the connection because it turns your lexical scoping into an automatic call to .release():

我个人更喜欢使用Promises 和useAsync()模式。这种模式与async/相结合await使得意外忘记release()连接变得更加困难，因为它会将您的词法范围变成对 的自动调用.release()：

async function usePooledConnectionAsync(actionAsync) {
  const connection = await new Promise((resolve, reject) => {
    pool.getConnection((ex, connection) => {
      if (ex) {
        reject(ex);
      } else {
        resolve(connection);
      }
    });
  });
  try {
    return await actionAsync(connection);
  } finally {
    connection.release();
  }
}

async function doSomethingElse() {
  // Usage example:
  const result = await usePooledConnectionAsync(async connection => {
    const rows = await new Promise((resolve, reject) => {
      connection.query('SELECT 2*4 "value"', (ex, rows) => {
        if (ex) {
          reject(ex);
        } else {
          resolve(rows);
        }
      });
    });
    return rows[0].value;
  });
  console.log(`result=${result}`);
}

回答by Felipe Jimenez

You will find this wrapper usefull :)

你会发现这个包装器很有用:)

var pool = mysql.createPool(config.db);

exports.connection = {
    query: function () {
        var queryArgs = Array.prototype.slice.call(arguments),
            events = [],
            eventNameIndex = {};

        pool.getConnection(function (err, conn) {
            if (err) {
                if (eventNameIndex.error) {
                    eventNameIndex.error();
                }
            }
            if (conn) { 
                var q = conn.query.apply(conn, queryArgs);
                q.on('end', function () {
                    conn.release();
                });

                events.forEach(function (args) {
                    q.on.apply(q, args);
                });
            }
        });

        return {
            on: function (eventName, callback) {
                events.push(Array.prototype.slice.call(arguments));
                eventNameIndex[eventName] = callback;
                return this;
            }
        };
    }
};

Require it, use it like this:

需要它，像这样使用它：

db.connection.query("SELECT * FROM `table` WHERE `id` = ? ", row_id)
          .on('result', function (row) {
            setData(row);
          })
          .on('error', function (err) {
            callback({error: true, err: err});
          });

回答by Sagi Tsofan

I am using this base class connection with mysql:

我在 mysql 中使用这个基类连接：

"base.js"

“base.js”

var mysql   = require("mysql");

var pool = mysql.createPool({
    connectionLimit : 10,
    host: Config.appSettings().database.host,
    user: Config.appSettings().database.username,
    password: Config.appSettings().database.password,
    database: Config.appSettings().database.database
});


var DB = (function () {

    function _query(query, params, callback) {
        pool.getConnection(function (err, connection) {
            if (err) {
                connection.release();
                callback(null, err);
                throw err;
            }

            connection.query(query, params, function (err, rows) {
                connection.release();
                if (!err) {
                    callback(rows);
                }
                else {
                    callback(null, err);
                }

            });

            connection.on('error', function (err) {
                connection.release();
                callback(null, err);
                throw err;
            });
        });
    };

    return {
        query: _query
    };
})();

module.exports = DB;

Just use it like that:

就这样使用它：

var DB = require('../dal/base.js');

DB.query("select * from tasks", null, function (data, error) {
   callback(data, error);
});

回答by Mukesh Chapagain

When you are done with a connection, just call connection.release()and the connection will return to the pool, ready to be used again by someone else.

完成连接后，只需调用connection.release()，连接就会返回到池中，准备再次被其他人使用。

var mysql = require('mysql');
var pool  = mysql.createPool(...);

pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // And done with the connection.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});

If you would like to close the connection and remove it from the pool, use connection.destroy()instead. The pool will create a new connection the next time one is needed.

如果您想关闭连接并将其从池中删除，请connection.destroy()改用。下次需要时，池将创建一个新连接。

Source: https://github.com/mysqljs/mysql

来源：https: //github.com/mysqljs/mysql

回答by Yordan

Using the standard mysql.createPool(), connections are lazily created by the pool. If you configure the pool to allow up to 100 connections, but only ever use 5 simultaneously, only 5 connections will be made. However if you configure it for 500 connections and use all 500 they will remain open for the durations of the process, even if they are idle!

使用标准的 mysql.createPool()，连接是由池延迟创建的。如果您将池配置为允许最多 100 个连接，但只同时使用 5 个，则只会建立 5 个连接。但是，如果您将其配置为 500 个连接并使用所有 500 个连接，它们将在整个过程中保持打开状态，即使它们处于空闲状态！

This means if your MySQL Server max_connections is 510 your system will only have 10 mySQL connections available until your MySQL Server closes them (depends on what you have set your wait_timeout to) or your application closes! The only way to free them up is to manually close the connections via the pool instance or close the pool.

这意味着如果您的 MySQL 服务器 max_connections 为 510，您的系统将只有 10 个 mySQL 连接可用，直到您的 MySQL 服务器关闭它们（取决于您将 wait_timeout 设置为什么）或您的应用程序关闭！释放它们的唯一方法是通过池实例手动关闭连接或关闭池。

mysql-connection-pool-manager module was created to fix this issue and automatically scale the number of connections dependant on the load. Inactive connections are closed and idle connection pools are eventually closed if there has not been any activity.

创建 mysql-connection-pool-manager 模块是为了解决这个问题，并根据负载自动扩展连接数。如果没有任何活动，不活动的连接将关闭，空闲的连接池最终会关闭。

    // Load modules
const PoolManager = require('mysql-connection-pool-manager');

// Options
const options = {
  ...example settings
}

// Initialising the instance
const mySQL = PoolManager(options);

// Accessing mySQL directly
var connection = mySQL.raw.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

// Initialising connection
connection.connect();

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

// Ending connection
connection.end();

Ref: https://www.npmjs.com/package/mysql-connection-pool-manager

参考：https: //www.npmjs.com/package/mysql-connection-pool-manager

回答by Alex

i always use connection.relase(); after pool.getconnetion like

我总是使用 connection.relase(); 在 pool.getconnetion 之后

pool.getConnection(function (err, connection) {
      connection.release();
        if (!err)
        {
            console.log('*** Mysql Connection established with ', config.database, ' and connected as id ' + connection.threadId);
            //CHECKING USERNAME EXISTENCE
            email = receivedValues.email
            connection.query('SELECT * FROM users WHERE email = ?', [email],
                function (err, rows) {
                    if (!err)
                    {
                        if (rows.length == 1)
                        {
                            if (bcrypt.compareSync(req.body.password, rows[0].password))
                            {
                                var alldata = rows;
                                var userid = rows[0].id;
                                var tokendata = (receivedValues, userid);
                                var token = jwt.sign(receivedValues, config.secret, {
                                    expiresIn: 1440 * 60 * 30 // expires in 1440 minutes
                                });
                                console.log("*** Authorised User");
                                res.json({
                                    "code": 200,
                                    "status": "Success",
                                    "token": token,
                                    "userData": alldata,
                                    "message": "Authorised User!"
                                });
                                logger.info('url=', URL.url, 'Responce=', 'User Signin, username', req.body.email, 'User Id=', rows[0].id);
                                return;
                            }
                            else
                            {
                                console.log("*** Redirecting: Unauthorised User");
                                res.json({"code": 200, "status": "Fail", "message": "Unauthorised User!"});
                                logger.error('*** Redirecting: Unauthorised User');
                                return;
                            }
                        }
                        else
                        {
                            console.error("*** Redirecting: No User found with provided name");
                            res.json({
                                "code": 200,
                                "status": "Error",
                                "message": "No User found with provided name"
                            });
                            logger.error('url=', URL.url, 'No User found with provided name');
                            return;
                        }
                    }
                    else
                    {
                        console.log("*** Redirecting: Error for selecting user");
                        res.json({"code": 200, "status": "Error", "message": "Error for selecting user"});
                        logger.error('url=', URL.url, 'Error for selecting user', req.body.email);
                        return;
                    }
                });
            connection.on('error', function (err) {
                console.log('*** Redirecting: Error Creating User...');
                res.json({"code": 200, "status": "Error", "message": "Error Checking Username Duplicate"});
                return;
            });
        }
        else
        {
            Errors.Connection_Error(res);
        }
    });