javascriptnode.jsdiscorddiscord.jsbetter-sqlite3

SQLite error "No such column" when I can see that the column exists


The backstory here is that I'm working on a Discord bot. One of the requested functions is having it lob insults. I thought it would be fun to give users the ability to add insults via a SQLite 3 database (specifically, better-sqlite3), and in doing so, I also want to keep track of the most recent use of each insult (this is for future use in an "insultinfo" type command). I added a DATETIME column called "lastUsed". My insultadd command is able to write to this column when new insults are added. The issue is when I call the insult command, it's supposed to say the insult and then update the lastUsed field with the current date. Pasting the code below.

The problem is that I'm getting an error that says "SQLiteError: no such column:" and then it prints the date value even though I'm trying to update the lastUsed column and I can't figure out how to resolve this. The issue has to be in the db.prepare statement, I'm just not seeing what I need to do to fix it.

execute(msg, args) {
    const SQLite = require("better-sqlite3");
    const db = new SQLite('./userinputs.sqlite');
    // Check if the table "userinputs" exists and has content from this guild.
    const table = db.prepare(`SELECT count(*) FROM userinputs WHERE (guild = ${msg.guild.id} OR guild = 'Global') AND type = 'insult';`).get();
    if (!table['count(*)']) {
        return msg.channel.send("I don't have any insults yet");
    }
    var date = new Date();
    const rawInsult = db.prepare(`SELECT * FROM userinputs WHERE type = 'insult' AND (guild = ${msg.guild.id} OR guild = 'Global') ORDER BY RANDOM() LIMIT 1;`).get();
    const insult = rawInsult['content'];
    const insultID = rawInsult['row'];
    if (args[0]) {
        var target = args[0];
    } else {
        var target = msg.author.username;
    }
    if (insult.includes('{}')) {
        var finalInsult = insult.replace('{}', target);
    } else {
        var finalInsult = target + ' ' + insult;
    }
    msg.channel.send(finalInsult);
    db.prepare(`UPDATE userinputs SET lastUsed = "${date}" WHERE row = ${insultID};`).run();
},

Solution

  • I think I got it. I ended up using date.toString() and it appears to have done the job. The error message I got after updating the db.prepare statement indicated it wasn't viewing that date variable as something it could work with, hence the .toString() workaround. I'll have to test this to see if that impacts my ability to sort on that column, since this is still all a pretty new to me, but at least the command itself is working and the database table is updating.

    I'm open to more feedback though if anyone sees the probable folly in my ways here.

    UPDATE: Upon further testing, setting date = Math.floor(new Date() / 1000) ended up working better in my case.