synchronous mysql queries in nodejs

Question

I'm pretty new to nodejs and I'm having some difficulties to understand how to use the mysql connection object.

My problem is not in the code but in the design pattern.

lets say I have a user module

module.exports = function(){
    return{
        id: "",
        load: function(id){
            var sql = 'SELECT * from users where id = '+ DB.escape(id);
            console.log(1);
            DB.query(sql, function (err, rows) {
                this.id = rows[0].id; // not working
                console.log(rows[0].id); // prints the id 4
                console.log(2);
            });
            console.log(3);
        }
    }
}

from outside the module i run the next code

var user = require('../modules/user');
var selected_user = user();
console.log("entering users me route");
selected_user.load(4);
console.log("user id is " + selected_user.id); //This does not print the id 4

when I run the code, the console logs 1, then 3, and then 2. This is due to the asynchronous flow of node js.

But if I'm building a website, and I need the query to end in order to populate my user object before I send the HTML to the browser???

What's the right way to do it ?

Also when I try to populate the id property of user in the id i receive from the DB it does not work.

Any ideas?

Thanks

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

3

There are several ways to do this. I would go with Promises.

Suppose you have an asynchronous function "getUsers". It looks like this:

function getUsers() {
    longQuery(function(err, result){
        // What to do with result?
    });

You need to rewrite it to be able to use the result. Let's try:

function getUsers() {
    return new Promise(function(resolve, reject) {
        longQuery(function(err, result){
        if(err) reject(err)
        else resolve(result)
        });
    });

Now this function returns a promise. What do we do with that promise?

function handleRequest(req, res) {
    getUsers().then(function(result) {
        // Do stuff with result
        res.send(myProcessedData);
    }).catch(function(err) {console.log(err)};
}

This could also have been done with callbacks, passing the response object as a parameter to the query function, and many other ways, but I think promises are a very elegant way for handling this.

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 15 '16 at 00:15

matanso

1,284
1
10
17

But if i have a webpage that requires a lot of queries from the data base, I would like it to work the same as lets say php. run query, get results, do something , move on – zion ben yacov Mar 16 '16 at 21:05
This is currently impossible, due to node.js non-blocking nature. However, chaining promises creates a very similar flow. – matanso Mar 16 '16 at 21:23
1

This is exactly what I did but it seems very weird, because if i have 20 calls to the DB, i need to change 20 "Promises" one inside another, it seems like a very poor way to build a website – zion ben yacov Mar 16 '16 at 21:30

score 0 · Answer 2 · edited May 23 '17 at 12:16

this.id = rows[0].id; // not working

The above line is not working because you are setting it to this.id from inside a callback function. When you are inside a callback function this does not mean the this in the main object.

For more discussion about this: see How to access the correct `this` context inside a callback?

To tackle the asynchronous nature of javascript you can either use promise like the answer from matanso or you can pass a callback function to your load method. So your load: function(id) method will be load: function(id, callbackFunction) and call the callback function when you get all the data that you need.

synchronous mysql queries in nodejs

2 Answers2