Building a Streamlit App That Chats with Your PostgreSQL Database

Your database already has the answers. The problem is that most of your users don’t speak SQL. Bridging that gap is what this project is about. By wiring together a Streamlit chat interface, a live psycopg2 connection, and a language model that translates plain English into working queries, you end up with something genuinely useful: a tool that lets anyone ask your data a question and get a real answer back, no query editor required.

Why This Approach Beats a Static Dashboard

Traditional dashboards are built around the questions you thought to ask when you built them. The moment a stakeholder wants something slightly different, you’re back in code. A conversational database interface flips that dynamic entirely. The user defines the question at runtime. The LLM handles translation. You stop being a query-writing bottleneck for people who just need a number.

This matters especially for teams already comfortable with tools like Redis caching layers or NoSQL document stores, where the schema can shift and rigid reporting tools start to crack. PostgreSQL’s relational structure is actually an advantage here. A well-defined schema gives the LLM clear guardrails for generating accurate SQL, because the model knows exactly what tables and columns exist before it writes a single line.

Three Approaches to Database Querying Worth Understanding

Before writing a line of code, it helps to understand what you’re choosing over and why the AI-assisted approach occupies its own lane.

SQL Access Methods and Their Practical Trade-offs

The AI-assisted approach isn’t a replacement for the others. It fills the gap where your users aren’t developers but your data lives in a relational store that absolutely deserves more than a canned report.

Setting Up Your Python Environment

You need four packages to get this working. Install them in this order so you catch any dependency issues early and don’t end up debugging a broken environment mid-build:

1. streamlit , the web UI framework that turns a Python script into an interactive app with a single command.
2. psycopg2-binary , the PostgreSQL adapter for Python. The binary distribution skips the C compiler requirement and installs cleanly on most machines.
3. anthropic , the official SDK for calling Claude models, handling authentication, request serialization, and retry logic for you.
4. pandas , for turning query result rows into a DataFrame that Streamlit can render as a proper data table.

One command handles all four: pip install streamlit psycopg2-binary anthropic pandas. Once that finishes without errors, your environment is ready.

Opening the psycopg2 Connection

psycopg2 is the standard PostgreSQL adapter for Python. psycopg2’s connection documentation confirms that connection objects are thread-safe and can be reused across requests, which matters for a Streamlit app where multiple script reruns happen within a single user session.

Keep credentials out of your script by reading from environment variables. Never hardcode them:

import psycopg2
import os

def get_connection():
    return psycopg2.connect(
        host=os.environ["PG_HOST"],
        port=int(os.environ.get("PG_PORT", 5432)),
        dbname=os.environ["PG_DATABASE"],
        user=os.environ["PG_USER"],
        password=os.environ["PG_PASSWORD"],
    )

Wrapping this in a function rather than opening a module-level connection is intentional. Streamlit reruns your entire script on every interaction. A module-level connection goes stale or closes between reruns. A function call gives you a fresh connection exactly when you need it.

Reading the Schema So the LLM Knows What Exists

The LLM can only generate accurate SQL if it knows what tables and columns actually exist in your database. Pull the schema at runtime from PostgreSQL’s built-in information schema view. This keeps your prompt synchronized with the real database structure, even as it evolves over time:

def get_schema(conn):
    cursor = conn.cursor()
    cursor.execute("""
        SELECT table_name, column_name, data_type
        FROM information_schema.columns
        WHERE table_schema = 'public'
        ORDER BY table_name, ordinal_position;
    """)
    rows = cursor.fetchall()
    cursor.close()

    schema_lines = []
    current_table = None
    for table, column, dtype in rows:
        if table != current_table:
            schema_lines.append(f"\nTable: {table}")
            current_table = table
        schema_lines.append(f"  - {column} ({dtype})")

    return "\n".join(schema_lines)

This produces a compact, readable schema string. It fits cleanly inside a prompt without burning tokens on irrelevant metadata like table privileges or constraint names. For large databases with dozens of tables, filter the WHERE clause to expose only the tables relevant to your use case. A focused schema reliably produces better SQL than dumping everything.

Building the Streamlit Chat Interface

Streamlit’s st.chat_input and st.chat_message components give you a proper chat layout in very little code. The session state dictionary keeps messages alive between reruns, which is what makes the conversation feel continuous rather than resetting on every keypress:

import streamlit as st
import pandas as pd

st.title("Chat with Your Database")

if "messages" not in st.session_state:
    st.session_state.messages = []

for msg in st.session_state.messages:
    with st.chat_message(msg["role"]):
        st.markdown(msg["content"])

if prompt := st.chat_input("Ask something about your data..."):
    st.session_state.messages.append({"role": "user", "content": prompt})
    with st.chat_message("user"):
        st.markdown(prompt)

The walrus operator (:=) inside the if block is idiomatic Streamlit. It captures the input value and checks whether it’s non-empty in a single expression. If the user submits an empty message, the block simply doesn’t execute.

Constructing the Prompt That Produces Reliable SQL

Prompt design is where most projects like this succeed or fail. The model needs to know the schema, the SQL dialect (PostgreSQL specifically), and exactly what format you expect back. Keep the instructions terse and unambiguous:

def build_prompt(schema: str, question: str) -> str:
    return f"""You are a PostgreSQL query assistant.
Given the schema below, write a single valid SQL SELECT statement that answers the user's question.
Return ONLY the SQL ,  no explanation, no markdown fences, no commentary.

Schema:
{schema}

Question: {question}
SQL:"""

The critical instruction is “return ONLY the SQL.” Without it, models frequently wrap output in markdown code fences or prepend a sentence of explanation, both of which break your downstream parsing. Explicit output formatting avoids that entirely. The trailing “SQL:” on its own line also primes the model to begin its response with a SELECT statement rather than a preamble.

Calling the AI Layer Without Paying to Prototype

This is where the LLM enters the picture. When you’re still iterating on prompt design and working through edge cases in your schema, paying per token for a large model doesn’t make sense. Starting with free Claude Haiku lets you run hundreds of test queries at zero cost while the integration takes shape, and you can always swap in a larger model later once you’ve validated the approach on real data.

Here’s the call using the Anthropic Python SDK:

import anthropic

def generate_sql(schema: str, question: str, api_key: str) -> str:
    client = anthropic.Anthropic(api_key=api_key)
    message = client.messages.create(
        model="claude-haiku-4-5-20251001",
        max_tokens=512,
        messages=[
            {
                "role": "user",
                "content": build_prompt(schema, question)
            }
        ]
    )
    return message.content[0].text.strip()

The response comes back as a list of content blocks. For text-only responses, message.content[0].text is always the correct place to read from. Strip whitespace before passing the SQL string to psycopg2, because a leading newline will cause the cursor to raise a syntax error even on a perfectly valid query.

Running the Generated Query and Showing the Results

You have a SQL string from the model. Now you execute it and display the output. Wrap execution in a try/except block, because the model will occasionally generate syntactically incorrect SQL on ambiguous questions or unusual column names. Surfacing the error alongside the generated query makes debugging far faster:

def run_query(conn, sql: str):
    try:
        cursor = conn.cursor()
        cursor.execute(sql)
        columns = [desc[0] for desc in cursor.description]
        rows = cursor.fetchall()
        cursor.close()
        return pd.DataFrame(rows, columns=columns), None
    except Exception as e:
        return None, str(e)

# Inside Streamlit's rerun flow:
conn = get_connection()
schema = get_schema(conn)
sql = generate_sql(schema, prompt, api_key=st.secrets["ANTHROPIC_API_KEY"])
df, error = run_query(conn, sql)
conn.close()

with st.chat_message("assistant"):
    if error:
        st.error(f"Query failed: {error}")
        st.code(sql, language="sql")
    else:
        st.dataframe(df)
        st.code(sql, language="sql")

Always show the generated SQL alongside the results, even when the query succeeds. Users trust the output more when they can see exactly what ran against the database. Developers debug faster when they can spot a bad column reference or a missing JOIN condition at a glance rather than treating the model’s output as a black box.

Keeping Credentials Out of the Code

Streamlit supports a secrets.toml file inside a .streamlit directory at the project root. Values are accessed with st.secrets["KEY"] and never end up in your Python files. Add .streamlit/secrets.toml to your .gitignore immediately. Projects like this have a habit of getting pushed to GitHub without that step, which is how API keys and database passwords end up in public repositories.

For the PostgreSQL connection specifically, you can store the full connection string as a single DATABASE_URL secret and pass it directly to psycopg2.connect(). That’s one secret value instead of five separate fields, which reduces both configuration overhead and the number of places a credential could leak.

What the Finished App Actually Gives You

When the pieces come together, the app accepts a plain-English question, reads the live schema from PostgreSQL, combines both into a structured prompt, sends that prompt to the language model, executes the returned SQL against the real database, and renders the results inside a chat interface. The complete implementation fits in under 120 lines of Python, not counting blank lines and imports.

The architecture is intentionally bare. There’s no caching layer, no authentication, no query history stored to disk. Those are real concerns for a production deployment. None of them belong in a prototype. Get the core loop working first. Once you’ve run a few hundred questions through it and trust the model’s SQL quality on your actual schema, you’ll know exactly which gaps are worth addressing and in what order.

The schema-injection pattern also holds up better than it might look at first. It means your prompt is always accurate without any manual maintenance. Add a column to a table and the LLM knows about it on the next request. Rename a table and the old name disappears from the context automatically. The information schema query does the work of keeping your AI layer in sync with your database layer.

From Prototype to the Tool Your Team Actually Uses

What starts as a weekend project tends to find real users fast. Non-technical teammates who previously had to wait for a developer to run a custom query suddenly have direct access to the data they need at any hour. The questions they ask often reveal gaps you didn’t know existed: schema naming that confuses even a capable model, columns with ambiguous names, missing indexes on fields that get filtered constantly.

Building on top of PostgreSQL rather than a document store or a flat export means the underlying data is normalized, transactionally consistent, and queryable with the full power of SQL aggregations, window functions, and multi-table joins. The language model doesn’t replace that power. It makes the power accessible to people who never learned the syntax to use it. That’s a genuinely different kind of value from what a dashboard or a report ever offered.