How to use a database with your Discord bot (SQLite)

Schokr doesn't include a per-server MySQL database, so the simplest option for most bots is SQLite: a database stored in a single file on your server's disk. For larger or multi-server setups, connect to an external database provider instead.

Which option to pick#

SQLite in discord.js with better-sqlite3#

npm install better-sqlite3

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

db.pragma('journal_mode = WAL');
db.exec(`
  CREATE TABLE IF NOT EXISTS xp (
    user_id TEXT NOT NULL,
    guild_id TEXT NOT NULL,
    points INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id, guild_id)
  )
`);

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

// usage
addXp.run(message.author.id, message.guild.id, 5);
const row = getXp.get(message.author.id, message.guild.id);

Store Discord IDs as TEXT. They're larger than JavaScript can safely represent as numbers.

better-sqlite3 is a native module. Don't upload your local node_modules; let the server install it from package.json so it's built for Linux.

SQLite with Bun#

Bun has SQLite built in, so there's nothing to install:

import { Database } from 'bun:sqlite';

const db = new Database('data.db', { create: true });
db.run('CREATE TABLE IF NOT EXISTS prefixes (guild_id TEXT PRIMARY KEY, prefix TEXT NOT NULL)');
const row = db.query('SELECT prefix FROM prefixes WHERE guild_id = ?').get(guildId);

SQLite in discord.py with aiosqlite#

Python's built-in sqlite3 module blocks the event loop while it runs, which can stall your bot under load. aiosqlite wraps it for async code:

pip install aiosqlite

import aiosqlite

class MyBot(commands.Bot):
    async def setup_hook(self):
        self.db = await aiosqlite.connect("data.db")
        await self.db.execute(
            "CREATE TABLE IF NOT EXISTS warnings ("
            "id INTEGER PRIMARY KEY, guild_id TEXT, user_id TEXT, reason TEXT)"
        )
        await self.db.commit()

    async def close(self):
        await self.db.close()
        await super().close()


# in a command
await bot.db.execute(
    "INSERT INTO warnings (guild_id, user_id, reason) VALUES (?, ?, ?)",
    (str(interaction.guild_id), str(member.id), reason),
)
await bot.db.commit()

Add aiosqlite to requirements.txt. For tiny, infrequent queries the standard sqlite3 module is fine.

Always use placeholders#

Use ? placeholders as shown. Never build SQL by joining user input into the string; that allows SQL injection.

Protect your data#

• The database file lives on the server's disk and counts toward your team's disk pool.
• Take a backup or snapshot before big changes or migrations.
• Keep the database in a folder like data/ so it's easy to find and not overwritten when you upload new code.

External databases#

If you use a hosted PostgreSQL, MySQL or MongoDB provider, put its connection string in .env and connect with the usual driver (pg, mysql2, mongodb, asyncpg, motor). Pick an Schokr location close to the database's region to cut latency. Schokr's own S3-compatible storage is coming soon.