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

1. Initialize a new MissionControl project
2. Write an action to retrieve data from your database
3. Connect to your SQL database & download your tables schema
4. 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

1. Make sure you have pg_dump installed - docs
2. 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

1. Add your repository to MissionControl, you will need your OPENAI_API_KEY and DISCORD_TOKEN
2. 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 running pdm dev
  • Make sure you have the DATABASE_URL variable in your .env file