Building a text to SQL tool¶

We’re going to build something genuinely useful. Weirdly enough, this is a “hello world” exercise for prompt engineering.

Ask a question of your database in English, get a response from a custom SQL query written by the LLM.

Prototyping against the logs database¶

We’re going to use the LLM logs database itself, and prototype against it using the sqlite-utils CLI tool:

sqlite-utils schema "$(llm logs path)"

Let’s write that to a file:

sqlite-utils schema "$(llm logs path)" > schema.sql

Now we can feed it to LLM and write our first query:

llm -f schema.sql \
  -s "reply with sqlite SQL" \
  "how many conversations are there?"

I got back this:

```sql
SELECT COUNT(*) AS conversation_count FROM conversations;
```

As you can see, the LLM decided to wrap it in a fenced code block.

We could ask it not to, but we can also use the --extract flag to extract the SQL from the response:

llm -f schema.sql \
  -s "reply with sqlite SQL" \
  --extract \
  "how many conversations are there?"

Let’s run that query in the most diabolical way possible:

sqlite-utils "$(llm logs path)" "$(llm -f schema.sql \
  -s 'reply with sqlite SQL' \
  --extract \
  'how many conversations are there?')"

Turning that into a Python function¶

Let’s upgrade our hacky CLI prototype into a Python function.

import sqlite_utils
import llm

model = llm.get_model("gpt-4.1-mini")

def text_to_sql(db: sqlite_utils.Database, question: str) -> str:
    """Convert a prompt to SQL using the LLM."""
    prompt = "Schema:\n\n{}\n\nQuestion:\n\n{}".format(
        db.schema, question
    )
    return model.prompt(
        prompt,
        system="reply with SQLite SQL, not in markdown, just the SQL",
    ).text()

db = sqlite_utils.Database(llm.user_dir() / "logs.db")

sql = text_to_sql(db, "how many conversations are there?")

print(sql)

# Now execute it
result = db.query(sql)
print(list(result))

Upgrading that to a CLI tool¶

Now that we have this working, let’s turn it into a small CLI tool using argparse from the Python standard library:

import argparse
from pathlib import Path

import sqlite_utils
import llm

# pick your model
model = llm.get_model("gpt-4.1-mini")

def text_to_sql(db: sqlite_utils.Database, question: str) -> str:
    """Convert an English question into a SQLite SQL statement."""
    prompt = "Schema:\n\n{}\n\nQuestion:\n\n{}".format(db.schema, question)
    resp = model.prompt(
        prompt,
        system="reply with SQLite SQL, not in markdown, just the SQL",
    )
    return resp.text().strip()

def main():
    parser = argparse.ArgumentParser(
        description="Turn a natural-language question into SQL (and optionally run it)."
    )
    parser.add_argument(
        "question",
        help="The question to ask of your SQLite database, in plain English.",
    )
    parser.add_argument(
        "--db",
        "-d",
        default=str(llm.user_dir() / "logs.db"),
        help="Path to the SQLite database file.  [default: %(default)s]",
    )
    parser.add_argument(
        "--execute",
        "-x",
        action="store_true",
        help="Execute the generated SQL and print the results instead of just showing the SQL.",
    )
    args = parser.parse_args()

    db_path = Path(args.db)
    if not db_path.exists():
        parser.error(f"Database file not found: {db_path!r}")

    db = sqlite_utils.Database(db_path)
    sql = text_to_sql(db, args.question)

    if args.execute:
        try:
            rows = list(db.query(sql))
        except Exception as e:
            print("ERROR running SQL:", e)
            print("SQL was:", sql)
            raise SystemExit(1)
        # print rows as simple CSV
        for row in rows:
            print(row)
    else:
        print(sql)

if __name__ == "__main__":
    main()

Here’s a fun note: the above block just said “FILL ME” and then I ran this command:

llm -m o4-mini -f text-to-sql.md -s 'Write the code for the FILL ME bit'

Ways to make this better¶

This is the most basic version of this, but it works pretty well!

Some ways we could make this better:

Examples. The single most powerful prompt engineering trick is to give the LLM illustrative examples of what you are trying to achieve. A small number of carefully selected examples of questions and the expected SQL answer can radically improve the results.
Column values. A common failure case for text to SQL is when the question is e.g. “How many schools are in California?” and the model queries for where state = 'California' when it should have queried for where state = 'CA'. Feeding in some example value from each column can be all the model needs to get it right.
Data documentation. Just like a real data analyst, the more information you can feed the model the better.
Loop on errors. If the SQL query fails to run, feed the error back to the LLM and have it try again. You can use EXPLAIN ... for a cheap validation of the query without running the whole thing.