Discord.js and MySQL – searching and updating records

This article assumes that you have already read the article: https://codelog.network/2020/08/14/discord-js-with-sequelize-and-mysql/ and that you have followed those steps to connect to you MySQL database. The console.log() should have output Logged in to DB. From there we will in this article search for your guilds prefix and update accordingly. This is a rather quick process, so the article will not be lengthy. Let’s begin!

In your index.js file, more precisely in the section: client.on(‘message’, async message => { – we assume that you have made commands into a constant, and that you are passing arguments as a variable.

if (command === 'prefix') {
    if (!message.member.hasPermission(`ADMINISTRATOR`)) { // Checks to see if the user issuing the command is the Admin of the current guild.
        message.channel.send(`You don't have permission to use this command!`); //Non-Admins get this error message in return.
        return;
    } else {
        newPrefix = args[0]; //This is refers to the first argument stated after your command: 'prefix'. Ex: g$prefix !    This will set args[0] to an exclamation mark.
        const guildID = message.guild.id; //This is your guilds ID, so you can have different prefixes for different guilds.
        const conf = await config.findOne({ //findOne finds the first occurrence of guildId = guildID. Note the await, so this needs to be in an async function
            where: {
                guildId: guildID //This is the where clause used in the SQL query i Sequelize
            }
        });
        //Now we need to use a number of if - else if statements, to do some checks and verifications
        if (conf && conf.prefix === newPrefix) { //This checks if there was a hit i the database, and whether or not the new prefix is the same as the current.
            message.channel.send(`This is already your prefix!`); //New prefix = Current prefix, so no change is necessary.
            return;
        } else if (conf && conf.prefix !== newPrefix) { //There was a hit in the database, and the new prefix is different from the current
            let updateValues = { prefix: newPrefix }; //This is the update we wish to perform to the database record.
            config.update(updateValues, { where: { configId: conf.configId, guildId: guildID } }); //The actual updating of the record. Note that we use where configId = conf.configId and guildId = guildID.
            message.reply(`Prefix is updated!`); //Simple return message to confirm update performed.
        } else if (!conf) { //This is the way to create a new record in the database. The: !conf states that there were no hits for this guild in the database, so we need to create one.
            config.create({
                guildId: guildID, //guildId = guildID - sets the column guildId to be this message's guildID
                prefix: newPrefix //prefix = newPrefix - sets the column prefix to be that of newPrefix which is also args[0].
            });
            message.channel.send(`Config created and prefix entered!`);
        }
    }

This is basically it, not much more hocus pokus. This is a simple way to do it, and should get you started using Discord.js Sequelize and MySQL.
I will be posting more snippets and code, but for now I think this should have gotten you going. Most of the code explanation is in comments in the code above, that’s why the article isn’t longer.

If you have questions about the code, commands or features; please leave it in a comment below or join my Discord server for support.