Unlocking the Power of LangChain’s SQL Agent: A Deep Dive into Natural Language Database Interactions
Introduction
Natural language querying allows users to interact with databases more intuitively and efficiently. By leveraging the power of LangChain, SQL Agents, and OpenAI’s Large Language Models (LLMs) like ChatGPT, we can create applications that enable users to query databases using natural language. In this blog post, we’ll discuss the key features of these technologies and provide a step-by-step guide on how to implement them for natural language database querying.
LangChain
LangChain is a framework designed to build applications powered by language models. It provides a standard interface for chains, integrates with various tools, and offers end-to-end chains for typical applications. The two main features of LangChain are data-awareness and agentic behaviour.
Data awareness enables the language model to connect to other sources of data, while agentic behaviour allows the model to interact with its environment. Using agents, LangChain can dynamically decide which tools to call based on user input. This makes agents extremely powerful when used correctly.
Tools, Agents, and Toolkits in LangChain
A. Tools are functions that perform specific duties, such as Google Search, database lookups, or Python REPL. They take a string as input and return a string as output.
B. Agents are responsible for determining which actions to take and in what order. They can use tools, observe the output, or return to the user.
C. Toolkits are collections of tools that can be utilized by agents. LangChain supports various toolkits to help developers create powerful applications.
Agents
The SQL Agent provided by LangChain is a tool that allows you to interact with SQL databases using natural language. It is designed to be more flexible and more powerful than the standard SQLDatabaseChain, and it can be used to answer more general questions about a database, as well as recover from errors.
Here are some of the advantages of using the SQL Agent:
- 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 is compatible with any SQL dialect supported by SQLAlchemy and TypeORM (e.g., MySQL, PostgreSQL, Oracle SQL, Databricks, SQLite).
Prerequisites
The following guide, we are using on our machine:
· SQLite database
· NodeJS
You’ll also need an OpenAI API Key. You can get one at https://openai.com.
Getting started
To get started, we’ll create a NodeJS/ExpressJS project:
npm init --yUpdate the new package.json file to use ES Modules by adding this line of configuration:
"type": "module",Next, we’ll install the required npm packages:
npm install cors dotenv express langchain typeormCreate a .env file in the root directory of the project
OPENAI_API_KEY=<YOUR-OWN-KEY>Create an index.js file with the following code
import express from "express";
import cors from "cors";
import { OpenAI } from "langchain/llms/openai";
import { SqlDatabase } from "langchain/sql_db";
import { createSqlAgent, SqlToolkit } from "langchain/agents/toolkits/sql";
import { DataSource } from "typeorm";
import { configDotenv } from "dotenv";
// Load configuration
try {
configDotenv();
} catch (e) {
console.error("Error loading configuration:", e);
process.exit(1);
}
// Create server
const app = express();
app.use(cors());
// Create database connection
const datasource = new DataSource({
type: "sqlite",
database: "./data/northwind.db",
});
const db = await SqlDatabase.fromDataSourceParams({
appDataSource: datasource,
});
const toolkit = new SqlToolkit(db);
// Create OpenAI model
const model = new OpenAI({
temperature: 0,
});
const executor = createSqlAgent(model, toolkit);
// Route handler
app.get("/api/query", async (req, res) => {
const prompt = req.query.prompt;
console.log("prompt: " + prompt);
let response = {
prompt: prompt,
sqlQuery: "",
result: [],
error: "",
};
try {
const result = await executor.call({ input: prompt });
result.intermediateSteps.forEach((step) => {
if (step.action.tool === "query-sql") {
response.prompt = prompt;
response.sqlQuery = step.action.toolInput;
response.result = JSON.parse(step.observation);
}
});
console.log(
`Intermediate steps ${JSON.stringify(result.intermediateSteps, null, 2)}`
);
res.status(200).json(response);
} catch (e) {
console.log(e + " " + "my error message");
response.error = "Server error. Try again with a different prompt.";
res.status(500).json(response);
}
await datasource.destroy();
});
// Start server
app.listen(5000, () => {
console.log("Server started on port 5000");
});To run the server, use either nodemon or npm run start
The schema of the example database is:
For testing API:
import axios from "axios";
const prompt = "Get all the employees who were hired after 2005?";
try {
const response = await axios.get(
`http://localhost:5000/api/query?prompt=${encodeURIComponent(prompt)}`
);
const data = response.data;
console.log(data);
} catch (error) {
console.log(error);
}Use node test.js to run the file
Output:
[
{
"action": {
"tool": "list-tables-sql",
"toolInput": "",
"log": "Action: list-tables-sql\nAction Input: \"\""
},
"observation": "Categories, CustomerCustomerDemo, CustomerDemographics, Customers, EmployeeTerritories, Order Details, Orders, Products, Regions, Shippers, Suppliers, Territories, Employees"
},
{
"action": {
"tool": "info-sql",
"toolInput": "Employees",
"log": " I should look at the Employees table to see what columns I can query.\nAction: info-sql\nAction Input: \"Employees\""
},
"observation": "CREATE TABLE Employees (\nEmployeeID INTEGER , LastName TEXT , FirstName TEXT , Title TEXT , TitleOfCourtesy TEXT , BirthDate DATE , HireDate DATE , Address TEXT , City TEXT , Region TEXT , PostalCode TEXT , Country TEXT , HomePhone TEXT , Extension TEXT , ReportsTo INTEGER , PhotoPath TEXT ) \nSELECT * FROM \"Employees\" LIMIT 3;\n EmployeeID LastName FirstName Title TitleOfCourtesy BirthDate HireDate Address City Region PostalCode Country HomePhone Extension ReportsTo PhotoPath\n 1 Davolio Nancy Sales Representative Ms. 1968-12-08 2012-05-01 507 - 20th Ave. E.Apt. 2A Seattle North America 98122 USA (206) 555-9857 5467 2 http://accweb/emmployees/davolio.bmp\n 2 Fuller Andrew Vice President, Sales Dr. 1972-02-19 2012-08-14 908 W. Capital Way Tacoma North America 98401 USA (206) 555-9482 3457 null http://accweb/emmployees/fuller.bmp\n 3 Leverling Janet Sales Representative Ms. 1983-08-30 2012-04-01 722 Moss Bay Blvd. Kirkland North America 98033 USA (206) 555-3412 3355 2 http://accweb/emmployees/leverling.bmp\n"
},
{
"action": {
"tool": "query-sql",
"toolInput": "SELECT * FROM Employees WHERE HireDate > '2005-01-01' LIMIT 10;",
"log": " I should query the Employees table for employees hired after 2005.\nAction: query-sql\nAction Input: SELECT * FROM Employees WHERE HireDate > '2005-01-01' LIMIT 10;"
},
"observation": "[{\"EmployeeID\":1,\"LastName\":\"Davolio\",\"FirstName\":\"Nancy\",\"Title\":\"Sales Representative\",\"TitleOfCourtesy\":\"Ms.\",\"BirthDate\":\"1968-12-08\",\"HireDate\":\"2012-05-01\",\"Address\":\"507 - 20th Ave. E.Apt. 2A\",\"City\":\"Seattle\",\"Region\":\"North America\",\"PostalCode\":\"98122\",\"Country\":\"USA\",\"HomePhone\":\"(206) 555-9857\",\"Extension\":\"5467\",\"ReportsTo\":2,\"PhotoPath\":\"http://accweb/emmployees/davolio.bmp\"},{\"EmployeeID\":2,\"LastName\":\"Fuller\",\"FirstName\":\"Andrew\",\"Title\":\"Vice President, Sales\",\"TitleOfCourtesy\":\"Dr.\",\"BirthDate\":\"1972-02-19\",\"HireDate\":\"2012-08-14\",\"Address\":\"908 W. Capital Way\",\"City\":\"Tacoma\",\"Region\":\"North America\",\"PostalCode\":\"98401\",\"Country\":\"USA\",\"HomePhone\":\"(206) 555-9482\",\"Extension\":\"3457\",\"ReportsTo\":null,\"PhotoPath\":\"http://accweb/emmployees/fuller.bmp\"},{\"EmployeeID\":3,\"LastName\":\"Leverling\",\"FirstName\":\"Janet\",\"Title\":\"Sales Representative\",\"TitleOfCourtesy\":\"Ms.\",\"BirthDate\":\"1983-08-30\",\"HireDate\":\"2012-04-01\",\"Address\":\"722 Moss Bay Blvd.\",\"City\":\"Kirkland\",\"Region\":\"North America\",\"PostalCode\":\"98033\",\"Country\":\"USA\",\"HomePhone\":\"(206) 555-3412\",\"Extension\":\"3355\",\"ReportsTo\":2,\"PhotoPath\":\"http://accweb/emmployees/leverling.bmp\"},{\"EmployeeID\":4,\"LastName\":\"Peacock\",\"FirstName\":\"Margaret\",\"Title\":\"Sales Manager\",\"TitleOfCourtesy\":\"Ms.\",\"BirthDate\":\"1963-09-19\",\"HireDate\":\"2005-05-03\",\"Address\":\"4110 Old Redmond Rd.\",\"City\":\"Redmond\",\"Region\":\"North America\",\"PostalCode\":\"98052\",\"Country\":\"USA\",\"HomePhone\":\"(206) 555-8122\",\"Extension\":\"5176\",\"ReportsTo\":2,\"PhotoPath\":\"http://accweb/emmployees/peacock.bmp\"},{\"EmployeeID\":5,\"LastName\":\"Buchanan\",\"FirstName\":\"Steven\",\"Title\":\"Sales Manager\",\"TitleOfCourtesy\":\"Mr.\",\"BirthDate\":\"1975-03-04\",\"HireDate\":\"2013-10-17\",\"Address\":\"14 Garrett Hill\",\"City\":\"London\",\"Region\":\"British Isles\",\"PostalCode\":\"SW1 8JR\",\"Country\":\"UK\",\"HomePhone\":\"(71) 555-4848\",\"Extension\":\"3453\",\"ReportsTo\":2,\"PhotoPath\":\"http://accweb/emmployees/buchanan.bmp\"},{\"EmployeeID\":6,\"LastName\":\"Suyama\",\"FirstName\":\"Michael\",\"Title\":\"Sales Representative\",\"TitleOfCourtesy\":\"Mr.\",\"BirthDate\":\"1983-07-02\",\"HireDate\":\"2013-10-17\",\"Address\":\"Coventry House\\nMiner Rd.\",\"City\":\"London\",\"Region\":\"British Isles\",\"PostalCode\":\"EC2 7JR\",\"Country\":\"UK\",\"HomePhone\":\"(71) 555-7773\",\"Extension\":\"428\",\"ReportsTo\":5,\"PhotoPath\":\"http://accweb/emmployees/suyama.bmp\"},{\"EmployeeID\":7,\"LastName\":\"King\",\"FirstName\":\"Robert\",\"Title\":\"Sales Representative\",\"TitleOfCourtesy\":\"Mr.\",\"BirthDate\":\"1980-05-29\",\"HireDate\":\"2014-01-02\",\"Address\":\"Edgeham Hollow\\nWinchester Way\",\"City\":\"London\",\"Region\":\"British Isles\",\"PostalCode\":\"RG1 9SP\",\"Country\":\"UK\",\"HomePhone\":\"(71) 555-5598\",\"Extension\":\"465\",\"ReportsTo\":5,\"PhotoPath\":\"http://accweb/emmployees/king.bmp\"},{\"EmployeeID\":8,\"LastName\":\"Callahan\",\"FirstName\":\"Laura\",\"Title\":\"Inside Sales Coordinator\",\"TitleOfCourtesy\":\"Ms.\",\"BirthDate\":\"1978-01-09\",\"HireDate\":\"2014-03-05\",\"Address\":\"4726 - 11th Ave. N.E.\",\"City\":\"Seattle\",\"Region\":\"North America\",\"PostalCode\":\"98105\",\"Country\":\"USA\",\"HomePhone\":\"(206) 555-1189\",\"Extension\":\"2344\",\"ReportsTo\":2,\"PhotoPath\":\"http://accweb/emmployees/callahan.bmp\"},{\"EmployeeID\":9,\"LastName\":\"Dodsworth\",\"FirstName\":\"Anne\",\"Title\":\"Sales Representative\",\"TitleOfCourtesy\":\"Ms.\",\"BirthDate\":\"1986-01-27\",\"HireDate\":\"2014-11-15\",\"Address\":\"7 Houndstooth Rd.\",\"City\":\"London\",\"Region\":\"British Isles\",\"PostalCode\":\"WG2 7LT\",\"Country\":\"UK\",\"HomePhone\":\"(71) 555-4444\",\"Extension\":\"452\",\"ReportsTo\":5,\"PhotoPath\":\"http://accweb/emmployees/dodsworth.bmp\"}]"
}
]Data in the form of a table from the above observations
| EmployeeID | LastName | FirstName | Title | TitleOfCourtesy | BirthDate | HireDate | Address | City | Region | PostalCode | Country | HomePhone | Extension | ReportsTo | PhotoPath |
|------------|-----------|-----------|-----------------------|-----------------|-------------|------------|-----------------------------|------------|---------------|------------|---------|-----------------|-----------|-----------|-------------------------------------|
| 1 | Davolio | Nancy | Sales Representative | Ms. | 1968-12-08 | 2012-05-01 | 507 - 20th Ave. E.Apt. 2A | Seattle | North America | 98122 | USA | (206) 555-9857 | 5467 | 2 | http://accweb/emmployees/davolio.bmp |
| 2 | Fuller | Andrew | Vice President, Sales| Dr. | 1972-02-19 | 2012-08-14 | 908 W. Capital Way | Tacoma | North America | 98401 | USA | (206) 555-9482 | 3457 | null | http://accweb/emmployees/fuller.bmp |
| 3 | Leverling | Janet | Sales Representative | Ms. | 1983-08-30 | 2012-04-01 | 722 Moss Bay Blvd. | Kirkland | North America | 98033 | USA | (206) 555-3412 | 3355 | 2 | http://accweb/emmployees/leverling.bmp |
| 4 | Peacock | Margaret | Sales Manager | Ms. | 1963-09-19 | 2005-05-03 | 4110 Old Redmond Rd. | Redmond | North America | 98052 | USA | (206) 555-8122 | 5176 | 2 | http://accweb/emmployees/peacock.bmp |
| 5 | Buchanan | Steven | Sales Manager | Mr. | 1975-03-04 | 2013-10-17 | 14 Garrett Hill | London | British Isles | SW1 8JR | UK | (71) 555-4848 | 3453 | 2 | http://accweb/emmployees/buchanan.bmp |
| 6 | Suyama | Michael | Sales Representative | Mr. | 1983-07-02 | 2013-10-17 | Coventry House\nMiner Rd. | London | British Isles | EC2 7JR | UK | (71) 555-7773 | 428 | 5 | http://accweb/emmployees/suyama.bmp |
| 7 | King | Robert | Sales Representative | Mr. | 1980-05-29 | 2014-01-02 | Edgeham Hollow\nWinchester Way| London | British Isles | RG1 9SP | UK | (71) 555-5598 | 465 | 5 | http://accweb/emmployees/king.bmp |
| 8 | Callahan | Laura | Inside Sales Coordinator | Ms. | 1978-01-09 | 2014-03-05 | 4726 - 11th Ave. N.E. | Seattle | North America | 98105 | USA | (206) 555-1189 | 2344 | 2 | http://accweb/emmployees/callahan.bmp |
| 9 | Dodsworth | Anne | Sales Representative | Ms. | 1986-01-27 | 2014-11-15 | 7 Houndstooth Rd. | London | British Isles | WG2 7LT | UK | (71) 555-4444 | 452 | 5 | http://accweb/emmployees/dodsworth.bmp |The SQL Agent works by first generating a SQL query from the user’s natural language question. It then executes the query and returns the results to the user. If the query fails, the SQL Agent will attempt to recover by interpreting the error in a subsequent LLM call and rectifying the issue.
You can access the GUI-based application and view the complete source code by visiting https://github.com/Syed007Hassan/NextJs-Langchain-Agents-SQL/.
If you enjoyed this article, please click on the clap button 👏 and share to help others find it!
