Return rows with nodejs and node-mysql

Question

I'm discovering Nodejs and the node-mysql module. I have a small problem. Every tutorial that I find explain how to do a select on the database but they never return the rows, they always log them, which is absolutely useless for my case.

I have a app.js file :

// Get continents
app.get("/continents", function(request, result) {
    console.log("Continents : " + database.findAllContinents());
});

And a mysql.js file :

exports.findAllContinents = function(connection) {
    var connection = getConnection();
    connection.query('select id, code, name from Continent', function (err, rows, fields) {
        if (err) {
            console.log("Error in findAllContinents : " + err)
        }
        return JSON.stringify(rows);
    });
    closeConnection(connection);
};

How can I make the function return the rows to use them in the app.js file ? I don't really want to create connections in the app.js file I want the DAO layer to be separated. Do you have any idea ?

Also, if someone has an idea of the pros/cons of using node-mysql instead of an ORM (sequelize, persistence.js...)

Thanks

Answer 1

query() is an asynchronous function from which you can't return any results. And consequently, any functions which call asynchronous functions themselves (like your findAllContinents) can't either.

Instead, you need to pass a callback function (also explained here) which will be called when the query is done:

// app.js
app.get("/continents", function(request, response) {
  database.findAllContinents(function(err, results) {
    if (err)
      throw err; // or return an error message, or something
    else
      res.send(results); // as a demo, we'll send back the results to the client;
                         // if you pass an object to 'res.send()', it will send
                         // a JSON-response.
  });
});

// mysql.js
exports.findAllContinents = function(cb) {
  var connection = getConnection();
  connection.query('select id, code, name from Continent', function (err, rows, fields) {
    // close connection first
    closeConnection(connection);
    // done: call callback with results
    cb(err, rows);
  });
};

As for (not) using an ORM, that really depends on the use case. I would choose an ORM (my favorite for MySQL is patio) in case my app requires multiple (complex) models, perhaps with associations between them. Also, the abstraction an ORM provides makes code more easy to read and usually allows to more easily port an app to a different database.