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

Your second error is due to the fact that you are still in your connection to the database, but you are trying to change your header. It was already set initially (after you first check for errors). — David Buzatu, Jul 22 '20 at 08:30
true. thanks. I thought it needed it for every query. I edited it to make it simpler. — Marco Disco, Jul 22 '20 at 08:33
Maybe this answer will help with your other problem: [answer](https://stackoverflow.com/a/52185624/11023871) — David Buzatu, Jul 22 '20 at 08:43
I think my case is different I don't have any connection problem. I need to send a second request once the one is over — Marco Disco, Jul 22 '20 at 08:47
the one. I didn't even know about a second. I just installed. let me try to understand the promises with that — Marco Disco, Jul 22 '20 at 09:06
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/218346/discussion-between-marco-disco-and-david-buzatu). — Marco Disco, Jul 22 '20 at 09:28
now I can't extract the values like before anymore. `var last_id = results[0].last_id` doesn't work anymore. how to do it with mysql2? — Marco Disco, Jul 22 '20 at 09:29
You should be able. Print your result and see if its right. Follow this answer for more details on how to make 2 API calls with `node-mysql2' [answer](https://stackoverflow.com/a/23267627/11023871) — David Buzatu, Jul 22 '20 at 09:43
now works. I tried with hard coded data (the array) and it does what I need. just `results[0]` doesn't give me nothing back. (undefined) . is a `TextRow` and not a `Raw Packet` — Marco Disco, Jul 22 '20 at 09:46
Try to stringify it with `JSON` and tell me if it helps. `res=JSON.parse(JSON.stringify(results))` — David Buzatu, Jul 22 '20 at 09:56
I tried stringify alone and it doesn't work and with parse neither — Marco Disco, Jul 22 '20 at 09:58

score 1 · Accepted Answer · answered Jul 23 '20 at 06:31

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"];
}

score 0 · Answer 2 · answered Jul 22 '20 at 08:52

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");
      });
    })

I believe you are talking about `catArray` . Why cant you pass it to second connection.query ? Also I think there is not need to pass `[catArray]` that will make it a array of array — Subin Sebastian, Jul 22 '20 at 09:10
Understand that. As you can see the code I am indeed passing that in the second query. I am trying to understand why you think you cant pass it to second query. — Subin Sebastian, Jul 22 '20 at 09:19
I don't understand. I need to put that array into the second query, or it can be even a third. — Marco Disco, Jul 22 '20 at 09:26
Dont you see catArray passed as second parameter in `let query = connection.query(sql, [catArray], (err, results) => {` — Subin Sebastian, Jul 22 '20 at 09:27

score 0 · Answer 3 · answered Jul 23 '20 at 07:06

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);

Node and Axios - How to make sequentials requests

3 Answers3