discord.js/node/mysql : how to wait until query returns result

Question

I'm currently trying to write a bot for several Discord servers. The problem is, that the code doesn't wait for the database to return the results. In the current situation, I'm trying to check if the author id of a message is already in the mysql database.

I read some stuff about async/await but I need your help to understand how to link these things together !

const Discord = require('discord.js');
var mysql = require('mysql');
// Discord client
const client = new Discord.Client();
// Load config file
const config = require('./config.json');
client.config = config;
// Database config
const connection = mysql.createConnection({
  host: config.mysqlHost,
  user: config.mysqlUser,
  password: config.mysqlPassword,
  database: config.mysqlDatabase
});

// Check if ownerId is already in database
function checkOwnerId(authorId) {
  var query = connection.query(
    'SELECT * FROM guilds WHERE ownerId = ?', 
    authorId, 
    function (error, results, fields) {
      // Handle error after the release.
      if (error) throw error;
      // Debug
      console.log(query.sql);
      // If match
      if (results.length > 0) return "verified";
      else return "not_verified";
  });
}

/*
 * Event will run on every single message received, from any channel or DM.
 */
client.on('message', async msg => {
  // Ignore any message that does not start with prefix and ignore itself
  if (!msg.content.startsWith(config.prefix) || msg.author.bot) return;
  // Verify in database if the author.id is already saved
  if (checkOwnerId(msg.author.id) === "not_verified") {
    // Delete the command line
    msg.delete();
    // Send pm to author with error
    msg.author.send( 
      {embed: {
        color: 15934014,
        description: 'No permission to use the bot'
      }
    });
    console.log("NOT VERIFIED");
    return;
  }
  else {
    // Continue
  }

});

// Discord bot token
client.login(config.token);

How to verify in a database if the author id is already saved and after let him to use some commands ? Thank you in advance for your help !

Answer 1

You could do something like this, which will also allow you to make further SQL queries and work with the results:

const Discord = require('discord.js');
var mysql = require('mysql');
// Discord client
const client = new Discord.Client();
// Load config file
const config = require('./config.json');
client.config = config;
// Database config
const connection = mysql.createConnection({
    host: config.mysqlHost,
    user: config.mysqlUser,
    password: config.mysqlPassword,
    database: config.mysqlDatabase
});

// Check if ownerId is already in database
function sqlQuery(query, params) {
    return new Promise((resolve, reject) => {
        connection.query(
            query, params,
            (error, results) => {
                if (error) return reject(error);
                return resolve(results);
            });
    });
}

/*
 * Event will run on every single message received, from any channel or DM.
 */
client.on('message', msg => {
    // Ignore any message that does not start with prefix and ignore itself
    if (!msg.content.startsWith(config.prefix) || msg.author.bot) return;
    // Verify in database if the author.id is already saved
    sqlQuery('SELECT * FROM guilds WHERE ownerId = ?', msg.author.id,)
        .then(results => {
            if (results.length > 0) {
                //do stuff with verified user
            } else {
                msg.delete();
                // Send pm to author with error
                msg.author.send(
                    {embed: {
                            color: 15934014,
                            description: 'No permission to use the bot'
                        }
                    });
            }
        })
        .catch(error => {
            //handle error
        });
});

// Discord bot token
client.login(config.token);