Discord bot database: SQLite and MySQL, Python and JS

A file, or a database?

A JSON file is fine for a handful of settings that rarely change. It goes wrong in two ways as a bot grows: a crash halfway through writing leaves a broken file, and two commands saving at the same moment overwrite each other. A database fixes both, and lets you ask questions like "this member's last ten warnings in this server" without loading everything.

SQLite is the right first database for almost every bot. It is a real SQL database kept in one file next to your code, there is no server to run, and it handles far more traffic than a bot sends it. Move to MySQL (or Postgres) when more than one program needs the same data, such as a bot and a web dashboard, or two bots.

The table

Both versions below use the same table: one row per warning, with the server, the member, who warned them, why and when.

JavaScript

CREATE TABLE IF NOT EXISTS warnings (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    guild_id   INTEGER NOT NULL,
    user_id    INTEGER NOT NULL,
    moderator  INTEGER NOT NULL,
    reason     TEXT    NOT NULL,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS warnings_member ON warnings (guild_id, user_id);

The index makes "warnings for this member in this server" fast however many rows there are. created_at is a Unix timestamp in seconds, which Discord can show in each reader's own time zone.

discord.py, with aiosqlite

Python has sqlite3 built in, but every call waits for the disk, and in an async bot that wait freezes everything else. aiosqlite runs the same database off the event loop. Add it to requirements.txt:

Text

discord.py
aiosqlite
python-dotenv

Open the connection once, when the bot starts, and keep it:

Python

import os
import pathlib

import aiosqlite
import discord
from discord import app_commands
from dotenv import load_dotenv

load_dotenv()

# Next to this file, wherever the bot is started from.
DB_PATH = pathlib.Path(__file__).with_name("bot.db")
SCHEMA = """
CREATE TABLE IF NOT EXISTS warnings (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    guild_id   INTEGER NOT NULL,
    user_id    INTEGER NOT NULL,
    moderator  INTEGER NOT NULL,
    reason     TEXT    NOT NULL,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS warnings_member ON warnings (guild_id, user_id);
"""

class MyBot(discord.Client):
    def __init__(self):
        super().__init__(
            intents=discord.Intents.default(),
            # A reason is typed by a person and can contain @everyone.
            allowed_mentions=discord.AllowedMentions(everyone=False, roles=False, users=True),
        )
        self.tree = app_commands.CommandTree(self)
        self.db = None

    async def setup_hook(self):
        self.db = await aiosqlite.connect(DB_PATH)
        await self.db.execute("PRAGMA journal_mode=WAL")
        await self.db.executescript(SCHEMA)
        await self.db.commit()
        await self.tree.sync()

    async def close(self):
        if self.db is not None:
            await self.db.close()
        await super().close()

bot = MyBot()

@bot.tree.command(description="Warn a member")
@app_commands.guild_only()
@app_commands.default_permissions(moderate_members=True)
async def warn(interaction: discord.Interaction, member: discord.Member, reason: str):
    await bot.db.execute(
        "INSERT INTO warnings (guild_id, user_id, moderator, reason, created_at) VALUES (?, ?, ?, ?, ?)",
        (interaction.guild_id, member.id, interaction.user.id, reason, int(discord.utils.utcnow().timestamp())),
    )
    await bot.db.commit()
    await interaction.response.send_message(f"Warned {member.mention}: {reason}")

@bot.tree.command(name="warnings", description="List a member's warnings")
@app_commands.guild_only()
async def list_warnings(interaction: discord.Interaction, member: discord.Member):
    async with bot.db.execute(
        "SELECT reason, created_at FROM warnings WHERE guild_id = ? AND user_id = ? ORDER BY id DESC LIMIT 10",
        (interaction.guild_id, member.id),
    ) as cursor:
        rows = await cursor.fetchall()
    if not rows:
        await interaction.response.send_message(f"{member.display_name} has no warnings.")
        return
    lines = [f"<t:{created}:R> {reason}" for reason, created in rows]
    await interaction.response.send_message(f"**{member.display_name}**\n" + "\n".join(lines))

bot.run(os.environ["BOT_TOKEN"])

The question marks are the point. Values go in the second argument, never into the SQL string with an f-string. That is what stops a reason like '); DROP TABLE warnings; -- from being run as SQL.
commit() after every write. Without it the row exists only in that connection until the bot stops, and then it is gone.
journal_mode=WAL lets reads carry on while a write happens, which is what stops database is locked on a busy bot. It only needs setting once per database file; setting it on every start does no harm.
Discord IDs fit in an SQLite INTEGER, which is 64 bits.
<t:...:R> is Discord's timestamp format, shown as "3 days ago" in the reader's own time zone.

In a cog, keep the connection on the bot as above and reach it through self.bot.db. See cogs in discord.py.

discord.js, with better-sqlite3

In Node, better-sqlite3 is the usual choice. It is synchronous, which sounds wrong in JavaScript but suits SQLite: a small query takes microseconds, faster than the bookkeeping an async call would add.

Terminal

npm install better-sqlite3

A db.js that opens the database and prepares the two queries once:

JavaScript

// db.js
const path = require("node:path");
const Database = require("better-sqlite3");

const db = new Database(path.join(__dirname, "bot.db"));
db.pragma("journal_mode = WAL");
db.exec(`
  CREATE TABLE IF NOT EXISTS warnings (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    guild_id   TEXT    NOT NULL,
    user_id    TEXT    NOT NULL,
    moderator  TEXT    NOT NULL,
    reason     TEXT    NOT NULL,
    created_at INTEGER NOT NULL
  );
  CREATE INDEX IF NOT EXISTS warnings_member ON warnings (guild_id, user_id);
`);

module.exports = {
  addWarning: db.prepare(
    "INSERT INTO warnings (guild_id, user_id, moderator, reason, created_at) VALUES (?, ?, ?, ?, ?)",
  ),
  listWarnings: db.prepare(
    "SELECT reason, created_at FROM warnings WHERE guild_id = ? AND user_id = ? ORDER BY id DESC LIMIT 10",
  ),
};

The IDs are TEXT here on purpose: a Discord ID is bigger than a JavaScript number can hold exactly, and discord.js gives them to you as strings. Keep them strings all the way into the database and back.

The commands, for your deploy script:

JavaScript

const { InteractionContextType, PermissionFlagsBits, SlashCommandBuilder } = require("discord.js");

module.exports = [
  new SlashCommandBuilder()
    .setName("warn")
    .setDescription("Warn a member")
    .addUserOption((o) => o.setName("member").setDescription("Who").setRequired(true))
    .addStringOption((o) => o.setName("reason").setDescription("Why").setRequired(true).setMaxLength(500))
    .setDefaultMemberPermissions(PermissionFlagsBits.ModerateMembers)
    .setContexts(InteractionContextType.Guild),
  new SlashCommandBuilder()
    .setName("warnings")
    .setDescription("List a member's warnings")
    .addUserOption((o) => o.setName("member").setDescription("Who").setRequired(true))
    .setContexts(InteractionContextType.Guild),
];

And the handler:

JavaScript

const { addWarning, listWarnings } = require("./db");

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

  if (interaction.commandName === "warn") {
    const member = interaction.options.getUser("member");
    const reason = interaction.options.getString("reason");
    addWarning.run(interaction.guildId, member.id, interaction.user.id, reason, Math.floor(Date.now() / 1000));
    // A reason is typed by a person and can contain @everyone: only the member gets pinged.
    await interaction.reply({ content: `Warned ${member}: ${reason}`, allowedMentions: { users: [member.id] } });
  }

  if (interaction.commandName === "warnings") {
    const member = interaction.options.getUser("member");
    const rows = listWarnings.all(interaction.guildId, member.id);
    if (rows.length === 0) {
      await interaction.reply(`${member.username} has no warnings.`);
      return;
    }
    const lines = rows.map((r) => `<t:${r.created_at}:R> ${r.reason}`);
    await interaction.reply({ content: `**${member.username}**\n${lines.join("\n")}`, allowedMentions: { parse: [] } });
  }
});

Writes are saved the moment run() returns; there is no separate commit unless you start a transaction yourself. Wrap several writes that belong together in db.transaction(...) so they all happen or none do.

When to move to MySQL

SQLite is one file used by one program. Move to MySQL when:

a web dashboard, a second bot or another server needs the same data;
you want to look at the data with a database tool while the bot runs;
a bot you are installing already expects MySQL or MariaDB.

The table changes a little: Discord IDs become BIGINT UNSIGNED, and AUTOINCREMENT is spelt AUTO_INCREMENT.

JavaScript

CREATE TABLE IF NOT EXISTS warnings (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guild_id   BIGINT UNSIGNED NOT NULL,
    user_id    BIGINT UNSIGNED NOT NULL,
    moderator  BIGINT UNSIGNED NOT NULL,
    reason     VARCHAR(500)    NOT NULL,
    created_at INT UNSIGNED    NOT NULL,
    INDEX warnings_member (guild_id, user_id)
);

Use a pool, created once at start-up: it keeps a few connections open and hands them out, and replaces ones the server has closed. Put the details in .env, never in the code.

Python

# discord.py, with aiomysql (add it to requirements.txt)
import aiomysql

async def setup_hook(self):
    self.pool = await aiomysql.create_pool(
        host=os.environ["DB_HOST"], user=os.environ["DB_USER"], password=os.environ["DB_PASSWORD"],
        db=os.environ["DB_NAME"], autocommit=True, maxsize=5, pool_recycle=300,
    )
    await self.tree.sync()

async def add_warning(pool, guild_id, user_id, moderator, reason, created_at):
    async with pool.acquire() as conn, conn.cursor() as cur:
        await cur.execute(
            "INSERT INTO warnings (guild_id, user_id, moderator, reason, created_at) VALUES (%s, %s, %s, %s, %s)",
            (guild_id, user_id, moderator, reason, created_at),
        )

aiomysql uses %s where SQLite uses ?, and they are still placeholders, not Python formatting: pass the values separately, exactly as before.

JavaScript

// discord.js, with mysql2
const mysql = require("mysql2/promise");

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 5,
  supportBigNumbers: true,
  bigNumberStrings: true, // Discord IDs come back as strings, not rounded numbers
});

async function addWarning(guildId, userId, moderator, reason, createdAt) {
  await pool.execute(
    "INSERT INTO warnings (guild_id, user_id, moderator, reason, created_at) VALUES (?, ?, ?, ?, ?)",
    [guildId, userId, moderator, reason, createdAt],
  );
}

Backing it up

Copying bot.db while the bot is writing to it can catch it halfway. Ask SQLite for a clean copy instead; this works while the bot runs:

Python

await bot.db.execute("VACUUM INTO ?", ("backup.db",))

In better-sqlite3 the same is await db.backup("backup.db"). For MySQL, mysqldump makes a consistent copy. Keep backups somewhere other than the bot's own folder if you can, and try restoring one once, before you need to.

Errors you will probably meet

sqlite3.OperationalError: database is locked (or SQLITE_BUSY): two programs writing to the same file, or a write left open. Turn on WAL as above, use one connection per program, and do not open the file in a database viewer while the bot writes to it.
no such table: warnings: the schema never ran, or the bot opened a different bot.db. A bare "bot.db" is relative to the folder the bot was started from, not the folder the code is in, which is why the examples build the path from the file's own location.
Warnings vanish after a restart: a missing await bot.db.commit() in Python.
IDs that are almost right, ending in 00 or off by a few: a Discord ID went through a JavaScript number. Store and pass them as strings.
The bot freezes while a command runs: plain sqlite3, pymysql or mysql-connector used inside an async command. Use aiosqlite or aiomysql in discord.py.
Lost connection to MySQL server or MySQL server has gone away after a quiet night: a single connection the server closed. A pool with pool_recycle replaces them.
Too many connections: a new connection opened in every command and never closed. One pool, made at start-up.

On SnowServers every plan includes a MySQL database on the same machine as your bot, and the SQLite file in your server's folder is in the daily backups with the rest of it. Using your MySQL database covers creating one in the panel.

Something here wrong or out of date? Tell us and it gets fixed.

PreviousEmbeds in discord.js NextOrganising a discord.py bot with cogs