Storing data: SQLite to PostgreSQL

Anything in a variable disappears when your bot restarts. To keep settings, levels or reminders, you need a database.

Start with SQLite. It's one file on disk, with no server to run, and it comfortably handles small and medium bots. Move to PostgreSQL when you outgrow it, which this guide covers at the end.

SQLite in discord.js

npm install better-sqlite3

Save this as db.js:

import Database from "better-sqlite3";

const db = new Database("bot.db");
db.pragma("journal_mode = WAL");

db.exec(`
  CREATE TABLE IF NOT EXISTS guild_settings (
    guild_id TEXT PRIMARY KEY,
    welcome_channel_id TEXT
  )
`);

const upsert = db.prepare(`
  INSERT INTO guild_settings (guild_id, welcome_channel_id) VALUES (?, ?)
  ON CONFLICT (guild_id) DO UPDATE SET welcome_channel_id = excluded.welcome_channel_id
`);
const select = db.prepare("SELECT welcome_channel_id FROM guild_settings WHERE guild_id = ?");

export const setWelcomeChannel = (guildId, channelId) => upsert.run(guildId, channelId);
export const getWelcomeChannel = (guildId) => select.get(guildId)?.welcome_channel_id ?? null;

Use it in a command:

import { setWelcomeChannel } from "./db.js";

setWelcomeChannel(interaction.guildId, interaction.channelId);
await interaction.reply({ content: "This channel will now get welcome messages.", ephemeral: true });

SQLite in discord.py

Use an async library, because a normal database call would freeze your bot while it waits.

pip install aiosqlite

import aiosqlite

DB_PATH = "bot.db"


async def init_db():
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute("PRAGMA journal_mode = WAL")
        await db.execute("""
            CREATE TABLE IF NOT EXISTS guild_settings (
                guild_id TEXT PRIMARY KEY,
                welcome_channel_id TEXT
            )
        """)
        await db.commit()


async def set_welcome_channel(guild_id: int, channel_id: int):
    async with aiosqlite.connect(DB_PATH) as db:
        await db.execute(
            """
            INSERT INTO guild_settings (guild_id, welcome_channel_id) VALUES (?, ?)
            ON CONFLICT (guild_id) DO UPDATE SET welcome_channel_id = excluded.welcome_channel_id
            """,
            (str(guild_id), str(channel_id)),
        )
        await db.commit()


async def get_welcome_channel(guild_id: int) -> int | None:
    async with aiosqlite.connect(DB_PATH) as db:
        async with db.execute(
            "SELECT welcome_channel_id FROM guild_settings WHERE guild_id = ?", (str(guild_id),)
        ) as cursor:
            row = await cursor.fetchone()
            return int(row[0]) if row and row[0] else None

Call await init_db() from your setup_hook.

Three rules that prevent most disasters

1. Store Discord IDs as text. IDs are 64-bit numbers. JavaScript can't represent them exactly as numbers and silently corrupts them. Text works everywhere.

2. Never build SQL by joining strings. Always use ? placeholders, as the examples above do. Otherwise, a user who types a carefully chosen value can read or delete your whole database:

// Dangerous: never do this
db.exec(`SELECT * FROM notes WHERE text = '${userInput}'`);

// Safe: the value can't change the query
db.prepare("SELECT * FROM notes WHERE text = ?").get(userInput);

3. Back it up, and keep it out of Git. Add bot.db* to your .gitignore. A backup you've never restored is only a hope, so try restoring one before you need it.

To copy a live SQLite database safely, use its backup command rather than copying the file:

sqlite3 bot.db ".backup 'backup-bot.db'"

Store only what you need

Bots see a lot of personal information. Saving it makes you responsible for it. Avoid logging message content, and keep users' IDs only where a feature depends on them. Give people a way to have their data deleted, and describe all of this in your bot's privacy policy, which you'll link when you list your bot.

Listen for your bot being removed from a server, and clean up that server's data when it happens:

client.on(Events.GuildDelete, (guild) => deleteGuildData(guild.id));

@bot.event
async def on_guild_remove(guild: discord.Guild):
    await delete_guild_data(guild.id)

When to move to PostgreSQL

Consider moving when any of these become true:

• You run several processes, for example shards or a website alongside your bot. SQLite allows only one writer at a time and works on one machine.
• Writes are often waiting on each other, and you see "database is locked" errors.
• You want managed backups or to run your database on its own server.

Connecting

The SQL barely changes, though placeholders become $1, $2 and so on, and you use a connection pool instead of opening a file.

In Node.js with pg:

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

export async function getWelcomeChannel(guildId) {
  const { rows } = await pool.query(
    "SELECT welcome_channel_id FROM guild_settings WHERE guild_id = $1",
    [guildId],
  );
  return rows[0]?.welcome_channel_id ?? null;
}

In Python with asyncpg:

import os

import asyncpg

pool = await asyncpg.create_pool(os.environ["DATABASE_URL"])


async def get_welcome_channel(guild_id: int):
    return await pool.fetchval(
        "SELECT welcome_channel_id FROM guild_settings WHERE guild_id = $1", str(guild_id)
    )

Moving your data

Tools such as pgloader can copy a SQLite database into PostgreSQL. Whichever you use, follow this order:

1. Set up PostgreSQL and create your tables.
2. Stop your bot, so that nothing changes during the copy.
3. Copy the data and spot-check row counts.
4. Start your bot with the new DATABASE_URL.
5. Keep the old SQLite file until you're confident.

Next

Your bot still only runs while your computer is on. Fix that with Hosting 24/7.