Using DSPy to evaluate and improve Datasette Agent's SQL system prompts
The project utilizes the DSPy framework to systematically evaluate and optimize system prompts for Datasette Agent, specifically targeting its read-only SQL query generation capabilities. An automated evaluation harness was constructed where DSPy agents interact with live Datasette instances, using a gold-standard, auto-generated dataset to measure performance via custom metrics. Key findings revealed that providing only table names in schema listings, combined with restrictive instructions agai
Analysis
TL;DR
- The project utilizes the DSPy framework to systematically evaluate and optimize system prompts for Datasette Agent, specifically targeting its read-only SQL query generation capabilities.
- An automated evaluation harness was constructed where DSPy agents interact with live Datasette instances, using a gold-standard, auto-generated dataset to measure performance via custom metrics.
- Key findings revealed that providing only table names in schema listings, combined with restrictive instructions against calling
describe_table, led to column-name guessing and inefficient error-retry loops. - The study demonstrates the efficacy of using LLMs (via Claude Code) to identify specific prompt engineering flaws and suggests concrete improvements, such as including column names in schema definitions.
Why It Matters
This case study highlights the practical application of programmatic prompt optimization frameworks like DSPy in real-world production environments, moving beyond theoretical discussions to tangible improvements in tool-use reliability. It underscores the importance of precise schema information in reducing hallucination and latency in agentic workflows that interact with databases. For AI practitioners, it serves as a blueprint for diagnosing and fixing common failure modes in SQL-generating agents through rigorous, metric-driven evaluation.
Technical Details
- Framework Integration: The solution integrates DSPy with Datasette Agent, creating a pipeline where DSPy manages the evaluation loop and prompt refinement process.
- Evaluation Harness: A custom testing environment was built where DSPy agents invoke the actual tool implementations of Datasette Agent against a live, in-process Datasette instance.
- Data Generation: A gold-standard dataset was automatically generated to provide rigorous ground truth for evaluating the accuracy of the SQL queries produced by the agent.
- Diagnostic Findings: Analysis of baseline traces identified that ambiguous schema listings (table names only) caused the model to guess column names (e.g.,
page_count,o.order_id), leading to unnecessarydescribe_tablecalls or retry loops. - Proposed Fixes: Recommendations included either expanding the prompt's schema listing to include column names or relaxing the instruction prohibiting
describe_tablecalls when information is missing.
Industry Insight
- Prompt Engineering Automation: Manual prompt tuning is insufficient for complex agentic systems; adopting frameworks like DSPy allows for scalable, data-driven optimization of system instructions.
- Schema Precision is Critical: When building agents that interact with structured data, providing comprehensive schema details (including column names and types) directly in the context window significantly reduces inference errors and API call overhead.
- Iterative Diagnostic Loops: Using LLMs to analyze their own failure traces (as done with Claude Code here) can rapidly surface subtle logical flaws in system prompts that might otherwise go unnoticed during standard testing.
Disclaimer: The above content is generated by AI and is for reference only.