As I posted earlier, I play fantasy football and have a database of player stats. I usually have written my own queries to answer questions I have about players - but what if an LLM could take natural language and convert it to SQL? This is what is called an agent - it uses an LLM as the brain and connects it to tools, in our case an SQLite database with player stats. We’ll build a simple agent, running as a command line app using Bun and Typescript since I’m familiar with both. We’ll use a local instance of Llama3 running via Ollama as our LLM. To start, let’s define a flow for our agent:
- User passes in a question when calling the CLI tool
- Tool understands database schema
- Uses the schema to create valid sqlite query
- Resulting query answers the question
Here’s the core of what we want to do:
const userQuestion = Bun.argv[2]
const db = new Database(STATS_DB, { readonly: true })
const llmMessages = [
/* Do something with the user question here. */
]
try {
const sqlQuery = await callLLM(startingQueries)
const result = queryDB(db, sqlQuery)
console.log(`Query: ${sqlQuery}`)
console.log(`Result: ${result}`)
} catch (e) {
console.error(e)
}This should satisfy requirements 1 and 4. We could give the LLM an understanding of the database schema by providing the schema as part of the system message. Let’s do that:
const systemMessage = {
role: "system",
content: `You are a fantasy football expert. I have an sqlite database containing stats for NFL players you should use to answer the following questions, concerned with fantasy football and understanding how stats and points are connected. Below are the tables in the database. Use them when generating queries: ${tables}.`,
}Now let’s pass in the user’s question:
const userMessage = {
role: "user",
content: `Generate an SQLite query to answer the following question: ${userQuestion} Only return a valid sqlite query without any explanation or reasoning.`,
}Wiring it all up gives us:
const userQuestion = Bun.argv[2]
const db = new Database(STATS_DB, { readonly: true })
const tables = getTableDefinitions(db)
const systemMessage = {
role: "system",
content: `You are a fantasy football expert. I have an sqlite database containing stats for NFL players you should use to answer the following questions, concerned with fantasy football and understanding how stats and points are connected. Below are the tables in the database. Use them when generating queries: ${tables}.`,
}
const userMessage = {
role: "user",
content: `Generate an SQLite query to answer the following question: ${userQuestion} Only return a valid sqlite query without any explanation or reasoning.`,
}
try {
const sqlQuery = await callLLM([systemMessage, userMessage])
const result = queryDB(db, sqlQuery)
console.log(`Query: ${sqlQuery}`)
console.log(`Result: ${result}`)
} catch (e) {
console.error(e)
}Great, let’s ask it a question.
bun run index.ts "Which wide receivers had the highest average points for the past two seasons?"
SQLiteError: ambiguous column name: position
errno: 1Hmm. Unfortunately the tool is useless if the SQL query is invalid. Maybe we could pipe the error back into the LLM to see if it can fix the query and iterate towards something that works. We’ll pass the full conversation history to the LLM so it has more context to hopefully fix the query.
interface Message {
role: string
content: string
}
async function fixFailure(messages: Message[], query: string, error: string) {
const prompt = `I received an error when running this sqlite query ${query}. The error was ${error}. Please fix. Only return a valid sqlite query without any explanation or reasoning. Make sure your new query is different than the original query.`
const newMessages = addMessage(messages, prompt)
const result = await callLLM(newMessages)
return { messages: newMessages, result }
}
async function iterateToAnswer(
db: Database,
initialMessages: Message[],
maxAttempts: number = 5
) {
let validAnswer = null
let sqlQuery = await callLLM(initialMessages)
let messages = initialMessages
let result
for (let i = 0; i <= maxAttempts; i++) {
if (validAnswer) continue
try {
result = queryDB(db, sqlQuery)
validAnswer = result
} catch (e: any) {
console.log(e.message)
const fixed = await fixFailure(messages, sqlQuery, e.message)
messages = fixed.messages
result = fixed.result
}
}
return [sqlQuery, validAnswer]
}Now we can use the iterateToAnswer function in place of calling the LLM:
try {
const [sqlQuery, result] = await iterateToAnswer(db, [
systemMessage,
userMessage,
])
console.log(`Query: ${sqlQuery}`)
console.log(`Result:`)
console.log(result)
} catch (e) {
console.error(e)
}Okay, let’s give it a shot with a new query:
bun run index.ts “Who was the highest scoring tight end in 2023 who played in at least 10 games?"
Query: SELECT player_name, fantasy_points_ppr FROM stats_by_game
WHERE season = 2023 AND position = "Tight End" AND receptions > 0 AND week >= 1 AND week <= 18
GROUP BY player_id, player_name, position
ORDER BY AVG(fantasy_points_ppr) DESC LIMIT 1;
Result:
[]Hmm, okay maybe we just need to run it again, these things are non-deterministic after all. Let’s see what another shot comes up with:
bun run index.ts “Who was the highest scoring tight end in 2023 who played in at least 10 games?"
Query: SELECT p.name, SUM(s.fantasy_points) AS total_points
FROM stats_by_game s
JOIN players p ON s.player_id = p.id
WHERE s.season = 2023 AND s.week >= 1 AND s.week <= 18 AND s.position = 'TE' AND s.carries + s.receptions > 0
GROUP BY p.name
ORDER BY total_points DESC
LIMIT 1;
Result:
[
{
name: "Sam LaPorta",
total_points: 153.29999947547913,
}
]Nice! That is at least a reasonable answer and it may even be correct. This is moving in a positive direction but we can see the agent is far from helpful. How could we improve it? Some of our options are:
1. Pass back better SQL error messages
Right now the errors are pretty opaque. They are not that readable even for a human. An unclear error makes it hard for the model to understand what went wrong.
2. Use another LLM
We're using LLama3 which is fairly powerful but also a general purpose model. Maybe a different model or one specifically trained on SQL would work better?
3. Better prompting
I used simple prompts I came up with on the spot. We could iterate through multiple messages to see what performs better.
4. Improved agent flow
We dump a lot of information on the model at once and expect it to follow the correct reasoning process. Instead we walk the LLM through breaking the problem down and interacting with the database multiple times to understand the data before it tries to answer the question.
This experiment has revealed some of the limitations of our naive agent and defined areas to explore going forward. Thanks for riding along! If you want to see the full source code, here is the Github repository.