How to Execute more queries in one Express Router.get

Question

router.get("/members", (req, res) => {
  let page = req.query.page;
  let pageSize = req.query.pageSize;

  if (typeof page === "undefined") {
    page = 0;
  } else {
    page -= 1;
  }

  if (typeof pageSize === "undefined") {
    pageSize = 5;
  }

  var skip = page * pageSize;

  db.sequelize.query(
    "SELECT id, mbr_id, mbr_name, mbr_type " +
    "FROM tbl_members " +
    "LIMIT " + pageSize + " " +
    "OFFSET " + skip
  )
  .then(members => {
      res.json(members[0])          
  })
  .catch(err => {
      res.send("error: " + err)
  })
})

I would like to add another mysql query. How do I modify my code so that I can add another mysql query and I add the result to the res variable. The query I like to add is getting the total number of records of the query above without the limit and offset. Going to use this for pagination.

Additionally, do I have to use async/await server side? My client side already has async/await before calling my code above. I've read this thread before I posted this. It has sample codes and some explanation. It talks about parallel with promise -- I do not require parallel query execution, just that I execute more than one query in a single router.get().

I also prefer using MySQL statement as I was used to SQL statements and not so much on ORM especially on defining relationship and using aggregates -- still confusing for now.

score 1 · Answer 1 · answered Oct 02 '19 at 19:29

You have more then one option:

async/await https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/async_function
Promise.all(query1, query2) https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise/all
You can chain your Promises

It depends on how you want and what feels right for you. I don't think there is a right way for this, however I would choose the async/await, from my point of view it results in a more clean code, but it has it pitfalls.

score 1 · Accepted Answer · answered Oct 02 '19 at 21:05

Just sequence you're queries. Assuming the second query is dependent upon the first, you could sequence them like this:

router.get("/members", (req, res) => {
  let page = req.query.page;
  let pageSize = req.query.pageSize;

  if (typeof page === "undefined") {
    page = 0;
  } else {
    page -= 1;
  }

  if (typeof pageSize === "undefined") {
    pageSize = 5;
  }

  var skip = page * pageSize;

  db.sequelize.query(
    "SELECT id, mbr_id, mbr_name, mbr_type " +
    "FROM tbl_members " +
    "LIMIT " + pageSize + " " +
    "OFFSET " + skip
  ).then(members => {
      return db.sequelize.query(your second query).then(results => {
          res.json({member: members[0], otherResults: results})
      });
  }).catch(err => {
      res.status(500).send("error: " + err)
  });
});

Or, you can use async/await like this:

router.get("/members", async (req, res) => {
  let page = req.query.page;
  let pageSize = req.query.pageSize;

  if (typeof page === "undefined") {
    page = 0;
  } else {
    page -= 1;
  }

  if (typeof pageSize === "undefined") {
    pageSize = 5;
  }

  var skip = page * pageSize;

  try {
      let members = await db.sequelize.query(
        "SELECT id, mbr_id, mbr_name, mbr_type " +
        "FROM tbl_members " +
        "LIMIT " + pageSize + " " +
        "OFFSET " + skip
      );
      let otherResults = await db.sequelize.query(your second query);
      res.json({member: members[0], otherResults: otherResults});
  } catch(err) {
      res.status(500).send("error: " + err)
  }
});

Or, if the db queries are completely independent of one another, you could use Promise.all() to track when they were both done.

I use the latter code async/await combo -- I'm just curious why the results are 2 arrays of the same info. That's why i use members[0] just to provide only one result. — Ben, Oct 03 '19 at 06:49

How to Execute more queries in one Express Router.get

2 Answers2