discord.py SQLite: Async Data Storage with aiosqlite

The moment a Discord bot starts storing user data, the discord.py SQLite combination becomes one of the most practical answers: a zero-config, single-file database that needs no server. But there is a critical trap here; the standard sqlite3 module is blocking, and every query freezes the bot's event loop, slowing down all your commands. The fix is aiosqlite: a library that brings SQLite into the async/await world and fits discord.py 2.x perfectly. In this article we build a layer that stores economy/level data asynchronously and safely.

Why aiosqlite and not sqlite3?

discord.py is built on a fully asynchronous architecture: a single-threaded event loop processes incoming messages and interactions one after another. Every query you run with the standard sqlite3 blocks that loop; while the database writes to disk, your bot cannot even answer pings. aiosqlite, by contrast, hands the query off to a background thread and returns the result as an awaitable coroutine. The practical wins:

• Non-blocking I/O: a slow query does not lock up your other commands.
• Natural syntax: async with and await slot straight into your discord.py code.
• Self-contained: no separate server like MySQL/PostgreSQL to set up; the database is a single .db file.

Installation is one line: pip install aiosqlite. The underlying SQLite already ships with Python.

Wiring the database into the bot

The cleanest approach is to open the connection in setup_hook as the bot starts and attach it to the bot object as an attribute. That way every cog shares the same connection via self.bot.db. We also close the connection cleanly on shutdown:

import discord
from discord.ext import commands
import aiosqlite

class MyBot(commands.Bot):
    def __init__(self):
        intents = discord.Intents.default()
        super().__init__(command_prefix="!", intents=intents)
        self.db: aiosqlite.Connection | None = None

    async def setup_hook(self):
        self.db = await aiosqlite.connect("data.db")
        # Improves concurrent reads/writes
        await self.db.execute("PRAGMA journal_mode=WAL;")
        await self.init_db()

    async def init_db(self):
        await self.db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                user_id INTEGER PRIMARY KEY,
                balance INTEGER NOT NULL DEFAULT 0,
                xp      INTEGER NOT NULL DEFAULT 0
            )
        """)
        await self.db.commit()

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

PRAGMA journal_mode=WAL (Write-Ahead Logging) stops readers from blocking the writer; it makes a noticeable difference on a multi-user bot. Thanks to CREATE TABLE IF NOT EXISTS, the table is safely prepared on every startup.

Writing data: parameterised queries

Never paste user input straight into the SQL text; that opens the door to SQL injection. Use ? placeholders and a tuple of parameters instead. SQLite's INSERT ... ON CONFLICT syntax does an insert-when-missing, update-when-present (upsert) in a single query:

async def add_balance(db, user_id: int, amount: int):
    await db.execute(
        """
        INSERT INTO users (user_id, balance)
        VALUES (?, ?)
        ON CONFLICT(user_id)
        DO UPDATE SET balance = balance + excluded.balance
        """,
        (user_id, amount),
    )
    await db.commit()

The critical point: aiosqlite does not persist changes automatically. If you do not call await db.commit() after each write, the data is never flushed to disk and is lost when the bot restarts.

Reading data: fetchone and fetchall

On the read side you work through a cursor. An async with block closes the cursor automatically:

async def get_balance(db, user_id: int) -> int:
    async with db.execute(
        "SELECT balance FROM users WHERE user_id = ?",
        (user_id,),
    ) as cursor:
        row = await cursor.fetchone()
        return row[0] if row else 0

For multiple rows use fetchall; for example a level leaderboard:

async def top_users(db, limit: int = 10):
    async with db.execute(
        "SELECT user_id, xp FROM users ORDER BY xp DESC LIMIT ?",
        (limit,),
    ) as cursor:
        return await cursor.fetchall()

If you want to access results by column name, set db.row_factory = aiosqlite.Row; then you can read like row["balance"].

Using it inside a command

Wiring the helper functions into a command inside a cog is very short. Since self.bot.db is reachable everywhere, the command focuses only on the business logic:

from discord.ext import commands

class Economy(commands.Cog):
    def __init__(self, bot):
        self.bot = bot

    @commands.command()
    async def daily(self, ctx):
        await add_balance(self.bot.db, ctx.author.id, 100)
        bal = await get_balance(self.bot.db, ctx.author.id)
        await ctx.send(f"Claimed 100 daily coins. Balance: {bal}")

async def setup(bot):
    await bot.add_cog(Economy(bot))

Performance and safety tips

• One shared connection: keep a single connection for the bot's lifetime instead of opening and closing one per command.
• Add indexes: defining a CREATE INDEX on columns you query often (e.g. xp for sorting) speeds up large tables.
• Batch operations: when processing many rows at once, use executemany and a single commit at the end.
• Backups: SQLite is one file; to copy it without touching the bot, consider VACUUM INTO or the online backup API.

SQLite is more than enough for single-server, mid-sized bots. Once you reach hundreds of thousands of users with heavy concurrent writes, a move to PostgreSQL is worth considering; but up to that point aiosqlite is a clean, fast choice.

Frequently Asked Questions

What happens if I forget to call commit?

Your written data stays visible only within that session's connection but is never persisted to disk. When the bot restarts, every change after the last commit is lost. Make it a habit to call await db.commit() after every INSERT/UPDATE/DELETE.

Can't I just use the regular sqlite3 module?

Technically it works, but every query blocks the bot's event loop and delays all commands. On a low-traffic bot you may not notice; in real usage aiosqlite is the only right choice because it fits the asynchronous architecture.

Can multiple cogs safely use the same database?

Yes. As long as you share the connection on self.bot.db, all cogs use the same single connection. WAL mode eases concurrent reads; writes are queued and executed safely by SQLite.

Want to build a solid data layer for your bot? To design an aiosqlite setup, migrate your existing sqlite3 code to async, or build an economy/level system, get in touch with me.