Node.js express and mySQL data

Question

I just started learning JavaScript and Node.JS and I'm really struggling with the concepts of callbacks. So I went on and tried to implement them to get mySQL data pushed to a site.

I began with this:

// connection stuff happened already. Connection works fine.

app.get('/home', function(req,res){

var sql = `select u.* from users u`;

var sql2 = `select u.* from users2 u`;

usersA = [];
usersB = [];
connection.query(sql, function(error, results, fields){
    if (error) throw error;
    results.forEach(function(user){
        usersA.push({
            "id":user.id,
            "name":user.name,
            "lastname":user.lastname
        });
    });
    connection.query(sql2, function(error, results, fields){
      if (error) throw error;
      results.forEach(function(user){
        usersB.push({
            "id":user.id,
            "name":user.name,
            "lastname":user.lastname
        });
      });
      res.render("index", {
        usersA: usersA,
        usersB: usersB
      });
    });
  });
});

This works. But I feel like this is the wrong approach considering there could be way more than 2 queries.

I need to get both arrays filled before the index is rendered. And I'd like to achieve that a little more straightforward without nesting multiple queries within each other.

Maybe I'm just not used to code looking like that. If this is a valid approach even for 10 or more querys I'll just stick with it. It just feels wrong.

So I started looking into this SO thread and tried to somehow get things to work but it didn't:

function executeQuery(query, callback) {
  connection.query(query, function (err, rows, fields) {
    if (err) {
      return callback(err, null);
    } else {
      return callback(null, rows);
    }
  })
}

function getResult(query,callback) {
  executeQuery(query, function (err, rows) {
    if (!err) {
      callback(null,rows);
    } else {
      callback(true,err);
    }
  });
}

function getUsers(sqlQry){
  getResult(sqlQry,function(err,rows){
    if(!err){
      return rows;
    } else {
      console.log(err);
    }
  });
}

With this prepared I tried the following:

var sql = `select u.* from users u`;
var sql2 = `select u.* from users2 u`;

app.get('/home', function(req,res){
  res.render("index", {
    usersA: getUsers(sql),
    usersB: getUsers(sql2)
  });
}

But my usersA/usersB are empty. I guess that's some kind of scoping/asynch problem as getUsers() returns before the query is executed.

From what I've read so far this might be a good place for promises.

I added an answer with my solution.

Have you tried to make a second query inside the first's callback? It doesn't look like you've even made an attempt. — jhpratt, Mar 01 '18 at 16:06
Take look into node.js [promises](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise). You can chain callbacks together using promises and then once all callbacks have finished, you can execute whatever you want. — Nicholas Siegmundt, Mar 01 '18 at 16:26
I'll add my attempt tomorrow. I definitely tried different approaches. — Akaino, Mar 01 '18 at 19:51
Please add the solution as an *answer* and remove it from your question. You'll be able to accept it a little bit after, so your question won't remain unanswered. — Veve, Mar 02 '18 at 08:41
Aye! Will do. Just wanted to see whether there are going to be more and maybe better answers. — Akaino, Mar 02 '18 at 09:07

score 0 · Answer 1 · answered Mar 02 '18 at 09:16

I found an answer to my question

I found this article on codeburst about Node.js and mySQL using promises and went trying it. So all the credit to them.

app.get('/home', function(req,res){
  var db = new Database({
    host     : "someHost.someDomain",
    user     : "someUser",
    password : "somePassword",
    database : "someDatabase"
  });

var sql = `select u.* from users u`;
var sql2 = `select u.* from users2 u`;

usersA = [];
usersB = [];

db.query(sql)
    .then(rows => {
                rows.forEach(function(user){
                    usersA.push({
                        "id":user.id,
                        "name":user.name,
                        "lastname":user.lastname
                    });
                });
            return db.query(sql2)
        })
    .then(rows => {
                rows.forEach(function(user){
                    usersB.push({
                        "id":user.id,
                        "name":user.name,
                        "lastname":user.lastname
                    });
                });
                return db.close();
        }, err => {
    return db.close().then( () => { throw err; } )
    })
    .then( () => {
        res.render("index", {
            usersA: usersA,
            usersB: usersB
        });
    }).catch( err => {
        console.log(err);   
} )

I don't really get how this works yet but I'll read into it. This allows for handling multiple nested queries with promises which are way easier to handle than simply nesting everything.

If anyone has another way of doing this or feels the need explaining what's happening here I'd very much appreaciate it!

For the sake of completion without visiting the URLs here is the mentioned database class:

class Database {
  constructor( config ) {
    this.connection = mysql.createConnection( config );
  }
  query( sql, args ) {
    return new Promise( ( resolve, reject ) => {
        this.connection.query( sql, args, ( err, rows ) => {
            if ( err )
                return reject( err );
            resolve( rows );
        } );
    } );
  }
  close() {
    return new Promise( ( resolve, reject ) => {
        this.connection.end( err => {
            if ( err )
                return reject( err );
            resolve();
        } );
    } );
  }
}

Node.js express and mySQL data

1 Answers1