node js mysql connection pooling

Question

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  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  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   Should I always use connection end   after each query  What if I forget about it somewhere   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

User · Answer

I am using this base class connection with mysql:

"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);
});

User · Answer

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   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   Source  https   github com mysqljs mysql

User · Answer

You should avoid using pool getConnection   if you can  If you call pool getConnection    you must call connection release   when you are done using the connection  Otherwise  your application will get stuck waiting forever for connections to be returned to the pool once you hit the connection limit  For simple queries  you can use pool query    This shorthand will automatically call connection release   for you   even in error conditions  function doSomething cb      pool query  SELECT 2 2  quot value quot     ex  rows    gt        if  ex          cb ex         else         cb null  rows 0  value                  However  in some cases you must use pool getConnection    These cases include   Making multiple queries within a transaction  Sharing data objects such as temporary tables between subsequent queries   If you must use pool getConnection    ensure you call connection release   using a pattern similar to below  function doSomething cb      pool getConnection  ex  connection    gt        if  ex          cb ex         else            Ensure that any call to cb releases the connection          by wrapping it        cb    cb   gt            return function                connection release              cb apply this  arguments                       cb         connection beginTransaction ex   gt            if  ex              cb ex             else             connection query  INSERT INTO table1   quot value quot   VALUES    my value       ex   gt                if  ex                  cb ex                 else                 connection query  INSERT INTO table2   quot value quot   VALUES    my other value      ex   gt                    if  ex                      cb ex                     else                     connection commit ex   gt                        cb ex                                                                                                                            I personally prefer to use Promises and the useAsync   pattern  This pattern combined with async await makes it a lot harder to accidentally forget to release   the connection because it turns your lexical scoping into an automatic call to  release    async function usePooledConnectionAsync actionAsync      const connection   await new Promise  resolve  reject    gt        pool getConnection  ex  connection    gt          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   gt        const rows   await new Promise  resolve  reject    gt          connection query  SELECT 2 4  quot value quot     ex  rows    gt            if  ex              reject ex             else             resolve rows                                   return rows 0  value          console log  result   result

User · Answer

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

User · Answer

i always use connection relase    after pool getconnetion like  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

User · Answer

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   Don t forget to end the connection when you are done using it   connection release

User · Answer

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   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-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          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

[mysql] node.js + mysql connection pooling

Examples related to mysql

Examples related to node.js