NodeJS/Express: Assign MySQL query result to Variable?

Question

I am creating a function which selects a file name from a MySQL table, then uses the file name to look up an object in Google Cloud storage and retrieve metadata. I need to assign the result of the query to a variable which I can then use in my Google Cloud storage bucket call - but I can't figure out how to do this. Here is what I have:

const File = function (file) {
  this.userId = file.userId;
  this.fileName = file.fileName;
};
    
File.getAvi = async (userId, result) => {
  const fileName = await sql.query(
    `SELECT fileName FROM avatars WHERE userId = ${userId}`, 
    (err, res) => {
      if (err) {
        console.log("Error selecting from AVATARS: ", err);
        return result(null, err);
      }
      
      console.log("Fil Name: ", res);
      result(null, res[0].fileName);
    }
  );
    
  const metadata = await bucket
    .file(fileName)
    .getMetadata();
    
  return result(null, metadata);
};

But this does not work. The function terminates at the first "result" line at the end of the query and it sends the query result back to the controller, rather than assign the query reulst to const fileName and let me use it in the Google Cloud storage call. How do I assign the query result to a variable which I can use in additional calls?

Don't mix promises with the callback functions. Use one or the other, preferably promises — Phil, Feb 22 '23 at 23:49
If `sql.query` is async then it shouldn't need the callback. `getAvi` terminates at the last `return`, likely with a broken `metadata` if `sql.query` isn't awaitable (or it's confused because you supply callbacks). Pick a paradigm. Please see [How do I return the response from an aynchronous call](https://stackoverflow.com/q/14220321/438992), which this likely duplicates. — Dave Newton, Feb 22 '23 at 23:52

score 1 · Accepted Answer · answered Feb 23 '23 at 07:40

Make sure to use prepared statements:

const File = function (file) {
  this.userId = file.userId;
  this.fileName = file.fileName;
};

File.getAvi = async (userId, result) => {
  try {
    const res = await sql.query(
      `SELECT fileName FROM avatars WHERE userId = ?`,
      [userId]
    );

    if (!fileName) {
        console.log('File not found');
        return result(null, new Error('File not found'));
    }
    const fileName = res[0].fileName;

    const metadata = await bucket.file(fileName).getMetadata();

    return result(null, metadata);
  } catch (err) {
    console.log('Error selecting from AVATARS: ', err);
    return result(null, err);
  }
};

Thank you, sir. New dev here (coming from DevOps background) and I appreciate you modeling this for me. I had to change !fileName to !res, but this otherwise did exactly what I was looking for and will use the example to build more code. May you live forever. — alphatango165, Feb 23 '23 at 22:25

NodeJS/Express: Assign MySQL query result to Variable?

1 Answers1