Mysql – node.js + thesql connection pooling

MySQLnode.js

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?

Best 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();
Related Topic