How to Make a Discord Bot Use Postgres for Persistence

Running a Discord bot that restarts or scales across multiple instances requires a reliable database to store user data, server settings, and command history. Without persistence, all data is lost when the bot goes offline. SQLite works for small bots but struggles with concurrent writes and larger datasets. This article explains how to connect your Discord bot to a PostgreSQL database to store data permanently, even after restarts or crashes.

What Is Postgres Persistence and Why Your Bot Needs It

PostgreSQL is an open-source relational database that stores data in tables with strict schemas. For a Discord bot, persistence means that data such as user XP, server prefixes, and moderation logs survive bot restarts. Without a database, your bot stores everything in memory, and a single restart wipes all data.

Postgres offers several advantages over file-based storage like JSON or SQLite:

• Concurrent connections from multiple bot instances without corruption.
• Advanced querying with JOINs, indexes, and full-text search.
• Role-based access control for secure data management.

Before you begin, you need a running PostgreSQL server. You can install it locally or use a cloud provider like Amazon RDS, Google Cloud SQL, or Railway. You also need your Discord bot token and the Python discord.py library installed.

Prerequisites

• Python 3.8 or higher installed on your system.
• A Discord bot created at the Discord Developer Portal with a token.
• PostgreSQL installed and running. Default port is 5432.
• Database name, username, and password for Postgres.

Steps to Connect Your Discord Bot to PostgreSQL

The following steps use the asyncpg library, which is asynchronous and works well with discord.py. If you prefer a synchronous approach, use psycopg2 instead. The steps assume you have a basic bot file called bot.py.

1. Install the asyncpg library
   Open your terminal and run: pip install asyncpg. If you plan to use psycopg2, run pip install psycopg2-binary instead.
2. Create a database and user in Postgres
   Connect to your Postgres server using a client like pgAdmin or the command line. Run: CREATE DATABASE discord_bot; and CREATE USER bot_user WITH PASSWORD 'your_password';. Then grant all privileges on the database to that user.
3. Import asyncpg in your bot code
   At the top of your bot.py file, add: import asyncpg. If using psycopg2, add: import psycopg2.
4. Create a connection pool on bot startup
   Inside your on_ready event, create a connection pool. Example code:

   async def on_ready():
       bot.db_pool = await asyncpg.create_pool(
           user='bot_user',
           password='your_password',
           database='discord_bot',
           host='localhost',
           port=5432
       )
       print('Connected to Postgres')

5. Create a table for your data
   Use a CREATE TABLE IF NOT EXISTS statement to ensure the table exists on every startup. Example:

   async with bot.db_pool.acquire() as conn:
       await conn.execute('''
           CREATE TABLE IF NOT EXISTS user_xp (
               user_id BIGINT PRIMARY KEY,
               xp INTEGER DEFAULT 0,
               level INTEGER DEFAULT 1
           )
       ''')

6. Write a command that stores data
   Create a slash command or prefix command that inserts or updates data. Example:

   @bot.tree.command(name='add_xp')
   async def add_xp(interaction: discord.Interaction, user: discord.User, amount: int):
       async with bot.db_pool.acquire() as conn:
           await conn.execute('''
               INSERT INTO user_xp (user_id, xp)
               VALUES ($1, $2)
               ON CONFLICT (user_id)
               DO UPDATE SET xp = user_xp.xp + $2
           ''', user.id, amount)
       await interaction.response.send_message('XP added')

7. Read data from the database
   To retrieve data, use a SELECT query. Example:

   async with bot.db_pool.acquire() as conn:
       row = await conn.fetchrow('SELECT xp, level FROM user_xp WHERE user_id = $1', user.id)
       if row:
           xp = row['xp']
           level = row['level']

Using psycopg2 Instead of asyncpg

If your bot is not asynchronous or you prefer a simpler setup, use psycopg2. The main difference is that you call cursor.execute() instead of await connection.execute(). Example:

import psycopg2

conn = psycopg2.connect(
    user='bot_user',
    password='your_password',
    database='discord_bot',
    host='localhost',
    port=5432
)
cursor = conn.cursor()
cursor.execute('SELECT  FROM user_xp')
rows = cursor.fetchall()

Remember to call conn.commit() after INSERT, UPDATE, or DELETE operations, and cursor.close() and conn.close() when done.

Common Mistakes and How to Avoid Them

Connection Timeout When the Bot Starts

If your bot fails to connect to Postgres, check that the database server is running and accepting connections. Verify the host and port. If using a remote database, ensure the IP address is whitelisted in the cloud provider’s firewall settings.

Duplicate Key Errors on Insert

When inserting data for a user that already exists, you get a duplicate key error. Use ON CONFLICT DO UPDATE (UPSERT) to update the existing row instead of failing. The example in step 6 shows this pattern.

Bot Crashes After Database Query

An unhandled exception during a database query will crash your bot. Wrap all database operations in try-except blocks. Use async with bot.db_pool.acquire() as conn: to ensure connections are returned to the pool even on error.

Data Not Saved After Restart

If you use psycopg2 and forget to call conn.commit(), changes are not saved to the database. Always commit after write operations. For asyncpg, commits happen automatically within the execute method.

Discord Bot Database Options: PostgreSQL vs SQLite vs JSON

Item	PostgreSQL	SQLite	JSON File
Concurrent connections	Unlimited	Single writer, multiple readers	Not supported
Data integrity	ACID compliant	ACID compliant	No built-in integrity
Query language	Full SQL	Full SQL	No query language
Setup complexity	Requires server install	File-based, no server	No setup
Best for	Large bots, multiple instances	Small single-instance bots	Prototypes and testing

PostgreSQL is the best choice for production bots that need to handle many users and commands without data loss. SQLite works for small personal bots. JSON files are only suitable for quick testing because they cannot handle concurrent writes and are slow for large datasets.

Now your Discord bot can store data permanently using PostgreSQL. Start by running the bot with the database connection and test the /add_xp command. Next, add more tables for server settings or moderation logs. For advanced performance, consider using connection pooling with a minimum and maximum pool size to handle high traffic.