Developing robust text-to-SQL features is a key challenge in the fields of natural language processing (NLP) and database management. This area increases the complexity of NLP and database management. In particular, it deals with complex queries and database structures. In this post, we'll show you a simple and powerful solution with code to attach intertext code using custom agent implementations along with Amazon Bedrock and Converse APIs.
The ability to translate natural language queries into SQL statements is a game changer for businesses and organizations as they allow users to interact with the database in a more intuitive and accessible way. However, database schema complexity, relationships between tables, and natural language nuances can often lead to inaccurate or incomplete SQL queries. This not only undermines the integrity of the data, but also hinders the overall user experience. Through a simple yet powerful architecture, agents can learn from the query, create execution plans, create SQL statements, self-correct if there are SQL errors, and run for future improvements. Overtime allows agents to develop a cohesive understanding of what to do and not do to efficiently answer queries from users.
Solution overview
The solution consists of an AWS lambda function that contains the logic of an agent that communicates with Amazon Dynamodb for long-term memory retention, and uses Amazon Realational Database (Amazon Redans (Amazon Rdds) that calls Anthropic's Claude Sonnet through Amazon Bedrock's Converse API and uses AWS Secrets Manager to invoke database connection details and credentials database database. The Lambda function is connected to a Virtual Private Cloud (VPC) and communicates with Dynamodb, Amazon Bedrock, and Secrets Manager through an AWS PrivateLink VPC endpoint, allowing Lambda to communicate with the RDS database while keeping traffic private through the AWS network.
In the demo, you can interact with the agent via the Lambda function. You can provide natural language queries such as “How many employees do you have in each department in each region?” Or, “What is an employee mix by gender in each region?” Below is the solution architecture:
Custom Agent Build Using the Converse API
The Converse API is provided by Amazon Bedrock to allow you to create conversational applications. It allows powerful features such as tool use. Using Tools is the ability to select from a list of tools, such as a large language model (LLM) to run SQL queries against a database, and decide which tool to use depending on the context of the conversation. The Converse API allows you to maintain a set of messages between user and assistant roles and chat with LLMs such as Anthropic's Claude 3.5 Sonnet. In this post, a custom agent called ConversesQlagent was created specifically to follow the long-term agent execution and execution plan.
Agent Loop: Agent Planning, Self-Revised, and Long-Term Learning
Agents include several important features, including planning and carryover, execution and using tools, SQLALCHEMY and self-correction, reflection, and long-term learning using memory.
Planning and carryover
The first step an agent takes is to create an execution plan to perform intertext tasks. First, think through what users are looking for and create a plan for how they will fulfill their requests. This behavior is controlled using a system prompt to define how the agent behaves. When the agent thinks about what to do, the plan is output.
One of the challenges with running agents over time is that agents sometimes forget plans that are supposed to run as the context grows longer as the agents perform steps. One of the main ways to deal with this is to carry over” The initial plan is returned to the system prompt section. The system prompt is part of every Converse API call and improves the ability of the agent to follow its plan. The system prompt plan is updated as new plans appear, as agents may modify plans as they progress through execution. See the following diagram for how carryover works.
Run and use tools
After the plan is created, the agent runs the plan one step at a time. You may decide to call one or more tools that you can access. The Converse API allows you to pass a ToolConfig containing the tool specs for each tool you can access. ToolSpec defines what the tool is, the description of the tool, and the parameters required for the tool. When LLM decides to use the tool, it outputs the tool usage block as part of the response. In this case, the application must identify the tool's usage block, run the corresponding tool, add the tool result response to the message list, and call the Converse API again. You can add a tool to select LLM tools by adding ToolConfig along with ToolSpec, as shown in (a) in the following diagram. Part (b) shows that in the implementation of ConversesQlagent, the tool group contains a collection of tools, each tool contains a tool spec and callable functions. The tool group is added to the agent and added to the Converse API call. Tool group instructions are additional instructions on how to use tool groups injected into the system prompt. Although you can add descriptions to individual tools, having instructions for the entire tool group allows for more effective use of the group.
sqlalchemy and self-correction
As shown in the previous diagram, the SQL Tools group (these tools are part of the demo code provided) is implemented using SQLalchemy. You can connect to Postgres, MySQL, etc. without changing the code every time.
This post has an InvakeSQlQuery tool that allows agents to execute arbitrary SQL statements. Almost all database-specific tasks, such as schemas and table searches, can be achieved through InvokeSQlquery, but it is recommended that you provide a SQLALCHEMY implementation of a specific task, such as GetDataBaseChemas, which retrieves all schemas for the database, significantly reduce the time it takes to generate the correct QUIRE. Think of it as giving the agent a shortcut to get the information they need. Agents can cause errors when querying the database via the InvakeSQLQuery tool. The InvokeSQlQuery tool responds with an error encountered by the agent, and the agent can perform self-correction to correct the query. This flow is shown in the following diagram:
Memory-based reflection and long-term learning
Self-correction is an important function of the agent, but the agent must be able to learn through mistakes to avoid the same mistakes in the future. Otherwise, the agent will continue to make mistakes, greatly reducing effectiveness and efficiency. The agent maintains a hierarchical memory structure, as shown in the following diagram: The agent decides how to configure memory. Below is an example of how it is constructed:
Agents can look back at their execution, learn best practices and error avoidance, and store it in long-term memory. Long-term memory is implemented through a hierarchical memory structure using Amazon DynamodB. The agent maintains main memory with other memory guidelines. Each memory is represented as a record in the dynamodb table. Once the agent runs and learns by encountering an error, it can update the main memory and create new memory by maintaining the index of the memory in the main memory. You can then tap this memory in the future to avoid errors and even improve the efficiency of your queries by caching facts.
Prerequisites
Before you begin, make sure you have the following prerequisites:
Deploy the solution
The complete code and instructions are available on github in the readme file.
- Clone the code into your work environment:
git clone https://github.com/aws-samples/aws-field-samples.git
- Go to
ConverseSqlAgentfolder - Follow the instructions in the readme file in the github repository
cleaning
To dispose of the stack, call the following command:
cdk destroy
Conclusion
Developing robust text-to-SQL features is a critical challenge in natural language processing and database management. Although the current approach has progressed, there remains room for improvement, especially with complex queries and database structures. The introduction of ConsulesQlagent, an implementation of custom agents using Amazon Bedrock and the Converse API, presents a promising solution to this problem. The agent's architecture features planning and carrying over, execution and use of tools, self-correction with SQLalchemy, and long-term reflection-based learning, demonstrating the ability to understand natural language questions, develop and execute SQL plans, and continuously improve their capabilities. As businesses want more intuitive ways to access and manage data, solutions like ConversesQlagent have the potential to bridge the gap between natural language and structured database queries, unlocking new levels of productivity and data-driven decision-making. For more information about diving deeper and generating AI, see these additional resources.
About the author
Pavan Kumar A solution architect for Amazon Web Services (AWS), helping customers design robust and scalable solutions in multiple industries in the cloud. With a background in enterprise architecture and software development, Pavan contributes to creating solutions that handle customer API security, API management, microservices, and geospatial information systems use cases. He is passionate about using these solutions to solve, automate and simplify customer problems.
Abdullah Siddiqui I am a Partner Sales Solutions Architect at Amazon Web Services (AWS) based in Toronto. He specializes in resilience and migration, helping AWS partners and customers build solutions using AWS services. In my spare time, I enjoy spending time with my family.
Parag Srivastava He is a solution architect for Amazon Web Services (AWS) and helps enterprise customers successfully adopt and migrate the cloud. During his professional career, he has been extensively involved in complex digital transformation projects. He is also passionate about building innovative solutions around the geospatial aspects of addresses.
