using async with mysql

Question

So i got the following code. I want to check if Name already exists in my database. If not I want to insert it. And it basically works with my code. My only problem is, that the last bit of code where it checks whether "success" is true gets executed before it even checked the database. (when using a Name that doesn't exist in the database my console is like this:

Name is already taken <--- this piece of code should be executed last. Then that would not appear No Duplicate, insert it starting insert function

I've heard of async and await but I just could not figure it out. I could really need some help.

Here is my Code:

app.post("/api/createProject", (req, res) => {
  var success = false;
  // console.log(req.body);
  const Name = req.body.Name;
  const Status = req.body.Status;
  const Deadline = req.body.Deadline;
  const EstimatedHours = req.body.EstimatedHours;
  const BudgetHours = req.body.BudgetHours;
  const BudgetCost = req.body.BudgetCost;
  const Description = req.body.Description;

  // check if Projct Name is already taken
  const SEARCH_NAME = `SELECT ID FROM projects WHERE Name = '${Name}'`;
  db.query(SEARCH_NAME, (err, results) => {
    if (err) {
      return err;
    } else {
      if ((results.length < 1)) {
        console.log("No Duplicate, insert it")
        insertDatabase();
      }
    }
  });
  
  // will only get executed when no Errors got detected above (Name is not already taken)
  function insertDatabase() {
    console.log("starting insert function");
    const CREATE_NEW_PROJECT = "INSERT INTO projects (Name, Status, Deadline, EstimatedHours, BudgetHours, BudgetCost, Description) VALUES (?,?,?,?,?,?,?)";
    db.query(
      CREATE_NEW_PROJECT,
      [
        Name,
        Status,
        Deadline,
        EstimatedHours,
        BudgetHours,
        BudgetCost,
        Description,
      ],
      (err, result) => {
        if (!err) {
          success = true;
        }
      }
    );
  }
  if (success == true){
    return 0;
  } else {
    console.log("Name is already taken");
    return "Name is already taken";
  }
});

gruffmeister · Accepted Answer

Your issue is to do with the asynchronous operation of NodeJS. What is happening in your code is all of your functions are getting "fired" because the code will execute sequentially, but it does not wait for the functions to return.

This is why your if / else statement is getting executed as the "else" - the query hasn't returned to set the "success" to true and therefore it hits the else statement because of its default value.

So we need to refactor your code a bit to make it suitable for async-await. The async-await will wait for a function to return before carrying on to the next statement. With the async-await command, the function it is calling must return a promise.

First we abstract your query into a function with a promise. A promise will run your query and only return when your query is complete:

async function Query (request) {
    return new Promise ((resolve, reject) => {
        const SEARCH_NAME = `SELECT ID FROM projects WHERE Name = '${request.Name}'`;
        db.query(SEARCH_NAME, (err, results) => {
            if (err) {
                reject(err);
            } else {
               resolve(results)
            }
         });
    })
}

The above function takes request, which would be your req.body as a parameter, carries out the query and returns the results.

We also need to refactor your insertDatabase function into async-await to see if our insert was successful or not (as this seems to be what you want to know):

async function insertDatabase(request) {
    return new Promise((resolve, reject) => {
        console.log("starting insert function");
        const CREATE_NEW_PROJECT = "INSERT INTO projects (Name, Status, Deadline, EstimatedHours, 
BudgetHours, BudgetCost, Description) VALUES (?,?,?,?,?,?,?)";
        db.query(
           CREATE_NEW_PROJECT,
           [
               request.Name,
               request.Status,
               request.Deadline,
               request.EstimatedHours,
               request.BudgetHours,
               request.BudgetCost,
               request.Description,
            ],
            (err) => {
                 if (err) {
                     reject(false)
                 }
                 else {
                     resolve(true)
                 }
            });
      });
}

We then check the results for length less than 1:

var result = await Query(req.body);
if (result <1) {
    console.log("No Duplicate, insert it")
    success = await insertDatabase(req.body);
}

Putting it all together our code will look something like:

app.post("/api/createProject", async (req, res) => {
   var success = false;
   var result = await Query(req.body);
   if (result <1) {
      console.log("No Duplicate, insert it")
      success = await insertDatabase(req.body);
      if (success == true){
          return 0;
      } else {
          console.log("Name is already taken");
          return "Name is already taken";
      }
   }
   
}

async function Query (request) {
    return new Promise ((resolve, reject) => {
        const SEARCH_NAME = `SELECT ID FROM projects WHERE Name = '${request.Name}'`;
        db.query(SEARCH_NAME, (err, results) => {
            if (err) {
                reject(err);
            } else {
               resolve(results)
            }
         });
    })
}

async function insertDatabase(request) {
    return new Promise((resolve, reject) => {
        console.log("starting insert function");
        const CREATE_NEW_PROJECT = "INSERT INTO projects (Name, Status, Deadline, EstimatedHours, 
BudgetHours, BudgetCost, Description) VALUES (?,?,?,?,?,?,?)";
        db.query(
           CREATE_NEW_PROJECT,
           [
               request.Name,
               request.Status,
               request.Deadline,
               request.EstimatedHours,
               request.BudgetHours,
               request.BudgetCost,
               request.Description,
            ],
            (err) => {
                 if (err) {
                     reject(false)
                 }
                 else {
                     resolve(true)
                 }
            });
      });
}

Notice our post callback has "async" in front of it. This is a required keyword in any function that contains the await command.

Additional:

I've left your code mostly how it was with a bit of refactoring, some extra code best practice notes for you:

Multiple return paths is a very bad idea as this leads to testing issues as well as later debugging nightmares, so I would suggest you refactor the return from the final if statement - maybe assign the variables in the if statement but return the variable at the end.

You shouldn't return different types from the same function. So either return 1 or 0 (true or false) on your result, or return two different sets of text. Do not return 0 for one case and text for another, this is very bad practice.

I've also short-cutted by passing your req.body into the functions which probably passes more data than needed. If you don't want to pass this, as a minimum you should encapsulate your assignments that go together into a JSON object:

var request = {
    "Name" : req.body.Name,
    "Status" : req.body.Status,
    "Deadline" : req.body.Deadline,
    "EstimatedHours" : req.body.EstimatedHours,
    "BudgetHours" : req.body.BudgetHours,
    "BudgetCost" : req.body.BudgetCost,
    "Description" : req.body.Description
}

and then pass this variable request into the functions instead of req.body.

using async with mysql

Answers (2)

Related Questions