Using DSPy to evaluate and improve Datasette Agent's SQL system prompts

2026-07-02 · Source: Simon Willison's Weblog · Field: Technology & Digital — Artificial Intelligence & Machine Learning, Software Development & Engineering · Depth: Intermediate, quick

Summary

A project utilizes the DSPy framework to evaluate and refine the core production system prompts for Datasette Agent's read-only SQL question answerer. The methodology involves DSPy agents invoking Datasette Agent's actual tool implementations and prompts against a live in-process Datasette. Rigorous evaluation is conducted using a gold-standard, auto-generated dataset and custom metrics. Initial research, prompted by an AIE keynote and executed via Claude Fable 5 using GPT 4.1 mini and nano, identified a critical issue: the prompt's schema listing, which only provided table names, led to column-name guessing (e.g., "page_count", "o.order_id") and error-retry loops. This problem was compounded by advice to "don't call describe_table if you already have the information." The proposed solution is to either include column names directly in the prompt's schema listing or modify the problematic advice.

Key takeaway

For Prompt Engineers developing SQL-generating agents, you should integrate frameworks like DSPy for systematic prompt evaluation. This approach helps uncover subtle issues, such as incomplete schema information or counterproductive instructions, that lead to execution errors. Specifically, ensure your database schema prompts include full column details and carefully review any advice that might restrict the agent's ability to gather necessary information, preventing costly error-retry loops and improving query accuracy.

Key insights

The DSPy framework effectively identifies and resolves prompt engineering issues in SQL agent systems.

Principles

• Schema listings in prompts need full detail.
• Overly restrictive prompt advice can backfire.
• Automated prompt evaluation reveals subtle flaws.

Method

Use DSPy agents to invoke tools against a live system. Evaluate with auto-generated gold-standard datasets and custom metrics.

In practice

• Test SQL agent prompts with DSPy.
• Include column names in database schema prompts.
• Re-evaluate restrictive prompt instructions.

Topics

• DSPy
• Datasette Agent
• Prompt Engineering
• SQL Generation
• LLM Evaluation
• Claude Fable 5

Code references

• stanfordnlp/dspy

Best for: Prompt Engineer, Machine Learning Engineer, AI Engineer

Related on AIssential

• Automate Writing Your LLM Prompts — DSPy automates and optimizes LLM prompt creation and evaluation, ensuring robust, production-ready applications.
• Querying an astronomical database using large language models: the ALeRCE text-to-SQL system — The ALeRCE text-to-SQL system uses LLMs and a step-by-step framework to query astronomical databases via natural language.
• GRACE-DS: a Guarded Reward-guided Agent Correction Environment in Data Science — GRACE-DS evaluates LLM-powered AutoML agents across full ML workflows using guarded, reward-guided correction in an isolated environment.
• Moving beyond manual prompting: A practical introduction to DSPy — DSPy offers a programmatic, declarative framework for building robust, optimizable LLM applications, moving beyond manual prompt engineering.
• Datasette Agent — Datasette Agent integrates LLMs with Datasette for conversational data querying and extensible AI assistance.
• Teaching Your SQL Generator to Fix Its Own Mistakes — LLMs can self-correct runtime SQL errors by processing database feedback in a retry loop.

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Simon Willison's Weblog.