CODEWITHSUNDEEP
node.jscallback
Alexander
Alexander

Reputation: 325

How to do async calls to MySQL database in NodeJS

Update:

I have a async function as follows:

async function userHandler(username, displayName, profilePicture, email) {
  connection = await connectDB()
    await connection.query('USE spyncdb;');
    await connection.query('SELECT * FROM users WHERE username = ?', [username], function(error, result, fields) {
      if (error) {
          console.log(error);
      }
      if (result) {
          if (result != 0) {
            console.log('User already in database')
            return result[0].user_id;
            // Do whatever should be done
            } else {
            // Add user to database
            connection.query('INSERT INTO users (username, displayname, profilePicture, email) VALUES (?, ?, ?, ?)', [username, displayName, profilePicture, email], function(error, result, fields) {
              if (error) {
                console.log('ERROR');
                console.log(error);
              }
              if (result) {
                console.log('user inserted into db');
                return;
              };
            })
            }
        }
    });
}

I then call this function, and want to store the return value from it (user_id).

I call the function from the following:

async () => {
    let user_id = await userHandler(aUser.username, aUser.displayName, 
    aUser.profilePicture, aUser.email);
    
     console.log(user_id);
}

But I only get "undefined" - why?

PS. I use the mysql library for my DB connection.

Upvotes: 3

Views: 4362

Answers (2)

Alexander
Alexander

Reputation: 325

Ok so I finally managed to solve the issue. The main thing that had to be done was to switch from a callback-based code to the more modern async/away way to handle async code, this make the code much less complicated and easier to handle and read.

Also I switched from the mysql library to mysql2 library, which is more adapted to async functions. The final code looks like this:

const mysql2 = require('mysql2/promise');


// Connect to server
const pool = mysql2.createPool({
    host     : "ENDPOINT",
    user     : "USERNAME",
    password : "PASSWORD",
    port     : "3306",
    database : "DATABASENAME",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Function for checking the user in the database
async function checkUser(username, displayName, profilePicture, email) {
  const result = await pool.query('SELECT * from users WHERE username = ?', [username]);
  if (result[0].length < 1) {
    console.log('User not found, adding new user...');
    const newResult = await pool.query('INSERT INTO users (username, displayname, profilePicture, email) VALUES (?, ?, ?, ?)', [username, displayName, profilePicture, email]);
    return newResult[0].insertId;
  }
  console.log('User found in DB')
  return result[0][0].user_id;
}

// Calling the check function with the input values from the user
async function check() {
let user = await checkUser(aUser.username, aUser.displayName, aUser.profilePicture, aUser.email);
console.log(`user ID is: ${user}`);
}

// Invoking the check function      
check();

Upvotes: 3

tadman
tadman

Reputation: 211720

There's two solutions. The easy one, don't use callbacks, use Promises and then:

aUser.user_id = await userHandler

Or you'll have to supply a callback function and synchronize your code accordingly:

function example(cb) {
  userHandler(..., function(result) {
    cb(user_id);
  });
}

example(function(user_id) {
  aUser.user_id = user_id;
});

Remember, callback-driven code is extremely not fun to implement and work with so if you can, pivot to Promises, if not full async/await.

The general rule here is if your function makes callbacks to get answers you must accept a callback you can chain on to. A return from a callback function is almost always thrown in the trash and ignored.

Upvotes: 0

Related Questions