Subquery in node.js sometimes works and sometimes not

i have created a node.js query where it works fine sometimes , it is an insert of a club and then insert the players in a second query , there is a subquery that takes the last inserted id of the Auto increment id in the first query , after execution i noticed that sometimes the players are inserted with the last inserted id of the club like they should be but sometimes they are inserted but with not with the last insert id but with the one before it!!!!!!

Tip: I watch the return of the callback in the times that everything works fine i see that the insertId in the callback value is the correct value , but when i does not work i see in the result of callback insertId = 0

Example of the problem: let’s say the last inserted id of club is 15 , when the problem happens i find the inserted players inserted with clubid 14 !!!!!!!!!!!!!! they should be 15 but the most weird in this i see that sometimes they are inserted correctly with the right value!!!!

it’s like a random something , sometimes it choose to work and sometimes not!

Here the json in postman:

{
    "userid": 41,
    "name": "fc dhya club",
    "price": 11,
    "players": [
        {
            "id":1,
            "firstname":"aaaaaaa",
            "lastname":"ee",
            "position":"df",
            "price":12.1,
            "appearences":2,
            "goals":1,
            "assists":1,
            "cleansheets":1,
            "redcards":1,
            "yellowcards":1,
            "image":"qq"
        },
        {
            "id":2,
            "firstname":"aabbbbbbbbbbb",
            "lastname":"ee",
            "position":"df",
            "price":12.1,
            "appearences":2,
            "goals":1,
            "assists":1,
            "cleansheets":1,
            "redcards":1,
            "yellowcards":1,
            "image":"qq"
        }
    ]
}

Here the node.js code

const { json } = require("express");
const mysql = require('mysql2');
const pool = require("../../config/database");

module.exports = {
    create: (data, callback) => {
        var myArray = new Array();

        data.players.forEach((player) => {
            var playerModel = {
                id : player.id,
                firstname : player.firstname,
                lastname : player.lastname,
                position : player.position,
                price : player.price,
                appearences : player.appearences,
                goals : player.goals,
                assists : player.assists,
                cleansheets : player.cleansheets,
                redcards : player.redcards,
                yellowcards : player.yellowcards,
                image : player.image
            };

            myArray.push(playerModel);
        });

        var id;

        pool.query(
            'insert into club(userid,name,price) values(?,?,?)',
            [
                data.userid,
                data.name,
                data.price
            ],
            (error, result) => {
                if (error) {
                    callback(error);
                }

                return callback(null, result);
            },
        );

        for(var item of myArray) {
            pool.query(
                'insert into players(id,firstname,lastname,position,price,appearences,goals,assists,cleansheets,redcards,yellowcards,image,clubid Where clubid = ( SELECT id from club where id > ? ORDER BY id DESC limit 1  )) values (?,?,?,?,?,?,?,?,?,?,?,?, ( SELECT id from club where id > ? ORDER BY id DESC limit 1  ) )',
                [ 
                    item.id,
                    item.firstname,
                    item.lastname,
                    item.position,
                    item.price,
                    item.appearences,
                    item.goals,
                    item.assists,
                    item.cleansheets,
                    item.redcards,
                    item.yellowcards,
                    item.image,
                    0
                ],
                (error, results, fields) => {
                    if(error){
                        callback(error);
                    }

                    return callback(null, results);
                },
            );
        }
    },
};
  

Here a screen capture of postman when the queries don’t work fine

Here when the insert works fine

Answer

The reason is that you are not taking into account the async nature of pool.query(). When you make the first query i.e. insert club, you do not wait for it to complete and you simply start inserting players expecting correct club to have been inserted. Sometimes the club may have been inserted and sometimes it won’t be because it is async. What you need to do is move the second loop inside the callback function of the first pool.query(). This way the second loop is run only after the club has been inserted for sure. Check this code.

pool.query(
    'insert into club(userid,name,price) values(?,?,?)',
    [
        data.userid,
        data.name,
        data.price
    ],
    (error, result) => {
        if (error) {
            return callback(error);
        }
        
        for(var item of myArray) {
            pool.query(
                'insert into players(id,firstname,lastname,position,price,appearences,goals,assists,cleansheets,redcards,yellowcards,image,clubid Where clubid = ( SELECT id from club where id > ? ORDER BY id DESC limit 1  )) values (?,?,?,?,?,?,?,?,?,?,?,?, ( SELECT id from club where id > ? ORDER BY id DESC limit 1  ) )',
                 ...
                 ...
    },
);