I haven’t really worked with databases, but I need to learn it

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.

Thank you,

Well there is some databases and frameworks you could use.

SQLite3 will make a database into your server

or you can store data with MongoDB wich you can use a free Sandbox for your little application


For this one you should use mongoose wich is easy to learn

It’s the same dynamic of your filesystem (fs) for save the usernames in the json file but with the database:

give mute => store username, guild and ms
check mutes => search that ones who has equal or greater ms and unmute them

The not is private, so no need to store the guild.

Perfect, so you don’t need to store the guild.

If you have a particular doubt about one of that frameworks we can help you to solve it!

  1. I just realized phone didn’t catch me writing “guild” and it messed up the message.
  2. 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!).

1 Like

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.

@zApexio 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}`);
	});
  }
}

Oh, I thought I had to include the connection code in every file I were to use it in.
Thank you.

Alright, so, I am actually getting another error,

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

It is your username the same as your database name?

If it doesn’t you should have two differents variables there.

MYSQLWHOST=host.mysql.net
MYSQLWUSER=userofdatabase
MYSQLWPASS=passwordofdatabase
MYSQLWNAME=nameofdatabase

Then in connection

let connection = mysql.createConnection({
	host: process.env.MYSQLWHOST,
	user: process.env.MYSQLWUSER,
	password: process.env.MYSQLWPASS,
	database: process.env.MYSQLWNAME
});

Oh, I thought it required the database user and password

Ok, nevermind, they are the same.
The database user and the database name is the exact same.

I tried with my Discord bot and it worked.

I don’t know about that error. But I read in someplaces maybe is because the MySQL server only listen local connections.

There is a way to change the configuration in your MySQL server? You need set to listen all IP

Just to make sure: Are you trying to run the MySQL server in the localhost?

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 Host: 
Database Port: 
Database Name: 
Database User: 
Database Password:
Database Version: 5.7
Database Storage Engine:

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.