MySQL with Typescript doesn't store multiple data - discord.js

Question

Query

I'm working on a messages tracking discord bot using typescript with discord.js. I've provided my code below. The issue here, is that it doesn't save the data. When a user sends messages, it stores his message count, but when another user sends messages in the same channel, the database stores his messages, and makes the first user's count as 0.

Code

// user-Schema.ts
import type { SapphireClient } from '@sapphire/framework';
import type { ModelStatic, Model } from 'sequelize';
import Sequelize from 'sequelize';

interface UserAttributes {
    guild: string;
    user: string;
    msgs: number;
}

interface UserInstance extends Model, UserAttributes {}

export class UserModel {
    client: SapphireClient;
    raw!: ModelStatic;

    constructor(client: SapphireClient) {
        this.client = client;
    }

    async init() {
        const db = this.client.sql.define('electra.users', {
            guild: {
                type: Sequelize.STRING,
                primaryKey: true,
                unique: true
            },
            user: {
                type: Sequelize.STRING,
                primaryKey: true
            },
            msgs: {
                type: Sequelize.INTEGER
            }
        });
        this.raw = db;
        await this.raw.sync();
        this.client.logger.info(`Synced UserModel`);
    }
}

// messageCreate.ts
const data = await this.container.client.userDB.raw.findOne({
            where: {
                guild: msg.guildId,
                user: msg.author.id
            }
        });

const msgs = data === null ? 0 : data.msgs;

await this.container.client.userDB.raw.upsert({
    guild: msg.guildId as string,
    user: msg.author.id,
    msgs: msgs + 1
});

// index.ts
import { UserModel } from './models/user-Schema';
const sequelize = new sql.Sequelize(process.env.DB_NAME!, process.env.DB_USER!, process.env.DB_PASS!, {
    logging: false,
    dialect: 'mysql'
});

client.sql = sequelize;
client.userDB = new UserModel(client);

// ready.ts
await this.container.client.userDB.init()

Info

I'm using @sapphire/framework as my bot's framework. I'd be happy for any sort of help, and I'm ready to provide more information if asked.

Thanks.

user_1376618 · Answer

Sequelize does not support composite primary and foreign keys. You need to add autogenerated unique primary key as a single column. If you wish to upsert using user and guild column values to update a certain record then you need to add an unique index on them (at least it's supported in PostgreSQL, you need to check if it's supported in MySQL as well).

user_15748819 · Answer

Okay so I basically figured out a good and easy fix. Updated code is below:

const db = this.client.sql.define('electra.users', {
            guild: {
                type: Sequelize.STRING,
                primaryKey: true,
                unique: false
            },
            user: {
                type: Sequelize.STRING,
                primaryKey: true,
                unique: false
            },
            msgs: {
                type: Sequelize.INTEGER,
                unique: false,
                allowNull: false,
                primaryKey: false
            }
        });
        this.raw = db;
        await this.raw.sync();
        this.client.logger.info(`Synced UserModel`);

Alright time to explain the changes, unique must be false for those values that will keep changing. Since the bot is multi-guild, there will be multiple guild instances, and for each of them - there will be a lot of user instances, and for each of those users - their messages. Hence, all of these must not be unique.
This is kinda off-topic but I would highly suggest you all to drop your tables if you have made any changes to the model instance. This will apply the new changes and won't lead to errors.

Feel free to answer or post some information regarding this :D

MySQL with Typescript doesn't store multiple data - discord.js

2 Answers