DiscordJS Database Not Storing Information Ideally

Question

I'm trying to get the Discord bot to create a database that is basically the user map (a row for each user and columns for ID, nick name, avatar URL, etc) when it receives a !getdata command.

I've gotten to the point where the database successfully takes data, in this case the username and user ID, but it displays all the unique values in two columns as long comma separated values (i.e. the username column displays 'user1,user2,user3').

I'm sure this is by design, but I'm really struggling with restructuring. I'd like to either have it take all the data from an object map (client.users or message.guild.members) but I cannot figure it out.

The other option, which is what I'm trying now, is to create a row for each user and then fill in the values that I want to store, but I'm getting nowhere fast.

I'm very new with SQLite (and node/DiscordJS/JS for that matter), so any advice is greatly appreciated.

Index.js

const Discord = require('discord.js');
const client = new Discord.Client();
const sql = require('sqlite3');

let db = new sql.Database("users.sqlite", (err) => {
    if (err) {
        console.log('Error connecting to the database', err)
    } else {
        console.log('Database connected.')
    }
})

let token = process.env.CLIENT_TOKEN;
let prefix = process.env.PREFIX ;

client.on('ready', () => {
    console.log(`Logged in as ${client.user.tag}!`);
    db.run(`CREATE TABLE IF NOT EXISTS users(username TEXT, id TEXT)`);
})

client.on('message', function(message) {

    if (!message.content.startsWith(prefix));

    const args = message.content.slice(prefix.length).split(/ +/);
    const command = args.shift().toLowerCase();

    if (command === 'getdata') {
        let username = message.guild.members.map(m=>m.user.username);
        let userid = message.guild.members.map(m=>m.user.id);
        db.run(`INSERT OR REPLACE INTO users(username, id) VALUES(?,?)`, [`${username}`,`${userid}`]);
        return message.channel.send(`User database updated.`);
    }
});

client.login(token);

If you're curious as to the formatting or way things are written, the answer is two fold:

1. I'm pretty new at this
2. This was the only way I could get the values in the database to return something other than null

Thanks in advance,

Answer 1

First off, welcome to the site.

I hope that I can shine some light here without diving into refactoring your code or making you change anything major.

One thing sticks out to me as to why you are storing an array instead of a single value.

let username = message.guild.members.map(m=>m.user.username);
let userid = message.guild.members.map(m=>m.user.id);

The .map call returns an array, not a single value.

Each user that issues a command is part of the message object. If I remember correctly, you would want this to be something like...

(simplified version)

const { username, id } = message.member.user;
db.run(`INSERT OR REPLACE INTO users(username, id) VALUES(?,?)`, [username, id]);
// ...

User documentation can be found here

Edit:

If you wanted to build the database for all users in that one command you could do something like the following with a bulk insert... (quick and dirty)

db.serialize(() => {
  db.run('BEGIN TRANSACTION;');
  // execute inserts in transaction
  for (const m of message.guild.members) {
    db.run('INSERT OR REPLACE INTO users(username, id) VALUES(?,?);', [m.user.username, m.user.id]);
  }
  // commit all inserts :)
  db.run('COMMIT;')
});

message.channel.send('User database updated.');

Control flow documenation

Hopefully this points you in the right direction :)