Setting Up A PostgreSQL Database For Discord Bots

If you make Discord bots and have any sort of data storage needs, I recommend using some kind of database. In my case I use a PostgreSQL database because it is fast, open source and satisfies all my needs. That being said if you prefer other databases like mysql or sqlite, those should easily satisfy any Discord bot need. In this blog I go over setting up the database, how to access the data through a GUI (pgAdmin), and how to access the database through your bot (done in python with discord.py and psycopg).

Setting Up A PostgreSQL Database

There are many ways to create a PostgreSQL database. One method is through a command line tool like psql and another is through some sort of GUI, such as pgAdmin4. I personally use pgAdmin4 and that is what I will be using for in this tutorial. To download pgAdmin4 use the following link https://www.pgadmin.org/download/pgadmin-4-windows/. If you don't have PostgreSQL either, download it here https://www.postgresql.org/download/

First things first, download pgAdmin4 and once you have it installed open the application. Next, right click on Servers -> PostgreSQL 15 (default one) -> Create -> Database.

Once the create window opens, give the database a name, I personally just use postgres. I personally like using the default owner which is postgres.

A new database will show up in the database tab. If you expand it and click Schemas -> Public -> Tables you can access and create all the table you will use with your Discord bots.

Accessing Your Database with a GUI (pgAdmin4)

Following the steps above on installing and accessing your tables through pgAdmin4. Being able to create and edit tables in pgAdmin is very easy. You can also use command prompts to create tables but using a GUI to create them makes it that much more visual and in my opinion, easier.

To create a table, from Servers click <server name> -> Databases -> <database name> -> Schema -> <schema name> -> Tables. Right click Tables -> Create -> Table. The basics to creating a table is to add info just in the General and Columns tabs, if you need more specific table setups then you can use the other tabs. In the general tab add a name and choose the correct owner and schema if you have multiple.

In the Columns tab, add the necessary columns you need. Because most of the uses of my database are for storing data related to Discord, I make the primary key usually a discord user ID or Discord Channel ID since it will always be unique member to member or channel to channel. Of course per table you can only have one pk so make sure to only select one, I also like to make all the default columns not null. 

Some key data types to note are:

• text (unlimited character string)
• char (limited length string)
• varchar (variable length string)
• integer (limited length int, 4 byte)
• bigint (longer length int, 8 byte)
• boolean (true/false)

All these data types can be found here.

After you have finished this the table will show up in the table drop down on the left side. you can then view the table by right clicking it and selecting view -> all rows. You can then easily add rows to the table manually but if you are planning on generating data with a bot or some script then you can leave it.

Basic Reading And Writing To A PostgreSQL Database with Psycopg

Psycopg is a great python library for accessing your data from your postgresql database. 

Leave a comment:

Comments:

On Dec. 2, 2024  Evolution.Org.Ua wrote:

Howdy! This is my first visit to your blog! We are a team of volunteers and starting a nnew iniiative in a community in the same niche. Your blog provided us useful information to work on. You have done a outstanding job! https://evolution.org.ua/