More people are talking about AI Agents than actual agents exist. And likely the ratio of builders to people talking about building is 1:10000 and still here I am talking about agents, so looks like I’m contributing with more shit. I hope not but you know, I’m just a guy with a blog.
(BTW, if you want to read the code with a better formatting, just read the post here)
I wanted to create a really simple SQL Agent to teach myself how to do it, no libraries to simplify the process, just a bash script using the llm cli tool. I don’t actually like bash so much but it’s somehow limited so that allows me to focus on the actual problem.
The basic algorithm to generate a working SQL based on a user question would work like (python-ish):
``chat = LLM(system="You are an expert SQL query generator, write the SQL given the prompt.")
answer = chat(prompt)
while not is_correct(answer):
answer = chat(answer + ".Please fix the SQL query.")
print(answer)
``
So, in theory, I’d just need to write a function (is_correct) that tells the model if the SQL is rigth, and the initial prompt. In theory.
Ok, so let’s build a super simple agent that write SQL given a prompt. For simplicity the is_correct function will return “OK” if the SQL runs. It’s is not a good criterion but it’s a good starting point to understand the dynamics. I’m using DuckDB (a embedded analytics database I’m testing lately) as runtime (so I don’t need to run a fully fledged database server)
``LLM="llm -m gemini-2.0-flash-exp"
prompt="You are an expert SQL for duckdb query generator. Do not write any explainations, just the SQL query. Generate a SQL query for: $1"
flags=""
while true; do
# Generate SQL using LLM
SQL=$($LLM $flags "$prompt")
# Remove any ``` markers from the SQL
SQL=$(echo "$SQL" | sed 's/^```sql$//g' | sed 's/```$//g' | sed 's/^```//g')
# Try executing the SQL with DuckDB and capture the error output
ERROR=$(echo "$SQL" | duckdb 2>&1)
if [ $? -ne 1 ]; then
echo "*** DONE!"
echo "$SQL"
break
else
echo "*** Invalid SQL generated, retrying..."
echo "*** Error: $ERROR"
# Include the error in the next LLM prompt
prompt="Previous attempt failed with error: $ERROR Please fix the SQL query."
flags="-c" # to tell LLM to continue the conversation
sleep 1
fi
done
``
It kind of works. If you test with prompts that don’t require any table, it gives you pretty decent SQL queries.
``./sqlagent_1.sh "generate a the fibonacci series"
./sqlagent_1.sh "generate the game of life"
``
For the game of life needs a few iterations but that’s ok, it’s a recursive query. I coded that query myself a few years ago and it took me an afternoon.
But if we ask for stuff like
``./sqlagent_1.sh "generate a histogram with table http_requests"
``
It fails. It does because we are asking to do something that can’t do as the http_requests table doesn’t exist. Humans are constanty asking for stuff that is not possible, so we need a way to stop the agent to try. Is there a way so the LLM can say “enough is enough” and stop trying?
To be honest, sometimes it finds the way and generates things like:
``SELECT
CASE
WHEN request_time < 1 THEN 1
WHEN request_time < 2 THEN 2
WHEN request_time < 3 THEN 3
WHEN request_time < 4 THEN 4
WHEN request_time < 5 THEN 5
WHEN request_time < 6 THEN 6
WHEN request_time < 7 THEN 7
WHEN request_time < 8 THEN 8
WHEN request_time < 9 THEN 9
ELSE 10
END AS bucket,
count(*) AS count
FROM (SELECT random()*10 as request_time FROM range(100))
GROUP BY bucket
ORDER BY bucket;
``
So I tried adding a “If you unable to fix it, just return select 'STOP'” to the prompt but it does not work :)
``prompt="Previous attempt failed with error: $ERROR Please fix the SQL query. If you are not able to fix it, just return `select 'STOP'`."
``
But being more explicit and setting the prompt to:
``prompt="Previous attempt failed with error: $ERROR Please fix the SQL query. If you can't find the tables just return select 'STOP'."
``
it works (added the iteration number as debugging info)
``-- [ITERATION 0] --------------------------------------
*** Invalid SQL generated, retrying...
*** Error: Catalog Error: Table with name http_requests does not exist!
Did you mean "pg_sequences"?
LINE 5: http_requests
^
-- [ITERATION 1] --------------------------------------
*** DONE!
SELECT 'STOP'
``
Generating a query that do not read any data is not really useful, so let’s add a step where we pass the duckdb database to the agent so it can use the data inside it.
There are just two changes to the agent: 1) we pass to the prompt a list of columns for each table with the type (using information_schema.columns table) 2) we pass the database to the agent so it can use the data inside the database.
In this case I’m using a duckdb database I generated with all the views I track on this website. It looks like this
``✗ duckdb test.db -c "describe web_requests"
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ timestamp │ UINTEGER │ YES │ │ │ │
│ session_id │ VARCHAR │ YES │ │ │ │
│ action │ VARCHAR │ YES │ │ │ │
│ version │ VARCHAR │ YES │ │ │ │
│ payload │ VARCHAR │ YES │ │ │ │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
``
The code:
``#!/bin/bash
LLM="llm -m gemini-2.0-flash-exp"
DB=$1
# Get the list of columns for each table
tables=$(duckdb $DB -c "select table_name, column_name, data_type, is_nullable from information_schema.columns")
prompt="You are an expert SQL for duckdb query generator. Do not write any explainations, just the SQL query. The list of columns for each table is:\n $tables. Generate a SQL query for: $2"
flags=""
ITERATION=0
while true; do
# Generate SQL using LLM
echo "-- [ITERATION $ITERATION] --------------------------------------"
SQL=$($LLM $flags "$prompt")
# Remove any ``` markers from the SQL
SQL=$(echo "$SQL" | sed 's/^```sql$//g' | sed 's/```$//g' | sed 's/^```//g')
# Try executing the SQL with DuckDB and capture the error output
ERROR=$(echo "$SQL" | duckdb $DB 2>&1)
if [ $? -ne 1 ]; then
echo "*** DONE!"
echo "$SQL"
res=$(duckdb $DB -c "$SQL")
SQL=$($LLM $flags "the query ran successfully, check the results and return 'select 'STOP' if you think is that what the initial prompt asked for. Otherwise, return a new the SQL query.")
echo "$res"
SQL=$(echo "$SQL" | sed 's/^```sql$//g' | sed 's/```$//g' | sed 's/^```//g')
if [[ "$SQL" == *"STOP"* ]]; then
break
fi
else
echo "*** Invalid SQL generated, retrying..."
echo "*** Error: $ERROR"
# Include the error in the next LLM prompt
#prompt="Previous attempt failed with error: $ERROR Please fix the SQL query. If you are not able to fix it, just return `select 'STOP'`."
prompt="Previous attempt failed with error: $ERROR\n Please fix the SQL query. If you don't find the tables just return select 'STOP'."
flags="-c" # to tell LLM to continue the conversation
sleep 1
fi
ITERATION=$((ITERATION + 1))
done
duckdb $DB -c "$SQL"
``
When running it, it can generate queries but the results are not good.
``✗ ./sqlagent_3.sh test.db "visitors per day"
-- [ITERATION 0] --------------------------------------
*** Invalid SQL generated, retrying...
*** Error: Catalog Error: Scalar Function with name date does not exist!
Did you mean "datesub"?
...
^
-- [ITERATION 8] --------------------------------------
*** DONE!
SELECT CAST(TO_TIMESTAMP(timestamp/1000) AS DATE) AS day, COUNT(DISTINCT session_id) AS visitors
FROM web_requests
GROUP BY day
ORDER BY day;
┌────────────┬──────────┐
│ day │ visitors │
│ date │ int64 │
├────────────┼──────────┤
│ 1970-01-21 │ 7502 │
└────────────┴──────────┘
``
I see many ways to fix this:
Run the query and ask the LLM if it thinks it correct based on the initial prompt.
Give more info about the data (like a sample) so in the case of the example, it can actually see how the timestamp look like
Put humans in the loop so it can give more context, save the context for future runs and so on. Actually that’s what people are doing, check this example from Uber on how they do it: QueryGPT.
I tested 1 (as you see) and it does not work well, and tested 3 that, of course, works.
And this could go on and on, but I think I now understand how to build an super simple SQL Agent and I hope you did too.
There are more people talking about AI Agents than actual agents. And likely the ratio of builders to people talking about building is 1:10000 and still here I am talking about agents, so looks like I’m contributing with more shit. I hope not but you know, I’m just a guy with a blog.
I wanted to create a really simple SQL Agent to teach myself how to do it, no libraries to simplify the process, just a bash script using the llm cli tool. I don’t actually like bash so much but it’s somehow limited so that allows me to focus on the actual problem.
The basic algorithm to generate a working SQL based on a user question would work like (python-ish):
chat = LLM(system="You are an expert SQL query generator, write the SQL given the prompt.")
answer = chat(prompt)
while not is_correct(answer):
answer = chat(answer + ".Please fix the SQL query.")
print(answer)
So, in theory, I’d just need to write a function (is_correct) that tells the model if the SQL is rigth, and the initial prompt. In theory.
Ok, so let’s build a super simple agent that write SQL given a prompt. For simplicity the is_correct function will return “OK” if the SQL runs. It’s is not a good criterion but it’s a good starting point to understand the dynamics. I’m using DuckDB (a embedded analytics database I’m testing lately) as runtime (so I don’t need to run a fully fledged database server)
LLM="llm -m gemini-2.0-flash-exp"
prompt="You are an expert SQL for duckdb query generator. Do not write any explainations, just the SQL query. Generate a SQL query for: $1"
flags=""
while true; do
# Generate SQL using LLM
SQL=$($LLM $flags "$prompt")
# Remove any ``` markers from the SQL
SQL=$(echo "$SQL" | sed 's/^```sql$//g' | sed 's/```$//g' | sed 's/^```//g')
# Try executing the SQL with DuckDB and capture the error output
ERROR=$(echo "$SQL" | duckdb 2>&1)
if [ $? -ne 1 ]; then
echo "*** DONE!"
echo "$SQL"
break
else
echo "*** Invalid SQL generated, retrying..."
echo "*** Error: $ERROR"
# Include the error in the next LLM prompt
prompt="Previous attempt failed with error: $ERROR Please fix the SQL query."
flags="-c" # to tell LLM to continue the conversation
sleep 1
fi
done
It kind of works. If you test with prompts that don’t require any table, it gives you pretty decent SQL queries.
./sqlagent_1.sh "generate a the fibonacci series"
./sqlagent_1.sh "generate the game of life"
For the game of life needs a few iterations but that’s ok, it’s a recursive query. I coded that query myself a few years ago and it took me an afternoon.
But if we ask for stuff like
./sqlagent_1.sh "generate a histogram with table http_requests"
It fails. It does because we are asking to do something that can’t do as the http_requests table doesn’t exist. Humans are constanty asking for stuff that is not possible, so we need a way to stop the agent to try. Is there a way so the LLM can say “enough is enough” and stop trying?
To be honest, sometimes it finds the way and generates things like:
SELECT
CASE
WHEN request_time < 1 THEN 1
WHEN request_time < 2 THEN 2
WHEN request_time < 3 THEN 3
WHEN request_time < 4 THEN 4
WHEN request_time < 5 THEN 5
WHEN request_time < 6 THEN 6
WHEN request_time < 7 THEN 7
WHEN request_time < 8 THEN 8
WHEN request_time < 9 THEN 9
ELSE 10
END AS bucket,
count(*) AS count
FROM (SELECT random()*10 as request_time FROM range(100))
GROUP BY bucket
ORDER BY bucket;
So I tried adding a “If you unable to fix it, just return select 'STOP'” to the prompt but it does not work :)
prompt="Previous attempt failed with error: $ERROR Please fix the SQL query. If you are not able to fix it, just return `select 'STOP'`."
But being more explicit and setting the prompt to:
prompt="Previous attempt failed with error: $ERROR Please fix the SQL query. If you can't find the tables just return select 'STOP'."
it works (added the iteration number as debugging info)
-- [ITERATION 0] --------------------------------------
*** Invalid SQL generated, retrying...
*** Error: Catalog Error: Table with name http_requests does not exist!
Did you mean "pg_sequences"?
LINE 5: http_requests
^
-- [ITERATION 1] --------------------------------------
*** DONE!
SELECT 'STOP'
Generating a query that do not read any data is not really useful, so let’s add a step where we pass the duckdb database to the agent so it can use the data inside it.
There are just two changes to the agent:
1) we pass to the prompt a list of columns for each table with the type (using information_schema.columns table)
2) we pass the database to the agent so it can use the data inside the database.
In this case I’m using a duckdb database I generated with all the views I track on this website. It looks like this
✗ duckdb test.db -c "describe web_requests"
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ timestamp │ UINTEGER │ YES │ │ │ │
│ session_id │ VARCHAR │ YES │ │ │ │
│ action │ VARCHAR │ YES │ │ │ │
│ version │ VARCHAR │ YES │ │ │ │
│ payload │ VARCHAR │ YES │ │ │ │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
The code:
#!/bin/bash
LLM="llm -m gemini-2.0-flash-exp"
DB=$1
# Get the list of columns for each table
tables=$(duckdb $DB -c "select table_name, column_name, data_type, is_nullable from information_schema.columns")
prompt="You are an expert SQL for duckdb query generator. Do not write any explainations, just the SQL query. The list of columns for each table is:\n $tables. Generate a SQL query for: $2"
flags=""
ITERATION=0
while true; do
# Generate SQL using LLM
echo "-- [ITERATION $ITERATION] --------------------------------------"
SQL=$($LLM $flags "$prompt")
# Remove any ``` markers from the SQL
SQL=$(echo "$SQL" | sed 's/^```sql$//g' | sed 's/```$//g' | sed 's/^```//g')
# Try executing the SQL with DuckDB and capture the error output
ERROR=$(echo "$SQL" | duckdb $DB 2>&1)
if [ $? -ne 1 ]; then
echo "*** DONE!"
echo "$SQL"
res=$(duckdb $DB -c "$SQL")
SQL=$($LLM $flags "the query ran successfully, check the results and return 'select 'STOP' if you think is that what the initial prompt asked for. Otherwise, return a new the SQL query.")
echo "$res"
SQL=$(echo "$SQL" | sed 's/^```sql$//g' | sed 's/```$//g' | sed 's/^```//g')
if [[ "$SQL" == *"STOP"* ]]; then
break
fi
else
echo "*** Invalid SQL generated, retrying..."
echo "*** Error: $ERROR"
# Include the error in the next LLM prompt
#prompt="Previous attempt failed with error: $ERROR Please fix the SQL query. If you are not able to fix it, just return `select 'STOP'`."
prompt="Previous attempt failed with error: $ERROR\n Please fix the SQL query. If you don't find the tables just return select 'STOP'."
flags="-c" # to tell LLM to continue the conversation
sleep 1
fi
ITERATION=$((ITERATION + 1))
done
duckdb $DB -c "$SQL"
When running it, it can generate queries but the results are not good.
✗ ./sqlagent_3.sh test.db "visitors per day"
-- [ITERATION 0] --------------------------------------
*** Invalid SQL generated, retrying...
*** Error: Catalog Error: Scalar Function with name date does not exist!
Did you mean "datesub"?
...
^
-- [ITERATION 8] --------------------------------------
*** DONE!
SELECT CAST(TO_TIMESTAMP(timestamp/1000) AS DATE) AS day, COUNT(DISTINCT session_id) AS visitors
FROM web_requests
GROUP BY day
ORDER BY day;
┌────────────┬──────────┐
│ day │ visitors │
│ date │ int64 │
├────────────┼──────────┤
│ 1970-01-21 │ 7502 │
└────────────┴──────────┘
I see many ways to fix this:
I tested 1 (as you see) and it does not work well, and tested 3 that, of course, works.
And this could go on and on, but I think I now understand how to build an super simple SQL Agent and I hope you did too.
I used to dislike SQL. I learned to deal with databases using ORMs and when I needed to open psql to understand what was going on I felt like I was losing my time dealing with SQL. Why would someone want to deal with SQL when having a simple API in the language you are working with?
After some time I moved to the database infra space (as CTO of a company dealing with hundreds of postgres databases) and I started to work with SQL every day. It was not that bad and I learned to appreciate it little by little. And I finally loved it, I have done all sorts of crazy stuff with it, like rendering my company logo, recreating a game of life simulation and starting a company (that raised +50M) where the only interface is SQL.
Don’t get me wrong, I don’t think ORMs are useless, quite the opposite, I think they are good and developers should use them (they help you to avoid making mistakes) but that shouldn’t stop someone from learning SQL. You are going to find it somewhere in your professional career, there are many systems that only talk SQL (actually all the database systems end up talking SQL, hello MongoDB). And being honest, any developer can learn the basics of SQL in a few hours. That plus a few LLM prompts and you are good to go for 90% of the use cases. Learning a little bit of database internals is also good, that’ll put you in the top 1% of developers worldwide.
But there is one thing about SQL that amazes me: the runtime. It’s so simple, a simple text language, you send it over the wire and boom, you have results. No npm install, just a simple language, focused on a simple (but powerful) task, that lived many iterations of databases and systems over 50 years (!!!). That’s hard to beat, I don’t know so many designs that survived that long.
You have local runtimes (sqlite, duckdb), you can install postgres, mysql… you can go to neon and get a postgres database in a few milliseconds. You don’t even need data locally, you can query data in S3 and other places, you can even query data coming from other processes, it’s just amazing how simple it is. There is no other programming language with this simplicity.
I know SQL is not perfect when you need to work with complex logic. You can always twist it but you end up with crazy 2000 line SQL queries. In 20 years working with it I only found a few cases where it gets actually unmanageable to run data operations but many times it’s easy to use any other procedural language. You need to like functional programming as well, but you get used to thinking in that way after a while.
I’ll probably end my career writing SQL or talking to an LLM that writes SQL :)
Sabéis ese tipo de pique entre personas, no diría sano pero no del todo dañino, que te permite llevarte bien aunque ambos sabéis que nada te alegraría más que ganarle al otro. Es un poco secreto para nadie, igual que cuando hay tensión sexual no resuelta, pero mal.
Esto pasa entre empresas también. Hace unos cuantos años competíamos en el mercado de los mapas Carto y Mapbox. Las dos con un origen similar, haciendo algo muy parecido y peleando por los mismos clientes. Lógicamente siempre decíamos que “no competíamos” pero la realidad es que el pique era evidente y cuando una empresa te dice que no compite con otra es que en realidad compite y además está perdiendo.
El pique entre empresas pasa porque la gente dentro está picada, es más que obvio. Además suele ser un tema de testosterona que no beneficia a nadie. Y allí estábamos unos cuantos españolitos picados con unos americanos claramente más listos y más valientes que nosotros. Intentábamos emularles, nunca nos salía tan bien, jugar al juego de otro nunca sale.
Al caso, que me lio. Años después, una vez me di cuenta lo estupido que fui, escribí a uno de aquellos americanos, ya los dos fuera de las respectivas empresas, con el que estaba enganchadísimo para preguntarle como hacían para ser tan putos amos, como cuando el segundo felicita al ganador y de paso deja caer un “cómo lo haces?”
Fui directo y le dije “os teníamos envidia, como lo hacíais
Me comentó algunas cosas que en realidad nos habían pasado a nosotros también, errores (que nosotros vimos como súper aciertos), etc pero la parte más importante es que no les importó tirar lo que no funcionaba. No miraban atrás.
Me voy a parar un momento aquí a definir que es algo que no funciona. A veces, especialmente cuando tienes mucho dinero, te da la ventolera y piensas que algo va a funcionar. Te pones, metes 400 mil euros en personal+AWS y lo pones en producción. Todo el mundo a tope, the next big thing, esto va a ser la polla, imparables. Yo cada vez que lanzo algo escribo al concesionario de Porsche para decirles que vayan calentando. Al cabo de 3 meses tienes 12 clientes pagando lo suficiente poco para que no rente pero no llega como para distinguir la señal del ruido.
Te agarras a un clavo ardiendo porque has metido en un embolado a media empresa y al cabo de unos meses muere por sus propios medios, dejando eso si, un legacy que tienes que mantener por esa docena de clientes. Y tu como un gilipollas mirando (y peleando por) esos $200k de ARR que no te van a solucionar nada (en el contexto de una empresa con inversión de VC, claro)
Y ahí es donde ellos fueron valientes y tiraron abajo, haciendo un pelín de daño inevitable a algunos clientes, lo que no había funcionado.
Probaron, no funcionó, a tomar por culo.
Sabían que o encontraban la forma de hacer que la empresa funcionase o daba igual. Y si vas a tomar una decisión, las medias tintas nunca funcionan. Que fácil parece hacer esto, qué difícil es.
Del mismo modo que a veces tus clientes cambian de proveedor, a veces a ti te toca cambiar de clientes. De hecho, este año he aprendido mucho de lo de tomar decisiones firmes de alguno de nuestros clientes que han decidido no mirar atrás.
I used to dislike SQL. I learned to deal with databases using ORMs and when I needed to open psql to understand what was going on I felt like I was losing my time having to deal with SQL. Why would someone want to deal with SQL when having a simple API in the language you are working with?
After some time I moved to the database infra space (as CTO of a company dealing with hundreds of postgres databases) and I started to work with SQL every day. It was not that bad and I learned to appreciate it little by little. And I finally loved it, I have done all sorts of crazy stuff with it, like rendering my company logo, recreating a game of life simulation and starting a company (that raised +50M) where the only interface is SQL.
Don’t get me wrong, I don’t think ORMs are useless, quite the opposite, I think they are good and developers should use them (they help you to avoid making mistakes) but that shouldn’t stop someone from learning SQL. You are going to find it somewhere in your professional career, there are many systems that only talk SQL (actually all the database systems end up talking SQL, hello MongoDB). And being honest, any developer can learn the basics of SQL in a few hours. That plus a few LLM prompts and you are good to go for 90% of the use cases. Learning a little bit of database internals is also good, that’ll put you in the top 1% of developers worldwide.
But there is one thing about SQL that amazes me: the runtime. It’s so simple, a simple text language, you send it over the wire and boom, you have results. No npm install, just a simple language, focused on a simple (but powerful) task that have lived many iterations of databases and systems. That’s hard to beat.
You have local runtimes (sqlite, duckdb), you can install postgres, mysql… you can go to neon and get a postgres database in a few milliseconds. You don’t even need data locally, you can query data in S3 and other places, you can even query data coming from other processes, it’s just amazing how simple it is. There is no other programming language with this simplicity.
I know SQL is not perfect when you need to work with complex logic. You can always twist it but you end up with crazy 2000 line SQL queries. In 20 years working with it I only found a few cases where it gets actually unmanageable to run data operations but many times it’s easy to use any other procedural language. You need to like functional programming as well, but you get used to thinking in that way after a while.
I’ll probably end my career writing SQL or talking to an LLM that writes SQL :)