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

Community · Accepted Answer · 2017-05-23 12:34:08Z

16

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.

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Apr 28, 2013 at 15:12

robertklep

205k38 gold badges421 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

c4k Over a year ago

Thanks that did the job :). I understand better the concept of asynchronous functions. Regarding, the ORM, I come from PHP and Java, with those languages I would have naturally used an ORM but things seems "special" with Node.js. I tried Sequelize and there are a lot of missing features (like constraints on foreign keys for example)

bguiz · Accepted Answer · 2021-06-11 08:32:59Z

-2

This code is to check if you have any single row in DB or not

if(result.length == 1) {
    var row = result[0];
    console.log(row);
}

edited Jun 11, 2021 at 8:32

bguiz

28.5k49 gold badges165 silver badges256 bronze badges

answered Jun 10, 2021 at 10:55

Haseeb Tariq

912 silver badges13 bronze badges

Collectives™ on Stack Overflow

Return rows with nodejs and node-mysql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related