Build a Text-to-SQL Agent for Smarter Database Queries
What if you could build an AI agent that's able to talk to your database? Most large language models have been trained on code, including SQL. And in this video, we're going to build an agent that's able to use that SQL knowledge to connect to your databases. We'll be using LangGraph to build a ReAct agent, Next.js for a frontend application and models running on watsonx.ai. We also will be running an in-memory database using SQLite. So, let's dive into VS Code. In VS Code, I'm going to use the Next.js CLI to set up my boilerplate project.
For this, I'll be running create-next-app at latest, together with the name of my project. You need to answer a few questions, such as will you be using TypeScript, which I will, and a few other default settings. We also will be using Tailwind, so we don't have to write any CSS. If you build your own project, you might want to make slightly different choices. After the CLI is finished, we can find some boilerplate code in our Text2SQL agent directory. Make sure to move into this directory before you try to start the application.
In here, we can find a couple of files that we're going to change. In pages.tsx, you can find boilerplate code for the default Next.js app. We're going to get rid of this and add our own code instead. In here, I'll be creating a component called Home, which has a div element, with some settings in Tailwind to make sure we render the page nicely. We also need to make sure that we run this as a client-side component.
In Next.js, you can run components either client-side, or you can run code server-side in the background. Inside this div, we'll be adding a header, we'll be adding some placeholder messages, and finally, we'll be adding an input box that we'll use to type a message to the large language model. Let me paste in the code for the header, which includes a title for this application. Then we're going to paste in the input bar and button that we need in order to submit a question.
We'll be hooking up the input element and the button element to some functions later on. And then finally, in the middle, we're going to add some placeholder messages, which, of course, we'll be populating with data coming from the large language model later on. After putting in the code for the messages, I'm going to format my code and start the Next.js application. I can do this by running npm run dev, and this should open up a new page in my browser. In the browser, you can see we have a header, we have an input bar and also a button to submit a question.
You can see there's two placeholder messages. The message from us is tell me a joke about SQL, and the placeholder reply would be a couple of relational databases walked into a NoSQL bar, and they left because they couldn't find a table. If you've been building SQL or NoSQL databases, you probably understand that NoSQL databases don't have any relational tables. So let's head back to VS Code and start implementing the logic to have the LLM tell us a joke instead of coming up with a placeholder message myself.
We're going to kill the process running in our terminal because we need to install some libraries. We're going to install LangGraph and we're also going to be installing LangChain community, as LangGraph is based on LangChain. With these libraries, we'll be able to create a ReAct agent, and also we'll be able to connect to models available on watsonx.ai. After the installation is finished, we can create a new file, which we're going to call actions.ts,
and this is where we'll implement the logic to create the ReAct agent, and also to connect to models in watsonx.ai. We will be calling this file actions.ts, and at the bottom, at the top of this file, we need to make sure that we set it as a server-side file. And in here we can add a new function, which we are going to call message.
This message function will take an input, which is a message history, and then at the end it should return the latest message. First, we need to import some functions from LangChain. The first one is a chat function in order to connect to models in watsonx.ai. The second one is a function to create a ReAct agent. We have two functions, which we need in order to build a tool. And then finally, we need to import two functions, which are used to connect the messages coming from our frontend component to the messages that we need in our backend server.
In XAS, you won't be able to pass down very rich objects, such as the message history and LangChain. So instead, we need to serialize these to JSON, and then we need to deserialize these. So, there is a bit of function in here in order to do the serialization, and later the deserialization. The store message function will actually be the reply, will actually be the result type of messages in here.
So, let me add the logic here. First, I'm going to deserialize the messages coming in. And as mentioned, these have been serialized in order to pass them from our frontend component to our backend component.
Then we're going to set up the agent, and for this we'll be using the chat watsonx interface. You can see the model we're using is Mistral Large but, of course, you can use any of the other models available to your watsonx.ai project. We won't be passing any tools in yet, because we first need to set up our database.
And then finally, we're going to return the response at the end of this function. So let me save this so the file gets cleaned up. I'm now also going to set up my environment variables for watsonx.ai. I need my project ID, I need an endpoint and I also need to pass in my API key.
For this, I'm going to create a new file, which I'll be calling .env inside my project directory. And in here, I need to set the following variables. I need to set my API key, my endpoint, which includes your region, for me it's US South, the identity server, which is static, the project ID, which is coming from your watsonx.ai project, and finally a static API version. To get your API key and your project ID, you need to visit the watsonx.ai dashboard, and you can find the developer access page where these credentials are available to you. Make sure to save this file and then close it.
The next step is to connect whatever we set up in actions.ts to something we can use in page.tsx. For this, I'm going to create a few state variables. First, I need to create a state variable to make our input box a controlled component. For this, I'll be creating a variable called input message, and then next to the variable to have the value of input message. We also need to create an update function, which I'll be calling set input message. In order to use local state, we need to use the useState hook from ReAct, not to be confused with ReAct agents.
ReAct is a JavaScript frontend framework, and I'm going to set the initial value to an empty string. And also make sure to import useState from ReAct because otherwise you won't be able to use it. I'm also going to set up a message local state variable later on. But first let me connect input message to our input box. I'm going to scroll down to my input element, and in here I'm going to set value to equal to input message. Then I also need to hook up the onchange event, so whenever you start typing in this input box, it should update the input message with the value in the element.
So, I'm going to set this to update the set input message function, and the value will be e.target.value. Let me save this. We can now use this state variable in order to send a message to the large language model. And for this, we need to first set up a local state message history. For this, I'm going to create another state variable, and this time I'll be calling it messages, which has, again, an input function, which are called setMessages.
I'm using a useState hook again, and this time the useState hook is going to get a slightly different value. It's going to be an array instead of an empty string, and I also want to set some type definitions in here. But first, I need to import some things from LangChain in order to set the state. The message state variable will be of type BaseMessage, and this is LangChain's type definition of a message.
It's important to use types here in order to not confuse LangChain whenever we send a message to it later on, as we are using LangChain and LangGraft to build our ReAct agent. So, the type of messages will be BaseMessage, and then, of course, we can also set a SystemMessage. You can see I imported some functions and types in order to do this.
So, I have class HumanMessage, class SystemMessage, and class AIMessage. And these are the three types of messages that we have. We have a system prompt, which is the SystemMessage, we have a HumanMessage coming from us or the user, and then we have the AIMessage, which is the LLM response.
In here, I'm going to set up a new system prompt by using the systemMessage class. And in here, I'm going to tell the large language model what it is and what it is supposed to do for us. So we'll be building a Text2SQL agent. It should generate SQL based on natural language and use it with any given tools. So, later on, we'll be creating a tool to connect to a database, and the large language model should be aware it can use this tool to execute the SQL.
After setting this, I can then set up my function to actually send the message to the LangGraph agent. I'll be calling this message function sendMessage. It has a shadow message history, which includes all the previous messages in state, and then a new message. So, this will be your input message.
Whatever you type in the input box will be its value. And then before I'm sending it to the message function, which I have in actions.ts, I'm going to make sure that I serialize it in order to transfer it to our actions.ts function. Now, finally, if there's a response from the large language model, I'm going to make sure I store it in a local state as an AIMessage because it's coming from the large language model. I need to scroll down to my button component and hook it up to send the message whenever I press it. Say in here, I can create an onclick handler, onclick, and what this will do, it will send message.
And I can just save this. I probably also want to make sure that I get rid of my input message whenever I send a message here. So I can also send this value to be an empty string whenever I start to press send. Something else I want to do here probably is set a loading state. So, I can create a very simple loading state, which will make sure I don't get confused whether or not I send something to the large language model.
So, I'll be creating a third state variable, which is called isLoading, and then a function to update the isLoading state. By default, I will say this will be false because we don't want the loading state whenever we render the application first. So, this will be equal to false.
Whenever I send a message, I want to make sure that set isLoading is true, so I can show some render. And then finally, whenever we get our messages, I want to make sure that the loading state is set to false again. And save this. I can probably hook up isLoading state somewhere else, so whenever I start pressing the button, I want to make sure that the button says we are loading.
So, in here, I can check for the isLoading state. Whenever it is loading, I can change the label to be loading, so our button will show loading instead of send. And when it's not loading, it can just say send.
I can also disable the input box. That way you won't be able to send multiple messages when the LLM is already looking at your previous message. So, this should be all that's needed to set up my frontline application.
I can actually run this and see what it looks like in the browser. You can see we still have the same application. We still have the two placeholder messages. This time we have an input box that should be hooked up to something, but we won't be able to render these messages yet until we make another small change. Let's go back to page.tsx and make sure that our placeholder messages are replaced with the actual messages created by the agent. In page.tsx, I need to find the place where I set the messages.
And in here, I'm going to check for the existence of a message history. For this, I'm going to check if the message state is not an empty array. And if it's not, I'm going to be using it to render the placeholder messages.
We have two types of messages. We have messages coming from us or the user, and then we have a message coming from the large language model. So, in here, I need to distinguish between two types of messages.
The first one is the HumanMessage, and the second one is the message coming from the large language model or, in this case, actually the agent. As we are using LangChain, we can use the instance of, together with our class for HumanMessage or AIMessage, to distinguish between these two types. So, we can add some if-else statements, if message is equal to an instance of HumanMessage, we can render the first type of message.
If it's equal to an instance of AIMessage, we can render the second one. And this way, we will make sure that we don't render the wrong message. We won't be rendering any of the SystemMessages because these are just meant for the LLM or the agent. Of course, we need to make sure that whatever data is in here is being rendered dynamically from the state and isn't our placeholder message. For this, we can use the message.content field. And we need to make sure that this is rendered as a string, as in theory, content could be an object.
But for this case, I know for sure it will be a string, so I can use the S string definition. And I can do the same for the AIMessage. As we are using ReAct, we need to make sure whatever we return from a map function has a key. And for this, I'll be setting the key to be the message type plus the index count.
And I can do the same for the AIMessage. Make sure to format this and then save it. If you would visit the application in the browser, you can see an empty screen because we don't have any messages in history that should be rendered there. With this set up, I can now do the final part of this video, and I can create my database. For this, I'll be using SQLite 3. So, I need to kill the process running on my terminal and then I can install SQLite.
The library is available on npm, so I can just run npm install SQLite 3. Of course, you could also run a database in the cloud. But for this video, I decided it's easier to run something right directly in my application. After installing the library, I need to create a couple of files. I'll first, I will be creating a constants.ts file.
In this file, I'll be creating the create SQL queries, which I will be needing later on because we're going to use these create SQL queries not only to see the database, but also to give the large language model additional information on the database schema. So, in here, I'll be first adding information about the customer table. So, we'll be having a customer table in our database, which has ID, email and name. And then we'll be having an order table, which has more information about orders these customers might have placed.
And you can see there's a foreign key relation between customer and order, meaning that we would be able to see which customer has placed what order. Let me save this and then create another file, which I'm going to call database.ts. In the database.ts file, I'm going to create the functions to see the database and also to execute a SQL query against this database.
Go on this database.ts. In here, I need to make sure that I set it up to be a server-side function. And then I can start to import the constants we just created, which will be customer table and order table. And then I can start to create the database function. First, I want to connect to the database.
And for this, I need to set up a new connection to SQLite. Of course, I need to import SQLite as well. And then I can start to create a new database. As mentioned before, I'll be running this in memory because it's the easiest to do for this video. Then I need to create a seed function.
In the seed function, I'm going to run both create queries. So, first, I'm going to create a table called customer. Then I'll be creating a table called order. And then, of course, I need to seed these with actual data as well.
So, I'll be adding two functions. The first one will be db.run. This will be used to seed the customer table. And then I'll be running another db.run. And this one will be used to seed the orders table. So, in here, I can place the SQL query to populate these tables with data.
For customers, I'm going to input some mock data. And I'll be adding 10 users, which are mock data, of course. And then I'll be adding more orders as well. And these orders are linked to customer IDs. So, later on, the agent would be able to match customers to orders. Paste this in as well.
You don't have to worry about the formatting because these are template literals. I now have the seed function. I need to make sure I run this function whenever my application starts so I'm able to retrieve data from the database.
There are multiple places where you could do this. You can probably set up a nice function that runs whenever you start your application. I'm going to be a bit lazy. And inside my page.tsx, I'm going to import the seed function.
And whenever my application mounts, I'm going to run the seed function. For this, I'll be using the useState, the useEffect lifecycle function from ReAct. And I'm going to make sure it only renders when the application first mounts by setting an empty dependency array. And in here, I need to run the seed function.
As mentioned, there are better ways to do this. But for the purposes of showing you how to build a TXS SQL agent, this is one of the easiest ways to get it done. In my database.tsx, I can also create a function to execute a SQL query against the database. For this, I will be creating a function which I'm going to call execute. This execute function will take a SQL query as input.
And this will be a string. And then it should return data coming from the database. For this, I'm going to set up a promise because I need to make sure I wait for the data returned by the database. You can see I'm also returning errors. So, in case the database responds with an error, I'm going to return this error to the large language mode l so it might be able to try again and do better this time.
Let me clean up this code and then save it. We've now set up our database. So, we have a seed function, and we have a function to execute. And I can use both of these to set up a tool, which I'm going to do in actions.ts. In actions.ts, I'm going to create a new tool. I already imported the tool function from LangChain.
So, the only next thing for me to do is to actually define this tool. And to define this tool, I'm going to create a constant, which I call GetFromDB. And this will be using the tool function from LangChain. First, it needs to set the callback. So, this callback will be used to execute whenever the tool is being called. And for this, we'll be using the execute function we created in database.ts.
So, I'll be adding this to my imports. And then I also need to set the tool definition. So, this will be used by the LLM to decide whether or not it should call your tool.
For this, I'm going to paste in a little bit of placeholder code. As you can see, we have to find a tool, which we call GetFromDB. The tool description is get data from a database and then we paste in the database schema.
So, this is important because we want the large language model to be aware of what data is available in the database. Make sure we import these from our constants file as well. And then finally, you can see the input schema includes SQL.
And there we say it's a SQL query to get data from a database. Put quotes around the field and table name. It's important to make sure the large language model understand how it should form a SQL query.
Finally, we need to hook up this GetFromDB function to our actual agent. And we can do this in the tools field. I'm going to save this. And before I'm going to rerun my application, I want to make sure that I update my system prompt so the large language model is aware of how it should use this tool. In page.tsx, we have a very simple system prompt, which I'll be making this a bit more complex by adding more guidelines for the large language model.
So, instead of saying you should create a SQL query based on natural language, we're going to tell it you should create a SQLite query based on natural language. You should use the GetFromDB tool whenever possible. And you should enclose field names and table names in double quotes, even if they contain no special characters.
So, this will help the large language model to create a better SQL query. Make sure to save this and run the application by running npm run dev. And we should now be able to go back to the browser and see the application in action. Make sure to refresh the page and let's see if the large language model and the agent can come up with a better joke about SQL than I did in the beginning. So, tell me a joke about SQL.
For this, it doesn't need to create a SQL query. Instead, it should be able to just use its own training data. The joke is actually why did the database go to therapy? It had too many join issues.
So, if you've built SQL databases before, you can understand why this could be funny. So, let's ask a more complex question, which actually involves the large language model to use our tool. So, we can ask it how many customers do I have? And for this, it should query the customer table. You can see it generated a SQL query right here we actually wanted to execute. So, give me the number of customers. And send and, hopefully, we'll come back with the actual number instead of just the SQL query I should use.
You can see we should have 10 customers in our database. If we go back to VS Code, we should be able to see the SQL query generated. In a terminal, you can see I'm actually printing the response of the database.
And I'm also printing the SQL query DLM generated. Let's ask a more complex one where we ask it to join both tables. We make sure to refresh so we have a clean message history, and we don't explode the context window.
So, which customer placed most orders? This might take a bit longer as now it needs to join multiple tables in order to get the answer. And you can see it replied Lucas Bill placed the most orders. If we go back to VS Code, we might be able to see what SQL query it used in order to generate this answer. In VS Code, you can see it did a join so, it's actually joining the customer table with the order table and finally the result will be Lucas Bill. If you're building a text to SQL agent, you might want to think about guardrails because you don't want to give the, you don't want to give the DLM unlimited control of your database.
And that's how easy it is to build a text to SQL agent. We use LangGraph to build a ReAct agent models available on watsonx.ai. And finally we use the SQLite in-memory database to return our data. If you want to know more about building this application yourself, make sure to find the link to the GitHub repository in the video description.
2025-03-29 12:35
