Node and Axios - How to make sequentials requests

Question

I need to make 2 requests to my API to insert data in 2 different table:

Workflow:

request to get the last id + 1 => create the array I need (last_id, values) => two INSERT in MySql, 1st with varius data, 2nd with the array I created.

router.post("/addentry", function (req, res) {
    
let sql = "SELECT MAX(id) + 1 AS last_id FROM entries;"; // I get the id

  let query = connection
    .query(sql, (err, results) => {
      if (err) throw err;
     
     res.header("Access-Control-Allow-Origin", "*");
        
    // put the id in a variable

    var last_id = results[0].last_id;

    var categoriesMap = req.body.categories;

    var valCat = Object.values(categoriesMap);

    // I create the array with other data
 
    var catArray = valCat.map((item) => {
        return [last_id, item];
      });

    })
    .then((catArray) => {
    let sql = `BEGIN; INSERT INTO entries (title,kindof) VALUES("${[
        req.body.title,
      ]}","${req.body.kindof}");
     INSERT INTO categories_main (entry_id, cat_id) VALUES  ? ;
     COMMIT;`;

     let query = connection.query(sql, [catArray], (err, results) => {
        if (err) throw err;
        console.log(results);
        res.header("Access-Control-Allow-Origin", "*");
        res.send("Entry added to DB");
      });
    });  

The first part works perfectly but with the second I get

TypeError: connection.query(...).then is not a function

Any idea how to do it?

Thanks

Answer 1

Here the complete solution, starting from what @SubinSebastian advised to me.

First of all I needed node-mysql2, that alows promises and therefore chained requests.

And then:

    router.post("/addentry", function (req, res) {
     
     let sql = "SELECT MAX(id) + 1 AS last_id FROM entries;";
    
      connection.promise().query(sql)
        .then((results) => {
        
    
          // I get the value from results
          var stringify = JSON.parse(JSON.stringify(results[0]));
          for (var i = 0; i < stringify.length; i++) {
            console.log(stringify[i]["last_id"]);
            var last_id = stringify[i]["last_id"];
          }
    

          // I get some parameters and I create the array
          
          var categoriesMap = req.body.categories;
    
          var valCat = Object.values(categoriesMap);
    
          var catArray = valCat.map((item) => {
            return [last_id, item];
          });
    
    
          let sql = `BEGIN; INSERT INTO entries (title,kindof) VALUES("${[
            req.body.title,
          ]}","${req.body.kindof}");
    
         INSERT INTO categories_main (entry_id, cat_id) VALUES  ? ;
    
         COMMIT;`;

    // array as second query parameter

          let query = connection.query(sql, [catArray], (err,results) => {
            if (err) throw err;     
          });
        })
        .catch(console.log);

Answer 2

First things first, you should make sure that you use node-mysql2 instead of node-mysql. node-mysql2 has a built in functionality that helps making multiple queries inside a single connection. I have provided you this answer that exemplifies how to use it properly.

Moving forward, after you've done that, to be able to work with your result object, you will need JSON.

The following syntax is what you probably want to use:

var stringify = JSON.parse(JSON.stringify(results[0]));
for (var i = 0; i < stringify.length; i++) {
    var last_id = stringify[i]["last_id"];
}

Answer 3

I need to make 2 requests to my API to insert data in 2 different table:

From code, I see that you are intending to do a single API call to the server and run 2 queries.

You can do .then only on a Promise, so as we can see connection.query is not returning a Promise and hence not then able.

Also you are setting response headers multiple times res.header("Access-Control-Allow-Origin", "*"); do this only once in a request cycle. So lets follow the callback approach instead of then.

  let sql = "SELECT MAX(id) + 1 AS last_id FROM entries;"; // I get the id
  let query = connection
    .query(sql, (err, results) => {
      if (err) {
        
        res.header("Access-Control-Allow-Origin", "*");
        return res.status(500).send({error:'server error'});
         
     }
        
    // put the id in a variable
    var last_id = results[0].last_id;
    var categoriesMap = req.body.categories;
    var valCat = Object.values(categoriesMap);

    // I create the array with other data
 
    var catArray = valCat.map((item) => {
        return [last_id, item];
      });
    let sql = `BEGIN; INSERT INTO entries (title,kindof) VALUES("${[
        req.body.title,
      ]}","${req.body.kindof}");
     INSERT INTO categories_main (entry_id, cat_id) VALUES  ? ;
     COMMIT;`;

     let query = connection.query(sql, [catArray], (err, results) => {
        if (err) {
           res.header("Access-Control-Allow-Origin", "*");
           return res.status(500).send({error:'server error'});
        }
        console.log(results);
        res.header("Access-Control-Allow-Origin", "*");
        res.send("Entry added to DB");
      });
    })