module.exports returns value too early

Question

I'm creating a Discord bot based on a tutorial and currently I'm working on a leveling system. I have a file for a function that looks for the rank of the user in MySQL and returns the rank. The file getUserGuildXPRank.js looks like this:

module.exports = async (userId, guildId) => {
    if(!userId || !guildId) return console.log(`Error while performing function getUserGuildXPRank, userId or guildId missing...`);

    
    try {
        var userRank = 0;
        connection.query(`SELECT \`user_id\`, \`user_xp_total\` FROM \`${guildId}\` WHERE \`user_xp_total\` > 0 ORDER BY \`user_xp_total\` DESC`,
            async function(err, rows, fields) {
                if (err) return console.log(`[MYSQL  ] Error while receiving user XP rank for user ${userId} in guild ${guildId}: ${err}`);
                if (rows < 1) return console.log(`[MYSQL  ] Function getUserGuildXPRank returned no result.`);
                for(let i=0; i < rows.length; i++) {
                    if(rows[i].user_id == userId) {
                        userRank = i + 1;
                        console.log(`DEBUG: userRank: ${userRank}`)
                    }
                }
            }
        );
        return userRank;
    } catch (error) {
        console.log(`[XP    ] Couldn't perform function getUserGuildXPRank: ${error}`);
    }
}

The file that handles the command looks like this:

const getUserGuildXPRank = require('../../utils/getUserGuildXPRank');
module.exports = {
    //some more code here
    callback: async (bot, interaction) => {
        const mentionUserId = interaction.options.get('target-user')?.value;
        const targetUserId = mentionUserId || interaction.member.id;
        const targetUserObject = await interaction.guild.members.fetch(targetUserId);

        const userRank = await getUserGuildXPRank(targetUserId, interaction.guild.id);

        connection.query(`SELECT \`user_xp\`, \`user_level\` FROM \`${interaction.guild.id}\` WHERE \`user_id\` = '${targetUserId}' LIMIT 1`,
            async function (err, rows, fields) {
                if (err) return console.log(`[MYSQL  ] Error while receiving user information in command level for user ${targetUserId} in guild ${interaction.guild.id}: ${err}`);
                if (rows < 1) return interaction.editReply(`I could not find any information at the moment, please try again later.`);


                console.log(`DEBUG LEVEL userRank: ${userRank}`);

                //some more code here

            }
        );
        return;
    }
}

I think the problem is in the getUserGuildXPRank.js and the userRank is returned before the query is actually finished. However, I'm unable to figure out how to delay returning the value until it has been updated by the query. Any help will be greatly appreciated!

Answer 1

The problem was indeed that the query doesn't return a Promise. Using the npm package mysql2, I used the Promise wrapper by mysql2/promise and changed the queries without callback functions like this:

module.exports = async (userId, guildId) => {
    if (!userId || !guildId) return console.log(`Error while performing function getUserGuildXPRank, userId or guildId missing...`);


    try {
        var userRank = 0;
        await connection.query(`SELECT \`user_id\`, \`user_xp_total\` FROM \`${guildId}\` WHERE \`user_xp_total\` > 0 ORDER BY \`user_xp_total\` DESC`)
            .then(([rows, fields]) => {
                if (rows < 1) return console.log(`[MYSQL  ] Function getUserGuildXPRank returned no result. Rows: ${rows}`);
                for (let i = 0; i < rows.length; i++) {
                    if (rows[i].user_id == userId) {
                        userRank = i + 1;
                        console.log(`DEBUG: userRank: ${userRank}`)
                    }
                }
            })
            .catch(err => console.log(`[MYSQL  ] Error while receiving user XP rank for user ${userId} in guild ${guildId}: ${err}`));
        return userRank;
    } catch (error) {
        console.log(`[XP    ] Couldn't perform function getUserGuildXPRank: ${error}`);
    }
}

This works fine now.