🎉 学 LangChain 免费领 openAI GPT key 限额1000名 →
新版文档(v1.x)
langchain
Build a SQL agent

Build a SQL agent

import ChatModelTabsPy from '/snippets/chat-model-tabs.mdx'; import ChatModelTabsJS from '/snippets/chat-model-tabs-js.mdx'; import SqlAgentDownloadChinookPy from '/snippets/code-samples/sql-agent-download-chinook-py.mdx'; import SqlAgentDownloadChinookJs from '/snippets/code-samples/sql-agent-download-chinook-js.mdx'; import SqlAgentExploreDatabasePy from '/snippets/code-samples/sql-agent-explore-database-py.mdx'; import SqlAgentToolsPy from '/snippets/code-samples/sql-agent-tools-py.mdx'; import SqlAgentSystemPromptPy from '/snippets/code-samples/sql-agent-system-prompt-py.mdx'; import SqlAgentCreateAgentPy from '/snippets/code-samples/sql-agent-create-agent-py.mdx'; import SqlAgentRunAgentPy from '/snippets/code-samples/sql-agent-run-agent-py.mdx'; import SqlAgentHitlMiddlewarePy from '/snippets/code-samples/sql-agent-hitl-middleware-py.mdx'; import SqlAgentHitlRunPy from '/snippets/code-samples/sql-agent-hitl-run-py.mdx'; import SqlAgentHitlResumePy from '/snippets/code-samples/sql-agent-hitl-resume-py.mdx'; import SqlAgentRunQueryJs from '/snippets/code-samples/sql-agent-run-query-js.mdx'; import SqlAgentSanitizeSqlJs from '/snippets/code-samples/sql-agent-sanitize-sql-js.mdx'; import SqlAgentExecuteSqlJs from '/snippets/code-samples/sql-agent-execute-sql-js.mdx'; import SqlAgentSystemPromptJs from '/snippets/code-samples/sql-agent-system-prompt-js.mdx'; import SqlAgentCreateAgentJs from '/snippets/code-samples/sql-agent-create-agent-js.mdx'; import SqlAgentRunAgentJs from '/snippets/code-samples/sql-agent-run-agent-js.mdx'; import SqlAgentStudioPy from '/snippets/code-samples/sql-agent-studio-py.mdx'; import SqlAgentStudioJs from '/snippets/code-samples/sql-agent-studio-js.mdx'; import SqlAgentHitlMiddlewareJs from '/snippets/code-samples/sql-agent-hitl-middleware-js.mdx'; import SqlAgentHitlRunJs from '/snippets/code-samples/sql-agent-hitl-run-js.mdx'; import SqlAgentHitlResumeJs from '/snippets/code-samples/sql-agent-hitl-resume-js.mdx';

Overview

In this tutorial, you will learn how to build an agent that can answer questions about a SQL database using LangChain agents (opens in a new tab).

At a high level, the agent will:

  1. Fetch the available tables and schemas from the database
  2. Decide which tables are relevant to the question
  3. Fetch the schemas for the relevant tables
  4. Generate a query based on the question and information from the schemas
  5. Double-check the query for common mistakes using an LLM
  6. Execute the query and return the results
  7. Correct mistakes surfaced by the database engine until the query is successful
  8. Formulate a response based on the results

Building Q&A systems of SQL databases requires executing model-generated SQL queries. There are inherent risks in doing this. Make sure that your database connection permissions are always scoped as narrowly as possible for your agent's needs. This will mitigate, though not eliminate, the risks of building a model-driven system.

Concepts

The following tutorial covers the following concepts:

Setup

pip install langchain langgraph

Set up LangSmith (opens in a new tab) to inspect what is happening inside your chain or agent. Then set the following environment variables:

export LANGSMITH_TRACING="true"
export LANGSMITH_API_KEY="..."

Build your SQL agent

Select a model that supports tool-calling (opens in a new tab):

The output shown in the examples below used OpenAI.

You will be creating a SQLite database (opens in a new tab) for this tutorial. SQLite is a lightweight database that is easy to set up and use. We will be loading the chinook database, which is a sample database that represents a digital media store.

For convenience, we have hosted the database (Chinook.db) on a public GCS bucket.

We will use Python's built-in sqlite3 module to interact with the database:

Dialect: sqlite
Available tables: ['Album', 'Artist', 'Customer', 'Employee', 'Genre', 'Invoice', 'InvoiceLine', 'MediaType', 'Playlist', 'PlaylistTrack', 'Track']
Sample output: [(1, 'AC/DC'), (2, 'Accept'), (3, 'Aerosmith'), (4, 'Alanis Morissette'), (5, 'Alice In Chains')]

The following database tools are minimal wrappers for demonstration purposes only. They are not intended to be secure or used in production. Use narrowly scoped database permissions and add application-specific validation before executing model-generated SQL.

We can implement database tools (opens in a new tab) as thin wrappers using the @tool decorator from langchain.tools:

sql_db_query: Input to this tool is a detailed and correct SQL query, output is a result from the database.
    If the query is not correct, an error message will be returned.
    If an error is returned, rewrite the query, check the query, and try again.
    If you encounter an issue with Unknown column 'xxxx' in 'field list', use sql_db_schema to query the correct table fields.

sql_db_schema: Input to this tool is a comma-separated list of tables, output is the schema and sample rows for those tables.
    Be sure that the tables actually exist by calling sql_db_list_tables first!
    Example Input: table1, table2, table3

sql_db_list_tables: Input is an empty string, output is a comma-separated list of tables in the database.

sql_db_query_checker: Use this tool to double check if your query is correct before executing it.
    Always use this tool before executing a query with sql_db_query!

Use @[create_agent] to build a ReAct agent (opens in a new tab) with minimal code. The agent will interpret the request and generate a SQL command, which the tools will execute. If the command has an error, the error message is returned to the model. The model can then examine the original request and the new error message and generate a new command. This can continue until the LLM generates the command successfully or reaches an end count. This pattern of providing a model with feedback - error messages in this case - is very powerful.

Initialize the agent with a descriptive system prompt to customize its behavior:

Now, create an agent with the model, tools, and prompt:

Run the agent on a sample query and observe its behavior:

================================ Human Message =================================

Which genre on average has the longest tracks?
================================== Ai Message ==================================
Tool Calls:
  sql_db_list_tables (call_BQsWg8P65apHc8BTJ1NPDvnM)
 Call ID: call_BQsWg8P65apHc8BTJ1NPDvnM
  Args:
================================= Tool Message =================================
Name: sql_db_list_tables

Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track
================================== Ai Message ==================================
Tool Calls:
  sql_db_schema (call_i89tjKECFSeERbuACYm4w0cU)
 Call ID: call_i89tjKECFSeERbuACYm4w0cU
  Args:
    table_names: Track, Genre
================================= Tool Message =================================
Name: sql_db_schema


CREATE TABLE "Genre" (
	"GenreId" INTEGER NOT NULL,
	"Name" NVARCHAR(120),
	PRIMARY KEY ("GenreId")
)

/*
3 rows from Genre table:
GenreId	Name
1	Rock
2	Jazz
3	Metal
*/


CREATE TABLE "Track" (
	"TrackId" INTEGER NOT NULL,
	"Name" NVARCHAR(200) NOT NULL,
	"AlbumId" INTEGER,
	"MediaTypeId" INTEGER NOT NULL,
	"GenreId" INTEGER,
	"Composer" NVARCHAR(220),
	"Milliseconds" INTEGER NOT NULL,
	"Bytes" INTEGER,
	"UnitPrice" NUMERIC(10, 2) NOT NULL,
	PRIMARY KEY ("TrackId"),
	FOREIGN KEY("MediaTypeId") REFERENCES "MediaType" ("MediaTypeId"),
	FOREIGN KEY("GenreId") REFERENCES "Genre" ("GenreId"),
	FOREIGN KEY("AlbumId") REFERENCES "Album" ("AlbumId")
)

/*
3 rows from Track table:
TrackId	Name	AlbumId	MediaTypeId	GenreId	Composer	Milliseconds	Bytes	UnitPrice
1	For Those About To Rock (We Salute You)	1	1	1	Angus Young, Malcolm Young, Brian Johnson	343719	11170334	0.99
2	Balls to the Wall	2	2	1	U. Dirkschneider, W. Hoffmann, H. Frank, P. Baltes, S. Kaufmann, G. Hoffmann	342562	5510424	0.99
3	Fast As a Shark	3	2	1	F. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman	230619	3990994	0.99
*/
================================== Ai Message ==================================
Tool Calls:
  sql_db_query_checker (call_G64yYm6R6UauiVPCXJZMA49b)
 Call ID: call_G64yYm6R6UauiVPCXJZMA49b
  Args:
    query: SELECT Genre.Name, AVG(Track.Milliseconds) AS AverageLength FROM Track INNER JOIN Genre ON Track.GenreId = Genre.GenreId GROUP BY Genre.Name ORDER BY AverageLength DESC LIMIT 5;
================================= Tool Message =================================
Name: sql_db_query_checker

SELECT Genre.Name, AVG(Track.Milliseconds) AS AverageLength FROM Track INNER JOIN Genre ON Track.GenreId = Genre.GenreId GROUP BY Genre.Name ORDER BY AverageLength DESC LIMIT 5;
================================== Ai Message ==================================
Tool Calls:
  sql_db_query (call_AnO3SrhD0ODJBxh6dHMwvHwZ)
 Call ID: call_AnO3SrhD0ODJBxh6dHMwvHwZ
  Args:
    query: SELECT Genre.Name, AVG(Track.Milliseconds) AS AverageLength FROM Track INNER JOIN Genre ON Track.GenreId = Genre.GenreId GROUP BY Genre.Name ORDER BY AverageLength DESC LIMIT 5;
================================= Tool Message =================================
Name: sql_db_query

[('Sci Fi & Fantasy', 2911783.0384615385), ('Science Fiction', 2625549.076923077), ('Drama', 2575283.78125), ('TV Shows', 2145041.0215053763), ('Comedy', 1585263.705882353)]
================================== Ai Message ==================================

On average, the genre with the longest tracks is "Sci Fi & Fantasy" with an average track length of approximately 2,911,783 milliseconds. This is followed by "Science Fiction," "Drama," "TV Shows," and "Comedy."

The agent correctly wrote a query, checked the query, and ran it to inform its final response.

You can inspect all aspects of the above run, including steps taken, tools invoked, what prompts were seen by the LLM, and more in the [LangSmith trace](https://smith.langchain.com/public/cd2ce887-388a-4bb1-a29d-48208ce50d15/r).

Studio provides a "client side" loop as well as memory so you can run this as a chat interface and query the database. You can ask questions like "Tell me the scheme of the database" or "Show me the invoices for the 5 top customers". You will see the SQL command that is generated and the resulting output. The details of how to get that started are below.

Run your agent in Studio

In addition to the previously mentioned packages, you will need to:

pip install -U langgraph-cli[inmem]>=0.4.0

In directory you will run in, you will need a langgraph.json file with the following contents:

{
  "dependencies": ["."],
  "graphs": {
      "agent": "./sql_agent.py:agent",
      "graph": "./sql_agent_langgraph.py:graph"
  },
  "env": ".env"
}

Create a file sql_agent.py and insert this:

It can be prudent to check the agent's SQL queries before they are executed for any unintended actions or inefficiencies.

LangChain agents feature support for built-in human-in-the-loop middleware (opens in a new tab) to add oversight to agent tool calls. Let's configure the agent to pause for human review on calling the sql_db_query tool:

We've added a checkpointer (opens in a new tab) to our agent to allow execution to be paused and resumed. See the human-in-the-loop guide (opens in a new tab) for detalis on this as well as available middleware configurations.

On running the agent, it will now pause for review before executing the sql_db_query tool:

...

INTERRUPTED:
Tool execution pending approval

Tool: sql_db_query
Args: {'query': 'SELECT g.Name AS Genre, AVG(t.Milliseconds) AS AvgTrackLength FROM Track t JOIN Genre g ON t.GenreId = g.GenreId GROUP BY g.Name ORDER BY AvgTrackLength DESC LIMIT 1;'}

We can resume execution, in this case accepting the query, using Command (opens in a new tab):

================================== Ai Message ==================================
Tool Calls:
  sql_db_query (call_7oz86Epg7lYRqi9rQHbZPS1U)
 Call ID: call_7oz86Epg7lYRqi9rQHbZPS1U
  Args:
    query: SELECT Genre.Name, AVG(Track.Milliseconds) AS AvgDuration FROM Track JOIN Genre ON Track.GenreId = Genre.GenreId GROUP BY Genre.Name ORDER BY AvgDuration DESC LIMIT 5;
================================= Tool Message =================================
Name: sql_db_query

[('Sci Fi & Fantasy', 2911783.0384615385), ('Science Fiction', 2625549.076923077), ('Drama', 2575283.78125), ('TV Shows', 2145041.0215053763), ('Comedy', 1585263.705882353)]
================================== Ai Message ==================================

The genre with the longest average track length is "Sci Fi & Fantasy" with an average duration of about 2,911,783 milliseconds, followed by "Science Fiction" and "Drama."

Refer to the human-in-the-loop guide (opens in a new tab) for details.

Next steps

For deeper customization, check out this tutorial (opens in a new tab) for implementing a SQL agent directly using LangGraph primitives.

Short-term memoryStreaming