Example code for connecting to and using a postgres database using discord.py and asyncpg.

example_db.py

# asyncpg docs: https://magicstack.github.io/asyncpg/current/

# This uses discord.py rewrite branch and .format(). If you are using the async branch of discord.py, it shouldn't matter much
# as only 'await ctx.send()' is something you should need to change. If you are using python 3.6+, you can use f strings as opposed to
# .format() for increased efficiency.

import discord
from discord.ext import commands
import asyncio
import asyncpg

async def run():
    description = "A bot written in Python that uses asyncpg to connect to a postgreSQL database."

    # NOTE: 127.0.0.1 is the loopback address. If your db is running on the same machine as the code, this address will work
    credentials = {"user": "USERNAME", "password": "PASSWORD", "database": "DATABSE", "host": "127.0.0.1"}
    db = await asyncpg.create_pool(**credentials)

    # Example create table code, you'll probably change it to suit you
    await db.execute("CREATE TABLE IF NOT EXISTS users(id bigint PRIMARY KEY, data text);")

    bot = Bot(description=description, db=db)
    try:
        await bot.start(config.token)
    except KeyboardInterrupt:
        await db.close()
        await bot.logout()

class Bot(commands.Bot):
    def __init__(self, **kwargs):
        super().__init__(
            description=kwargs.pop("description"),
            command_prefix="?"
        )

        self.db = kwargs.pop("db")

    async def on_ready(self):
        # .format() is for the lazy people who aren't on 3.6+
        print("Username: {0}\nID: {0.id}".format(self.user))

    # Example commands, don't use them
    # Don't even use this format, this is not an example of how to make commands.
    @commands.command()
    async def query(ctx):
        query = "SELECT * FROM users WHERE id = $1;"

        # This returns a asyncpg.Record object, which is similar to a dict
        row = await bot.db.fetchrow(query, ctx.author.id)
        await ctx.send("{}: {}".format(row["id"], row["data"]))

    @commands.command()
    async def update(ctx, *, new_data: str):
        # Once the code exits the transaction block, changes made in the block are committed to the db

        connection = await bot.db.acquire()
        async with connection.transaction():
            query = "UPDATE users SET data = $1 WHERE id = $2"
            await bot.db.execute(query, new_data, ctx.author.id)
        await bot.db.release(connection)

        await ctx.send("NEW:\n{}: {}".format(ctx.author.id, new_data))

loop = asyncio.get_event_loop()
loop.run_until_complete(run())

what is the use of the run function, and the bot variable, and the bot class?

I mean, I can just declare the database in the on_ready event, right?

@xMont4hdaRx
on_ready can be called multiple times. Other events where you may check your database can be called before on_ready.

The main reason I did it the way I did is because at the time of me writing this gist I was looking at an example of sub-classing bot.
You do not have to do it this way, it is just an example.

If you wanted to do it with on_ready you could check within there if the database is already declared in case of it running multiple times and add an await bot.wait_until_ready() to the beginning of any event (such as on_message, which would also apply to commands assuming you override the default on_message with an event instead of listener) where you access the database.

While I won't say the way I do it is the best, I will say it feels better than doing it in on_ready as you do not have to check that the database connection hasn't already been opened.

Actually I like the use of this function, because it allows you to do stuff like on keyboard interrupt have the database close and the bot logout instead of just "crashing"

Love the keyboard interrupt function. Never used it, but now its so much easier.

how to implement with a Flask?

how to implement with a Flask?

How do you want to use it with Flask @perymerdeka?

Can you show an example of this without a bot class? This seems really helpful but my bot doesn't have a bot class, it instead does client = commands.AutoShardedBot(command_prefix = get_prefix, intents=intents). Thanks!

How did you make variable 'bot' visible outside of run() function?

@xMont4hdaRx on_ready can be called multiple times. Other events where you may check your database can be called before on_ready.

The main reason I did it the way I did is because at the time of me writing this gist I was looking at an example of sub-classing bot. You do not have to do it this way, it is just an example.

If you wanted to do it with on_ready you could check within there if the database is already declared in case of it running multiple times and add an await bot.wait_until_ready() to the beginning of any event (such as on_message, which would also apply to commands assuming you override the default on_message with an event instead of listener) where you access the database.

While I won't say the way I do it is the best, I will say it feels better than doing it in on_ready as you do not have to check that the database connection hasn't already been opened.

There is a setup_hook function now tho. Which you can use to do it.

https://discordpy.readthedocs.io/en/latest/migrating.html#asyncio-event-loop-changes
https://discordpy.readthedocs.io/en/latest/ext/commands/api.html#discord.ext.commands.Bot.setup_hook
https://gist.github.com/Master326486/6cf48c1ca0509b98e673451e356ba625#setup-hook

you shouldn't be using .format. btw why are you using that weird .commit thing in the update command? can't you just do bot.db.execute?

you shouldn't be using .format.

From the comment at the top of the file: "If you are using python 3.6+, you can use f strings as opposed to .format() for increased efficiency."

btw why are you using that weird .commit thing in the update command? can't you just do bot.db.execute?

Probably. It has been so long since I touched a Discord bot or Postgres code, and basically 5 years since I touched this gist, I'm not really sure. I think if you want to roll back SQL database operations you need to wrap them in a transaction. Also if you have multiple operations that should be performed as a unit they should be wrapped in a transaction together, so if one of them fails the whole transaction is thrown out and you are not left with in-between state that your application doesn't want to handle. There's only one SQL action there, so maybe I just put it there as an example of how to do transactions.

I also just noticed the person who asked about bot being used in places it's not declared. Yeah, this code probably doesn't actually work, huh. Probably supposed to use the self parameter in those commands, then access via self? Or maybe via ctx?

@Ender2K89 The old way for doing DB commits was to first stage them then commit changes. Not sure why, not sure if its changed, but that is how it used to work.