How to build a backoffice Discord bot connected to your SQL database
In this tutorial, we will build a bot that performs backoffice tasks, specifically, the bot will be able to retrieve data from your database and summarize the results right from your Discord server.
Before we dive into the technical guide, let's explore why a backoffice bot can be transformative for your business through a customer story - the story of PolyLM.
Meet PolyLM
PolyLM began with a vision to bridge a gap in AI technology for languages like Hebrew that were often overlooked. As our startup grew and the operational demands started to swell, we faced a critical decision. The challenge wasn't just about managing data; it was about managing our time.
That's where our journey with Artie began. Initially, Artie was a simple bot within Discord, integrated to streamline the small yet time-consuming tasks that pulled us away from our core mission. We started by programming it to send us real-time alerts from our analytics stream, something that kept us connected to user activities and system health.
But as Artie proved its worth, we developed it further. It became the digital nerve center of our operationsβa back office bot that could execute tasks with a quick command. Cancelling subscriptions, updating user plans, and maintaining system access became actions we could initiate from anywhere, directly within our chats.
Helping to build and evolve Artie was a transformative experience. It showcased the potential of what a few lines of code and the right tools could do for a startup like ours.
MissionControl is the next step in that evolution. It's a platform that makes it easy to build, deploy and maintain tool-use GPTs, and it's a platform we're proud to support.
Prerequisites
- SQL database (This guide uses PostgreSQL from Supabase)
- Discord Token
- OpenAI Token
- git & GitHub account
- PDM & Python(3.10+) environment - we recommend pyenv
If you are new to Python, check out our Python environment setup tutorial here.
Overview
- Initialize a new MissionControl project
- Write an action to retrieve data from your database
- Connect to your SQL database & download your tables schema
- Deploy! πππ
1. Initialize a new MissionControl project
Initialize a new project
mkdir backoffice-bot
cd backoffice-bot
git init
pdm init --copier gh:loopbackai/missioncontrol-template
pdm install
This will create a new project using the MissionControl template, install the dependencies and create a new git repository.
Test it
Run the following command to talk to your bot in the terminal
pdm dev # make sure you have the OPENAI_API_KEY variable in your .env file
The bot at this point can perform the 2 example actions that come with the template in the actions.py
file
Recap
At this point we have a new project with the following structure
βββ .env # Store your secrets here
βββ Dockerfile # Used to build your project
βββ pyproject.toml
βββ src
βΒ Β βββ jarvis_backoffice
βΒ Β βΒ Β βββ __init__.py
βΒ Β βΒ Β βββ actions.py # Where you will write your actions
βββ tests
Feel free to explore the code in the src
folder, we will focus on the actions.py
file in the next step.
You may also run pdm test
to run the tests in the tests
folder.
2. Write an action to retrieve data from your database
LaunchKit turns your functions to actions that can be invoked by your bot.
The bot will choose the function based on the user input and the function signature (parameter names, types) and docstring. So it's important to write a good action with:
- Descriptive name
- Descriptive parameter names and types
- Detailed docstring
Write your function
DATABASE_URL = os.getenv("DATABASE_URL")
def query_orders_and_products(postgres_sql_query: str):
"""
Query orders and products tables
Params:
- postgres_sql_query: a postgres SQL query to execute
table schemas:
-- Insert your table schemas here --
"""
# use pandas to query your database and get a dataframe
df = pd.read_sql(postgres_sql_query.replace("%", "%%"), DATABASE_URL)
# convert the dataframe to a list of dictionaries so the bot can read it
df = df.applymap(str)
df.reset_index(inplace=True)
return df.to_dict(orient="records")
Configure
Install dependencies for the new function
pdm add pandas psycopg2-binary sqlalchemy
Add a DATABASE_URL
variable to your .env file, grab your db connection string from your Supabase project settings.
Your connection string looks something like postgresql://postgres:DB_PASSWORD@db.PROJECT_ID.supabase.co:5432/postgres
echo "DATABASE_URL=<YOUR_DB_CONNECTION_STRING>" >> .env
Finally we will need to modify the actions.py
file
actions = Launchkit([query_orders_and_products, launch_rocket, add_user_to_newsletter])
Test it
pdm dev # make sure you have the .env file with the DATABASE_URL and OPENAI_API_KEY variable
You probably noticed the function is not very useful at this point, your bot doesn't know how your tables are structured so it can't formulate queries. Let's fix that in the next step.
3. Connect to your SQL database & download your tables schema
This step assumes you have a Supabase project up and running if not create one here to get started.
We are going to use pg_dump to download the schema for your tables, another option is to use the Supabase UI, go to the table editor and click on Definition
button to view the table schema.
Download the schema
- Make sure you have pg_dump installed - docs
- Download the schema of the tables you want your bot to interact with
DATABASE_URL=postgresql://postgres:DB_PASSWORD@db.PROJECT_ID.supabase.co:5432/postgres
pg_dump $DATABASE_URL --schema public --schema-only --table orders
pg_dump $DATABASE_URL --schema public --schema-only --table products
In our example this is the relevant part of the schema
CREATE TABLE public.products (
id uuid DEFAULT gen_random_uuid() NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
name text NOT NULL,
price real NOT NULL,
description text
);
CREATE TABLE public.orders (
id uuid DEFAULT gen_random_uuid() NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
product_id uuid NOT NULL,
customer_id uuid DEFAULT gen_random_uuid() NOT NULL,
paid real NOT NULL,
discount real NOT NULL
);
Update your function
DATABASE_URL = os.getenv("DATABASE_URL")
def query_orders_and_products(postgres_sql_query: str):
"""
Query orders and products tables
Params:
- postgres_sql_query: a postgres SQL query to execute
table schemas:
CREATE TABLE public.products (
id uuid DEFAULT gen_random_uuid() NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
name text NOT NULL,
price real NOT NULL, -- in dollars
description text
);
CREATE TABLE public.orders (
id uuid DEFAULT gen_random_uuid() NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
product_id uuid NOT NULL,
customer_id uuid DEFAULT gen_random_uuid() NOT NULL,
paid real NOT NULL,
discount real NOT NULL
"""
df = pd.read_sql(postgres_sql_query.replace("%", "%%"), DATABASE_URL)
df = df.applymap(str)
df.reset_index(inplace=True)
return df.to_dict(orient="records")
It's a good idea to annotate the schema with comments to make the columns function and shape more clear to the bot.
Test it
pdm dev # make sure you have the .env file with the DATABASE_URL and OPENAI_API_KEY variable
Now you can ask your bot questions like
how many orders were made today
how many orders were made this week
what products do we have
what products do we have that cost less than 100 dollars
4. Deploy! πππ
We will deploy with MissionControl. MissionControl let us ship LaunchKit projects to Discord(and other platforms).
Upload to github
Commit your code
git add .
git commit -m "first commit"
Create a GitHub repository and push your code according to the instructions on GitHub.
Connect your repo to MissionControl
- Add your repository to MissionControl, you will need your
OPENAI_API_KEY
andDISCORD_TOKEN
- Add your DATABASE_URL secret in the MissionControl UI (Project Overview -> Secrets)
Now every time you push to your repository MissionControl will deploy your bot to Discord
Test it
After a few minutes your bot will be deployed to Discord (Notice the bot changing status to green), you can test it by sending a message to your bot in Discord.
Conclusion
In this tutorial we learned how to build a backoffice bot connected to a SQL database. We learned how to write a good action and how to deploy it to Discord with MissionControl.
We would love to hear your feedback, please reach out to us on Discord or Twitter.
Next Steps
Next up - we will learn how to write actions that modify your database safely, stay tuned!
FAQ & Troubleshooting
- I'm getting a
psycopg2.OperationalError: could not connect to server: Connection refused
error when runningpdm dev
- Make sure you have the DATABASE_URL variable in your .env file