5. Query Callback (쿼리콜백) in Node.js

0. Problem

app.post('/api/rate', (req,res) => {
  const {residentName, quality, diff, willing, comment, tags} = req.body;
  let id = 0;

  db.query(`SELECT id FROM resident WHERE name = ?;`, [residentName],(err,result)=>{
    if(err) throw err;
    console.log(result);

    id = result[0].id;
  });
  
  
  const query = `INSERT INTO rate(resident_id, quality_rate, difficulty_rate, willingness, comment${tagQueryFront} VALUES (${id}, ${quality}, ${diff}, '${willing}', '${comment}'${tagQueryBack}`;
}

This is the original code when I faced the problem. What I wanted to do was to retrieve 'id' and there was no way 'residentName' was wrong value since any code lines never touchned or variated it after 'residentName' first gotten from table. No syntax error either. I couldn't get anything for 'id' and I'd like to share how I solved this.

1. Callback

As I said, I got nothing and it could not happen. And eventually I found out the key was callback handling.

1-1. db.query()

The function 'query()' is asynchronous. That was the reason why I got nothing because before the query has completed, id won't be updated. Therefore, when I tried to access 'id', it wasn't supposed to be available because the query were still on the process to fetch data.

2. Fixed code

  db.query(`SELECT id FROM resident WHERE name = ?;`, [residentName],(err,result)=>{
    if(err) {
      console.error(err);
      return res.status(500).json({ error: 'Failed to retrieve resident ID.' });
    }

    const id = result[0]?.id;

    if (!id)
      return res.status(400).json({ error: 'Resident not found.' });
    
    const query = `INSERT INTO rate(resident_id, quality_rate, difficulty_rate, willingness, comment${tagQueryFront} VALUES (${id}, ${quality}, ${diff}, '${willing}', '${comment}'${tagQueryBack}`;

    db.query(query, (err, result) => {
      if (err) throw err;

      res.send('Rate has been added successfully!');
    });
  });

 

const id = result[0]?.id: the character '?' allows you to see "undefined" msg instead of error msg when one occurs.

tagQueryBack and tagQueryFront is defined above this code.