Skip to content

Run a text-to-SQL benchmark

Score a model on Spider or BIRD and see its execution accuracy (EX): the fraction of questions where the model's SQL returns the same result as the gold query.

evaldata is tested against Spider's result_eq and BIRD's set comparison on every question in each dataset. The following intentional differences are excluded from that comparison:

  • A handful of questions select the same output column name twice. evaldata rejects duplicate column names, so it scores those differently.
  • A couple of Spider questions return text that isn't valid UTF-8. The official scorer ignores the bad bytes; evaldata raises an error instead.

These are design choices, not bugs, and together they're under 0.2% of each dataset.

Fetch a dataset

evaldata fetch downloads a benchmark, validates its checksum, and caches it locally:

evaldata fetch spider
evaldata fetch bird

The download is pinned to the checksum used for validation. A checksum mismatch fails the fetch. Re-download with --force; choose where it is cached with --cache-dir PATH.

Run the benchmark

evaldata bench loads the cached dataset, runs a solver that puts the database schema in the prompt, scores each question, and prints the overall EX:

evaldata bench spider --model openai/gpt-4o-mini
evaldata bench bird --model openai/gpt-4o-mini --limit 100

--model is any litellm model id. Useful options:

  • --limit N: run only the first N questions.
  • --split dev: which part of the dataset to load (dev by default).
  • --json PATH: also save a JSON file with the scores and every question's result.
  • path (positional): use an already-unzipped dataset folder instead of the cache.

BIRD tags each question with a difficulty, so the output also breaks the EX down by difficulty. Example output:

EX (bird): 54.8% (841/1534)
EX by difficulty (bird)
difficulty   EX      passed/total
challenge    33.1%   49/148
moderate     48.9%   189/386
simple       60.6%   603/995

How a benchmark is scored

Each benchmark sets ExecutionAccuracy up to match its own rules, so the two aren't scored the same way:

  • Spider matches columns by value (column_alignment="by_value"), so the column order doesn't have to line up.
  • BIRD compares the results as a set (row_order="ignore", multiplicity="set"), ignoring row order and duplicate rows.

ExecutionAccuracy runs both the model's SQL and the gold query and compares the results under these rules. A question passes when the two match; the EX is the fraction that pass.

Score your own model

The CLI uses a PromptSolver that puts each question's database schema in the prompt. To benchmark your own prompt, fine-tune, or multi-step agent, load the cases and pass any Solver to run_benchmark:

from evaldata import ExecutionAccuracy, load_bird, run_benchmark
from your_system import MySolver

cases = list(load_bird("/path/to/bird", split="dev"))
summary = run_benchmark(
    cases,
    MySolver(),
    scorers=[ExecutionAccuracy(row_order="ignore", multiplicity="set")],  # BIRD's config
    limit=100,
)
print(f"EX: {summary.accuracy:.1%} ({summary.passed}/{summary.total})")

load_spider and load_bird yield EvalCases with the question as input and the gold query as the expected answer. You can score them with any scorer, not only ExecutionAccuracy.

Bundled example

The bundled example creates a small Spider-shaped dataset and uses fixed model responses. It needs no downloaded benchmark or model call:

"""Benchmark example: load a text-to-SQL dataset and measure execution accuracy (EX).

This builds a tiny Spider-shaped dataset in a temp directory so the example is self-contained.
To run a real benchmark, download Spider or BIRD and point the CLI at it:

    evaldata bench spider /path/to/spider --model openai/gpt-4o-mini
    evaldata bench bird /path/to/bird --model openai/gpt-4o-mini --limit 100
"""

import json
import os
import sqlite3
import tempfile
from collections.abc import Iterator
from pathlib import Path

import pytest

from evaldata import ExecutionAccuracy, PromptSolver, load_spider, run_benchmark
from evaldata.solvers import SCHEMA_PROMPT_TEMPLATE

_ROOT = Path(tempfile.mkdtemp(prefix="evaldata_ex06_"))
_DB_ID = "ex06_shop"
_MODEL = os.getenv("EVALDATA_HOSTED_MODEL", "openai/gpt-4o-mini")


@pytest.fixture(scope="module", autouse=True)
def _build_dataset() -> Iterator[None]:
    db_dir = _ROOT / "database" / _DB_ID
    db_dir.mkdir(parents=True, exist_ok=True)
    con = sqlite3.connect(db_dir / f"{_DB_ID}.sqlite")
    con.execute("CREATE TABLE items (id INTEGER, name TEXT, price REAL)")
    con.executemany("INSERT INTO items VALUES (?, ?, ?)", [(1, "apple", 3.0), (2, "pear", 2.0), (3, "kiwi", 5.0)])
    con.commit()
    con.close()
    questions = [
        {"db_id": _DB_ID, "question": "How many items are there?", "query": "SELECT count(*) FROM items"},
        {"db_id": _DB_ID, "question": "What is the total price of all items?", "query": "SELECT sum(price) FROM items"},
        {
            "db_id": _DB_ID,
            "question": "List item names alphabetically.",
            "query": "SELECT name FROM items ORDER BY name",
        },
    ]
    (_ROOT / "dev.json").write_text(json.dumps(questions), encoding="utf-8")
    yield


def test_execution_accuracy() -> None:
    """Load the dataset, run a schema-prompted solver, and check the aggregate EX."""
    cases = load_spider(_ROOT)
    solver = PromptSolver(model=_MODEL, prompt_template=SCHEMA_PROMPT_TEMPLATE, temperature=0)
    summary = run_benchmark(cases, solver, scorers=[ExecutionAccuracy()])

    # The mocked model answers the first two questions and misses the third, so 2 of 3 pass.
    assert summary.total == 3
    assert summary.passed == 2
    assert summary.accuracy == pytest.approx(2 / 3)

uv run pytest examples/06_benchmark -q

Next steps