Node.js returning result from MySQL query

Question

I have the following function that gets a hexcode from the database

function getColour(username, roomCount) {     connection.query('SELECT hexcode FROM colours WHERE precedence = ?', [roomCount], function(err, result)     {         if (err) throw err;         return result[0].hexcode;     }); } 

My problem is that I am returning the result in the callback function but the getColour function doesn't return anything. I want the getColour function to return the value of result[0].hexcode.

At the moment when I called getColour it doesn't return anything

I've tried doing something like

function getColour(username, roomCount) {     var colour = '';     connection.query('SELECT hexcode FROM colours WHERE precedence = ?', [roomCount], function(err, result)     {         if (err) throw err;         colour = result[0].hexcode;     });     return colour; } 

but of course the SELECT query has finished by the time return the value in colour

Answer 1

You have to do the processing on the results from the db query on a callback only. Just like.

function getColour(username, roomCount, callback) {     connection.query('SELECT hexcode FROM colours WHERE precedence = ?', [roomCount], function(err, result)     {         if (err)              callback(err,null);         else             callback(null,result[0].hexcode);      });  }  //call Fn for db query with callback getColour("yourname",4, function(err,data){         if (err) {             // error handling code goes here             console.log("ERROR : ",err);                     } else {                         // code to execute on data retrieval             console.log("result from db is : ",data);            }      }); 

Answer 2

If you want to use promises to avoid the so-called "callback hell" there are various approaches.

Here's an example using native promises and the standard MySQL package.

const mysql = require("mysql");  //left here for testing purposes, although there is only one colour in DB const connection = mysql.createConnection({   host: "remotemysql.com",   user: "aKlLAqAfXH",   password: "PZKuFVGRQD",   database: "aKlLAqAfXH" });  (async () => {   connection.connect();   const result = await getColour("username", 2);   console.log(result);   connection.end(); })();  function getColour(username, roomCount) {   return new Promise((resolve, reject) => {     connection.query(       "SELECT hexcode FROM colours WHERE precedence = ?",       [roomCount],       (err, result) => {         return err ? reject(err) : resolve(result[0].hexcode);       }     );   }); } 

In async functions, you are able to use the await expression which will pause the function execution until a Promise is resolved or rejected. This way the getColour function will return a promise with the MySQL query which will pause the main function execution until the result is returned or a query error is thrown.

A similar but maybe more flexible approach might be using a promise wrapper package of the MySQL library or even a promise-based ORM.