how to handle assynchronous SQL query execution in node.js

Question

I am new to node.js and mysql, I asked a similar question before, but my whole server is chaos, so I took sometime improving it. But I got back to the asynchronous problem: How can I handle synchronous and assynchrounous MYSQL query execution: my function of signup would firstly run a query to look if the user is already registerd, and if not, create a user in the database.

// create a new user
User.signUp = (user, result) => {
    flag = true
    // Does Email adress has been registered?
    sql.query(`SELECT * FROM user_info WHERE EMAIL_ADRESS = ?`, user.username, (err, res) => {
        if (err) {
            console.log("error: ", err);
            result(err, null);
            return;
        }
        if (res.length) {
            //=========tell that it is registered=======/
            console.log("found user: ", res[0]);
            let judge = { 
                isRegistered: true,
            };
            result(null, judge);
            flag = false
            return;
        }
    });
    // If not found: insert the user
    if(flag){
        console.log('No!!!!!!! in')
        const uLoginAuth = {
            USER_ID: user.id,
            EMAIL_ADRESS: user.username, 
            PSWORD:user.password,
            VERIFIED: false,
            VERIFYCODE: uuid.v1()
        };

        const uInfo = {
            USER_ID: user.id, 
            FIRST_NME: user.firstName,
            LAST_NME: user.lastName, 
            USER_ROLE: user.role,
            EMAIL_ADRESS: user.username
        };

        sql.query("INSERT INTO user_info SET ?", uInfo, (err, res) => {
            if (err) {
                console.log("error: ", err);
                result(err, null);
                return;
            }
            console.log("created user: ", { id: res.insertId, ...user });
        });

        sql.query('INSERT INTO login_authentication SET ?', uLoginAuth, (err, res) => {
            if (err) {
                console.log("error: ", err);
                result(err, null);
                return;
            }
            console.log("created user: ", { id: res.insertId, ...user });      
        });
        let judge = { 
            isRegistered: true,
        };
        result(null, judge);
        return;
    }
};

However, I am getting an error of: error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client which means I sent back my response twice, so I set the console.log to see, then I realize, when the user is already registered, I do get the correct response:

{
  "isRegistered": true
}

but the code executes the lines that handle normal registration after comment // If not found And here is the output of the program:

Server is running on port 8080.
Successfully connected to the database.
No!!!!!!! in
found user:  RowDataPacket {
  USER_ID: 'b7f632ef-a8b9-489f-b3eb-c8f25c2b5a32',
  FIRST_NME: 'Test',
  LAST_NME: 'Test',
  USER_ROLE: 'student',
  EMAIL_ADRESS: 'test@gmail.com'
}

I have tried multiple way of wait until first query done to execute next line, but it does not work, in this case, can someone please help me on this? How could I wait until query is done?? Thank you!!!!!!!!

Answer 1

So if I'm not wrong, here's what you're trying to here:

1. On user signUp, you're trying to check if the user already exists in DB.
2. If not, you're inserting it into the DB.
3. In either scenario, you're returning this as a response:

{
    isRegistered: true
}

The if check you've put is not waiting for the first find/SELECT operation to finish and therefore is fired right when the signUp function is invoked (most probably when the user visits the signUp route).

This is the reason you can see the "No!!!!!!! in" console (synchronous, shown immediately) coming before the "found user: ..." log (async, shown after DB operation).

To fix this, you can structure your code like this:

User.signUp = (user, result) => {
  let judge = { 
    isRegistered: true,
  };

  // Does Email adress has been registered?
  sql.query(`SELECT * FROM user_info WHERE EMAIL_ADRESS = ?`, user.username, (err, res) => {
      if (err) {
          console.log("error: ", err);
          result(err, null);
          return;
      }
      if (res.length) {
          //=========tell that it is registered=======/
          console.log("found user: ", res[0]);
      } else { // <-------------- can make it "else if (preferred condition)" too
        console.log('No!!!!!!! in')
        const uLoginAuth = {
            USER_ID: user.id,
            EMAIL_ADRESS: user.username, 
            PSWORD:user.password,
            VERIFIED: false,
            VERIFYCODE: uuid.v1()
        };
    
        const uInfo = {
            USER_ID: user.id, 
            FIRST_NME: user.firstName,
            LAST_NME: user.lastName, 
            USER_ROLE: user.role,
            EMAIL_ADRESS: user.username
        };
    
        sql.query("INSERT INTO user_info SET ?", uInfo, (err, res) => {
            if (err) {
                console.log("error: ", err);
                result(err, null);
                return;
            }
            console.log("created user: ", { id: res.insertId, ...user });
        });
    
        sql.query('INSERT INTO login_authentication SET ?', uLoginAuth, (err, res) => {
            if (err) {
                console.log("error: ", err);
                result(err, null);
                return;
            }
            console.log("created user: ", { id: res.insertId, ...user });      
        });
      }

      result(null, judge);
  });
};