0

I have a server on sails nodejs and I am trying to connect my controllers with my MySQL db through a wrapper file that would create the connection pool. My purpose is that I use that pool everytime a function in any controller needs to interact with DB, and in such a way that connection is created at the time interaction starts and connection is closed at the time interaction is over. For this, I have created a wrapper file db.js

db.js

var mysql = require('mysql');

var connection = mysql.createConnection({
  host:"localhost",
  port: '3306',
  user:"ye_old_username",
  password:"ye_old_password",
  database: "ye_old_schema"
});   

module.exports = connection; 

Now, I am creating a connection pool called ConnectionPool.js

ConnectionPool.js

var mysql   = require('mysql'),
    config  = require("./db");

/*
 * @sqlConnection
 * Creates the connection, makes the query and close it to avoid concurrency conflicts.
 */
var sqlConnection = function sqlConnection(sql, values, next) {

    // It means that the values hasnt been passed
    if (arguments.length === 2) {
        next = values;
        values = null;
    }
    var connection = mysql.createConnection(config);
    connection.connect(function(err) {
        if (err !== null) {
            console.log("[MYSQL] Error connecting to mysql:" + err+'\n');
        }
    });
    connection.query(sql, values, function(err) {
        connection.end(); 
        if (err) {
            throw err;
        }
        next.apply(this, arguments);
    });
}
module.exports = sqlConnection;

I have followed the method answered on this question to create the connection pool: How to provide a mysql database connection in single file in nodejs

And finally, I am trying to run a function from a controller using the wrapper and the connection pool. The code inside the Controller is

var connPool = require('./ConnectionPool');

module.exports = {
    testConn: function(req, res){
        connPool('SELECT * from user where ?', {id: '1'}, function(err, rows) {
            if(err){
                sails.log.debug(err);
            }else{
                console.log(rows);
            }        
        });         
    }
};

All the three files, the wrapper, the connection pool, and the controller are in the same Controllers folder.

Now, when I send a request to the URL through my client, that would invoke the testConn function inside the controller, I get the following response on server log:

[MYSQL] Error connecting to mysql:Error: ER_ACCESS_DENIED_ERROR: Access denied for user ''@'localhost' (using password: NO)

This error is coming from the line connection.connect(function(err) { in connection pool file. When I try to log on my MySQL db through the same credentials on command line, I am through it. Therefore I believe that db.js file has some format related issue because of which a proper connection is not getting initiated. There can be other reason as well, but the reason I suspect seems to be very strong.

I need some guidance on solving this issue. Any help will be appreciated.

Community
  • 1
  • 1
harshvardhan
  • 765
  • 13
  • 32
  • Try this connection pool: https://github.com/JQluv/NodeJs-Libraries/blob/master/MySQL%20Wrapper/MySQLClient.js Its tested and works. – JQluv Aug 25 '16 at 16:58
  • @JQluv can you please give me an example usage of this file? suppose I have saved this file as ConnectionPool.js then how can I use this in a controller to interact with DB? – harshvardhan Aug 25 '16 at 21:33
  • This is how I use it, http://pastebin.com/3z6pS53m – JQluv Aug 26 '16 at 03:17
  • @JQluv, I am getting the following response on my console: **TypeError: mysqlHandler.RunQuery is not a function** – harshvardhan Aug 26 '16 at 06:40
  • This is example code its not just simply runnable, I provided studiable code and a use example that wont compile and run but is a example for you to use. – JQluv Aug 27 '16 at 07:34

0 Answers0