Text-to-SQL

The Text-to-SQL Tool (TextToSQLTool) is a powerful component in the Canso toolkit that enables AI agents to convert natural language queries into valid SQL statements. It leverages underlying language models to understand user intent and generate appropriate SQL code based on database schema information.

Overview

The Text-to-SQL Tool bridges the gap between natural language understanding and database querying, allowing agents to:

• Parse natural language questions about data

• Convert these questions into valid, optimized SQL queries

• Maintain context about database schemas and relationships

• Leverage examples and domain knowledge for better query generation

Usage

The constructor for the TextToSQLTool has the following parameters:

Parameter	Description	Required
service	An instance of TextToSQLService with configured LLM and context retriever	Yes

The input parameters for the tool, provided at runtime, include:

Parameter	Description	Example	Required
query	Natural language query to convert to SQL	"Show me all customers who made purchases last month"	Yes
table_info	Optional table schema information to initialize or update context	[{"table": "customers", "schema": "CREATE TABLE..."}]	No

Integration

The following code snippet illustrates how the TextToSQLTool can be integrated with your AI Agent:

from gru.agents.tools.text_to_sql import TextToSQLTool
from gru.agents.tools.core.services.text_to_sql import TextToSQLService
from gru.agents.tools.core.llm_client.openai import OpenAILLMClient
from gru.agents.tools.core.context_retriever.sql import SQLContextRetriever
from gru.agents.tools.core.vector_db.milvus import MilvusClient
from langchain_openai import ChatOpenAI
from langgraph.prebuilt import ToolNode

# Initialize dependencies
llm_client = OpenAILLMClient(model="gpt-4o")
vector_store = MilvusClient()
context_retriever = SQLContextRetriever(vector_store=vector_store)

# Create the service
text_to_sql_service = TextToSQLService(
    llm_client=llm_client,
    context_retriever=context_retriever
)

# Initialize the tool
text_to_sql_tool = TextToSQLTool(service=text_to_sql_service)

# Add to your agent's tools
tools = [text_to_sql_tool]
tool_node = ToolNode(tools)

model = ChatOpenAI(model="gpt-4o", temperature=0)
model = model.bind_tools(tools)

Memory Integration

The TextToSQLTool can leverage CansoMemory to:

1. Store and retrieve database schemas

2. Save example queries and their SQL translations

3. Learn from past query conversions

4. Maintain domain-specific knowledge related to databases

Memory-enhanced Text-to-SQL transformations become increasingly accurate as the system learns from more examples and builds a richer context of your database structure.

Example

When a user asks a natural language question:

"Which customers spent more than $1000 last quarter and haven't made a purchase this month?"

The TextToSQLTool can generate a SQL query like:

SELECT c.customer_id, c.first_name, c.last_name, c.email 
FROM customers c
JOIN (
    SELECT customer_id, SUM(total_amount) as quarterly_spend
    FROM orders
    WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
    GROUP BY customer_id
    HAVING SUM(total_amount) > 1000
) q ON c.customer_id = q.customer_id
WHERE c.customer_id NOT IN (
    SELECT DISTINCT customer_id 
    FROM orders 
    WHERE order_date >= '2024-04-01'
)
ORDER BY q.quarterly_spend DESC;

Tool Tips

• Go to Toolkit ⬅️

• See SQL Runner Tool ➡️

• Learn about Memory ➡️

• Explore Examples ➡️