I have made a cooldowns database with sequalize and sqlite3 for my discord bot. This is to add individual cooldowns for certain commands however I am getting an error that says "id must be unique" even when I turn off the unique: true to unique: false or even make a new database storage for the other command. Below is the full error.
node:internal/process/promises:246
triggerUncaughtException(err, true /* fromPromise */);
^
Error
at Database.<anonymous> (C:\Users\brade\Desktop\Bear battles\node_modules\sequelize\lib\dialects\sqlite\query.js:179:27)
at C:\Users\brade\Desktop\Bear battles\node_modules\sequelize\lib\dialects\sqlite\query.js:177:50
at new Promise (<anonymous>)
at Query.run (C:\Users\brade\Desktop\Bear battles\node_modules\sequelize\lib\dialects\sqlite\query.js:177:12)
at C:\Users\brade\Desktop\Bear battles\node_modules\sequelize\lib\sequelize.js:311:28
at async SQLiteQueryInterface.insert (C:\Users\brade\Desktop\Bear battles\node_modules\sequelize\lib\dialects\abstract\query-interface.js:308:21)
at async model.save (C:\Users\brade\Desktop\Bear battles\node_modules\sequelize\lib\model.js:2432:35)
at async Function.create (C:\Users\brade\Desktop\Bear battles\node_modules\sequelize\lib\model.js:1344:12) {
name: 'SequelizeUniqueConstraintError',
errors: [
ValidationErrorItem {
message: 'id must be unique',
type: 'unique violation',
path: 'id',
value: '250412979835764738',
origin: 'DB',
instance: cooldown {
dataValues: {
id: '250412979835764738',
expiry: 1655869677206,
command: 'hunt',
updatedAt: 2022-06-22T03:42:57.207Z,
createdAt: 2022-06-22T03:42:57.207Z
},
_previousDataValues: { id: undefined, expiry: undefined, command: undefined },
uniqno: 1,
_changed: Set(3) { 'id', 'expiry', 'command' },
_options: {
isNewRecord: true,
_schema: null,
_schemaDelimiter: '',
attributes: undefined,
include: undefined,
raw: undefined,
silent: undefined
},
isNewRecord: true
},
validatorKey: 'not_unique',
validatorName: null,
validatorArgs: []
}
],
parent: [Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: cooldown.id] {
errno: 19,
code: 'SQLITE_CONSTRAINT',
sql: 'INSERT INTO `cooldown` (`id`,`expiry`,`command`,`createdAt`,`updatedAt`) VALUES ($1,$2,$3,$4,$5);'
},
original: [Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: cooldown.id] {
errno: 19,
code: 'SQLITE_CONSTRAINT',
sql: 'INSERT INTO `cooldown` (`id`,`expiry`,`command`,`createdAt`,`updatedAt`) VALUES ($1,$2,$3,$4,$5);'
},
fields: [ 'id' ],
sql: 'INSERT INTO `cooldown` (`id`,`expiry`,`command`,`createdAt`,`updatedAt`) VALUES ($1,$2,$3,$4,$5);'
}
Here is the code for my daily command.
const { SlashCommandBuilder } = require('@discordjs/builders')
const ms = require('ms')
const { defaultColor } = require('../../command-imports')
module.exports = {
data: new SlashCommandBuilder()
.setName('daily')
.setDescription('Claim your daily reward.'),
async execute (interaction, Cooldowns) {
let getCooldown = await Cooldowns.findOne({where: {id: interaction.user.id}}) // Finds if user has cooldown
let cooldownTime = getCooldown?.expiry
if(getCooldown && cooldownTime > new Date().getTime()) { // If cooldown is active run this
return interaction.reply({content: `You are still under cooldown! Please wait **${ms(cooldownTime - new Date().getTime(), {long: true})}**`})
} else if (getCooldown) { // If cooldown is expired remove from db and run rest of code
Cooldowns.destroy({where: {id: interaction.user.id, command: 'daily'}})
}
const claimedDaily = {
color: defaultColor,
description: "You have recieved N/A from your daily reward"
}
interaction.reply({embeds: [claimedDaily]})
Cooldowns.create({ // Creates 5 minute cooldown for hunt command
id: interaction.user.id,
expiry: new Date().getTime() (60000 * 5),
command: 'daily'
})
}
}
Here is my hunt command.
const { SlashCommandBuilder } = require('@discordjs/builders')
const { errorColor, defaultColor } = require('../../command-imports')
const ms = require('ms')
module.exports = {
data: new SlashCommandBuilder()
.setName('hunt')
.setDescription('Hunt for a chance at finding a bear.'),
async execute(interaction, Cooldowns, Economy) {
let getCooldown = await Cooldowns.findOne({where: {id: interaction.user.id, command: 'hunt'}}) // Finds if user has cooldown
let getUser = await Economy.findOne({where: {id: interaction.user.id}})
if(!getUser) {
getUser = await Economy.create({id: interaction.user.id, coins: 0})
}
let cooldownTime = getCooldown?.expiry
if(getCooldown && cooldownTime > new Date().getTime()) { // If cooldown is active run this
return interaction.reply({content: `You are still under cooldown! Please wait **${ms(cooldownTime - new Date().getTime(), {long: true})}**`})
} else if (getCooldown) { // If cooldown is expired remove from db and run rest of code
Cooldowns.destroy({where: {id: interaction.user.id, command: 'hunt'}})
}
let whichExecute = Math.floor(Math.random() * 8) 1 // 7/8 Chance for coins 1/8 Chance for bears
if(whichExecute <= 7) {
let coinsFound = Math.floor(Math.random() * 10) 1 // Picks random coin amount between 1 - 10
const nothingFound = {
color: errorColor,
description: `No bear was found however you found ${coinsFound} :coin: \n You have ${getUser.coins} :coin:` // Displays coins earned and total coins
}
interaction.reply({embeds: [nothingFound]})
await Economy.update({coins: getUser.coins coinsFound}, {where: {id: interaction.user.id}}) // Updates user in db
Cooldowns.create({ // Creates 5 minute cooldown for hunt command
id: interaction.user.id,
expiry: new Date().getTime() (60000 * 5),
command: 'hunt'
})
}
else if(whichExecute === 8) {
const bearFound = { // Displays bear found
color: defaultColor,
description: "You found placeholder_beartype :bear:;;"
}
interaction.reply({embeds: [bearFound]})
Cooldowns.create({ // Creates 5 minute cooldown for hunt command
id: interaction.user.id,
expiry: new Date().getTime() (60000 * 5),
command: 'hunt'
})
}
}
}
I still get the error for unique id even though it should be stored with the same id but under a different command name. This error doesn't appear until I run both commands (doesn't matter the order) for example I do /hunt and then do /daily later on. Any help fixing this would be great if you have any questions or stuff I can clarify let me know.
CodePudding user response：
Note: This code is written for Discord.js v13.7.0 and Sequelize v6
Improperly altering tables
<Sequelize>.sync() is not executed with alter or force
Sequelize, according to their v6 documentation, provides a function called sync(). This function is used to ensure that your models are up to date with the database. However, there is one caveat to this. If you execute sync() with no arguments, the database will not overwrite existing data. This is where your issue stems from. When you first defined the models, most likely you did the following two things:
- Defined
idasDataTypes.INTEGER - Set
idto be a unique field
Due to these and you executing .sync() with no arguments, the database's tables will not be overwritten, therefore preserving the old unique fields. Also, if you attempt to store a Discord ID as an Integer, you may encounter an issue where Discord IDs are shortened or rounded.
Alternatively, you can manually drop the tables from sqlite3 using this command and rerun the bot without modifying sync() which will create the table with the right data:
DROP TABLE 'Cooldowns'
Solutions
Fixing outdated tables
In order to fix the outdated tables, you have two options. However, be careful as these are destructive actions. You can execute the sync() command with the following arguments:
<Sequelize>.sync({ alter: true }); // Alters tables
<Sequelize>.sync({ force: true }); // Forcefully recreates tables
As stated before, be careful with these actions as they are destructive and cannot be reverted if you do not have backups.
Properly storing Discord IDs
All you need to do is store the Discord IDs as a DataTypes.STRING or DataTypes.TEXT. This will preserve the Snowflake form of the ID and prevent shortening.
sequelize.define("User", {
id: {
type: DataTypes.STRING, // Or .TEXT
unique: false
},
// ...
});