*   [](/v0.2/)
*   [Tutorials](/v0.2/docs/tutorials/)
*   Build a Question/Answering system over SQL data

On this page

Build a Question/Answering system over SQL data
===============================================

Prerequisites

This guide assumes familiarity with the following concepts:

*   [Chaining runnables](/v0.2/docs/how_to/sequence/)
*   [Chat models](/v0.2/docs/concepts/#chat-models)
*   [Tools](/v0.2/docs/concepts/#tools)
*   [Agents](/v0.2/docs/concepts/#agents)

In this guide we'll go over the basic ways to create a Q&A chain and agent over a SQL database. These systems will allow us to ask a question about the data in a SQL database and get back a natural language answer. The main difference between the two is that our agent can query the database in a loop as many time as it needs to answer the question.

⚠️ Security note ⚠️[​](#️-security-note-️ "Direct link to ⚠️ Security note ⚠️")
-------------------------------------------------------------------------------

Building Q&A systems of SQL databases can require 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 chain/agent's needs. This will mitigate though not eliminate the risks of building a model-driven system. For more on general security best practices, see [here](/v0.2/docs/security).

Architecture[​](#architecture "Direct link to Architecture")
------------------------------------------------------------

At a high-level, the steps of most SQL chain and agent are:

1.  **Convert question to SQL query**: Model converts user input to a SQL query.
2.  **Execute SQL query**: Execute the SQL query
3.  **Answer the question**: Model responds to user input using the query results.

![SQL Use Case Diagram](/v0.2/assets/images/sql_usecase-d432701261f05ab69b38576093718cf3.png)

⚠️ Security note ⚠️[​](#️-security-note-️-1 "Direct link to ⚠️ Security note ⚠️")
---------------------------------------------------------------------------------

Building Q&A systems of SQL databases can require 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 chain/agent's needs. This will mitigate though not eliminate the risks of building a model-driven system. For more on general security best practices, see [here](/v0.2/docs/security).

Architecture[​](#architecture-1 "Direct link to Architecture")
--------------------------------------------------------------

At a high-level, the steps of most SQL chain and agent are:

1.  **Convert question to SQL query**: Model converts user input to a SQL query.
2.  **Execute SQL query**: Execute the SQL query
3.  **Answer the question**: Model responds to user input using the query results.

![SQL Use Case Diagram](/v0.2/assets/images/sql_usecase-d432701261f05ab69b38576093718cf3.png)

Setup[​](#setup "Direct link to Setup")
---------------------------------------

First, get required packages and set environment variables:

tip

See [this section for general instructions on installing integration packages](/v0.2/docs/how_to/installation#installing-integration-packages).

*   npm
*   Yarn
*   pnpm

    npm i langchain @langchain/community @langchain/openai

    yarn add langchain @langchain/community @langchain/openai

    pnpm add langchain @langchain/community @langchain/openai

We default to OpenAI models in this guide.

    export OPENAI_API_KEY=<your key># Uncomment the below to use LangSmith. Not required, but recommended for debugging and observability.# export LANGCHAIN_API_KEY=<your key># export LANGCHAIN_TRACING_V2=true

    import { SqlDatabase } from "langchain/sql_db";import { DataSource } from "typeorm";const datasource = new DataSource({  type: "sqlite",  database: "../../../../Chinook.db",});const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,});console.log(db.allTables.map((t) => t.tableName));/**[  'Album',       'Artist',  'Customer',    'Employee',  'Genre',       'Invoice',  'InvoiceLine', 'MediaType',  'Playlist',    'PlaylistTrack',  'Track'] */

#### API Reference:

*   [SqlDatabase](https://v02.api.js.langchain.com/classes/langchain_sql_db.SqlDatabase.html) from `langchain/sql_db`

Great! We've got a SQL database that we can query. Now let's try hooking it up to an LLM.

Chain[​](#chain "Direct link to Chain")
---------------------------------------

Let's create a simple chain that takes a question, turns it into a SQL query, executes the query, and uses the result to answer the original question.

### Convert question to SQL query[​](#convert-question-to-sql-query "Direct link to Convert question to SQL query")

The first step in a SQL chain or agent is to take the user input and convert it to a SQL query. LangChain comes with a built-in chain for this: [`createSqlQueryChain`](https://v02.api.js.langchain.com/functions/langchain_chains_sql_db.createSqlQueryChain.html)

    import { ChatOpenAI } from "@langchain/openai";import { createSqlQueryChain } from "langchain/chains/sql_db";import { SqlDatabase } from "langchain/sql_db";import { DataSource } from "typeorm";const datasource = new DataSource({  type: "sqlite",  database: "../../../../Chinook.db",});const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,});const llm = new ChatOpenAI({ model: "gpt-4", temperature: 0 });const chain = await createSqlQueryChain({  llm,  db,  dialect: "sqlite",});const response = await chain.invoke({  question: "How many employees are there?",});console.log("response", response);/**response SELECT COUNT(*) FROM "Employee" */console.log("db run result", await db.run(response));/**db run result [{"COUNT(*)":8}] */

#### API Reference:

*   [ChatOpenAI](https://v02.api.js.langchain.com/classes/langchain_openai.ChatOpenAI.html) from `@langchain/openai`
*   [createSqlQueryChain](https://v02.api.js.langchain.com/functions/langchain_chains_sql_db.createSqlQueryChain.html) from `langchain/chains/sql_db`
*   [SqlDatabase](https://v02.api.js.langchain.com/classes/langchain_sql_db.SqlDatabase.html) from `langchain/sql_db`

We can look at the [LangSmith trace](https://smith.langchain.com/public/6d8f0213-9f02-498e-aeb2-ec774e324e2c/r) to get a better understanding of what this chain is doing. We can also inspect the chain directly for its prompts. Looking at the prompt (below), we can see that it is:

*   Dialect-specific. In this case it references SQLite explicitly.
*   Has definitions for all the available tables.
*   Has three examples rows for each table.

This technique is inspired by papers like [this](https://arxiv.org/pdf/2204.00498.pdf), which suggest showing examples rows and being explicit about tables improves performance. We can also inspect the full prompt via the LangSmith trace:

![Chain Prompt](/v0.2/assets/images/sql_quickstart_langsmith_prompt-e90559eddd490ceee277642d9e76b37b.png)

### Execute SQL query[​](#execute-sql-query "Direct link to Execute SQL query")

Now that we've generated a SQL query, we'll want to execute it. This is the most dangerous part of creating a SQL chain. Consider carefully if it is OK to run automated queries over your data. Minimize the database connection permissions as much as possible. Consider adding a human approval step to you chains before query execution (see below).

We can use the [`QuerySqlTool`](https://v02.api.js.langchain.com/classes/langchain_tools_sql.QuerySqlTool.html) to easily add query execution to our chain:

    import { ChatOpenAI } from "@langchain/openai";import { createSqlQueryChain } from "langchain/chains/sql_db";import { SqlDatabase } from "langchain/sql_db";import { DataSource } from "typeorm";import { QuerySqlTool } from "langchain/tools/sql";const datasource = new DataSource({  type: "sqlite",  database: "../../../../Chinook.db",});const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,});const llm = new ChatOpenAI({ model: "gpt-4", temperature: 0 });const executeQuery = new QuerySqlTool(db);const writeQuery = await createSqlQueryChain({  llm,  db,  dialect: "sqlite",});const chain = writeQuery.pipe(executeQuery);console.log(await chain.invoke({ question: "How many employees are there" }));/**[{"COUNT(*)":8}] */

#### API Reference:

*   [ChatOpenAI](https://v02.api.js.langchain.com/classes/langchain_openai.ChatOpenAI.html) from `@langchain/openai`
*   [createSqlQueryChain](https://v02.api.js.langchain.com/functions/langchain_chains_sql_db.createSqlQueryChain.html) from `langchain/chains/sql_db`
*   [SqlDatabase](https://v02.api.js.langchain.com/classes/langchain_sql_db.SqlDatabase.html) from `langchain/sql_db`
*   [QuerySqlTool](https://v02.api.js.langchain.com/classes/langchain_tools_sql.QuerySqlTool.html) from `langchain/tools/sql`

tip

See a LangSmith trace of the chain above [here](https://smith.langchain.com/public/3cbcf6f2-a55b-4701-a2e3-9928e4747328/r).

### Answer the question[​](#answer-the-question "Direct link to Answer the question")

Now that we have a way to automatically generate and execute queries, we just need to combine the original question and SQL query result to generate a final answer. We can do this by passing question and result to the LLM once more:

    import { ChatOpenAI } from "@langchain/openai";import { createSqlQueryChain } from "langchain/chains/sql_db";import { SqlDatabase } from "langchain/sql_db";import { DataSource } from "typeorm";import { QuerySqlTool } from "langchain/tools/sql";import { PromptTemplate } from "@langchain/core/prompts";import { StringOutputParser } from "@langchain/core/output_parsers";import {  RunnablePassthrough,  RunnableSequence,} from "@langchain/core/runnables";const datasource = new DataSource({  type: "sqlite",  database: "../../../../Chinook.db",});const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,});const llm = new ChatOpenAI({ model: "gpt-4", temperature: 0 });const executeQuery = new QuerySqlTool(db);const writeQuery = await createSqlQueryChain({  llm,  db,  dialect: "sqlite",});const answerPrompt =  PromptTemplate.fromTemplate(`Given the following user question, corresponding SQL query, and SQL result, answer the user question.Question: {question}SQL Query: {query}SQL Result: {result}Answer: `);const answerChain = answerPrompt.pipe(llm).pipe(new StringOutputParser());const chain = RunnableSequence.from([  RunnablePassthrough.assign({ query: writeQuery }).assign({    result: (i: { query: string }) => executeQuery.invoke(i.query),  }),  answerChain,]);console.log(await chain.invoke({ question: "How many employees are there" }));/**There are 8 employees. */

#### API Reference:

*   [ChatOpenAI](https://v02.api.js.langchain.com/classes/langchain_openai.ChatOpenAI.html) from `@langchain/openai`
*   [createSqlQueryChain](https://v02.api.js.langchain.com/functions/langchain_chains_sql_db.createSqlQueryChain.html) from `langchain/chains/sql_db`
*   [SqlDatabase](https://v02.api.js.langchain.com/classes/langchain_sql_db.SqlDatabase.html) from `langchain/sql_db`
*   [QuerySqlTool](https://v02.api.js.langchain.com/classes/langchain_tools_sql.QuerySqlTool.html) from `langchain/tools/sql`
*   [PromptTemplate](https://v02.api.js.langchain.com/classes/langchain_core_prompts.PromptTemplate.html) from `@langchain/core/prompts`
*   [StringOutputParser](https://v02.api.js.langchain.com/classes/langchain_core_output_parsers.StringOutputParser.html) from `@langchain/core/output_parsers`
*   [RunnablePassthrough](https://v02.api.js.langchain.com/classes/langchain_core_runnables.RunnablePassthrough.html) from `@langchain/core/runnables`
*   [RunnableSequence](https://v02.api.js.langchain.com/classes/langchain_core_runnables.RunnableSequence.html) from `@langchain/core/runnables`

tip

See a LangSmith trace of the chain above [here](https://smith.langchain.com/public/d130ce1f-1fce-4192-921e-4b522884ec1a/r).

### Next steps[​](#next-steps "Direct link to Next steps")

For more complex query-generation, we may want to create few-shot prompts or add query-checking steps. For advanced techniques like this and more check out:

*   [Prompting strategies](/v0.2/docs/how_to/sql_prompting): Advanced prompt engineering techniques.
*   [Query checking](/v0.2/docs/how_to/sql_query_checking): Add query validation and error handling.
*   [Large databases](/v0.2/docs/how_to/sql_large_db): Techniques for working with large databases.

Agents[​](#agents "Direct link to Agents")
------------------------------------------

LangChain offers a number of tools and functions that allow you to create SQL Agents which can provide a more flexible way of interacting with SQL databases. The main advantages of using SQL Agents are:

*   It can answer questions based on the databases' schema as well as on the databases' content (like describing a specific table).
*   It can recover from errors by running a generated query, catching the traceback and regenerating it correctly.
*   It can answer questions that require multiple dependent queries.
*   It will save tokens by only considering the schema from relevant tables.
*   To initialize the agent, we use [`createOpenAIToolsAgent`](https://v02.api.js.langchain.com/functions/langchain_agents.createOpenAIToolsAgent.html) function. This agent contains the [`SqlToolkit`](https://v02.api.js.langchain.com/classes/langchain_agents_toolkits_sql.SqlToolkit.html) which contains tools to:
*   Create and execute queries
*   Check query syntax
*   Retrieve table descriptions
*   … and more

* * *

#### Was this page helpful?

  

#### You can also leave detailed feedback [on GitHub](https://github.com/langchain-ai/langchainjs/issues/new?assignees=&labels=03+-+Documentation&projects=&template=documentation.yml&title=DOC%3A+%3CPlease+write+a+comprehensive+title+after+the+%27DOC%3A+%27+prefix%3E).

[

Previous

Build a Retrieval Augmented Generation (RAG) App

](/v0.2/docs/tutorials/rag)[

Next

How-to guides

](/v0.2/docs/how_to/)

*   [⚠️ Security note ⚠️](#️-security-note-️)
*   [Architecture](#architecture)
*   [⚠️ Security note ⚠️](#️-security-note-️-1)
*   [Architecture](#architecture-1)
*   [Setup](#setup)
*   [Chain](#chain)
    *   [Convert question to SQL query](#convert-question-to-sql-query)
    *   [Execute SQL query](#execute-sql-query)
    *   [Answer the question](#answer-the-question)
    *   [Next steps](#next-steps)
*   [Agents](#agents)