Embarking on the AI Adventure Part 5: Building a Simplified SQL Reporting Agent
In the previous article, we described how can we create a simple RAG to get data from a data file with the help of Langchain and Chroma.
In this article, we will introduce what Agent is and build a simplified SQL Agent That will query a Postgres database and generate an HTML report.
What is an Agent?
The core idea of agents is to use a language model to choose a sequence of actions to take. In agents, a language model is used as a reasoning engine to determine which actions to take and in which order.
ReAct:
Thought: Thinking about what to do and which tool to use
Action: Use needed tools to do an Action
Observation: The result either with an Action Finish(Output) or Stop condition (didn’t get an output and exit the loop)
What is a Tool?
A tool is a set of instructions to do a specific task that provides additional functionality and capabilities to agents. A tool can be functionality to query a database, make a web search, or event to get the sum of two numbers.
You can follow our code: https://github.com/Ahmedhemaz/langChain-Articles
For this article, we will create a simple agent to query the Postgres database and generate a simple HTML page with the data.
I used generated data from ChatGPT with a random generation so we will have some limitations with that demo.
1- create a new branch
git checkout -b agent2- Add Postgres container to our docker-compose file
version: "3"
services:
api:
build:
dockerfile: Dockerfile.dev
context: ./
volumes:
- ./:/usr/app
- /usr/app/node_modules
env_file:
- ./.env
ports:
- "3000:3000"
entrypoint: ./entry-point.sh
lc-postgres:
image: postgres
container_name: "lc-postgres"
env_file:
- .env
environment:
POSTGRES_DB: ${DATABASE_NAME}
POSTGRES_USER: ${DATABASE_USER}
POSTGRES_PASSWORD: ${DATABASE_PASSWORD}
ports:
- "5432:5432"
volumes:
- postgresdata:/var/lib/postgresql/data
extra_hosts:
- "host.docker.internal:host-gateway"
volumes:
postgresdata: {}3- install Typeorm and pg packages
npm install typeorm pg4- create db connection
import { DataSource } from "typeorm";
export const dataSource = new DataSource({
type: "postgres",
host: process.env.DATABASE_HOST, // Your PostgreSQL host
port: Number(process.env.DATABASE_PORT), // Your PostgreSQL port (default is 5432)
username: process.env.DATABASE_USER, // Your PostgreSQL username
password: process.env.DATABASE_PASSWORD, // Your PostgreSQL password
database: process.env.DATABASE_NAME, // Your PostgreSQL database name
schema: "public",
poolSize: 10,
});5- create agent
import { ChatOpenAI } from "@langchain/openai";
import { ChatPromptTemplate, MessagesPlaceholder } from "langchain/prompts";
import { HumanMessage } from "@langchain/core/messages";
import { AgentExecutor, createOpenAIToolsAgent } from "langchain/agents";
import { SqlToolkit } from "langchain/agents/toolkits/sql";
import { SqlDatabase } from "langchain/sql_db";
import { dataSource } from "../db/db-connection.js";
export interface Message {
message: string;
}
const model = new ChatOpenAI({
openAIApiKey: process.env.OPENAI_KEY,
modelName: "gpt-3.5-turbo",
temperature: 0,
});
export const sqlAgentChat = async (params: Message) => {
const prompt = ChatPromptTemplate.fromMessages([
["system", "You are an AI assistant who has access to postgres database and can generate some reports!"],
new MessagesPlaceholder("messages"),
new MessagesPlaceholder("agent_scratchpad"),
]);
const db = await SqlDatabase.fromDataSourceParams({
appDataSource: dataSource,
});
const toolkit = new SqlToolkit(db, model);
toolkit.dialect = "postgres";
const tools: any = [];
tools.push(...toolkit.getTools());
const agent = await createOpenAIToolsAgent({
llm: model,
tools,
prompt,
});
const agentExecutor = new AgentExecutor({ agent, tools, verbose: true });
const ans = await agentExecutor.invoke({
messages: [new HumanMessage(params.message)],
});
return ans;
};6- create an endpoint to execute that agent:
import express from "express";
import dotenv from "dotenv";
import { dataSource } from "./db/db-connection.js";
import { sqlAgentChat } from "./agents/sql-agent.js";
dotenv.config();
const app: express.Express = express();
const port: string | number = process.env.PORT || 3000;
app.use(express.json());
app.listen(port, async () => {
await dataSource.initialize();
console.log(`Server is listening on port ${port}`);
});
app.post("/agent-sql-chat", async (req, res, next) => {
try {
const result = await sqlAgentChat(req.body);
return res.status(200).send({
result,
});
} catch (error) {
console.log(error);
}
});7- let's try to get the number of orders:
As we can see we have 5000 orders.
Let’s try another query and take a look at the intermediate results to see the actions and observations we talked about earlier :
Let's query our Postgres database to make sure of that result:
Let’s take a closer look to see Actions and Observations and how the agent corrects itself.
In this step, it queried using total_price and the query failed as we can see from the observations
After that, it runs a step to get the table info
Then it runs the correct query and gets the result
8- let’s create an agent to generate an HTML report from the SQL output
import { Tool, ToolParams } from "@langchain/core/tools";
import { promises as fsPromises } from "fs";
export class HtmlGeneratorTool extends Tool {
static lc_name() {
return "HtmlGeneratorTool";
}
name = "html-generator-tool";
description = "Use this tool to generate styled html with the given data in a proper way.";
constructor(config?: ToolParams) {
super(config);
}
async _call(htmlContent: string) {
try {
await fsPromises.writeFile("./report.html", htmlContent);
return "created";
} catch (err) {
console.error("Error writing file:", err);
} finally {
return "created";
}
}
}9- let’s add the tool to our agent’s tools
import { ChatOpenAI } from "@langchain/openai";
import { ChatPromptTemplate, MessagesPlaceholder } from "langchain/prompts";
import { HumanMessage } from "@langchain/core/messages";
import { AgentExecutor, createOpenAIToolsAgent } from "langchain/agents";
import { SqlToolkit } from "langchain/agents/toolkits/sql";
import { SqlDatabase } from "langchain/sql_db";
import { dataSource } from "../db/db-connection.js";
import { HtmlGeneratorTool } from "../tools/html.tool.js";
export interface Message {
message: string;
}
const model = new ChatOpenAI({
openAIApiKey: process.env.OPENAI_KEY,
modelName: "gpt-3.5-turbo",
temperature: 0,
});
export const sqlAgentChat = async (params: Message) => {
const prompt = ChatPromptTemplate.fromMessages([
["system", "You are an AI assistant who has access to postgres database and can generate some reports!"],
new MessagesPlaceholder("messages"),
new MessagesPlaceholder("agent_scratchpad"),
]);
const db = await SqlDatabase.fromDataSourceParams({
appDataSource: dataSource,
});
const toolkit = new SqlToolkit(db, model);
toolkit.dialect = "postgres";
const tools: any = [];
tools.push(...toolkit.getTools());
tools.push(new HtmlGeneratorTool());
const agent = await createOpenAIToolsAgent({
llm: model,
tools,
prompt,
});
const agentExecutor = new AgentExecutor({ agent, tools, verbose: true, returnIntermediateSteps: true });
const ans = await agentExecutor.invoke({
messages: [new HumanMessage(params.message)],
});
return ans;
};10- let’s generate an HTML report to get orders count grouped by order status:
The generated HTML:
The raw query result
conclusion:
In summary, we’ve built a smart SQL Reporting Agent that fetches data from Postgres databases and creates HTML reports. Through careful integration and refinement, we’ve created a versatile tool for efficient data analysis and presentation. With this accomplishment, we’re ready to explore new AI-driven reporting and analytics possibilities.
