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?

Answer 1

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();

Answer 2

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

• 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) => {
    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/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) => {
    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}`);
}

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

You can use this format as I used

    const mysql = require('mysql');
    const { HOST, USERNAME, PASSWORD, DBNAME, PORT } = process.env;
    console.log();
    const conn = mysql.createPool({
        host: HOST,
        user: USERNAME,
        password: PASSWORD,
        database: DBNAME
    }, { debug: true });
    
    conn.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
        if (error) throw error;
        console.log('Db is connected - The solution is: ', results[0].solution);
    });
    
    
    module.exports = conn;

Answer 7

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

Answer 8

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