Skip to main content

Discord Bot Database: Persistent User Data

A database persists bot data across restarts, enabling features like user profiles, scores, settings, and audit logs. Discord bots use SQLite for local development (file-based, no external service) and PostgreSQL for production (scalable, multi-server). SQLAlchemy is Python's Object-Relational Mapper (ORM) that maps database tables to Python classes, eliminating raw SQL and preventing injection attacks. Learning database design and querying teaches you data modeling, transactions, and stateful programming—essential for any real-world application.

Setting Up SQLite with SQLAlchemy​

SQLite stores data in a single file, making it perfect for development. Install SQLAlchemy:

pip install sqlalchemy

Create a models file to define your data structure:

# models.py
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime

# Create an in-memory SQLite database for testing, or a file for persistence
DATABASE_URL = 'sqlite:///./discord_bot.db'

engine = create_engine(DATABASE_URL, connect_args={'check_same_thread': False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

class UserProfile(Base):
    """Store user data: ID, level, coins, join date."""
    __tablename__ = 'user_profiles'
    
    id = Column(Integer, primary_key=True)
    discord_id = Column(Integer, unique=True, nullable=False)
    username = Column(String, nullable=False)
    level = Column(Integer, default=1)
    coins = Column(Float, default=0.0)
    joined_at = Column(DateTime, default=datetime.utcnow)

class ModLog(Base):
    """Log moderation actions: warns, kicks, bans."""
    __tablename__ = 'mod_logs'
    
    id = Column(Integer, primary_key=True)
    guild_id = Column(Integer, nullable=False)
    moderator_id = Column(Integer, nullable=False)
    user_id = Column(Integer, nullable=False)
    action = Column(String, nullable=False)  # 'warn', 'kick', 'ban'
    reason = Column(String, nullable=True)
    timestamp = Column(DateTime, default=datetime.utcnow)

# Create tables
Base.metadata.create_all(bind=engine)

Run this once to create tables. Then, import these models in your bot:

# bot.py
import discord
from discord.ext import commands
from models import UserProfile, ModLog, SessionLocal, engine, Base
import os
from dotenv import load_dotenv

load_dotenv()
bot = commands.Bot(command_prefix='!', intents=discord.Intents.default())

# Ensure tables exist
Base.metadata.create_all(bind=engine)

@bot.command(name='profile')
async def profile(ctx, target: discord.Member = None):
    """Show a user's profile."""
    target = target or ctx.author
    
    session = SessionLocal()
    try:
        # Query for the user in the database
        user = session.query(UserProfile).filter(
            UserProfile.discord_id == target.id
        ).first()
        
        if not user:
            # Create a new profile if they don't exist
            user = UserProfile(
                discord_id=target.id,
                username=target.name
            )
            session.add(user)
            session.commit()
        
        embed = discord.Embed(
            title=f'{target.name}\'s Profile',
            color=discord.Color.blue()
        )
        embed.add_field(name='Level', value=user.level, inline=True)
        embed.add_field(name='Coins', value=f'{user.coins:.2f}', inline=True)
        embed.add_field(
            name='Joined',
            value=user.joined_at.strftime('%Y-%m-%d %H:%M'),
            inline=False
        )
        
        await ctx.send(embed=embed)
    
    finally:
        session.close()

bot.run(os.getenv('DISCORD_TOKEN'))

SessionLocal() creates a database session for querying. session.query(UserProfile) retrieves records. .filter() narrows results by condition. .first() gets the first match or None. session.add() stages a new object; session.commit() persists changes to disk. Always close sessions in a finally block to avoid connection leaks.

Creating and Updating Records​

Modify user data with database operations:

@bot.command(name='addcoin')
async def addcoin(ctx, amount: float):
    """Give the user coins."""
    session = SessionLocal()
    try:
        user = session.query(UserProfile).filter(
            UserProfile.discord_id == ctx.author.id
        ).first()
        
        if not user:
            user = UserProfile(
                discord_id=ctx.author.id,
                username=ctx.author.name,
                coins=amount
            )
            session.add(user)
        else:
            user.coins += amount
        
        session.commit()
        await ctx.send(f'You now have {user.coins:.2f} coins!')
    
    finally:
        session.close()

@bot.command(name='leaderboard')
async def leaderboard(ctx):
    """Show top 10 users by coins."""
    session = SessionLocal()
    try:
        # Query top 10 users, sorted by coins descending
        top_users = session.query(UserProfile).order_by(
            UserProfile.coins.desc()
        ).limit(10).all()
        
        embed = discord.Embed(title='Leaderboard', color=discord.Color.gold())
        
        for rank, user in enumerate(top_users, 1):
            embed.add_field(
                name=f'{rank}. {user.username}',
                value=f'{user.coins:.2f} coins',
                inline=False
            )
        
        await ctx.send(embed=embed)
    
    finally:
        session.close()

.order_by() sorts results; .limit() restricts count; .all() returns a list. To update an existing record, fetch it, modify its attributes, and commit.

Moderation Logging​

Log administrative actions for auditing:

@bot.command(name='warn')
@commands.has_permissions(manage_messages=True)
async def warn(ctx, member: discord.Member, *, reason: str = 'No reason'):
    """Warn a user."""
    session = SessionLocal()
    try:
        # Create a mod log entry
        log_entry = ModLog(
            guild_id=ctx.guild.id,
            moderator_id=ctx.author.id,
            user_id=member.id,
            action='warn',
            reason=reason
        )
        session.add(log_entry)
        session.commit()
        
        await ctx.send(f'{member.mention} has been warned: {reason}')
        
        # Send a DM to the warned user
        try:
            await member.send(f'You were warned in {ctx.guild.name}: {reason}')
        except discord.Forbidden:
            pass
    
    finally:
        session.close()

@bot.command(name='modlog')
@commands.has_permissions(manage_messages=True)
async def modlog(ctx, member: discord.Member):
    """Show moderation history for a user."""
    session = SessionLocal()
    try:
        logs = session.query(ModLog).filter(
            ModLog.guild_id == ctx.guild.id,
            ModLog.user_id == member.id
        ).order_by(ModLog.timestamp.desc()).limit(10).all()
        
        if not logs:
            await ctx.send(f'No moderation history for {member.mention}.')
            return
        
        embed = discord.Embed(
            title=f'Moderation Log: {member.name}',
            color=discord.Color.red()
        )
        
        for log in logs:
            moderator = ctx.guild.get_member(log.moderator_id)
            mod_name = moderator.name if moderator else 'Unknown'
            
            embed.add_field(
                name=f'{log.action.upper()} by {mod_name}',
                value=f'{log.reason}\n{log.timestamp.strftime("%Y-%m-%d %H:%M")}',
                inline=False
            )
        
        await ctx.send(embed=embed)
    
    finally:
        session.close()

This pattern logs moderation actions and retrieves them for review. order_by() with .desc() sorts newest-first. Audit logs are critical for transparency and accountability.

Migrating from SQLite to PostgreSQL​

For production, use PostgreSQL (scalable, more robust). Install the PostgreSQL adapter:

pip install psycopg2-binary

Change the DATABASE_URL in models.py:

# For PostgreSQL
DATABASE_URL = 'postgresql://user:password@localhost:5432/discord_bot'

The rest of your code remains unchanged; SQLAlchemy abstracts the database. PostgreSQL handles concurrent connections better and supports larger data volumes than SQLite.

Transactions and Error Handling​

For critical operations, use transactions to ensure data consistency:

@bot.command(name='transfer')
async def transfer(ctx, recipient: discord.Member, amount: float):
    """Transfer coins between users."""
    session = SessionLocal()
    try:
        # Fetch both users
        sender = session.query(UserProfile).filter(
            UserProfile.discord_id == ctx.author.id
        ).first()
        recipient_user = session.query(UserProfile).filter(
            UserProfile.discord_id == recipient.id
        ).first()
        
        # Check sufficient balance
        if not sender or sender.coins < amount:
            await ctx.send('Insufficient balance.')
            return
        
        if not recipient_user:
            recipient_user = UserProfile(
                discord_id=recipient.id,
                username=recipient.name,
                coins=amount
            )
            session.add(recipient_user)
        
        # Perform transfer
        sender.coins -= amount
        recipient_user.coins += amount
        
        # Commit all changes atomically
        session.commit()
        await ctx.send(
            f'Transferred {amount} coins to {recipient.mention}.'
        )
    
    except Exception as e:
        # Rollback if anything fails
        session.rollback()
        await ctx.send(f'Transfer failed: {str(e)}')
    
    finally:
        session.close()

Transactions ensure that either both transfers occur or neither does; if an exception occurs, rollback() undoes pending changes. This prevents inconsistent state (coins disappearing or duplicating).

DatabaseUse CaseProsCons
SQLiteDevelopment, local botsFile-based, no setupNot scalable beyond single process
PostgreSQLProduction, multi-serverScalable, robust, concurrentRequires external server
RedisCaching, leaderboardsVery fast, in-memoryNot persistent without write-through

Key Takeaways​

  • Use SQLAlchemy to define database models as Python classes; tables are auto-created with Base.metadata.create_all().
  • Create a SessionLocal() session for each database operation; always close it in a finally block.
  • Query records with .filter(), .first(), .all(), .order_by(), and .limit().
  • Add new records with session.add() and modify existing ones by changing attributes, then session.commit().
  • Use transactions and rollback() to ensure atomicity for multi-step operations like transfers.
  • Start with SQLite for development, migrate to PostgreSQL for production.

Frequently Asked Questions​

How do I run database migrations in production?​

Use Alembic (SQLAlchemy's migration tool) to version schema changes. See https://alembic.sqlalchemy.org/ for details. For simple bots, manually back up the database before schema changes.

Can I query users across multiple Discord guilds?​

Yes, always include guild_id in your model if data is guild-specific, then filter by both guild_id and user_id in queries.

How do I prevent SQL injection in SQLAlchemy?​

SQLAlchemy parameterizes queries automatically; avoid using string formatting for filters. Use .filter(Model.column == value), not .filter(f"column = '{value}'").

What happens if my database connection drops?​

SQLAlchemy raises an exception. Wrap queries in try-except, catch sqlalchemy.exc.OperationalError, and retry or gracefully degrade.

Can I use multiple databases simultaneously?​

Yes, create separate Engine and SessionLocal for each database, then use them independently. This is useful for migrating data or maintaining separate user and log databases.

Further Reading​