Create a Telegram Bot to Query SQL Database on the go

Hey there, Darian Vance here. As a Senior DevOps Engineer at TechResolve, my day is a constant juggle of deployments, monitoring, and firefighting. I used to get pings from the product team asking for quick stats—like “How many new users signed up today?”—and it meant firing up my laptop, connecting to the VPN, and running a manual query. It felt like a massive waste of time. That’s when I built this little tool: a Telegram bot that acts as my personal database assistant. Now, I just type /user_count into my phone and get an instant answer. It’s a small change that saves me a couple of hours every week. Today, I’m going to walk you through how to build one yourself.

Prerequisites

Before we dive in, make sure you have a few things ready. This guide assumes you’re comfortable with the basics.

• Python 3.x installed on your machine or server.
• A Telegram account. You’ll need it to create and interact with your bot.
• Access credentials for an SQL database (e.g., PostgreSQL, MySQL, SQLite). We’ll be doing read-only queries.
• A basic grasp of SQL. You should know how to write a SELECT statement.

The Guide: Step-by-Step

Step 1: Create Your Bot and Get the Token

First things first, you need to register your bot with Telegram. It’s surprisingly easy.

1. Open your Telegram app and search for a user called “BotFather” (he’s the one with the official checkmark).
2. Start a chat with him and send the /newbot command.
3. He’ll ask for a name for your bot (like “TechResolve DB Bot”) and a unique username (which must end in “bot”, e.g., TechResolveDB_bot).
4. Once you’re done, BotFather will give you a precious piece of information: an HTTP API token. It’ll look something like 123456:ABC-DEF1234ghIkl-zyx57W2v1u123456. Guard this token like a password; anyone with it can control your bot.

Step 2: Setting Up Your Python Environment

I’ll skip the standard virtual environment setup since you likely have your own workflow for that. Let’s jump straight to the dependencies you’ll need. You’ll want to install a few key Python libraries using pip: python-telegram-bot (for the Telegram API), python-dotenv (to manage our secrets), and a database driver specific to your SQL dialect, like psycopg2-binary for PostgreSQL.

Next, create two files in your project directory: bot.py for our main logic and config.env to store our secrets. Never commit your config.env file to version control!

Your config.env file should look like this:

TELEGRAM_TOKEN="YOUR_TELEGRAM_TOKEN_HERE"
DB_NAME="your_database_name"
DB_USER="your_database_user"
DB_PASSWORD="your_database_password"
DB_HOST="your_database_host"
DB_PORT="5432"

Step 3: Writing the Bot Logic in `bot.py`

Alright, let’s get our hands dirty. We’ll build this script piece by piece, and I’ll explain the logic as we go. The goal is to create a bot that listens for specific commands (like /users), runs a predefined SQL query, and sends the result back.

First, we import our libraries and load the environment variables from our config.env file.

import os
import psycopg2
from dotenv import load_dotenv
from telegram import Update
from telegram.ext import Application, CommandHandler, ContextTypes

# Load environment variables from config.env
load_dotenv('config.env')

TELEGRAM_TOKEN = os.getenv("TELEGRAM_TOKEN")
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT")

# A dictionary to map commands to safe, predefined SQL queries
# This is CRITICAL for security. Do not run arbitrary user input!
WHITELISTED_QUERIES = {
    'users': "SELECT COUNT(*) FROM users;",
    'recent_logs': "SELECT level, message FROM logs ORDER BY timestamp DESC LIMIT 5;"
}

Pro Tip: Notice the WHITELISTED_QUERIES dictionary. This is the most important security feature of this bot. We are mapping a simple, user-friendly command (like ‘users’) to a hardcoded, safe SQL query. We will never execute SQL that comes directly from a user message. That’s how you get SQL injection vulnerabilities.

Next, let’s create a function to handle the database connection and query execution. This keeps our code clean.

async def execute_query(query: str):
    """Connects to the database, executes a query, and returns the result."""
    conn = None
    try:
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST,
            port=DB_PORT
        )
        cur = conn.cursor()
        cur.execute(query)
        result = cur.fetchall()
        cur.close()
        return result
    except Exception as e:
        print(f"Database error: {e}")
        return None
    finally:
        if conn is not None:
            conn.close()

Now for the function that handles incoming Telegram commands. This function will be triggered when a user sends a command like /query users.

async def query_command_handler(update: Update, context: ContextTypes.DEFAULT_TYPE):
    """Handles the /query <key> command."""
    if not context.args:
        await update.message.reply_text("Please provide a query key. Usage: /query <key>\nAvailable keys: users, recent_logs")
        return

    query_key = context.args[0]
    
    if query_key not in WHITELISTED_QUERIES:
        await update.message.reply_text(f"Sorry, '{query_key}' is not a valid query key.")
        return
    
    sql_query = WHITELISTED_QUERIES[query_key]
    
    await update.message.reply_text("Running query...")
    
    results = await execute_query(sql_query)
    
    if results is None:
        await update.message.reply_text("An error occurred while querying the database.")
        return

    if not results:
        await update.message.reply_text("Query executed successfully, but returned no results.")
        return

    # Format the results into a readable string
    formatted_result = ""
    for row in results:
        formatted_result += " | ".join(map(str, row)) + "\n"
        
    # Use a code block for monospaced font in Telegram
    await update.message.reply_text(f"<pre>{formatted_result}</pre>", parse_mode='HTML')

async def start_command_handler(update: Update, context: ContextTypes.DEFAULT_TYPE):
    """Sends a welcome message."""
    await update.message.reply_text("Welcome to the TechResolve DB Bot! Use /query <key> to get data.")

Finally, we need the main part of the script to tie everything together and start the bot.

def main():
    """Start the bot."""
    print("Bot is starting...")
    application = Application.builder().token(TELEGRAM_TOKEN).build()

    # Register command handlers
    application.add_handler(CommandHandler("start", start_command_handler))
    application.add_handler(CommandHandler("query", query_command_handler))

    # Start polling for updates
    application.run_polling()

if __name__ == "__main__":
    main()

Pro Tip: In my production setups, I use webhooks instead of polling. Polling constantly asks Telegram “Any new messages?”, while a webhook waits for Telegram to send a message to a specific URL when one arrives. It’s much more efficient and scalable, but polling is perfectly fine for getting started.

And that’s it! You can now run this script on a server (python3 bot.py), and your bot will come to life. Send it a /query users command in Telegram, and watch the magic happen.

Common Pitfalls (Where I Usually Mess Up)

I’ve built a few of these, and here are the traps I’ve fallen into so you don’t have to:

• Firewall Rules: This is the classic one. You run the bot, everything looks fine, but it can’t connect to the database. Nine times out of ten, it’s because the IP address of the server running the bot hasn’t been whitelisted in the database’s firewall rules. Always check your network access controls first.
• Forgetting to Sanitize: I can’t stress this enough. My first-ever version of a similar tool was a little too “flexible” with its inputs. Whitelisting commands as we did above is non-negotiable.
• Leaking Credentials: Accidentally committing the `config.env` file or hardcoding a token in the script is a rookie mistake we all make once. Use a `.gitignore` file to explicitly ignore your config file, and consider a proper secrets manager for production environments.
• Query Timeouts: If a whitelisted query is too slow, the Telegram API might time out before your bot can send a reply. Keep the queries fast and efficient. If you need to run a heavy report, have the bot send a “Working on it…” message and then edit it later with the result.

Conclusion

You now have a secure, efficient way to get critical data from your database without ever leaving your chat app. This is more than just a convenience; it’s a productivity multiplier. From here, you can expand it by adding more whitelisted queries, formatting the output into tables, or even creating charts. You’ve built a solid foundation. Now go make it your own.

Happy coding!

– Darian Vance

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions