!function(){function t(t){document.documentElement.setAttribute("data-theme",t)}var e=function(){var t=null;try{t=new URLSearchParams(window.location.search).get("docusaurus-theme")}catch(t){}return t}()||function(){var t=null;try{t=localStorage.getItem("theme")}catch(t){}return t}();t(null!==e?e:"light")}(),document.documentElement.setAttribute("data-announcement-bar-initially-dismissed",function(){try{return"true"===localStorage.getItem("docusaurus.announcement.dismiss")}catch(t){}return!1}())

[Skip to main content](#__docusaurus_skipToContent_fallback)

LangChain v0.2 is coming soon! Preview the new docs [here](/v0.2/docs/introduction/).

[

![🦜️🔗 Langchain](/v0.1/img/brand/wordmark.png)![🦜️🔗 Langchain](/v0.1/img/brand/wordmark-dark.png)

](/v0.1/)[Docs](/v0.1/docs/get_started/introduction/)[Use cases](/v0.1/docs/use_cases/)[Integrations](/v0.1/docs/integrations/platforms/)[API Reference](https://api.js.langchain.com)

[More](#)

*   [People](/v0.1/docs/people/)
*   [Community](/v0.1/docs/community/)
*   [Tutorials](/v0.1/docs/additional_resources/tutorials/)
*   [Contributing](/v0.1/docs/contributing/)

[v0.1](#)

*   [v0.2](https://js.langchain.com/v0.2/docs/introduction)
*   [v0.1](/v0.1/docs/get_started/introduction/)

[🦜🔗](#)

*   [LangSmith](https://smith.langchain.com)
*   [LangSmith Docs](https://docs.smith.langchain.com)
*   [LangChain Hub](https://smith.langchain.com/hub)
*   [LangServe](https://github.com/langchain-ai/langserve)
*   [Python Docs](https://python.langchain.com/)

[Chat](https://chatjs.langchain.com)[](https://github.com/langchain-ai/langchainjs)

Search

On this page

SQL
===

This example demonstrates the use of the `SQLDatabaseChain` for answering questions over a SQL database.

This example uses Chinook database, which is a sample database available for SQL Server, Oracle, MySQL, etc.

info

These are legacy docs. It is now recommended to use LCEL over legacy implementations.

Looking for the LCEL docs? Click [here](/v0.1/docs/modules/chains/popular/sqlite/).

Set up[​](#set-up "Direct link to Set up")
------------------------------------------

First install `typeorm`:

*   npm
*   Yarn
*   pnpm

    npm install typeorm

    yarn add typeorm

    pnpm add typeorm

Then install the dependencies needed for your database. For example, for SQLite:

*   npm
*   Yarn
*   pnpm

    npm install sqlite3

    yarn add sqlite3

    pnpm add sqlite3

Currently, LangChain.js has default prompts for Postgres, SQLite, Microsoft SQL Server, MySQL, and SAP HANA.

Finally follow the instructions on [https://database.guide/2-sample-databases-sqlite/](https://database.guide/2-sample-databases-sqlite/) to get the sample database for this example.

tip

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

*   npm
*   Yarn
*   pnpm

    npm install @langchain/openai

    yarn add @langchain/openai

    pnpm add @langchain/openai

    import { DataSource } from "typeorm";import { OpenAI } from "@langchain/openai";import { SqlDatabase } from "langchain/sql_db";import { SqlDatabaseChain } from "langchain/chains/sql_db";/** * This example uses Chinook database, which is a sample database available for SQL Server, Oracle, MySQL, etc. * To set it up follow the instructions on https://database.guide/2-sample-databases-sqlite/, placing the .db file * in the examples folder. */const datasource = new DataSource({  type: "sqlite",  database: "Chinook.db",});const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,});const chain = new SqlDatabaseChain({  llm: new OpenAI({ temperature: 0 }),  database: db,});const res = await chain.run("How many tracks are there?");console.log(res);// There are 3503 tracks.

#### API Reference:

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

You can include or exclude tables when creating the `SqlDatabase` object to help the chain focus on the tables you want. It can also reduce the number of tokens used in the chain.

    const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,  includesTables: ["Track"],});

If desired, you can return the used SQL command when calling the chain.

    import { DataSource } from "typeorm";import { OpenAI } from "@langchain/openai";import { SqlDatabase } from "langchain/sql_db";import { SqlDatabaseChain } from "langchain/chains/sql_db";/** * This example uses Chinook database, which is a sample database available for SQL Server, Oracle, MySQL, etc. * To set it up follow the instructions on https://database.guide/2-sample-databases-sqlite/, placing the .db file * in the examples folder. */const datasource = new DataSource({  type: "sqlite",  database: "Chinook.db",});const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,});const chain = new SqlDatabaseChain({  llm: new OpenAI({ temperature: 0 }),  database: db,  sqlOutputKey: "sql",});const res = await chain.invoke({ query: "How many tracks are there?" });/* Expected result: * { *   result: ' There are 3503 tracks.', *   sql: ' SELECT COUNT(*) FROM "Track";' * } */console.log(res);

#### API Reference:

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

SAP Hana[​](#sap-hana "Direct link to SAP Hana")
------------------------------------------------

Here's an example of using the chain with a SAP HANA database:

    import { DataSource } from "typeorm";import { OpenAI } from "@langchain/openai";import { SqlDatabase } from "langchain/sql_db";import { SqlDatabaseChain } from "langchain/chains/sql_db";/** * This example uses a SAP HANA Cloud database. You can create a free trial database via https://developers.sap.com/tutorials/hana-cloud-deploying.html * * You will need to add the following packages to your package.json as they are required when using typeorm with SAP HANA: * *    "hdb-pool": "^0.1.6",             (or latest version) *    "@sap/hana-client": "^2.17.22"    (or latest version) * */const datasource = new DataSource({  type: "sap",  host: "<ADD_YOURS_HERE>.hanacloud.ondemand.com",  port: 443,  username: "<ADD_YOURS_HERE>",  password: "<ADD_YOURS_HERE>",  schema: "<ADD_YOURS_HERE>",  encrypt: true,  extra: {    sslValidateCertificate: false,  },});const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,});const chain = new SqlDatabaseChain({  llm: new OpenAI({ temperature: 0 }),  database: db,});const res = await chain.run("How many tracks are there?");console.log(res);// There are 3503 tracks.

#### API Reference:

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

Custom prompt[​](#custom-prompt "Direct link to Custom prompt")
---------------------------------------------------------------

You can also customize the prompt that is used. Here is an example prompting the model to understand that "foobar" is the same as the Employee table:

    import { DataSource } from "typeorm";import { OpenAI } from "@langchain/openai";import { SqlDatabase } from "langchain/sql_db";import { SqlDatabaseChain } from "langchain/chains/sql_db";import { PromptTemplate } from "@langchain/core/prompts";const template = `Given an input question, first create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.Use the following format:Question: "Question here"SQLQuery: "SQL Query to run"SQLResult: "Result of the SQLQuery"Answer: "Final answer here"Only use the following tables:{table_info}If someone asks for the table foobar, they really mean the employee table.Question: {input}`;const prompt = PromptTemplate.fromTemplate(template);/** * This example uses Chinook database, which is a sample database available for SQL Server, Oracle, MySQL, etc. * To set it up follow the instructions on https://database.guide/2-sample-databases-sqlite/, placing the .db file * in the examples folder. */const datasource = new DataSource({  type: "sqlite",  database: "data/Chinook.db",});const db = await SqlDatabase.fromDataSourceParams({  appDataSource: datasource,});const chain = new SqlDatabaseChain({  llm: new OpenAI({ temperature: 0 }),  database: db,  sqlOutputKey: "sql",  prompt,});const res = await chain.invoke({  query: "How many employees are there in the foobar table?",});console.log(res);/*  {    result: ' There are 8 employees in the foobar table.',    sql: ' SELECT COUNT(*) FROM Employee;'  }*/

#### API Reference:

*   [OpenAI](https://api.js.langchain.com/classes/langchain_openai.OpenAI.html) from `@langchain/openai`
*   [SqlDatabase](https://api.js.langchain.com/classes/langchain_sql_db.SqlDatabase.html) from `langchain/sql_db`
*   [SqlDatabaseChain](https://api.js.langchain.com/classes/langchain_chains_sql_db.SqlDatabaseChain.html) from `langchain/chains/sql_db`
*   [PromptTemplate](https://api.js.langchain.com/classes/langchain_core_prompts.PromptTemplate.html) from `@langchain/core/prompts`

* * *

#### Help us out by providing feedback on this documentation page:

*   [Set up](#set-up)
*   [SAP Hana](#sap-hana)
*   [Custom prompt](#custom-prompt)

Community

*   [Discord](https://discord.gg/cU2adEyC7w)
*   [Twitter](https://twitter.com/LangChainAI)

GitHub

*   [Python](https://github.com/langchain-ai/langchain)
*   [JS/TS](https://github.com/langchain-ai/langchainjs)

More

*   [Homepage](https://langchain.com)
*   [Blog](https://blog.langchain.dev)

Copyright © 2024 LangChain, Inc.