Building an SQL AI Agent with LangChain, Groq, and Streamlit

A comprehensive technical guide to constructing an autonomous task management system backed by a real relational database. By combining large language models (LLMs) via Groq with LangChain's SQL toolkits and Streamlit's web framework, this architecture translates natural language intents into validated SQL operations to manage full Create, Read, Update, and Delete (CRUD) workflows.

1. Why Do We Need an SQL AI Agent?

Traditional software applications require users to interact with structured interfaces: filling out forms, clicking dropdown menus, or manually issuing complex structured query language (SQL) statements. When interacting with relational databases, non-technical users are blocked by the syntax barrier of SQL, while developers are burdened with building bespoke UI workflows for every possible query variation.

The Fundamental Problem

• Rigid Interfaces: A standard web interface only allows filtering and mutation along pre-programmed dimensions (e.g., filtering strictly by a predefined dropdown).
• Context Blindness: Standard database wrappers cannot infer implicit actions. If a user states, "I just finished watching my movie," a typical backend cannot deduce that a specific record's status must be transitioned from Pending or In Progress to Completed.
• The LLM Gap: General-purpose LLMs generate text based on training data. They cannot natively read private runtime state or persist data changes unless equipped with validated database execution tools.

The Solution: Tool-Augmented Autonomous Agents

An SQL AI Agent bridges natural language understanding and relational persistence :

1. It ingests natural user utterances.
2. It examines the live schema of the underlying database.
3. It formulates syntactically valid SQL queries.
4. It performs deterministic checks on the formulated queries.
5. It executes the statements and summarizes the tabular results back into clear natural language and Markdown representations.

2. Core Mental Model and System Architecture

The core architecture operates as an autonomous feedback loop consisting of four foundational components: the Model (the reasoning engine), the Toolkit (the database interface), the Memory Checkpointer (session persistence), and the System Prompt (the behavioral contract).

Real-Life Analogy

The Executive and the Database Administrator (DBA)
Think of the User as a Company Executive, the LLM Agent as their Chief of Staff, and the SQL Toolkit as the Senior Database Administrator. The Executive never opens a terminal or writes an UPDATE statement; they simply declare: "I returned from the Tokyo conference." The Chief of Staff translates this intent into a business action, asks the DBA for the schema of the projects table, prepares an UPDATE query, has the DBA double-check the syntax, executes the update, and reports back to the Executive: "Your Tokyo Conference task has been marked as Completed."

3. Database Layer Setup (SQLite and Schema Design)

Before granting an AI agent write permissions, the persistent relational store must be established with rigid structural guarantees and validation checks.

Establishing the Connection via SQLDatabase

LangChain provides an abstraction layer over SQLAlchemy through SQLDatabase. It allows zero-code schema introspection and statement execution across dialect engines (SQLite, PostgreSQL, MySQL).

from langchain_community.utilities import SQLDatabase

# Connect to a local SQLite instance
db = SQLDatabase.from_uri("sqlite:///apps/my_tasks.db")

Designing the Task Table Schema

To ensure database integrity, column constraints must prevent invalid statuses and manage row lifecycle states automatically.

CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT CHECK(status IN ('Pending', 'In Progress', 'Completed')) DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Schema Highlights:

• id: Auto-incrementing primary key guaranteeing deterministic entity selection.
• title: Non-nullable string representing the core task summary.
• description: Flexible field containing verbose contextual notes parsed by the LLM.
• status: A SQL CHECK constraint restricting entries strictly to Pending, In Progress, or Completed, preventing arbitrary LLM hallucinations from corrupting lifecycle states.
• created_at: Populated with the database engine's current timestamp to allow accurate sorting.

# Programmatic table creation via the LangChain db utility
create_table_query = """
CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT CHECK(status IN ('Pending', 'In Progress', 'Completed')) DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
"""

db.run(create_table_query)
print("DB Table created successfully.")

4. The Anatomy of LangChain's SQLDatabaseToolkit

Manual prompt engineering often fails to teach models how to query relational tables accurately. LangChain solves this with the SQLDatabaseToolkit, which injects four pre-built, specialized tools directly into the agent runtime.

from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_groq import ChatGroq

model = ChatGroq(model="openai/gpt-oss-20b")
toolkit = SQLDatabaseToolkit(db=db, llm=model)
tools = toolkit.get_tools()

Inspection of the Built-In Database Tools

The relationship between these inspection and execution tools follows a strict operational pipeline:

5. Controlling the Agent via System Prompts

An unconstrained AI agent connected to a relational database poses substantial operational risks, including catastrophic data loss (DROP TABLE, full table updates without WHERE clauses) and unformatted outputs. The system prompt serves as an operational sandbox.

Core Guardrails Defined in the System Prompt

1. Query Limits: Enforce maximum row returns ($10$ rows max) to prevent token window overflow.
2. Deterministic Ordering: Sort listings using ORDER BY created_at DESC so recent updates appear first.
3. Execution Verification: Mandate a verification step: every INSERT, UPDATE, or DELETE must be confirmed with an immediate SELECT verification query.
4. Structured Presentation: Mandate that all record listings be presented to the user formatted cleanly as Markdown tables.

SYSTEM_PROMPT = """
You are a task management assistant that interacts with a SQL database containing a 'tasks' table.

Your responsibilities and rules are:
1. QUERY CONSTRAINTS: Always limit SELECT queries to a maximum of 10 results, ordered by 'created_at' in DESCENDING order (ORDER BY created_at DESC).
2. CONFIRMATION PROTOCOL: After performing any CREATE (INSERT), UPDATE, or DELETE operation, always execute a follow-up SELECT query to verify that the operation completed successfully.
3. OUTPUT FORMATTING: When presenting a list of tasks to the user, always format the output as a clean, structured Markdown table.
4. CRUD CAPABILITIES: You have full permissions to Create, Read, Update, and Delete tasks based on the user's intent.
5. SCHEMA SPECIFICATION:
   - Table name: tasks
   - Columns:
     * id: INTEGER PRIMARY KEY AUTOINCREMENT
     * title: TEXT NOT NULL
     * description: TEXT
     * status: TEXT ('Pending', 'In Progress', 'Completed') [Default: 'Pending']
     * created_at: TIMESTAMP DEFAULT CURRENT_TIMESTAMP
"""

6. Building the Core Agent Engine (CLI Prototype)

Before adding a web layer, test the core loop through a command-line interface. This ensures the agent handles tool calling, state checkpoints, and dynamic invocations properly.

import os
from dotenv import load_dotenv
from langchain.agents import create_agent
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_community.utilities import SQLDatabase
from langchain_groq import ChatGroq
from langgraph.checkpoint.memory import InMemorySaver

load_dotenv()

# Initialize Database
db = SQLDatabase.from_uri("sqlite:///apps/my_tasks.db")

# Initialize Model
model = ChatGroq(model="openai/gpt-oss-20b")

# Initialize Tooling
toolkit = SQLDatabaseToolkit(db=db, llm=model)
tools = toolkit.get_tools()

# Initialize Agent Checkpoint Memory
memory = InMemorySaver()

# Assemble Agent
agent = create_agent(
    model=model,
    tools=tools,
    checkpointer=memory,
    system_prompt=SYSTEM_PROMPT
)

def run_cli():
    print("AI Task Manager CLI initialized. Type 'exit' to quit.\n")
    while True:
        user_query = input("User: ")
        if user_query.lower() in ["exit", "quit"]:
            break

        # Configuration dictionary provides session isolation via thread_id
        config = {"configurable": {"thread_id": "1"}}
        
        response = agent.invoke(
            {"messages": [{"role": "user", "content": user_query}]},
            config=config
        )
        
        # Extract the final generation step from the message trail
        final_message = response["messages"][-1].content
        print(f"\nAI:\n{final_message}\n")

if __name__ == "__main__":
    run_cli()

Parsing the Response Payload

agent.invoke() returns a dictionary containing a messages array:

• The array acts as an execution trace, recording tool requests, SQL syntax outputs, tool execution returns, and intermediate thoughts.
• Indexing with [-1] extracts the final synthesized output delivered back to the user:

final_answer = response["messages"][-1].content

7. Migrating to a Web UI with Streamlit

A production web interface must handle two critical factors that the CLI ignores: execution caching and reactive state preservation.

The Streamlit Execution Loop Challenge

Streamlit scripts execute from top to bottom on every user interaction or state change. If an agent is instantiated globally in the script body, every button click or prompt submission:

1. Re-instantiates the LLM client.
2. Re-runs the database connection and introspection routines.
3. Re-instantiates InMemorySaver(), completely wiping previous conversation history.

Solution: Caching with @st.cache_resource

@st.cache_resource ensures that stateful, expensive singletons (such as the database connection, the LangGraph compiled agent, and the in-memory checkpointer) persist across reruns without re-initialization.

import streamlit as st

@st.cache_resource
def get_agent():
    db = SQLDatabase.from_uri("sqlite:///apps/my_tasks.db")
    model = ChatGroq(model="openai/gpt-oss-20b")
    toolkit = SQLDatabaseToolkit(db=db, llm=model)
    tools = toolkit.get_tools()
    memory = InMemorySaver()
    
    agent = create_agent(
        model=model,
        tools=tools,
        checkpointer=memory,
        system_prompt=SYSTEM_PROMPT
    )
    return agent

8. Managing Frontend State and Dynamic Reruns

To build a clean chat interface, the front-end must maintain a message history array in st.session_state and render it before accepting new inputs.

Implementation Pattern

# Initialize session state array for message history
if "messages" not in st.session_state:
    st.session_state.messages = []

# Re-render complete conversation on rerun
for message in st.session_state.messages:
    with st.chat_message(message["role"]):
        st.markdown(message["content"])

9. Complete Production Implementation

Below is the complete, integrated application code (app.py), bridging database management, agent caching, session preservation, and the reactive UI layer.

import streamlit as st
import os
from dotenv import load_dotenv
from langchain.agents import create_agent
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_community.utilities import SQLDatabase
from langchain_groq import ChatGroq
from langgraph.checkpoint.memory import InMemorySaver

# 1. Environment Configurations
load_dotenv()

# 2. Database Schema Definition
DATABASE_URI = "sqlite:///my_tasks.db"

def initialize_database():
    """Initializes the database schema if not already present."""
    db = SQLDatabase.from_uri(DATABASE_URI)
    table_schema = """
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        status TEXT CHECK(status IN ('Pending', 'In Progress', 'Completed')) DEFAULT 'Pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    """
    db.run(table_schema)

initialize_database()

# 3. Behavioral System Prompt
SYSTEM_PROMPT = """
You are a task management assistant that interacts with a SQL database containing a 'tasks' table.

Your responsibilities and rules are:
1. QUERY CONSTRAINTS: Always limit SELECT queries to a maximum of 10 results, ordered by 'created_at' in DESCENDING order (ORDER BY created_at DESC).
2. CONFIRMATION PROTOCOL: After performing any CREATE (INSERT), UPDATE, or DELETE operation, always execute a follow-up SELECT query to verify that the operation completed successfully.
3. OUTPUT FORMATTING: When presenting a list of tasks to the user, always format the output as a clean, structured Markdown table.
4. CRUD CAPABILITIES: You have full permissions to Create, Read, Update, and Delete tasks based on the user's intent.
5. SCHEMA SPECIFICATION:
   - Table name: tasks
   - Columns:
     * id: INTEGER PRIMARY KEY AUTOINCREMENT
     * title: TEXT NOT NULL
     * description: TEXT
     * status: TEXT ('Pending', 'In Progress', 'Completed') [Default: 'Pending']
     * created_at: TIMESTAMP DEFAULT CURRENT_TIMESTAMP
"""

# 4. Agent Resource Factory (Cached Singleton)
@st.cache_resource
def get_agent():
    """Compiles and caches the AI agent along with its tools and state checkpointer."""
    db = SQLDatabase.from_uri(DATABASE_URI)
    model = ChatGroq(model="openai/gpt-oss-20b")
    toolkit = SQLDatabaseToolkit(db=db, llm=model)
    tools = toolkit.get_tools()
    memory = InMemorySaver()
    
    agent_instance = create_agent(
        model=model,
        tools=tools,
        checkpointer=memory,
        system_prompt=SYSTEM_PROMPT
    )
    return agent_instance

# Initialize Agent
agent = get_agent()

# 5. User Interface Setup
st.set_page_config(page_title="AI Task Manager", layout="centered")
st.subheader("TaskBot: Manage Your Tasks")

# 6. Session State Initialization
if "messages" not in st.session_state:
    st.session_state.messages = []

# 7. Render Historical Chat Log
for message in st.session_state.messages:
    with st.chat_message(message["role"]):
        st.markdown(message["content"])

# 8. Handling User Prompts and Dynamic Processing
if prompt := st.chat_input("Ask me to manage your tasks..."):
    # Append & display user prompt
    st.session_state.messages.append({"role": "user", "content": prompt})
    with st.chat_message("user"):
        st.markdown(prompt)

    # Agent execution block
    with st.chat_message("assistant"):
        with st.spinner("Processing request..."):
            config = {"configurable": {"thread_id": "1"}}
            
            response = agent.invoke(
                {"messages": [{"role": "user", "content": prompt}]},
                config=config
            )
            
            result_content = response["messages"][-1].content
            st.markdown(result_content)
            
    # Persist assistant response to session state
    st.session_state.messages.append({"role": "assistant", "content": result_content})

10. Real-World Execution Scenarios and Trace Analysis

Below are concrete runtime execution traces illustrating how natural language is translated into SQL mutations behind the scenes.

Scenario A: Complex Creation with Inferred Context

• User Prompt: "Recharge mobile because it's going to end by today EOD."
• Agent Reasoning Process:
  1. Detects an intent to insert a task.
  2. Synthesizes a clean task title: "Mobile Recharge".
  3. Contextualizes the description: "Recharge mobile before it expires by end of day".
  4. Sets status to its default value: 'Pending'.
• Underlying SQL Executed:
  SQL

  Copy

  INSERT INTO tasks (title, description, status) 
  VALUES ('Mobile Recharge', 'Recharge mobile before it expires by end of day', 'Pending');
  

• Post-Action Verification:
  SQL

  Copy

  SELECT * FROM tasks WHERE title = 'Mobile Recharge' ORDER BY created_at DESC LIMIT 1;
  

Scenario B: Implicit State Mutation

• User Prompt: "Just came back from the movie."
• Agent Reasoning Process:
  1. Identifies that the user has completed a prior scheduled activity.
  2. Queries the database for tasks matching "movie".
  3. Finds task: id = 2, title = 'Go for movie at 4:15 PM', status = 'Pending'.
  4. Transitions status from 'Pending' to 'Completed'.
• Underlying SQL Executed:
  SQL

  Copy

  UPDATE tasks SET status = 'Completed' WHERE id = 2;
  

• Agent UI Response:

  "The task 'Go for movie at 4:15 PM' has been successfully marked as Completed."

Scenario C: Structured Read with Markdown Output

• User Prompt: "Give me all my tasks."
• Agent Reasoning Process:
  1. Identifies read intent.
  2. Queries up to 10 tasks ordered by creation date.
• Underlying SQL Executed:
  SQL

  Copy

  SELECT id, title, description, status, created_at 
  FROM tasks 
  ORDER BY created_at DESC 
  LIMIT 10;
  

• Agent Output Rendered in Browser:

11. Practical Pitfalls and Production Hardening

Deploying an autonomous database agent into production environments requires addressing several key operational risks:

1. Guarding Against Unbounded Context Consumption

Using SELECT * FROM tasks without limits on large tables will flood the LLM's context window, causing latency spikes, high token costs, or out-of-memory crashes.

• Remedy: Enforce strict query caps in the system prompt (LIMIT 10) and provide read-only database views for large reference tables.

2. Guarding Against Destructive Data Mutations

Allowing an LLM to run unconstrained DROP, TRUNCATE, or ALTER TABLE statements creates massive security and stability vulnerabilities.

• Remedy:
  • Restrict the underlying database user's credentials using the principle of least privilege (e.g., granting only SELECT, INSERT, UPDATE, and DELETE).
  • Add explicit negative constraints to the system prompt:
    CODE

    Copy

    NEVER execute DDL operations such as DROP TABLE, ALTER TABLE, or TRUNCATE.
    

3. Handling Streamlit Session Desynchronization

Relying solely on InMemorySaver can lead to dropped chat histories if server workers scale horizontally or restart.

• Remedy: In multi-user production deployments, swap InMemorySaver for an external state store like PostgresSaver or RedisSaver, pairing each session with a persistent, authenticated thread_id.

12. Summary and Key Takeaways

• Natural Language Database Interfaces: Combining LLM reasoning with relational databases lets users run complex CRUD workflows through conversational English without writing SQL.
• Automated Toolkits: LangChain's SQLDatabaseToolkit removes the need for custom database adapters by providing out-of-the-box introspection, schema extraction, query validation, and statement execution tools.
• Query Verification Safeguards: The built-in sql_db_query_checker tool acts as a critical reliability filter, catching syntax errors and structural flaws before queries reach the database engine.
• Strict Behavioral Prompts: Rigorous system prompts are essential for enforcing operational boundaries, including mandatory ORDER BY sorting, record return limits (LIMIT 10), and verified mutations.
• Schema-Enforced Quality: Adding constraints at the database level—such as NOT NULL, DEFAULT CURRENT_TIMESTAMP, and CHECK(status IN (...))—prevents LLM hallucinations from corrupting persistent application state.
• Streamlit Lifecycle Management: Because Streamlit re-executes scripts on every user interaction, stateful singletons (such as the agent graph and memory savers) must be isolated inside @st.cache_resource to avoid wiped history and memory leaks.
• Two-Tiered State Preservation: Chat interactions require two layers of memory: an internal agent checkpointer (InMemorySaver) to track execution context, and a UI-level state store (st.session_state) to maintain visual message history.