Build a SQL Analyst Agent with Gemini and Postgres for Free

What We're Building

We're shipping a natural-language SQL analyst agent. You point it at a Postgres database, ask a question in plain English, and it returns a data-backed answer—no SQL required from the user. The agent introspects the schema, crafts a query using Google Gemini, executes it safely, and formats the result.

Feature list:

• Connects to any Postgres database (we'll use Supabase's free tier)
• Introspects tables, columns, types, and foreign keys automatically
• Generates syntactically correct Postgres SQL from natural language
• Executes read-only queries and returns results as structured text
• Handles edge cases: nonexistent tables, ambiguous column names, and query refusals
• Runs entirely on free-tier tools: Gemini API, Supabase, LangChain

If you've ever wanted a lightweight text-to-SQL assistant that doesn't phone home to a SaaS dashboard, this is it. We've covered similar patterns for different use cases—like a Discord community FAQ bot backed by Supabase and Gemini and an on-call incident summarizer that reads logs. This agent follows the same pragmatic, free-tier-first philosophy.

Architecture Overview

Here's how the pieces connect. The user asks a question. The agent fetches the database schema, packages it into a prompt, and sends it to Gemini. Gemini returns SQL. The agent executes it against Postgres, captures the result, and returns a human-readable summary.

We keep the architecture flat: no vector databases, no caching layers. Just a Python script with three core modules—introspection, generation, execution. LangChain provides the thin orchestration wrapper around Gemini, but you could swap it for a raw google-generativeai call if you prefer fewer dependencies.

Prerequisites

All free-tier, no credit card tricks.

• Supabase account (free tier includes a 500 MB Postgres database). Sign up at supabase.com.
• Google AI Studio API key for Gemini. Grab one at aistudio.google.com. The free tier gives you 60 requests per minute on Gemini 1.5 Flash—plenty for a personal analyst agent.
• Python 3.10+ with pip.
• A sample database with a few tables. We'll use Supabase's built-in SQL editor to seed a tiny e-commerce schema.

Step 1: Provision a Free Postgres Database on Supabase

1. Create a new project in Supabase. Name it sql-analyst-demo.
2. Once provisioned, navigate to Settings > Database and copy the Connection string under Connection pooling. It looks like postgresql://postgres.[ref]:[password]@aws-0-us-west-1.pooler.supabase.com:6543/postgres.
3. Open the SQL Editor in Supabase and run this seed script to create a simple schema:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    signup_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    category TEXT
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE DEFAULT CURRENT_DATE,
    total NUMERIC(12,2)
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL
);

INSERT INTO customers (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Carol', 'carol@example.com');

INSERT INTO products (name, price, category) VALUES
('Widget', 9.99, 'Gadgets'),
('Gizmo', 24.50, 'Gadgets'),
('Thingamajig', 15.00, 'Widgets');

INSERT INTO orders (customer_id, total) VALUES
(1, 34.49),
(2, 15.00),
(1, 24.50);

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 2, 9.99),
(1, 2, 1, 24.50),
(2, 3, 1, 15.00),
(3, 2, 1, 24.50);

You now have customers, products, orders, and order_items with a few rows.

Step 2: Get Your Google Gemini API Key

1. Visit aistudio.google.com/apikey.
2. Click Create API Key and copy it.
3. Set it as an environment variable:

export GEMINI_API_KEY="your-api-key-here"

Step 3: Set Up the Python Environment

mkdir sql-analyst-agent && cd sql-analyst-agent
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate
pip install langchain langchain-google-genai psycopg2-binary python-dotenv

Create a .env file:

GEMINI_API_KEY=your-api-key-here
DATABASE_URL=postgresql://postgres.[ref]:[password]@aws-0-us-west-1.pooler.supabase.com:6543/postgres

Step 4: Build the Schema Introspection Module

This module queries Postgres's information_schema to produce a compact schema representation for the prompt. We want table names, column names, data types, and foreign key relationships—enough for Gemini to reason about joins.

Create schema_introspector.py:

import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

def get_schema_summary() -> str:
    conn = psycopg2.connect(os.getenv("DATABASE_URL"))
    cur = conn.cursor()

    # Fetch all user tables
    cur.execute("""
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
        ORDER BY table_name;
    """)
    tables = [row[0] for row in cur.fetchall()]

    schema_lines = []
    for table in tables:
        # Columns
        cur.execute("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = %s
            ORDER BY ordinal_position;
        """, (table,))
        cols = cur.fetchall()
        col_strs = [f"  {c[0]} ({c[1]}, {'nullable' if c[2] == 'YES' else 'not null'})" for c in cols]

        # Foreign keys
        cur.execute("""
            SELECT
                kcu.column_name,
                ccu.table_name AS foreign_table,
                ccu.column_name AS foreign_column
            FROM information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
                ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage AS ccu
                ON ccu.constraint_name = tc.constraint_name
            WHERE tc.constraint_type = 'FOREIGN KEY'
                AND tc.table_schema = 'public'
                AND tc.table_name = %s;
        """, (table,))
        fks = cur.fetchall()
        fk_strs = [f"  FK: {fk[0]} -> {fk[1]}({fk[2]})" for fk in fks]

        schema_lines.append(f"Table: {table}")
        schema_lines.extend(col_strs)
        if fk_strs:
            schema_lines.extend(fk_strs)
        schema_lines.append("")

    cur.close()
    conn.close()
    return "\n".join(schema_lines)

This produces output like:

Table: customers
  id (integer, not null)
  name (text, not null)
  email (text, not null)
  signup_date (date, nullable)

Table: orders
  id (integer, not null)
  customer_id (integer, nullable)
  order_date (date, nullable)
  total (numeric, nullable)
  FK: customer_id -> customers(id)

Step 5: Build the SQL Generation Agent with LangChain

We use LangChain's ChatGoogleGenerativeAI wrapper. The prompt template includes the schema, the user question, and strict guardrails: read-only, Postgres dialect, no DML.

Create sql_agent.py:

from langchain_google_genai import ChatGoogleGenerativeAI
from langchain.prompts import ChatPromptTemplate
from schema_introspector import get_schema_summary
import os
from dotenv import load_dotenv

load_dotenv()

llm = ChatGoogleGenerativeAI(
    model="gemini-1.5-flash",
    temperature=0,
    google_api_key=os.getenv("GEMINI_API_KEY")
)

prompt = ChatPromptTemplate.from_messages([
    ("system", """You are a PostgreSQL SQL expert. Given a database schema and a user question, produce a single, valid PostgreSQL SELECT query that answers the question.

Rules:
- Return ONLY the SQL query, no markdown fences, no explanations.
- Use only SELECT statements. Never generate INSERT, UPDATE, DELETE, DROP, or ALTER.
- Use the exact table and column names from the schema.
- If the question cannot be answered with the available schema, return: UNABLE_TO_ANSWER
- Use proper PostgreSQL syntax, including appropriate JOINs and aggregations.
- Always qualify column names with table aliases when joining.

Schema:
{schema}"""),
    ("human", "{question}")
])

chain = prompt | llm

def generate_sql(question: str) -> str:
    schema = get_schema_summary()
    response = chain.invoke({"schema": schema, "question": question})
    sql = response.content.strip()
    # Strip accidental markdown fences
    if sql.startswith("```"):
        sql = sql.split("```")[1]
        if sql.startswith("sql"):
            sql = sql[3:]
    return sql.strip()

Step 6: Execute Queries and Format Results

We wrap psycopg2 in a read-only transaction to enforce safety. If Gemini returns UNABLE_TO_ANSWER or generates non-SELECT SQL, we reject it.

Create executor.py:

import psycopg2
import os
from dotenv import load_dotenv

load_dotenv()

def execute_query(sql: str):
    if sql == "UNABLE_TO_ANSWER":
        return None, "I couldn't generate a query for that question. Try rephrasing or check your schema."

    # Safety: only allow SELECT
    if not sql.upper().strip().startswith("SELECT"):
        return None, f"Query rejected: only SELECT statements are allowed. Got: {sql[:50]}..."

    conn = psycopg2.connect(os.getenv("DATABASE_URL"))
    conn.set_session(readonly=True, autocommit=True)
    cur = conn.cursor()

    try:
        cur.execute(sql)
        rows = cur.fetchall()
        colnames = [desc[0] for desc in cur.description]
        return (colnames, rows), None
    except Exception as e:
        return None, f"Query execution failed: {str(e)}"
    finally:
        cur.close()
        conn.close()

def format_result(result, error):
    if error:
        return f"Error: {error}"
    colnames, rows = result
    if not rows:
        return "Query returned no results."
    # Simple table formatting
    header = " | ".join(colnames)
    separator = "-" * len(header)
    row_lines = [" | ".join(str(cell) for cell in row) for row in rows]
    return f"{header}\n{separator}\n" + "\n".join(row_lines)

Step 7: Assemble the Full Agent

Create main.py:

from sql_agent import generate_sql
from executor import execute_query, format_result

question = input("Ask a question about your database: ")
print("\nGenerating SQL...")
sql = generate_sql(question)
print(f"Generated SQL:\n{sql}\n")

print("Executing query...")
result, error = execute_query(sql)
answer = format_result(result, error)
print(f"\nAnswer:\n{answer}")

Running the Agent

python main.py

Try these questions:

• "Which customer placed the most orders?"
• "What is the total revenue per product category?"
• "List all products ordered by Alice along with quantities."
• "Show me the email of every customer who ordered a Gizmo."

Sample output:

Ask a question about your database: What is the total revenue per product category?

Generating SQL...
Generated SQL:
SELECT p.category, SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.category
ORDER BY total_revenue DESC;

Executing query...

Answer:
category | total_revenue
------------------------
Gadgets  | 68.98
Widgets  | 15.00

Sensible Extensions

Once the basic agent works, here's where you push it:

1. Conversational memory. Add LangChain's ConversationBufferMemory so follow-up questions like "and what about Bob?" work without restating the full context.
2. Query explanation. Ask Gemini to also return a plain-English explanation of what the SQL does. Add a second output field to the prompt.
3. Result visualization. Pipe the output into a lightweight charting library like matplotlib or stream it to a simple Streamlit UI.
4. Schema caching. Introspection hits the DB on every question. Cache the schema string in memory and refresh only when the user explicitly asks or after a TTL.
5. Multi-turn refinement. If execution fails, feed the error back to Gemini with the original question and ask it to fix the query. This is a tight loop that often resolves typos or bad column references.

For a deeper dive on shipping prototypes fast, check out the FDE weekly workflow from messy problem to shipped prototype.

Common Pitfalls and Mitigations

Security note: the readonly=True session setting is a defense-in-depth measure, but a determined prompt injection could still craft expensive queries (e.g., Cartesian joins). Always run this agent against a database you control, never expose it to untrusted users without additional sandboxing.

FAQ

Q: Can I use this with an existing production database? A: Yes, but connect with a read-only user, not your admin credentials. Create a dedicated Postgres role with SELECT privileges only on the specific tables you want the agent to access.

Q: Why Gemini Flash instead of Pro? A: Flash is free, fast, and more than capable for text-to-SQL. Pro adds cost with marginal accuracy gains for this task. If you hit complexity ceilings (e.g., 10+ table joins), consider Pro, but start with Flash.

Q: How do I handle time-series questions like "sales last month"? A: The agent can't know the current date unless you inject it. Add CURRENT_DATE context to the prompt: "Today's date is {current_date}. Use it for relative date filters."

Q: Can I deploy this as a Slack bot or API? A: Absolutely. Wrap main.py in a FastAPI endpoint or a Slack bolt app. The same pattern powers our Discord FAQ bot build, which you can adapt for Slack.

Q: What if my schema has hundreds of tables? A: The full schema will blow past Gemini's context window. Implement table selection: either ask the user to specify relevant tables, or use a lightweight embedding search over table/column names to retrieve only the most relevant subset before calling Gemini.

Q: Where can I learn more about shipping AI features under enterprise constraints? A: We've written about deploying LLM features at risk-averse enterprise customers—the same principles apply when you move this agent from hobby project to production.