Discord Bot Database Setup: SQLite vs MySQL for Persistent Data

Your bot needs to remember things between restarts - user preferences, economy data, moderation logs. A database makes it persistent.

SQLite vs MySQL

SQLite

A file-based database. No server to install - just a file on disk.

Best for:

• Bots on fewer than 500 servers
• Simple data (settings, user preferences)
• Quick setup
• Bots on limited hosting (like Discord Bot plans)

// Using better-sqlite3
const Database = require('better-sqlite3');
const db = new Database('bot.db');

// Create table
db.exec(`
    CREATE TABLE IF NOT EXISTS guild_settings (
        guild_id TEXT PRIMARY KEY,
        prefix TEXT DEFAULT '!',
        welcome_channel TEXT,
        log_channel TEXT
    )
`);

// Insert/update
const upsert = db.prepare(`
    INSERT INTO guild_settings (guild_id, prefix) VALUES (?, ?)
    ON CONFLICT(guild_id) DO UPDATE SET prefix = ?
`);
upsert.run(guildId, newPrefix, newPrefix);

// Query
const settings = db.prepare('SELECT * FROM guild_settings WHERE guild_id = ?').get(guildId);

MySQL/MariaDB

A full client-server database.

Best for:

• Bots on 500+ servers
• Complex queries (leaderboards, analytics)
• Multiple processes accessing the same data (sharded bots)
• When you need concurrent write performance

// Using mysql2
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
    host: 'localhost',
    user: 'bot',
    password: 'secure_password',
    database: 'discord_bot',
    connectionLimit: 10
});

// Query
const [rows] = await pool.query(
    'SELECT * FROM guild_settings WHERE guild_id = ?',
    [guildId]
);

// Insert
await pool.query(
    'INSERT INTO guild_settings (guild_id, prefix) VALUES (?, ?) ON DUPLICATE KEY UPDATE prefix = ?',
    [guildId, prefix, prefix]
);

Data Models

Guild Settings

CREATE TABLE guild_settings (
    guild_id VARCHAR(20) PRIMARY KEY,
    prefix VARCHAR(10) DEFAULT '!',
    welcome_channel VARCHAR(20),
    log_channel VARCHAR(20),
    language VARCHAR(5) DEFAULT 'en',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

User Economy

CREATE TABLE economy (
    user_id VARCHAR(20) NOT NULL,
    guild_id VARCHAR(20) NOT NULL,
    balance BIGINT DEFAULT 0,
    bank BIGINT DEFAULT 0,
    last_daily TIMESTAMP,
    PRIMARY KEY (user_id, guild_id)
);

Moderation Logs

CREATE TABLE mod_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    guild_id VARCHAR(20) NOT NULL,
    user_id VARCHAR(20) NOT NULL,
    moderator_id VARCHAR(20) NOT NULL,
    action ENUM('warn', 'mute', 'kick', 'ban') NOT NULL,
    reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_guild_user (guild_id, user_id)
);

ORM Options

Sequelize (Node.js)

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize({ dialect: 'sqlite', storage: 'bot.db' });

const GuildSettings = sequelize.define('GuildSettings', {
    guildId: { type: DataTypes.STRING, primaryKey: true },
    prefix: { type: DataTypes.STRING, defaultValue: '!' }
});

// Auto-create tables
await sequelize.sync();

// Usage
const settings = await GuildSettings.findByPk(guildId);

Prisma (Node.js)

Modern ORM with TypeScript support:

model GuildSettings {
    guildId String @id
    prefix  String @default("!")
}

Best Practices

1. Always use parameterized queries - never concatenate user input into SQL
2. Index columns you query frequently (guild_id, user_id)
3. Backup your database (SQLite: copy the file; MySQL: mysqldump)
4. Handle connection errors gracefully
5. Cache frequent reads in memory with TTL

Storage on Bot Hosting

On Space-Node's Discord Bot plans:

• Free plan (1GB): SQLite database for small bots
• Middle (5GB): Room for growing databases
• Large (10GB): Comfortable for bots with extensive data

SQLite works perfectly on these plans. For MySQL, you'd need a VPS or external database service.