构建 SQL 智能体

概述

在本教程中，你将学习如何使用 LangChain 智能体构建一个能够回答 SQL 数据库相关问题的智能体。在高层面上，智能体将：

从数据库获取可用的表和 schema

决定哪些表与问题相关

获取相关表的 schema

基于问题和 schema 信息生成查询

使用 LLM 仔细检查查询中的常见错误

执行查询并返回结果

纠正数据库引擎发现的错误，直到查询成功

基于结果形成响应

构建 SQL 数据库的问答系统需要执行模型生成的 SQL 查询。这样做存在固有风险。确保你的数据库连接权限始终尽可能地缩小到智能体所需的范围。这将减轻（但不能消除）构建模型驱动系统的风险。

概念

我们将涵盖以下概念：

用于从 SQL 数据库读取的工具
LangChain 智能体
人机协作流程

设置

安装

npm i langchain @langchain/core typeorm sqlite3 zod

yarn add langchain @langchain/core typeorm sqlite3 zod

pnpm add langchain @langchain/core typeorm sqlite3 zod

LangSmith

设置 LangSmith 来检查链或智能体内部发生了什么。然后设置以下环境变量：

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

1. 选择 LLM

选择一个支持工具调用的模型：

OpenAI
Anthropic
Azure
Google Gemini
Bedrock Converse

👉 Read the OpenAI chat model integration docs

npm install @langchain/openai

pnpm install @langchain/openai

yarn add @langchain/openai

bun add @langchain/openai

import { initChatModel } from "langchain";

process.env.OPENAI_API_KEY = "your-api-key";

const model = await initChatModel("gpt-5.4");

import { ChatOpenAI } from "@langchain/openai";

const model = new ChatOpenAI({
  model: "gpt-5.4",
  apiKey: "your-api-key"
});

👉 Read the Anthropic chat model integration docs

npm install @langchain/anthropic

pnpm install @langchain/anthropic

yarn add @langchain/anthropic

pnpm add @langchain/anthropic

import { initChatModel } from "langchain";

process.env.ANTHROPIC_API_KEY = "your-api-key";

const model = await initChatModel("claude-sonnet-4-6");

import { ChatAnthropic } from "@langchain/anthropic";

const model = new ChatAnthropic({
  model: "claude-sonnet-4-6",
  apiKey: "your-api-key"
});

👉 Read the Azure chat model integration docs

npm install @langchain/azure

pnpm install @langchain/azure

yarn add @langchain/azure

bun add @langchain/azure

import { initChatModel } from "langchain";

process.env.AZURE_OPENAI_API_KEY = "your-api-key";
process.env.AZURE_OPENAI_ENDPOINT = "your-endpoint";
process.env.OPENAI_API_VERSION = "your-api-version";

const model = await initChatModel("azure_openai:gpt-5.4");

import { AzureChatOpenAI } from "@langchain/openai";

const model = new AzureChatOpenAI({
  model: "gpt-5.4",
  azureOpenAIApiKey: "your-api-key",
  azureOpenAIApiEndpoint: "your-endpoint",
  azureOpenAIApiVersion: "your-api-version"
});

👉 Read the Google GenAI chat model integration docs

npm install @langchain/google-genai

pnpm install @langchain/google-genai

yarn add @langchain/google-genai

bun add @langchain/google-genai

import { initChatModel } from "langchain";

process.env.GOOGLE_API_KEY = "your-api-key";

const model = await initChatModel("google-genai:gemini-2.5-flash-lite");

import { ChatGoogleGenerativeAI } from "@langchain/google-genai";

const model = new ChatGoogleGenerativeAI({
  model: "gemini-2.5-flash-lite",
  apiKey: "your-api-key"
});

👉 Read the AWS Bedrock chat model integration docs

npm install @langchain/aws

pnpm install @langchain/aws

yarn add @langchain/aws

bun add @langchain/aws

import { initChatModel } from "langchain";

// Follow the steps here to configure your credentials:
// https://docs.aws.amazon.com/bedrock/latest/userguide/getting-started.html

const model = await initChatModel("bedrock:gpt-5.4");

import { ChatBedrockConverse } from "@langchain/aws";

// Follow the steps here to configure your credentials:
// https://docs.aws.amazon.com/bedrock/latest/userguide/getting-started.html

const model = new ChatBedrockConverse({
  model: "gpt-5.4",
  region: "us-east-2"
});

以下示例中显示的输出使用了 OpenAI。

2. 配置数据库

本教程你将创建一个 SQLite 数据库。SQLite 是一个轻量级数据库，易于设置和使用。我们将加载 chinook 数据库，这是一个代表数字媒体商店的示例数据库。为方便起见，我们已将数据库（Chinook.db）托管在公共 GCS 存储桶上。

import fs from "node:fs/promises";
import path from "node:path";

const url = "https://storage.googleapis.com/benchmarks-artifacts/chinook/Chinook.db";
const localPath = path.resolve("Chinook.db");

async function resolveDbPath() {
  if (await fs.exists(localPath)) {
    return localPath;
  }
  const resp = await fetch(url);
  if (!resp.ok) throw new Error(`Failed to download DB. Status code: ${resp.status}`);
  const buf = Buffer.from(await resp.arrayBuffer());
  await fs.writeFile(localPath, buf);
  return localPath;
}

3. 添加数据库交互工具

使用 langchain/sql_db 中可用的 SqlDatabase 包装器与数据库交互。该包装器提供了一个简单的接口来执行 SQL 查询和获取结果：

import { SqlDatabase } from "@langchain/classic/sql_db";
import { DataSource } from "typeorm";

let db: SqlDatabase | undefined;
async function getDb() {
  if (!db) {
    const dbPath = await resolveDbFile();
    const datasource = new DataSource({ type: "sqlite", database: dbPath });
    db = await SqlDatabase.fromDataSourceParams({ appDataSource: datasource });
  }
  return db;
}

async function getSchema() {
  const db = await getDb();
  return await db.getTableInfo();
}

4. 执行 SQL 查询

在运行命令之前，对 LLM 生成的命令在 _safe_sql 中进行检查：

const DENY_RE = /\b(INSERT|UPDATE|DELETE|ALTER|DROP|CREATE|REPLACE|TRUNCATE)\b/i;
const HAS_LIMIT_TAIL_RE = /\blimit\b\s+\d+(\s*,\s*\d+)?\s*;?\s*$/i;

function sanitizeSqlQuery(q) {
  let query = String(q ?? "").trim();

  // 阻止多条语句（允许一个可选的尾部 ;）
  const semis = [...query].filter((c) => c === ";").length;
  if (semis > 1 || (query.endsWith(";") && query.slice(0, -1).includes(";"))) {
    throw new Error("multiple statements are not allowed.")
  }
  query = query.replace(/;+\s*$/g, "").trim();

  // 只读门控
  if (!query.toLowerCase().startsWith("select")) {
    throw new Error("Only SELECT statements are allowed")
  }
  if (DENY_RE.test(query)) {
    throw new Error("DML/DDL detected. Only read-only queries are permitted.")
  }

  // 如果没有 LIMIT 则追加
  if (!HAS_LIMIT_TAIL_RE.test(query)) {
    query += " LIMIT 5";
  }
  return query;
}

然后，使用 SQLDatabase 的 run 通过 execute_sql 工具执行命令：

import { tool } from "langchain"
import * as z from "zod";

const executeSql = tool(
  async ({ query }) => {
    const q = sanitizeSqlQuery(query);
    try {
      const result = await db.run(q);
      return typeof result === "string" ? result : JSON.stringify(result, null, 2);
    } catch (e) {
      throw new Error(e?.message ?? String(e))
    }
  },
  {
    name: "execute_sql",
    description: "Execute a READ-ONLY SQLite SELECT query and return results.",
    schema: z.object({
      query: z.string().describe("SQLite SELECT query to execute (read-only)."),
    }),
  }
);

5. 使用 `createAgent`

使用 createAgent 以最少的代码构建 ReAct 智能体。智能体将解释请求并生成 SQL 命令。工具将检查命令的安全性，然后尝试执行命令。如果命令出错，错误消息将返回给模型。然后模型可以检查原始请求和新的错误消息并生成新命令。这可以持续到 LLM 成功生成命令或达到结束计数。这种向模型提供反馈——在这种情况下是错误消息——的模式非常强大。使用描述性系统提示初始化智能体以自定义其行为：

import { SystemMessage } from "langchain";

const getSystemPrompt = async () => new SystemMessage(`You are a careful SQLite analyst.

Authoritative schema (do not invent columns/tables):
${await getSchema()}

Rules:
- Think step-by-step.
- When you need data, call the tool \`execute_sql\` with ONE SELECT query.
- Read-only only; no INSERT/UPDATE/DELETE/ALTER/DROP/CREATE/REPLACE/TRUNCATE.
- Limit to 5 rows unless user explicitly asks otherwise.
- If the tool returns 'Error:', revise the SQL and try again.
- Limit the number of attempts to 5.
- If you are not successful after 5 attempts, return a note to the user.
- Prefer explicit column lists; avoid SELECT *.
`);

现在，使用模型、工具和提示创建智能体：

import { createAgent } from "langchain";

const agent = createAgent({
  model: "gpt-5.4",
  tools: [executeSql],
  systemPrompt: getSystemPrompt,
});

6. 运行智能体

在示例查询上运行智能体并观察其行为：

const question = "Which genre, on average, has the longest tracks?";
const stream = await agent.stream(
  { messages: [{ role: "user", content: question }] },
  { streamMode: "values" }
);
for await (const step of stream) {
  const message = step.messages.at(-1);
  console.log(`${message.role}: ${JSON.stringify(message.content, null, 2)}`);
}

human: Which genre, on average, has the longest tracks?
ai:
tool: [{"Genre":"Sci Fi & Fantasy","AvgMilliseconds":2911783.0384615385}]
ai: Sci Fi & Fantasy — average track length ≈ 48.5 minutes (about 2,911,783 ms).

智能体正确地编写了查询、检查了查询并运行它以支持其最终响应。

你可以在 LangSmith 追踪中检查上述运行的所有方面，包括采取的步骤、调用的工具、LLM 看到的提示等。

（可选）使用 Studio

Studio 提供了”客户端”循环以及记忆功能，让你可以将其作为聊天界面运行并查询数据库。你可以问诸如”告诉我数据库的 schema”或”显示前 5 个客户的发票”之类的问题。你将看到生成的 SQL 命令和结果输出。下面是如何开始的详细信息。

在 Studio 中运行你的智能体

除了前面提到的包之外，你还需要：

npm i -g @langchain/langgraph-cli@latest

在你运行的目录中，你需要一个包含以下内容的 langgraph.json 文件：

{
  "dependencies": ["."],
  "graphs": {
      "agent": "./sqlAgent.ts:agent",
      "graph": "./sqlAgentLanggraph.ts:graph"
  },
  "env": ".env"
}

import fs from "node:fs/promises";
import path from "node:path";
import { SqlDatabase } from "@langchain/classic/sql_db";
import { DataSource } from "typeorm";
import { SystemMessage, createAgent, tool } from "langchain"
import * as z from "zod";

const url = "https://storage.googleapis.com/benchmarks-artifacts/chinook/Chinook.db";
const localPath = path.resolve("Chinook.db");

async function resolveDbPath() {
  if (await fs.exists(localPath)) {
    return localPath;
  }
  const resp = await fetch(url);
  if (!resp.ok) throw new Error(`Failed to download DB. Status code: ${resp.status}`);
  const buf = Buffer.from(await resp.arrayBuffer());
  await fs.writeFile(localPath, buf);
  return localPath;
}

let db: SqlDatabase | undefined;
async function getDb() {
  if (!db) {
    const dbPath = await resolveDbPath();
    const datasource = new DataSource({ type: "sqlite", database: dbPath });
    db = await SqlDatabase.fromDataSourceParams({ appDataSource: datasource });
  }
  return db;
}

async function getSchema() {
  const db = await getDb();
  return await db.getTableInfo();
}

const DENY_RE = /\b(INSERT|UPDATE|DELETE|ALTER|DROP|CREATE|REPLACE|TRUNCATE)\b/i;
const HAS_LIMIT_TAIL_RE = /\blimit\b\s+\d+(\s*,\s*\d+)?\s*;?\s*$/i;

function sanitizeSqlQuery(q) {
  let query = String(q ?? "").trim();

  // 阻止多条语句（允许一个可选的尾部 ;）
  const semis = [...query].filter((c) => c === ";").length;
  if (semis > 1 || (query.endsWith(";") && query.slice(0, -1).includes(";"))) {
    throw new Error("multiple statements are not allowed.")
  }
  query = query.replace(/;+\s*$/g, "").trim();

  // 只读门控
  if (!query.toLowerCase().startsWith("select")) {
    throw new Error("Only SELECT statements are allowed")
  }
  if (DENY_RE.test(query)) {
    throw new Error("DML/DDL detected. Only read-only queries are permitted.")
  }

  // 如果没有 LIMIT 则追加
  if (!HAS_LIMIT_TAIL_RE.test(query)) {
    query += " LIMIT 5";
  }
  return query;
}

const executeSql = tool(
  async ({ query }) => {
    const q = sanitizeSqlQuery(query);
    try {
      const result = await db.run(q);
      return typeof result === "string" ? result : JSON.stringify(result, null, 2);
    } catch (e) {
      throw new Error(e?.message ?? String(e))
    }
  },
  {
    name: "execute_sql",
    description: "Execute a READ-ONLY SQLite SELECT query and return results.",
    schema: z.object({
      query: z.string().describe("SQLite SELECT query to execute (read-only)."),
    }),
  }
);

const getSystemPrompt = async () => new SystemMessage(`You are a careful SQLite analyst.

Authoritative schema (do not invent columns/tables):
${await getSchema()}

Rules:
- Think step-by-step.
- When you need data, call the tool \`execute_sql\` with ONE SELECT query.
- Read-only only; no INSERT/UPDATE/DELETE/ALTER/DROP/CREATE/REPLACE/TRUNCATE.
- Limit to 5 rows unless user explicitly asks otherwise.
- If the tool returns 'Error:', revise the SQL and try again.
- Limit the number of attempts to 5.
- If you are not successful after 5 attempts, return a note to the user.
- Prefer explicit column lists; avoid SELECT *.
`);

export const agent = createAgent({
  model: "gpt-5.4",
  tools: [executeSql],
  systemPrompt: getSystemPrompt,
});

后续步骤

如需更深入的自定义，请查看本教程，了解如何直接使用 LangGraph 原语实现 SQL 智能体。

将这些文档连接到 Claude、VSCode 等，通过 MCP 获取实时答案。

在 GitHub 上编辑此页面或提交问题。

Tutorials

Conceptual overviews

LangChain Academy

Additional resources

概述

概念

设置

安装

LangSmith

1. 选择 LLM

2. 配置数据库

3. 添加数据库交互工具

4. 执行 SQL 查询

5. 使用 `createAgent`

6. 运行智能体

（可选）使用 Studio

后续步骤

​概述

​概念

​设置

​安装

​LangSmith

​1. 选择 LLM

​2. 配置数据库

​3. 添加数据库交互工具

​4. 执行 SQL 查询

​5. 使用 createAgent

​6. 运行智能体

​（可选）使用 Studio

​后续步骤

概述

概念

设置

安装

LangSmith

1. 选择 LLM

2. 配置数据库

3. 添加数据库交互工具

4. 执行 SQL 查询

5. 使用 `createAgent`

6. 运行智能体

（可选）使用 Studio

后续步骤