how to structure json in rest api call and make it synchronous

Question

I am trying to structure a json response with nodejs restapi but my response is always sent earlier even though the function has not been executed.

i tried await with promise nothing works and send error..

//this is my desired structure for json

[
    {
        "user_id": "2",
        "user_name": "Mehedi Hasan",
        "permissions": [
            {
                "table_name": "questionbank",
                "permission_create": "true",
                "permission_update": "true",
                "permission_delete": "true"
            },
            {
                "table_name": "questionbankquestions",
                "permission_create": "true",
                "permission_update": "true",
                "permission_delete": "true"
            },
            {
                "table_name": "subjects",
                "permission_create": "false",
                "permission_update": "true",
                "permission_delete": "false"
            },
            {
                "table_name": "subjectquestions",
                "permission_create": "true",
                "permission_update": "true",
                "permission_delete": "true"
            },
            {
                "table_name": "modeltests",
                "permission_create": "true",
                "permission_update": "true",
                "permission_delete": "true"
            },
            {
                "table_name": "modeltestquestions",
                "permission_create": "true",
                "permission_update": "true",
                "permission_delete": "true"
            }
        ]
    },

............................

]

//this is what i tried

function getAllUser(done) {
  var sql = "SELECT users.id, users.username FROM users";
  db.query(sql, [], function(err, rows, fields) {
    if (err) throw err;
    done(rows);
  });
}

/*--------------------------Check user permission --------------------------*/
router.get("/adminpermissions", function(req, res) {
  var sql =
    "SELECT table_name, permission_create, permission_update, permission_delete FROM permissions " +
    "WHERE user_id = ?";

  finalArray = [];
  jsonFormat = [];
  permissionArray = [];

  getAllUser(function(user) {
    for (var i = 0; i < user.length; i++) {
      userId = user[i].id;
      userName = user[i].username;

      db.query(sql, [userId], function(err, rows, fields) {
        if (err) {
          res.status(500).send({ error: "Something failed!" });
        }
        console.log(rows);
        permissionArray[i] = rows;
        var data = {
          user_id: userId,
          user_name: userName,
          permission: permissionArray[i]
        };
        jsonFormat.push(data);
      });     
    }

    res.json(jsonFormat);

  });
});

i am always getting [] array as response.. though i know the problem that the response is being send even though the loop has not been finished but dont know how to solve.. tried await with promise but end up with so many error messages..

Does this answer your question? [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — Davin Tryon, Nov 11 '19 at 09:22
it is useful but yet in my case if i put async before function getAllUser() i am not able to use await inside router.get()... since before router.get... cannot put async there.. and await cannot be used without async.. — MEHEDI HASAN, Nov 11 '19 at 09:43
Each db.query call is async. You can’t magically convert it to synchronous. You must wait for all the promises (one for each db.query) and then send your response. If you can’t use async/await then you must do it with Promises.all and a .then continuation. — Joe, Nov 11 '19 at 15:17

score 1 · Answer 1 · answered Nov 12 '19 at 15:54

You can solve the issue by using async/await. You can do something like this:-

router.get("/adminpermissions", function(req, res) {
var sql =
"SELECT table_name, permission_create, permission_update, permission_delete FROM 
 permissions " +
"WHERE user_id = ?";

finalArray = [];
jsonFormat = [];
permissionArray = [];

getAllUser(async function(user) {
for (var i = 0; i < user.length; i++) {
  userId = user[i].id;
  userName = user[i].username;
  sql =  "SELECT table_name, permission_create, permission_update, permission_delete 
  FROM permissions WHERE user_id = "+  userId;

  try {
  let result = await db.query(sql)
  permissionArray[i] = result[0];
    var data = {
      user_id: userId,
      user_name: userName,
      permission: permissionArray[i]
    };
    jsonFormat.push(data);     
} catch(err) {
    res.status(500).send({ error: "Something failed!" });
}
}

res.json(jsonFormat);

}); });

though i already solved the problem by following another way, the way you described needs little modification in two parts. first the sql need to put in different function otherwise it is just returning the same object multiple times(user.length) . and the second problem is, the response is being sent before all the data objects are being populated so just the user list. so i had to check the data length before sending the response other wise it was just returning the user info without the permissions. anyway thank you.. — MEHEDI HASAN, Nov 14 '19 at 15:47

score 0 · Accepted Answer · answered Nov 14 '19 at 15:52

this is what i did which worked perfectly

/*------------------------ Get Permissions of All User -----------------------------*/
function getAllUser(done) {
  var sql = "SELECT users.id, users.username FROM users";
  db.query(sql, [], function(err, rows, fields) {
    if (err) throw err;
    done(rows);
  });
}

function getPermission(user_id, user_name,profile_image, done) {
  var sql =
    "SELECT table_name, permission_create, permission_update, permission_delete FROM permissions " +
    "WHERE user_id =?";
  db.query(sql, [user_id], function(err, rows, fields) {
    if (err) throw err;
    done(user_id, user_name, profile_image, rows);
  });
}

router.get("/allpermissions", function(req, res) {
  var i = 0;
  var data = [];
  getAllUser(function(user) {
    for (i = 0; i < user.length; i++) {
      getPermission(user[i].id, user[i].username,user[i].profile_image, function(
        userId,
        userName,
        profileImage,
        rows
      ) {
        data.push({
          user_id: userId,
          user_name: userName,
          profile_image: profileImage,
          permissions: rows
        });
        if (data.length == user.length) {
          res.json(data);
        }
        //console.log(data);
      });
    }
  });
});
/*------------------------ ***************************** ---------------------------*/

how to structure json in rest api call and make it synchronous

2 Answers2