A leveling system with SQLite

A leveling system rewards people for chatting: every message earns XP, XP builds levels, and levels get harder as they climb. This recipe builds the whole thing — a real database with better-sqlite3, a fair XP curve, /rank and /leaderboard, and level-up announcements — so it survives restarts instead of resetting every time the bot stops.

We use better-sqlite3 — a real SQL database in a single file next to your bot, no separate service to run. It ships prebuilt binaries, so it installs cleanly on the Node.js application. Add it from the Packages page (search better-sqlite3, install, restart).

How XP and levels work

Two decisions define a leveling system: how much a message is worth, and how much XP each level costs. We store a running total XP per user and compute the level from it, using the well-known Mee6 curve — each level costs a little more than the last:

XP to go from level L to L+1  =  5·L² + 50·L + 100

So level 1 costs 100 XP, level 2 costs another 155, level 3 another 220, and so on. To turn a total into a level, spend the XP one level at a time until it runs out.

🎯 Good to know: Storing total XP (not "XP within the current level") keeps the leaderboard trivial — the person with the most XP is rank 1 — and means changing the curve later never corrupts anyone's progress.

The data layer

Put all the database code in its own file, leveling-db.js, so the bot file stays about Discord and this file stays about data:

const Database = require('better-sqlite3');
const db = new Database('levels.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS levels (
    guild_id  TEXT    NOT NULL,
    user_id   TEXT    NOT NULL,
    xp        INTEGER NOT NULL DEFAULT 0,
    last_msg  INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (guild_id, user_id)
  )
`);

// XP needed to go FROM `level` to the next one (the Mee6 curve).
function xpToNext(level) {
  return 5 * level * level + 50 * level + 100;
}

// Turn a total-XP number into a level by spending it one level at a time.
function levelFromXp(totalXp) {
  let level = 0;
  while (totalXp >= xpToNext(level)) {
    totalXp -= xpToNext(level);
    level++;
  }
  return level;
}

const getRow = db.prepare('SELECT xp, last_msg FROM levels WHERE guild_id = ? AND user_id = ?');
const addXp  = db.prepare(`
  INSERT INTO levels (guild_id, user_id, xp, last_msg) VALUES (?, ?, ?, ?)
  ON CONFLICT(guild_id, user_id) DO UPDATE SET xp = xp + excluded.xp, last_msg = excluded.last_msg
`);

// Award XP at most once every 60s per user. Returns {leveledUp, level, xp} or null if ignored.
function grantMessageXp(guildId, userId, amount) {
  const now = Date.now();
  const before = getRow.get(guildId, userId);
  if (before && now - before.last_msg < 60_000) return null; // anti-spam: too soon

  const oldLevel = levelFromXp(before ? before.xp : 0);
  addXp.run(guildId, userId, amount, now);
  const after = getRow.get(guildId, userId);
  const newLevel = levelFromXp(after.xp);
  return { leveledUp: newLevel > oldLevel, level: newLevel, xp: after.xp };
}

const rankStmt = db.prepare(`
  SELECT COUNT(*) + 1 AS rank FROM levels
  WHERE guild_id = ? AND xp > (SELECT xp FROM levels WHERE guild_id = ? AND user_id = ?)
`);
function getRank(guildId, userId) {
  const row = getRow.get(guildId, userId);
  if (!row) return null;
  const { rank } = rankStmt.get(guildId, guildId, userId);
  return { xp: row.xp, level: levelFromXp(row.xp), rank };
}

const topStmt = db.prepare('SELECT user_id, xp FROM levels WHERE guild_id = ? ORDER BY xp DESC LIMIT ?');
function leaderboard(guildId, limit = 10) {
  return topStmt.all(guildId, limit).map(r => ({ ...r, level: levelFromXp(r.xp) }));
}

module.exports = { grantMessageXp, getRank, leaderboard, xpToNext, levelFromXp };

The parts worth understanding:

• The primary key is (guild_id, user_id) — a person's XP is per server, so the same user has separate progress in each server the bot is in. Every query filters by guild_id.
• ON CONFLICT ... DO UPDATE is an upsert: one statement that inserts a new row or adds to an existing one, so the first message and the thousandth both "just work". excluded.xp refers to the value you tried to insert.
• grantMessageXp enforces anti-spam — it ignores anyone who messaged in the last 60 seconds by returning null, so nobody farms XP by flooding a channel.
• getRank counts everyone with more XP and adds one — a person nobody outranks is #1. leaderboard just sorts by XP.
• Prepared statements (db.prepare(...)) are reusable, fast, and safe against injection because every value goes in as a ? parameter — never build SQL by concatenating user input.

Award XP on every message

In the bot file, listen for MessageCreate and grant XP. This needs the GuildMessages intent so the event fires — but not Message Content, because you're only counting that a message happened, not reading it:

require('dotenv').config();
const { Client, Events, GatewayIntentBits, SlashCommandBuilder, EmbedBuilder } = require('discord.js');
const { grantMessageXp, getRank, leaderboard } = require('./leveling-db.js');

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

client.on(Events.MessageCreate, async (message) => {
  if (message.author.bot || !message.guild) return;         // skip bots and DMs
  const result = grantMessageXp(message.guild.id, message.author.id, 20);
  if (result?.leveledUp) {
    await message.channel.send(`🎉 ${message.author}, you reached **level ${result.level}**!`);
  }
});

Each message is worth 20 XP (tune to taste). When grantMessageXp reports a level-up, the bot congratulates them in the channel. The result?.leveledUp handles the null from the anti-spam window gracefully — no announcement, no error.

/rank and /leaderboard

Register two slash commands in ClientReady and answer them with embeds:

client.once(Events.ClientReady, async (c) => {
  await c.application.commands.set([
    new SlashCommandBuilder().setName('rank').setDescription('See your level and XP'),
    new SlashCommandBuilder().setName('leaderboard').setDescription('Top members by XP'),
  ]);
  console.log(`Listening as ${c.user.tag}`);
});

client.on(Events.InteractionCreate, async (interaction) => {
  if (!interaction.isChatInputCommand()) return;

  if (interaction.commandName === 'rank') {
    const r = getRank(interaction.guild.id, interaction.user.id);
    if (!r) return interaction.reply({ content: 'No XP yet — send a message first!', ephemeral: true });
    await interaction.reply({ embeds: [new EmbedBuilder()
      .setTitle(`${interaction.user.username}'s rank`)
      .addFields(
        { name: 'Level', value: `${r.level}`, inline: true },
        { name: 'XP', value: `${r.xp}`, inline: true },
        { name: 'Rank', value: `#${r.rank}`, inline: true },
      )] });
  }

  if (interaction.commandName === 'leaderboard') {
    const rows = leaderboard(interaction.guild.id, 10);
    const text = rows.length
      ? rows.map((r, i) => `**${i + 1}.** <@${r.user_id}> — level ${r.level} (${r.xp} XP)`).join('\n')
      : 'Nobody has any XP yet.';
    await interaction.reply({ embeds: [new EmbedBuilder().setTitle('🏆 Leaderboard').setDescription(text)] });
  }
});

client.login(process.env.DISCORD_TOKEN);

getRank returns null for someone who's never earned XP, so /rank handles that with a friendly nudge instead of crashing.

Where the data lives

⚠️ Heads up: levels.db sits in /home/container on the server. It survives restarts — that's the whole point over an in-memory Map — but a reinstall wipes it, and switching the server to a different application (or deploying a template that switches it) is a reinstall. Take a Backup before risky changes, and keep levels.db out of Git. If the data must outlive reinstalls or be shared between servers, use a managed database instead.

Verify it works

Start the bot and send a few messages in the server (waiting past the 60-second window between the ones that should count). Run /rank — your level, XP, and position show. Run /leaderboard — you're on it. Now restart the bot and check /rank again: the numbers are still there. That persistence is the payoff of SQLite over an in-memory store.

Troubleshooting

• No XP is ever awarded — the GuildMessages intent is missing, or the MessageCreate listener isn't attached. Add the intent and confirm the handler runs (log inside it).
• Cannot find module 'better-sqlite3' — it's not installed. Add it from the Packages page and restart; it ships prebuilt binaries, so no compiler is involved.
• Everyone shares one XP total — a query is missing its guild_id filter, or the table lacks the composite primary key. XP is per (guild_id, user_id).
• XP resets after a reinstall — expected: levels.db is local to the server. Back it up, or move to a managed database.
• Level-up messages spam the channel — you're announcing on every message, not only when leveledUp is true. Guard on result?.leveledUp.

Next steps

• Currency, daily rewards, and a shop
• Store data for your bot
• Evolving your SQLite schema safely