How to use properly MySQL ?

[GreepTheSheep]

Hi, I've tried to use MySQL as custom database for my project. Here's my class

const GiveawayManager = class extends DiscordGiveaways.GiveawaysManager {
    async getAllGiveaways(){
        sql.query('SELECT `data` FROM `giveaways`', (err, res) => {
            if (err) {
                console.error(err)
                return false
            }
            return JSON.parse(res[0].data)
        })
    }
    async saveGiveaway(messageID, giveawayData){
        var okay = false
        await sql.query('SELECT `data` FROM `giveaways`', async (err, res) => {
            if (err) {
                console.error(err)
                return
            }
            var newdata = JSON.parse(res[0].data)
            newdata.push(giveawayData)
            await sql.query('UPDATE `giveaways` SET `data` = ? WHERE `id` = 1;', JSON.stringify(newdata), (err, res) => {
                if (err) {
                    console.error(err)
                    return
                }
                okay = true
            })
        })
        return okay
    }
    async editGiveaway(messageID, giveawayData){
        var okay = false
        await sql.query('SELECT `data` FROM `giveaways`', async (err, res) => {
            if (err) {
                console.error(err)
                return
            }
            var newdata = JSON.parse(res[0].data).filter((giveaway) => giveaway.messageID !== messageID)
            newdata.push(giveawayData)
            await sql.query('UPDATE `giveaways` SET `data` = ? WHERE `id` = 1;', JSON.stringify(newdata), (err, res) => {
                if (err) {
                    console.error(err)
                    return
                }
                okay = true
            })
        })
        return okay
    }
    async deleteGiveaway(messageID){
        var okay = false
        await sql.query('SELECT `data` FROM `giveaways`', async (err, res) => {
            if (err) {
                console.error(err)
                return
            }
            var newdata = JSON.parse(res[0].data).filter((giveaway) => giveaway.messageID !== messageID)
            await sql.query('UPDATE `giveaways` SET `data` = ? WHERE `id` = 1;', JSON.stringify(newdata), (err, res) => {
                if (err) {
                    console.error(err)
                    return
                }
                okay = true
            })
        })
        return okay
    }
};

When I start the script, it outputs this error:

 TypeError: Cannot read property 'forEach' of undefined
at GiveawayManager._init (/home/ao-bot-beta/node_modules/discord-giveaways/src/Manager.js:392:22)
at processTicksAndRejections (internal/process/task_queues.js:93:5)

What I missed? Thanks in advance

[GreepTheSheep]

Found in the web that you can't use return when there's a request (like sql queries), so i'll use a Promise like this:

async getAllGiveaways(){
        return new Promise(function (resolve, reject) {
            sql.query('SELECT `data` FROM `giveaways`', (err, res) => {
                if (err) {
                    console.error(err)
                    reject(err);
                }
                resolve(JSON.parse(res[0].data));
            })
        });
    }

I'll close the issue because this was fixed. If anyone has questions with mysql, open this issue

[Nico105]

Could you maybe provide your initialization code/an example for initializing MYSQL like you can see in the ss for quick.db? And did you change anything else in your code which I see in this issue until now? I'm asking both of this so I can add your extended class to the examples for custom DB usage in the README, because if it's there it's there, when I make a pr for many other things in the future.

[GreepTheSheep]

const MySQL = require('mysql')
const sql = MySQL.createConnection({
    host     : config.mysql.host,
    user     : config.mysql.user,
    password : config.mysql.password,
    database : config.mysql.database
})
sql.connect((err)=>{
    if (err){
        console.error('Impossible to connect to MySQL server. Code: ' + err.code)
        process.exit(99)
    } else {
        console.log('[SQL] Connected to the MySQL server! Connexion ID: ' + sql.threadId)
    }
})

// Create giveaways table
sql.query("CREATE TABLE IF NOT EXISTS `giveaways` (`id` INT(1) NOT NULL AUTO_INCREMENT, `message_id` VARCHAR(64) NOT NULL, `data` JSON NOT NULL, PRIMARY KEY (`id`));", (err, res) => {
        if (err) console.error(err)
        console.log('[SQL] Created table `giveaways`')
    })

Then call MySQL scripts with there things, for me it creates new entries when a new giveaway is set, and to list all giveaways it selects all then reunite in a array:

const DiscordGiveaways = require("discord-giveaways");
const GiveawayManager = class extends DiscordGiveaways.GiveawaysManager {
    async getAllGiveaways(){
        return new Promise(function (resolve, reject) {
            sql.query('SELECT `data` FROM `giveaways`', (err, res) => {
                if (err) {
                    console.error(err)
                    reject(err);
                }
                var all = []
                res.forEach(r=>{
                    all.push(r.data)
                })
                resolve(all);
            })
        });
    }
    async saveGiveaway(messageID, giveawayData){
        return new Promise(function (resolve, reject) {
            sql.query("INSERT INTO `giveaways` (`message_id`, `data`) VALUES (?,?)", [messageID, JSON.stringify(giveawayData)], (err, res) => {
                if (err) {
                    console.error(err)
                    reject(err);
                }
                resolve(true);
            })
        })
    }
    async editGiveaway(messageID, giveawayData){
        return new Promise(function (resolve, reject) {
            sql.query('UPDATE `giveaways` SET `data` = ? WHERE `message_id` = ?', [JSON.stringify(giveawayData), messageID], (err, res) => {
                if (err) {
                    console.error(err)
                    reject(err);
                }
                resolve(true);
            })
        })
    }
    async deleteGiveaway(messageID){
        return new Promise(function (resolve, reject) {
            sql.query('DELETE FROM `giveaways` WHERE `message_id` = ?', messageID, (err, res) => {
                if (err) {
                    console.error(err)
                    reject(err);
                }
                resolve(true);
            })
        })
    }
};

[Nico105]

Thanks a lot. Lmk if you ever make some bug-fixing changes to it. Future users of this module will be thankful to you.