A Guide to Discord Bots

Storing Data Using SQL

You might wonder what's the advantage of using SQL over JSON.
This is simple: JSON uses FS, the built-in Node.js FileSystem module, to read and write data.
Writing and reading files with FS can lead to race conditions, which could corrupt your data.

The causes of race conditions are:

Writing AND reading the same file at once
Writing to a file multiple times at once (multi-threading)

For small bots, JSON can be prefered.
For larger bots, we tend to prefer to use a database.
SQL doesn't have this race condition issue, and that's why I'll present it here.

We will use the sqlite package, which is available on NPM with npm i sqlite.
I recommend downloading DB Browser for SQLite for debugging and testing.

Set up the Database

const sql = require('sqlite');

sql.open('./data/myData.sqlite');
client.login('token');

client.on('ready', () => {
    /* Create a table named 'users' with three columns:
        |id|coins|vip|
        You should only need this once
        You can alternatively use DB Browser to set up the table
    */
    sql.run('CREATE TABLE IF NOT EXISTS users (id TEXT, coins INTEGER, vip INTEGER)');
});

The following will use this example table
Note: I'm using the command system we saw on 'Getting Started/Prefix & More Commands'.

Adding and getting values

const sql = require('sqlite');

sql.open('./data/myData.sqlite');
client.login('token');

client.on('message', message => {
    switch (command) {
        case 'balance': {
            /* Using the character ` instead of ' lets you use variables and code
               more conveniently using the syntax ${}
               ${message.author.id} and ${userID} (if you have a 'userID' variable)
               both work
            */
            sql.get(`SELECT * FROM users WHERE id='${message.author.id}'`).then(row => {
                // If row not found, create it
                if (!row) {
                    sql.run('INSERT INTO users (id, coins, vip) VALUES (?, ?, ?)', [message.author.id, 0, 0]);
                    console.log(`Created row for user ${message.author.id}.`);
                }
                else
                    console.log(`Got row for user ${message.author.id}:  ${row}`);
            });
        }
    }
});

Updating values

const sql = require('sqlite');

sql.open('./data/myData.sqlite');
client.login('token');

client.on('message', message => {
    switch (command) {
        case 'getcoins': {
            // You should add conditions here
            // to avoid coin farming

            sql.get(`SELECT * FROM users WHERE id ='${message.author.id}'`).then(row => {
                sql.run(`UPDATE users SET coins=${row.coins + 1} WHERE id='${message.author.id}'`);
                // Conditional operator
                console.log(row.coins == 1 ? 'You now have your first coin!'
                    : 'You now have ' + (row.coins + 1) + ' coins!');
            }).catch(() => {
                // User not in the DB! Create a row for them.
                sql.run('INSERT INTO users (id, coins, vip) VALUES (?, ?, ?)', [message.author.id, 0, 0]).then(() => {
                    // Try again
                    sql.run(`UPDATE users SET coins=1 WHERE id='${message.author.id}'`);
                    console.log('You now have your first coin!');
                });
            });
        }
    }
});

Conditional operator.

Deleting values

const sql = require('sqlite');

sql.open('./data/myData.sqlite');
client.login('token');

client.on('message', message => {
    switch (command) {
        case 'delete': {
            // Usage: *delete @user
            //        *delete user_id

            if (message.author.id == message.guild.ownerID)
                sql.run(`DELETE FROM users WHERE id='${args}'`).catch(() => {
                    // Not found, nothing to delete
                });
        }
    }
});

Going Deeper

const sql = require('sqlite');

sql.open('./data/myData.sqlite');
client.login('token');

client.on('ready', () => {
    // Consider the following table
    // |id|coins|infos|
    sql.run('CREATE TABLE IF NOT EXISTS users (id TEXT, coins INTEGER, infos TEXT)');
});

client.on('message', message => {
    switch (command) {
        case 'update': {
            updateUserInfo(message.author.id).then(obj => console.log(obj));
            break;
        }
    }
});

// Asynchronous functions fit in well for this
// Check 'Async/Await' for informations about them
async function updateUserInfo(userID) {
    let storedObj = {};

    // '.then(async row => {}'
    // You can make promises asynchronous! (which is awesome)
    await sql.get(`SELECT * FROM users WHERE id=${userID}`).then(async row => {
        if (!row) {
            await sql.run('INSERT INTO users (id, coins, infos) VALUES (?, ?, ?)', [userID, 0, '{}']);
            storedObj = { id: playerID, coins: 0, infos: '{}' };
        }
        else
            storedObj = row;
    });

    // We stored 'infos' as a string, so we convert it to an object
    // to edit it
    storedObj.infos= JSON.parse(storedObj.infos);
    storedObj.infos.test = true;

    // make it into a string to store it
    await sql.run(`UPDATE users SET infos=${JSON.stringify(storedObj)} WHERE id=${userID}`);
    return storedObj;
}

Using SQL