Discord.js with Sequelize and MySQL

Note! This assumes that you have a working bot, that uses the command handling from: https://discordjs.guide/command-handling/#individual-command-files, that you use Visual Studio Code (VSC from here) as the editor of choice. (This is what I use, and it’s a free and powerful editor for many languages.) and finally that you have a working running MySQL database you can connect to.

So, you want to add a database to your Discord bot? That’s probably a very good idea, because that eliminates the use of hard coded strings in your config and/or command files. Let’s try and get you going! First, in VSC open a new terminal, and install the following packages: npm install –save sequelize npm install –save mysql npm install –save mysql2. This installs the packages require to use MySQL and Sequelize with you Discord.js bot.

First you need to connect to your database, which you can do in a file, let’s call it database.js, inside a new folder on your source level, called database:

const { Sequelize } = require('sequelize');

module.exports = new Sequelize(process.env.DB_NAME, process.env.DB_USER, process.env.DB_PASS,{
    host: process.env.DB_SERVER,
    dialect: 'mysql'
});

As you can see, I use environmental variables for my connection, so that I don’t have them hard coded. You can replace these with strings, if you want to make it simple. (I use Heroku as a host, and set these variables in the dashboard of Heroku.)

Further, you have to connect to the DB, and let’s do that in the file: index.js (which is my main source file).

//First, lets require the database definition file:
const db = require('./database/database');
//From here we need to log in, and the way I do it is via the client.on('ready') event
client.on('ready', async () => {
    console.log(`Client is logged in and ready!`);
    db.authenticate()
        .then(() => {
            console.log('Logged in to DB!');
        })
        .catch(err => console.log(err));
});

This code logs in to you DB specified in database.js and logs to console that it has logged in to DB. (Or logs error if error occurs.)

On you source level, make a new folder called models. This is where you make models of your tables.
First off, let’s start with a model called config.js:

const { DataTypes, Model } = require('sequelize');

module.exports = class config extends Model {
    static init(sequelize){
        return super.init({
            configId: {
                type: DataTypes.INTEGER,
                autoIncrement: true,
                primaryKey: true
            },
            guildId: { type: DataTypes.STRING },
            prefix: { type: DataTypes.STRING },
            welcomechannel: { type: DataTypes.STRING },
            logchannel: { type: DataTypes.STRING },
        }, {
            tableName: 'config',
            timestamps: true,
            sequelize
        });
    }
}

Study this code, in order to understand how it works and what it does. Basically it creates a table called config and it has 5 columns, configId, guildId, prefix, welcomechannel and logchannel. I have also added timestamps: true, to see timestamps of created and updated. configId is the primary key and will increment by one, for every record in the table. The remaining 4 columns can be created and/or updated as needed.

To create this table, we go back to index.js and the on(‘ready’) event. It should now look something like this:

client.on('ready', async () => {
    console.log(`Client is logged in and ready!`);
    db.authenticate()
        .then(() => {
            console.log('Logged in to DB!');
            config.init(db); // initiates the table config
            config.sync(); //creates the table, if it doesn't already exist
        })
        .catch(err => console.log(err));
});

I you in you mysql console and using your selected database type: show tables; you should find config as a new table. You can further type: describe config; and that shows how the table is set up and what columns it holds.

Congratulations, you have now connected your bot to your MySQL database and have successfully created your first table.
In a future post (give me a couple of days) I will write a new section, where I show you have to search, create and update records in your table.

If you have comments, questions or suggestions, please leave them in a comment to this post.