🚀 Introduction

Imagine asking your database:

“Show me the top 10 customers by revenue.”

…and instantly getting results without writing a single SQL query.

That’s exactly what an AI-powered database agent can do.

In this tutorial, we’ll build a secure PostgreSQL AI Agent using:

• 🧩 LangChain for agent orchestration
• 🦙 Ollama for running local LLMs
• 🐘 PostgreSQL as the database
• 🛡️ A custom SQL safety layer to block destructive queries

By the end, you’ll have a local AI assistant capable of converting natural language into SQL queries safely and efficiently.

💻 Source Code:
postgres-agent GitHub Repository

🤖 What is a PostgreSQL AI Agent?

A PostgreSQL AI Agent is essentially an LLM-powered assistant that can:

• Understand natural language
• Generate SQL queries
• Execute them against PostgreSQL
• Return readable results

Think of it like ChatGPT connected to your database — but with guardrails and controlled execution.

⚙️ Tech Stack

🧱 System Architecture

🔌 Step 1: Connect to PostgreSQL

We’ll start by creating a PostgreSQL connection using psycopg2.

import psycopg2

DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "database": "postgres",
    "user": "postgres",
    "password": "root",
}

def get_connection():
    return psycopg2.connect(**DB_CONFIG)

🔐 Production Tip:
Never hardcode credentials in production. Use environment variables or a secret manager.

🛠️ Step 2: Create LangChain Tools

LangChain agents interact with systems using tools.

These tools expose safe and structured database operations to the LLM.

📋 Tool: List Database Tables

@tool
def list_tables() -> str:
    """List all tables in the database."""
    conn = get_connection()
    try:
        cur = conn.cursor()
        cur.execute("""
            SELECT table_name FROM information_schema.tables
            WHERE table_schema = 'public'
        """)
        tables = [row[0] for row in cur.fetchall()]
        return f"Tables: {', '.join(tables)}" if tables else "No tables found."
    finally:
        conn.close()

This gives the agent dynamic schema awareness instead of relying on hardcoded table names.

📑 Tool: Fetch Table Schema

@tool
def get_table_schema(table_name: str) -> str:
    """Get the schema (columns and types) of a specific table."""
    conn = get_connection()
    try:
        cur = conn.cursor()
        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_name,))
        columns = cur.fetchall()

        if not columns:
            return f"Table '{table_name}' not found."

        schema = "\n".join([
            f"  {col[0]} ({col[1]}, nullable={col[2]})"
            for col in columns
        ])

        return f"Schema for '{table_name}':\n{schema}"
    finally:
        conn.close()

This helps the LLM understand:

• Column names
• Data types
• Nullability
• Table structure

⚡ Tool: Execute SQL Queries

@tool
def execute_sql(query: str) -> str:
    """Execute a SQL query against the PostgreSQL database and return results. Use this for SELECT queries."""

    is_safe, reason = validate_read_only_sql(query)

    if not is_safe:
        return f"Safety Guard: Blocked query. {reason}"

    conn = get_connection()

    try:
        cur = conn.cursor()
        cur.execute(query)

        if cur.description:
            columns = [desc[0] for desc in cur.description]
            rows = cur.fetchall()

            if not rows:
                return "Query returned no results."

            result = " | ".join(columns) + "\n"
            result += "\n".join([
                " | ".join(str(v) for v in row)
                for row in rows[:50]
            ])

            if len(rows) > 50:
                result += f"\n... ({len(rows)} total rows)"

            return result
        else:
            conn.commit()
            return f"Query executed successfully. Rows affected: {cur.rowcount}"

    except Exception as e:
        conn.rollback()
        return f"SQL Error: {e}"

    finally:
        conn.close()

This becomes the core execution engine of the AI agent.

🛡️ Step 3: Add a SQL Safety Guard

Allowing an LLM to execute unrestricted SQL is dangerous.

That’s why every query should pass through a validation layer before execution.

✅ Allowed vs Blocked Queries

🧼 Query Normalization

Before validation, we sanitize queries by removing:

• Comments
• Hidden injections
• String-based bypass attempts

This significantly improves safety when working with LLM-generated SQL.

🧠 Step 4: Setup Ollama for Local LLMs

Ollama makes it incredibly easy to run large language models locally.

No OpenAI API.
No usage limits.
No cloud dependency.

Useful links:

• Ollama Documentation
• Ollama Model Library
• Ollama GitHub Repository

🔽 Pull the Model

ollama pull qwen2.5:7b

Verify installation:

ollama list

🧩 Recommended Models for SQL Agents

📦 Install LangChain Ollama Integration

pip install langchain-ollama

References:

• langchain-ollama PyPI Package
• LangChain ChatOllama Docs
• LangChain-Ollama API Reference

⚙️ Configure ChatOllama

from langchain_ollama import ChatOllama

llm = ChatOllama(
    model="qwen2.5:7b",
    temperature=0
)

Setting temperature=0 helps generate more deterministic and reliable SQL queries.

Why Use Ollama?

• ✅ Completely local execution
• ✅ No API cost
• ✅ Privacy-friendly
• ✅ GPU acceleration support
• ✅ Supports many open-source models

🔗 Step 5: Create the LangChain Agent

tools = [list_tables, get_table_schema, execute_sql]

agent = create_agent(llm, tools)

LangChain enables the AI agent to:

• Select tools dynamically
• Chain multiple operations
• Reason step-by-step
• Generate context-aware SQL

💬 Step 6: Create an Interactive Chat Loop

while True:
    user_input = input("\nYou: ").strip()

    if user_input.lower() in ("exit", "quit"):
        print("Goodbye!")
        break

    if not user_input:
        continue

This turns the application into a conversational SQL assistant.

🧾 Step 7: Add Debugging & Observability

Debugging AI agents becomes much easier when you can inspect tool calls and outputs.

def print_turn_details(messages: list[BaseMessage]) -> None:
    final_response = ""

    for message in messages:
        if isinstance(message, AIMessage):

            for tool_call in message.tool_calls:
                tool_name = tool_call.get("name", "unknown_tool")
                tool_args = format_tool_payload(
                    tool_call.get("args", {})
                )

                print(f"\nTool call: {tool_name}({tool_args})")

            content = format_content(message.content).strip()

            if content:
                final_response = content

        elif isinstance(message, ToolMessage):
            tool_name = getattr(message, "name", None) or "tool"

            tool_output = (
                format_content(message.content).strip()
                or "(no output)"
            )

            print(f"\nTool response [{tool_name}]: {tool_output}")

    if final_response:
        print(f"\nAgent: {final_response}")
    else:
        print("\nAgent: I couldn't generate a response.")

This helps you inspect:

• Tool invocations
• Tool outputs
• Agent reasoning flow
• Final responses

🧪 Example Queries

Try prompts like:

• "List all tables"
• "Show schema of users table"
• "Get top 5 users by revenue"
• "How many orders were placed last month?"

🌍 Real-World Use Cases

This architecture can be extended into real production systems.

📊 AI Analytics Dashboards

Allow non-technical users to query business data using plain English.

💬 Internal Data Chatbots

Integrate with Slack or Teams for self-serve analytics.

🧾 Automated Reporting

Generate recurring reports automatically using natural language prompts.

🏢 SaaS Admin Tools

Provide operations teams with an AI-powered database interface.

🤖 AI Copilots for Analysts

Speed up SQL generation and analytics workflows.

🎯 Final Thoughts

You’ve now built a secure and extensible PostgreSQL AI Agent powered by LangChain and Ollama.

The biggest takeaway here is that tool-based AI architecture gives LLMs structured access to databases without exposing unrestricted control.

Key Learnings

• LangChain simplifies AI agent orchestration
• Ollama enables local LLM execution
• SQL validation is essential for security
• Tool-driven agents are highly extensible

With a few additional improvements like authentication, query caching, and semantic memory, this can evolve into a powerful production-grade AI data assistant.

📦 Full Working Source Code:
postgres-agent GitHub Repository