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: '[email protected]'
}

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!!!!!!!!

For a web server, you should always assume updates are asynchronous, because multiple browser clients can be triggering multiple updates at the same time. And that's before considering attackers using scripts to trigger a flood of queries. One common pattern is to attempt an insert then if it fails attempt an update. For your case it might be to attempt to create then if it fails go to an "account already exists" handler. — Dave S
– Dave S, Commented Feb 6, 2022 at 19:00

Rishabh · Accepted Answer · 2022-02-14 08:03:13Z

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

On user signUp, you're trying to check if the user already exists in DB.
If not, you're inserting it into the DB.
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);
  });
};

Collectives™ on Stack Overflow

how to handle assynchronous SQL query execution in node.js

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related