Alright so, I’ve been working with Discord.js for a while now, and I’ve never really been working with databases.
Although, with recent experience, I’ve found out that I would need to learn it.
I’ve usually just used setTimeout()s to have the bot automatically do a action after the time has run out.
So I do know how I will set the time, but my problem is just the databases.
Let’s say I mute someone for 1 hour, what I want to do is to store the data like this is a json file
{
{
“username1”: “ms”
}
{
“username2”: “ms”
}
}
and then in a runable file, I would have a setInterval() to run the code, every 10-60 seconds, finding the current real life time ms and then comparing it to every username’s ms in the json file.
If it is equal or greater than, it would run the rest of the code, resulting in the username getting the muted role removed and the json object with the username and time ms getting deleted from the json file.
Now I do not know how to do this with a database, since well, I haven’t worked with databases.
I don’t need help getting and comparing the time ms.
I just need help storing the username and time ms, comparing the item in every object in the json file separately, storing the object name and the time ms as variables of course, and deleting that one object alone.
And all this without accidentally taking the wrong data or deleting or adding anything to someone else’s object.
I just realized phone didn’t catch me writing “guild” and it messed up the message.
I use fs and would need to learn how properly store the data in a JSON file, get and compare all the items separately and get the ms from the username.
The rest, I should be able to do it myself.
If you are storing lots of data, I would use a SQL database (like sqlite) or a NoSQL database (like MongoDB).
Personally, I wouldn’t store the data in the container due to disk limits. Currently I’m using Google’s Cloud Firestore (1GB data for free!), MongoDB atlas is a good alternative but only gives you 500MB of free quota/data.
I wouldn’t use JSON files for saving all of your data, this is because JSON isn’t designed to be read/written to often and could cause file corruption (hasn’t happened to me yet thankfully!).
So I’ve just been trying to do it with MySQL, but I just keep getting this error - The error is modified a bit to not expose the ip address and port of the server hosting the database.
{ Error: connect ECONNREFUSED real ip address : real port
at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1191:14)
--------------------
at Protocol._enqueue (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/mysql/2.17.1/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Protocol.handshake (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/mysql/2.17.1/node_modules/mysql/lib/protocol/Protocol.js:51:23)
at Connection.connect (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/mysql/2.17.1/node_modules/mysql/lib/Connection.js:119:18)
at Object.execute (/app/commands/warnings.js:30:16)
at Client.client.on.message (/app/apocryptix.js:34:13)
at emitOne (events.js:121:20)
at Client.emit (events.js:211:7)
at MessageCreateHandler.handle (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/discord.js/11.5.1/node_modules/discord.js/src/client/websocket/packets/handlers/MessageCreate.js:9:34)
at WebSocketPacketManager.handle (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/discord.js/11.5.1/node_modules/discord.js/src/client/websocket/packets/WebSocketPacketManager.js:105:65)
at WebSocketConnection.onPacket (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/discord.js/11.5.1/node_modules/discord.js/src/client/websocket/WebSocketConnection.js:333:35)
errno: 'ECONNREFUSED',
code: 'ECONNREFUSED',
syscall: 'connect',
address: 'real ip address',
port: real port,
fatal: true }
As I’ve meantion earlier, I have never worked with databases in the past, so I do not understand properly what the error really means.
Here is my file,
const Discord = require('discord.js');
const mysql = require('mysql');
module.exports = {
name: "warnings",
description: "Check the warnings of a member.",
aliases: ['warns'],
usage: '<mention>',
category: 'mod',
guildOnly: true,
execute(client, message, args) {
if (!message.member.hasPermission('MANAGE_MESSAGES')) {
return message.reply(`You can't check warnings...`);
}
let User = message.mentions.users.first();
let userID = User.id;
let connection = mysql.createConnection({
host: process.env.MYSQLWHOST,
user: process.env.MYSQLWNAME,
password: process.env.MYSQLWPASS,
database: process.env.MYSQLWNAME
});
connection.connect(error => {
console.log('Connected')
});
connection.query(`SELECT * FROM message WHERE id = '${userID}'`, (error, rows) => {
if (error) console.log(error)
console.log(rows)
let messages;
if (rows.length < 1) {
messages = `${User.username} got no warnings`;
return message.channel.send(messages);
}
return message.channel.send(`${User.username} has ${rows.length} warnings.\n${messages}`);
});
}
}
And what I get from the console.log(rows) is just undefined.
@Aprixia thanks, now I can help you with some things. I didn’t use MySQL with Node Js but I did long time ago with PHP
I can see you made the connection to the database in your warn file. This connection should be before the client.login(token) function.
So inside your main.js or bot.js or index.js (whatever is called your main file) add this:
/* MYSQL CONNECTION */
let connection = mysql.createConnection({
host: process.env.MYSQLWHOST,
user: process.env.MYSQLWNAME,
password: process.env.MYSQLWPASS,
database: process.env.MYSQLWNAME
});
connection.connect(error => {
if(error) {
console.error(`error connecting MySQL: ${error.stack}`);
return;
} else {
console.log(`connected to MySQL as id ${connection.threadId}`);
}
});
Take out this functions from your warn file
The bot is going to be connected to the database. Then you should send this connection object to your warn file and at that point you will be able to do connection.query()
warn.js
module.exports = {
name: "warnings",
description: "Check the warnings of a member.",
aliases: ['warns'],
usage: '<mention>',
category: 'mod',
guildOnly: true,
execute(client, message, args, db) { // Note I added connection like 'db'
if (!message.member.hasPermission('MANAGE_MESSAGES')) {
return message.reply(`You can't check warnings...`);
}
let User = message.mentions.users.first();
let userID = User.id;
db.query(`SELECT * FROM message WHERE id = '${userID}'`, (error, rows) => {
if (error) console.log(error)
let messages;
if (rows.length < 1) {
messages = `${User.username} got no warnings`;
return message.channel.send(messages);
}
return message.channel.send(`${User.username} has ${rows.length} warnings.\n${messages}`);
});
}
}
error connecting MySQL: Error: connect ECONNREFUSED the ip address
at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1191:14)
--------------------
at Protocol._enqueue (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/mysql/2.17.1/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Protocol.handshake (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/mysql/2.17.1/node_modules/mysql/lib/protocol/Protocol.js:51:23)
at Connection.connect (/rbd/pnpm-volume/47e93fa1-b54a-41de-a797-9454cd7e7b38/node_modules/.registry.npmjs.org/mysql/2.17.1/node_modules/mysql/lib/Connection.js:119:18)
I think there is something about the .env values that are not correct, but like, I do not know exactly what is supposed to be in each field of the connection.
What I got in there is pretty much this
MYSQLWHOST=secret.awardspace.net
MYSQLWNAME=numbers that I am unsure if it is safe to expose_apocryptixbotwarnings
MYSQLWPASS=secret
Sorry about the late response, I am on a little vacation, so I am not that much on the forums or the development.
Anyways, so,
I just checked and the website I am using to host my database says nothing about any localhost, nor configuration in the MySQL server.
I checked the database information, and the information that was listed there was
Database name and user was equal, Database Host is what I got in the host variable, I do not use the Database Port anywhere.
I don’t know if I am supposed to use the Database port, but it isn’t being used.
And the IP, I checked that, before I created this thread, by going into the command prompt and pinging the host url and checking if the IP was the same as in the logs.