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.

5
  • Try this connection pool: github.com/JQluv/NodeJs-Libraries/blob/master/MySQL%20Wrapper/… Its tested and works. Commented Aug 25, 2016 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? Commented Aug 25, 2016 at 21:33
  • This is how I use it, pastebin.com/3z6pS53m Commented Aug 26, 2016 at 3:17
  • @JQluv, I am getting the following response on my console: TypeError: mysqlHandler.RunQuery is not a function Commented Aug 26, 2016 at 6: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. Commented Aug 27, 2016 at 7:34

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.