How to Create a Currency and Inventory System in Discord.js

Question

So I've been trying to make a currency system and an inventory system for my Discord bot for the past few days. However, I can't seem to make any progress at all. I've tried using sequelize and better-sqlite3 (which is what I'm currently using), all with no success.

const table = sql.prepare("SELECT count(*) FROM sqlite_master WHERE type ='table' AND name = 'balance';").get();
if (table["count(*)"] === 0) {
  console.log("creating table...");
  sql.prepare("CREATE TABLE scores (id TEXT PRIMARY KEY, user TEXT, guild TEXT, money INTEGER);").run();
  sql.prepare("CREATE UNIQUE INDEX idx_scores_id ON scores (id);").run();
  sql.pragma("synchronous = 1");
  sql.pragma("journal_mode = wal");
}

client.getScore = sql.prepare("SELECT * FROM balance WHERE user = ? AND guild = ?");
client.setScore = sql.prepare("INSERT OR REPLACE INTO money (id, user, guild, money) VALUES (@id, @user, @guild, @money);");

The above code results in this error:

(node:13036) UnhandledPromiseRejectionWarning: SqliteError: table scores already exists

I don't have that much knowledge on stuff like this since there is so little material out there for me to refer to.

Answer 1

This is a bad approach to validate tables. You should use IF NOT EXISTS instead. See this question for reference:

From http://www.sqlite.org/lang_createtable.html:

CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, ...);

You can also use IF NOT EXISTS for your index.

In the future I recommend focusing on the specific technology you're having issues with when researching and asking questions. There is in fact plenty of material out there about proper usage patterns for sqlite - but you have to search and ask specifically for sqlite.

In this question, for example, you ask about making a currency and inventory system in discord.js, but there is no discord.js code here, the error is a sqlite error, and searching for "how to create table only if it doesn't exist sqlite" would have brought you to the links above. In short, search for the specific technology you're using, not for the whole project.

That being said, there is also an "official unofficial" resource on creating a currency system with discord.js here: https://discordjs.guide/sequelize/currency.html - but it uses sequelize, so you may not find it as helpful.