如何在Discord.js中实现外部SQLite数据库文件？

需求概述 #

• 将SQLite数据库操作逻辑从主JavaScript文件中分离，实现代码解耦
• 实现用户发送消息时随机给予XP的功能

现有代码（用户提供） #

client.on("ready", () => {
  // Check if the table "points" exists.
  const table = sql.prepare("SELECT count(*) FROM sqlite_master WHERE type='table' AND name = 'scores';").get();
  if (!table['count(*)']) {
    // If the table isn't there, create it and setup the database correctly.
    sql.prepare("CREATE TABLE scores (id TEXT PRIMARY KEY, user TEXT, guild TEXT, points INTEGER, level INTEGER);").run();
    // Ensure that the "id" row is always unique and indexed.
    sql.prepare("CREATE UNIQUE INDEX idx_scores_id ON scores (id);").run();
    sql.pragma("synchronous = 1");
    sql.pragma("journal_mode = wal");
  }

  // And then we have two prepared statements to get and set the score data.
  client.getScore = sql.prepare("SELECT * FROM scores WHERE user = ? AND guild = ?");
  client.setScore = sql.prepare("INSERT OR REPLACE INTO scores (id, user, guild, points, level) VALUES (@id, @user, @guild, @points, @level);");
});

client.on("messageCreate", message => {
  if (message.author.bot) return;
  let score;
  if (message.guild) {
    score = client.getScore.get(message.author.id, message.guild.id);
    if (!score) {
      score = { id: `${message.guild.id}-${message.author.id}`, user: message.author.id, guild: message.guild.id, points: 0, level: 1 }
    }
    score.points++;
    const curLevel = Math.floor(0.1 * Math.sqrt(score.points));
    if (score.level < curLevel) {
      score.level++;
      message.reply(`You've leveled up to level **${curLevel}**! Ain't that dandy?`);
    }
    client.setScore.run(score);
  }
  if (message.content.indexOf(config.prefix) !== 0) return;

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

  if (command === "give") {
    // Limited to guild owner - adjust to your own preference!
    if (!message.author.id === message.guild.ownerId) return message.reply("You're not the boss of me, you can't do that!");
  
    const user = message.mentions.users.first() || client.users.cache.get(args[0]);
    if (!user) return message.reply("You must mention someone or give their ID!");
  
    const pointsToAdd = parseInt(args[1], 10);
    if (!pointsToAdd) return message.reply("You didn't tell me how many points to give...");
  
    // Get their current points.
    let userScore = client.getScore.get(user.id, message.guild.id);
  
    // It's possible to give points to a user we haven't seen, so we need to initiate defaults here too!
    if (!userScore) {
      userScore = { id: `${message.guild.id}-${user.id}`, user: user.id, guild: message.guild.id, points: 0, level: 1 }
    }
    userScore.points += pointsToAdd;
  
    // We also want to update their level (but we won't notify them if it changes)
    let userLevel = Math.floor(0.1 * Math.sqrt(score.points));
    userScore.level = userLevel;
  
    // And we save it!
    client.setScore.run(userScore);
  
    return message.channel.send(`${user.tag} has received ${pointsToAdd} points and now stands at ${userScore.points} points.`);
  }
  
  if (command === "leaderboard") {
    /*const top10 = sql.prepare("SELECT * FROM scores WHERE guild = ? ORDER BY points DESC LIMIT 10;").all(message.guild.id);*/
  
      // Now shake it and show it! (as a nice embed, too!)
    const embed = new EmbedBuilder()
      .setTitle("Leader board")
      .setDescription("Our top 10 points leaders!")
      .setColor("#ff0000")
      .addFields({ name: '\u200b', value: '\u200b' });
  
    /*for (const data of top10) {
      embed.addFields({ name: client.users.cache.get(data.user).tag, value: `${data.points} points (level ${data.level})` });
    }*/
    return message.channel.send({ embed: embed });

  }
  // Command-specific code here!
});

解决方案 #

1. 数据库逻辑外部化：创建独立DB模块 #

新建database.js文件，将所有SQL操作封装为可复用的异步方法，实现与主文件的解耦：

const sql = require('sqlite');
const path = require('path');

// 初始化数据库连接与表结构
async function initDB() {
  await sql.open(path.join(__dirname, 'database.sqlite'));
  
  // 检查scores表是否存在，不存在则创建
  const tableExists = await sql.get("SELECT count(*) FROM sqlite_master WHERE type='table' AND name = 'scores';");
  if (!tableExists['count(*)']) {
    await sql.run("CREATE TABLE scores (id TEXT PRIMARY KEY, user TEXT, guild TEXT, points INTEGER, level INTEGER);");
    await sql.run("CREATE UNIQUE INDEX idx_scores_id ON scores (id);");
    await sql.pragma("synchronous = 1");
    await sql.pragma("journal_mode = wal");
  }
}

// 获取指定用户在服务器的积分数据
async function getScore(userId, guildId) {
  return await sql.get("SELECT * FROM scores WHERE user = ? AND guild = ?", userId, guildId);
}

// 保存或更新用户积分数据
async function setScore(scoreData) {
  return await sql.run(
    "INSERT OR REPLACE INTO scores (id, user, guild, points, level) VALUES (@id, @user, @guild, @points, @level);",
    scoreData
  );
}

// 获取服务器积分排行榜前10
async function getLeaderboard(guildId) {
  return await sql.all("SELECT * FROM scores WHERE guild = ? ORDER BY points DESC LIMIT 10;", guildId);
}

module.exports = {
  initDB,
  getScore,
  setScore,
  getLeaderboard
};

2. 主文件重构：引入DB模块并实现随机XP #

修改主JavaScript文件，引入database.js，替换原有数据库逻辑，并添加随机XP功能：

const { Client, EmbedBuilder } = require('discord.js');
const config = require('./config');
const db = require('./database');

const client = new Client({ intents: ['Guilds', 'GuildMessages', 'MessageContent'] });

// 初始化数据库
client.on("ready", async () => {
  await db.initDB();
  console.log(`已登录为 ${client.user.tag}!`);
});

// 随机XP范围（可自定义调整）
const MIN_XP = 5;
const MAX_XP = 15;

client.on("messageCreate", async message => {
  if (message.author.bot) return;
  
  if (message.guild) {
    // 获取用户当前积分数据
    let score = await db.getScore(message.author.id, message.guild.id);
    if (!score) {
      score = { 
        id: `${message.guild.id}-${message.author.id}`, 
        user: message.author.id, 
        guild: message.guild.id, 
        points: 0, 
        level: 1 
      };
    }
    
    // 生成随机XP并添加
    const randomXP = Math.floor(Math.random() * (MAX_XP - MIN_XP + 1)) + MIN_XP;
    score.points += randomXP;
    
    // 计算当前等级并判断是否升级
    const curLevel = Math.floor(0.1 * Math.sqrt(score.points));
    if (score.level < curLevel) {
      score.level = curLevel;
      message.reply(`你升级到**${curLevel}**级啦！🎉`);
    }
    
    // 保存更新后的积分数据
    await db.setScore(score);
  }
  
  // 命令处理逻辑
  if (!message.content.startsWith(config.prefix)) return;
  
  const args = message.content.slice(config.prefix.length).trim().split(/ +/g);
  const command = args.shift().toLowerCase();

  if (command === "give") {
    // 仅服务器所有者可执行
    if (message.author.id !== message.guild.ownerId) {
      return message.reply("你没有权限执行这个命令！");
    }
  
    const user = message.mentions.users.first() || client.users.cache.get(args[0]);
    if (!user) return message.reply("请提及用户或输入用户ID！");
  
    const pointsToAdd = parseInt(args[1], 10);
    if (!pointsToAdd || pointsToAdd <= 0) return message.reply("请输入有效的积分数量！");
  
    let userScore = await db.getScore(user.id, message.guild.id);
    if (!userScore) {
      userScore = { 
        id: `${message.guild.id}-${user.id}`, 
        user: user.id, 
        guild: message.guild.id, 
        points: 0, 
        level: 1 
      };
    }
    userScore.points += pointsToAdd;
    userScore.level = Math.floor(0.1 * Math.sqrt(userScore.points));
    
    await db.setScore(userScore);
    return message.channel.send(`${user.tag} 获得了 ${pointsToAdd} 积分，当前总积分：${userScore.points}！`);
  }
  
  if (command === "leaderboard") {
    const top10 = await db.getLeaderboard(message.guild.id);
    const embed = new EmbedBuilder()
      .setTitle("积分排行榜")
      .setDescription("服务器前10名积分大佬！")
      .setColor("#ff0000");
  
    if (top10.length === 0) {
      embed.addFields({ name: "暂无数据", value: "还没有用户获得积分哦～" });
    } else {
      top10.forEach((data, index) => {
        const user = client.users.cache.get(data.user);
        embed.addFields({ 
          name: `${index + 1}. ${user ? user.tag : "未知用户"}`, 
          value: `${data.points} 积分（${data.level}级）` 
        });
      });
    }
    return message.channel.send({ embeds: [embed] });
  }
});

client.login(config.token);

关键说明 #

• 代码解耦：数据库操作集中在database.js，主文件仅处理Discord事件与命令，后续扩展警告功能时，只需在DB模块添加对应表和方法即可
• 随机XP实现：通过Math.random()生成MIN_XP到MAX_XP之间的随机整数，替换原固定+1的逻辑，可根据需求调整数值范围
• 异步处理：所有数据库操作使用async/await，避免回调地狱，保证数据操作的顺序性与可靠性

内容的提问来源于stack exchange，提问作者TechXplore