NodeJS won't connect to local DB with node-mysql

Question

I'm having an issue with NodeJS and node-mysql. For some reason, in my local environment, I'm able to connect to the database and access the endpoints to update and insert information. I'm using mac os x Sierra for development. I'm using Ubuntu 16.04 on my Dev server and i'm just running node on port 80.

My connection.js file looks like this.

var mysql = require('mysql');

function Connection() {
  this.pool = null;

  this.init = function() {
    this.pool = mysql.createPool({
      connectionLimit: 100,
      host: 'localhost',
      user: 'Miguel',
      password: 'thepassword',
      database: 'lavishwebLogin',
     port:'3306'
    });
  };

  this.acquire = function(callback) {
    this.pool.getConnection(function(err, connection) {
      callback(err, connection);
    });
  };
}

module.exports = new Connection();

My endpoint looks like this...

this.login = function(req, res){
        connection.acquire(function(err, con) {
            con.query('SELECT * FROM users WHERE Email = "' + req.Email + '";', function(err, result) {
                if(!err){
                    if(bcrypt.compareSync(req.Password, result[0].Pass) == true){
                        result[0].Pass = "";
                        res.send(result[0])
                        con.release();
                    } 
                } else {
                    res.send(err)
                    con.release()
                }
            });
        });
    } 

the error I'm getting an error response in the console that reads like this

I'm getting an error in the terminal as well that reads like this.

build.js:1275 POST http://lavishweb.com/api/login net::ERR_CONNECTION_REFUSED

Cannot read property 'query' of undefined at /root/syn-tacticsolutions/api/users/users.js:38:13 at /root/syn-tacticsolutions/connection.js:18:7

And before you say it has to do with permissions... I called this from within the v-server

mysql -u Miguel -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.7.16-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Any help would be much appreciated. I've looked everywhere to find out why it might not be working. I'm truly stumped at this point.

Answer 1

Ok guys I found the problem. So for some reason localhost is not correct when connecting to a database server that is on the same system. What I ended up having to do was create a user for the static external IP address(not 127.0.0.1) of my remote server and then access it using that user.

mysql -u root -p
password: thepassword

>create user 'user'@'ip' identified by 'thepassword';
>grant all on *.* to 'user'@'ip';
>flush privileges;
>exit;

 this.init = function() {
   this.pool = mysql.createPool({
     host: 'ip',
     user: 'user',
     password: 'thepassword',
     database: 'table'
   });
 };

And everything works. Yay!!