Asyncpg integration with discord.py examples

01-primer.md

Asyncpg examples with discord.py

This gist goes over how asyncpg can be integrated with discord.py. In addition with the example, this document also serves as an explanation on some design decisions for who are interested.

Contact

Please contact noellieuwu on the discord.py server if you are interested in contributing to changes or have feedback.

Notes

Connection Pools

This example explictly uses connection pools, which is a way to reduce the cost of opening and closing connections by maintaining a “pool” of open connections that can be passed from database operation to database operation as needed. This spares the expense of opening and closing fresh connections for each operation that would be done to the database. In addition of being best practice, they provide several advantages, such as:

• Improved database and application performance
• Lower latency
• Cheaper way to perform database operations

These princples apply to most databases such as PostgreSQL and MongoDB, but locally file-bound databases such as SQLite are not bound to these performance gains. Although asqlite does provide connection pools, Danny, the author of asqlite discussed that the rationale is to allow transactions to work properly with the barriers of asyncio (message can be found here). aiosqlite does not do this either as all connections are bound to file I/O speeds, and does not present an speedy alternative for reducing these latencies. Thus, when using SQLite as your database, connection pooling does not apply.

Biases

I cannot guantee that these examples are not full of biases, but I tried my best to give an rationale and non-biased presentation of how the examples would be designed. Some people may complain about an certain style of my design and would prefer to do things differently, but this serves as an basic example to guide and help others.

02-example.py

# This method is what is preferred by the author
# Below this gist is an alternative example with an cog integration

import asyncpg
import discord
from discord.ext import commands


class MyBot(commands.Bot):
    pool: asyncpg.Pool

    def __init__(
        self, initial_extensions: list[str], intents: discord.Intents, *args, **kwargs
    ):
        super().__init__(command_prefix="!", intents=intents, *args, **kwargs)

        # We want to assign an attribute here so this can be fetched at a later time
        self.initial_extensions = initial_extensions

    async def close(self) -> None:
        # In order to cleanly close the connection pool,
        # we need to override the close coroutine of commands.Bot
        # and instruct asyncpg to close the pool gracefully
        await self.pool.close()
        await super().close()

    async def setup_hook(self) -> None:
        # Pyright keeps on complaining that this is wrong but it is fine
        self.pool = await asyncpg.create_pool("connection_uri") # type: ignore


intents = discord.Intents.default()
intents.message_content = True

initial_extensions = ["admin", "general", "fun"]

bot = MyBot(initial_extensions=initial_extensions, intents=intents)


@bot.command(name="demo")
async def demo(ctx: commands.Context, amount: int) -> None:
    # An example query. Could be modified for your needs
    query = """
    INSERT INTO users (coins)
    VALUES ($1);
    """
    await self.pool.execute(query, amount)
    await ctx.send(f"Added {amount} coins")


bot.run("token")

03-alternative-example.py

import asyncio
import logging
from logging.handlers import RotatingFileHandler

import asyncpg
import discord
from discord.ext import commands


class MyBot(commands.Bot):
    def __init__(
        self,
        initial_extensions: list[str],
        intents: discord.Intents,
        pool: asyncpg.Pool,
        *args,
        **kwargs,
    ):
        super().__init__(command_prefix="!", intents=intents, *args, **kwargs)

        self.initial_extensions = initial_extensions
        
        # We want to assign an attribute here so this can be fetched at a later time
        self.pool = pool

    async def setup_hook(self) -> None:
        for extension in self.initial_extensions:
            await self.load_extension(extension)


async def main():
    # This is pulled from examples/advanced_startup.py within the discord.py examples
    logger = logging.getLogger("discord")
    logger.setLevel(logging.INFO)

    handler = RotatingFileHandler(
        filename="discord.log",
        encoding="utf-8",
        maxBytes=32 * 1024 * 1024,  # 32 MiB
        backupCount=5,  # Rotate through 5 files
    )
    dt_fmt = "%Y-%m-%d %H:%M:%S"
    formatter = logging.Formatter(
        "[{asctime}] [{levelname:<8}] {name}: {message}", dt_fmt, style="{"
    )
    handler.setFormatter(formatter)
    logger.addHandler(handler)

    # Set default intents for demo purposes
    intents = discord.Intents.default()
    intents.message_content = True

    initial_extensions = ["04-alternative-example-ext"]

    async with asyncpg.create_pool("connection_uri") as pool:
        async with MyBot(
            initial_extensions=initial_extensions, intents=intents, pool=pool
        ) as bot:
            await bot.start("token")


asyncio.run(main())

04-alternative-example-ext.py

from discord.ext import commands

class Demonstration(commands.Cog):
    def __init__(self, bot: commands.Bot):
        self.bot = bot
        self.pool = self.bot.pool

    @commands.command(name="demo")
    async def demo(self, ctx: commands.Context, amount: int) -> None:
        # An example query. Could be modified for your needs
        query = """
        INSERT INTO users (coins)
        VALUES ($1);
        """
        await self.pool.execute(query, amount)
        await ctx.send(f"Added {amount} coins")


async def setup(bot: commands.Bot) -> None:
    await bot.add_cog(Demonstration(bot))