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.
These are legacy docs. It is now recommended to use LCEL over legacy implementations.
Looking for the LCEL docs? Click here.
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/ to get the sample database for this example.
- 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 from
@langchain/openai
- SqlDatabase from
langchain/sql_db
- SqlDatabaseChain 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 from
@langchain/openai
- SqlDatabase from
langchain/sql_db
- SqlDatabaseChain from
langchain/chains/sql_db
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 from
@langchain/openai
- SqlDatabase from
langchain/sql_db
- SqlDatabaseChain from
langchain/chains/sql_db
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 from
@langchain/openai
- SqlDatabase from
langchain/sql_db
- SqlDatabaseChain from
langchain/chains/sql_db
- PromptTemplate from
@langchain/core/prompts