Retrieving data from MySQL using Nodejs

Question

I'm new to javascript and nodeJS.

I'm having some trouble trying to run a SQL query to retrieve data using Nodejs.

I created a connection module (db.js) but unable to get the results from executing controller.js.

I know for sure that the connection module is working well as I manage to retrieve data from DB if I were to swap the resolve(connection) to queries.

Please help me take a look where did I go wrong.

Here is the db.js

var mysql = require('mysql2');
var Client = require('ssh2').Client;
var ssh = new Client();

var db = new Promise(function (resolve, reject) {
    ssh.on('ready', function () {
        ssh.forwardOut(
            '127.0.0.1',
            12345,
            '127.0.0.1',
            3306,
            function (err, stream) {
                if (err) throw err;
                let connection = mysql.createConnection({
                    host: '127.0.0.1',
                    user: 'username',
                    password: 'password',
                    database: 'test',
                    stream: stream
                });

                connection.connect(function (err) {
                    if (!err) {
                        resolve(connection)
                    } else {
                        reject(err);
                    }
                });
            });
    }).connect({
        host: 'hostname',
        port: 22,
        username: 'username',
        password: 'password'
    });
});

module.exports = db;

Here is the controller.js

var database = require('./db');

module.exports.getcats = (function(req, res) {
    database().then(function (connection) {
        connection.query("SELECT * FROM listUnit", function (error, results, fields) {
            if (error) {
                console.log(error);
                return;
            }
            res.write(results);
        });
    });
});

What are the logs tellin? What is the log output for results? — Simon Franzen
– Simon Franzen, Commented Mar 10, 2018 at 16:59
Hi Simon. Nothing came up after I ran the command (node controller.js). However, if I were to put the query into db.js and run it, the result of the query will appear on the console. — user65816
– user65816, Commented Mar 10, 2018 at 18:03

Matt Morgan · Accepted Answer · 2018-03-11 13:22:23Z

If your code is as you describe it above, nothing visible will happen when you run

node controller.js

This is because your controller module defines an exported function that opens a database connection and makes a query, but it never invokes that function.

Probably in a real application you would use the exported getcats() function in some other module, like you've used the db() function. But, if you wanted to, you could modify your controller so that the code defines, exports, and invokes the function, giving you the result of your query when you run it from the command line:

var database = require('./db');

// define the function
function getcats(req, res) {
    database().then(function (connection) {
        connection.query("SELECT * FROM listUnit", function (error, results, fields) {
            if (error) {
                console.log(error);
                return;
            }
            res.write(results);
        });
    });
}

// export the function for use in other places
module.exports = getcats;

// invoke so it runs the query when you call "node controller.js"
getcats();

Collectives™ on Stack Overflow

Retrieving data from MySQL using Nodejs

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related