Build a Secure PostgreSQL AI Agent with LangChain + Ollama

Gaurav Kumar — Tue, 12 May 2026 04:30:00 +0000

🚀 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

Tool	Purpose
LangChain	Agent orchestration and tool calling
Ollama	Run LLMs locally without API costs
langchain-ollama	LangChain integration for Ollama
psycopg2	PostgreSQL adapter for Python
Python	Core application runtime

🧱 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

Allowed	Blocked
`SELECT`	`INSERT`
`WITH`	`UPDATE`
`SHOW`	`DELETE`
`EXPLAIN`	`DROP`
—	`ALTER`
—	`TRUNCATE`

🧼 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:

🔽 Pull the Model

ollama pull qwen2.5:7b

Verify installation:

ollama list

🧩 Recommended Models for SQL Agents

Model	Command	Notes
Qwen 2.5 7B	`ollama pull qwen2.5:7b`	Used in this tutorial
Llama 3.1 8B	`ollama pull llama3.1`	Strong general-purpose model
DeepSeek-R1 7B	`ollama pull deepseek-r1`	Excellent reasoning
Mistral 7B	`ollama pull mistral`	Lightweight and fast

📦 Install LangChain Ollama Integration

pip install langchain-ollama

References:

⚙️ 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

The Day I Stopped Building Alone: OpenClaw as My Virtual Team

Gaurav Kumar — Thu, 26 Mar 2026 04:00:00 +0000

Building a SaaS product solo means you're never just a developer. You're the researcher, the architect, the QA engineer, the SEO specialist — and somewhere in between all that, you're supposed to actually write code.

That was my reality while building ShiftMailer, my AI-powered email marketing product. Constant context-switching. Constant skill gaps. Constant exhaustion.

Then I started using OpenClaw — an AI agent framework that doesn't just chat, but actually does things: reads files, runs commands, searches the web, analyzes code, and coordinates with other tools.

Here's the short version of what I learned:

What works really well

Research: Instead of spending hours comparing tools or validating ideas, I delegate it. OpenClaw synthesizes, not just searches.
Code amplification: I'm a backend engineer — I don't need AI to replace me, I need it to handle the repetitive stuff so I can focus on the interesting parts. That's exactly what it does.
Unexpected wins: It analyzed my site's SEO and gave me specific, implementable suggestions. Not generic advice — actual recommendations based on my content.

What doesn't (yet)

Orchestration: Complex multi-step workflows can get messy. I'm still the conductor — the agent executes well, but I keep the orchestra in sync.
Security: When an agent has access to your files and environment, "trust but verify" isn't optional. Review code before shipping. Keep sensitive configs isolated.

The honest comparison

AI agents aren't cofounders. A cofounder brings human judgment, equity, and fixed skills. An AI agent brings on-demand skills, zero scheduling overhead, and no sleep requirements — but needs oversight on the hard calls.

The gap between "idea" and "working product" has shrunk dramatically for me. ShiftMailer exists today because I stopped trying to do everything myself.

If you want the full story — including the detailed breakdown of workflows, a cofounder comparison table, and what I'm still figuring out — read the complete article on Hashnode.

Originally published at gauravbytes.hashnode.dev

DEV Community: Gaurav Kumar