Query Agent

An AI-powered database query agent using Google Generative AI (Gemini) with Socket.IO and Express integration. Features a dedicated /query-agent namespace for Socket.IO and HTTP routes that integrate seamlessly with your existing Express application.

Features

• 🤖 AI-powered natural language to SQL conversion
• 🔒 Security-first approach (only SELECT queries allowed)
• 🗄️ Support for multiple database types (MySQL, PostgreSQL, SQLite, etc.)
• 📊 Intelligent query result formatting
• 🔄 Iterative query refinement
• 📝 HTML-formatted responses
• 🔌 Socket.IO with dedicated namespace (/query-agent)
• 🛤️ Express POST route (/query-agent)
• 📡 Real-time status updates via WebSocket
• 🔧 Flexible database integration (Sequelize, Prisma, custom)
• 🎯 No conflicts with existing Express apps - integrates seamlessly

Installation

npm install query-agent

Module System Support

This package supports both ES Modules (ESM) and CommonJS (CJS), with full TypeScript support:

ESM Usage (Recommended)

import queryAgent from "query-agent";
// or
import { init, setDatabaseQueryFunction } from "query-agent";

CommonJS Usage

const queryAgent = require("query-agent");
// or
const { init, setDatabaseQueryFunction } = require("query-agent");

TypeScript Usage

import queryAgent, { QueryAgentOptions } from "query-agent";

const options: QueryAgentOptions = {
  executeSQLQuery: async (query: string) => {
    // Your database query execution logic
    return await yourDatabase.execute(query);
  },
  corsOptions: {
    origin: "*",
    methods: ["GET", "POST"],
  },
};

const { io, queryAgentNamespace } = queryAgent(serverOrIo, options);

The package automatically detects your module system and provides the appropriate format.

Quick Start

1. Basic Integration with Existing Express App

import express from "express";
import { createServer } from "http";
import { init, setDatabaseQueryFunction } from "query-agent";

// Your existing Express app
const app = express();
const server = createServer(app);

app.use(express.json());

// Set up your database query function
const myQueryFunction = async (query) => {
  // Your database logic here (Sequelize, Prisma, etc.)
  return await yourDatabase.query(query);
};

setDatabaseQueryFunction(myQueryFunction);

// Initialize Query Agent with your existing app and server
const { io, queryAgentNamespace } = init(app, server);

// Your existing routes continue to work
app.get("/", (req, res) => {
  res.json({ message: "My app with Query Agent!" });
});

// Start your server
server.listen(3000, () => {
  console.log("Server running on port 3000");
});

2. Environment Variables

Create a .env file:

GOOGLE_API_KEY=your_google_api_key_here
PORT=3000

API Endpoints

Query Agent adds the following endpoints to your existing Express app:

HTTP POST Route

• URL: POST /query-agent
• Purpose: HTTP-based queries

Socket.IO Namespace

• Namespace: /query-agent
• Socket Path: /query-agent (customizable)
• Purpose: Real-time WebSocket communication
• Events: query, result, error, status

Health Check

• URL: GET /query-agent/health
• Purpose: Server health monitoring

API Reference

init(app, server, options)

Initializes Query Agent with your existing Express app and HTTP server.

Parameters:

• app (Express): Your existing Express application
• server (HTTP Server): Your HTTP server instance
• options (object, optional): Configuration options

Options:

{
  corsOptions: {
    origin: "*",
    methods: ["GET", "POST"]
  },
  socketPath: "/query-agent",      // Socket.IO path
  routePrefix: "/query-agent"      // HTTP route prefix
}

Returns:

{
  io, // Socket.IO server instance
    queryAgentNamespace; // Socket.IO namespace instance
}

setDatabaseQueryFunction(queryFunction)

Sets the database query function before initializing.

Parameters:

• queryFunction (async function): Function that executes SQL queries

Example:

const myQueryFunction = async (query) => {
  const results = await sequelize.query(query);
  return results[0]; // Return the data part
};

setDatabaseQueryFunction(myQueryFunction);

runAIControlledWorkflow(userQuery, dbType, otherDetails)

Core AI workflow function (can be used directly).

Parameters:

• userQuery (string): Natural language query
• dbType (string, optional): Database type (default: "MySQL")
• otherDetails (string, optional): Additional context

Returns:

{
  success: boolean,
  data: string,      // HTML formatted response
  iterations: number,
  query: string,
  dbType: string,
  error?: string     // Only if success is false
}

Usage Examples

1. HTTP POST Request

const response = await fetch("http://localhost:3000/query-agent", {
  method: "POST",
  headers: { "Content-Type": "application/json" },
  body: JSON.stringify({
    userQuery: "Show me all users from the users table",
    dbType: "MySQL",
    otherDetails: "Production database",
  }),
});

const result = await response.text(); // HTML response

2. Socket.IO Client (Node.js)

import { io } from "socket.io-client";

const socket = io("http://localhost:3000/query-agent");

socket.on("connect", () => {
  console.log("Connected to Query Agent");

  // Send query
  socket.emit("query", {
    userQuery: "How many users are in the system?",
    dbType: "MySQL",
  });
});

// Listen for real-time updates
socket.on("status", (data) => console.log("Status:", data.message));
socket.on("result", (data) => console.log("Result:", data.data));
socket.on("error", (data) => console.error("Error:", data.error));

3. TypeScript Integration

import { createServer } from "http";
import { Server } from "socket.io";
import queryAgent, { QueryAgentOptions } from "query-agent";

// Create HTTP server
const server = createServer();

// Create Socket.IO server
const io = new Server(server, {
  cors: {
    origin: "*",
    methods: ["GET", "POST"],
  },
});

// Define your SQL execution function with proper typing
const executeSQLQuery = async (query: string): Promise<any> => {
  // Your database query execution logic here
  console.log("Executing SQL query:", query);
  // Example: return await yourDatabase.execute(query);
  return { result: "query executed successfully" };
};

// Initialize Query Agent with proper TypeScript types
const queryAgentOptions: QueryAgentOptions = {
  executeSQLQuery,
  corsOptions: {
    origin: "*",
    methods: ["GET", "POST"],
  },
};

const { io: queryAgentIo, queryAgentNamespace } = queryAgent(
  io,
  queryAgentOptions
);

// Start the server
server.listen(3000, () => {
  console.log("Server with Query Agent running on port 3000");
});

// Example of using the returned objects with proper typing
queryAgentNamespace.on("connection", (socket) => {
  console.log("Client connected to query agent namespace");
});

4. Browser Integration

<!DOCTYPE html>
<html>
  <head>
    <script src="https://cdn.socket.io/4.7.5/socket.io.min.js"></script>
  </head>
  <body>
    <input type="text" id="queryInput" placeholder="Enter your query..." />
    <button onclick="sendQuery()">Send Query</button>
    <div id="result"></div>

    <script>
      const socket = io("http://localhost:3000/query-agent");

      socket.on("result", (data) => {
        document.getElementById("result").innerHTML = data.data;
      });

      function sendQuery() {
        const query = document.getElementById("queryInput").value;
        socket.emit("query", {
          userQuery: query,
          dbType: "MySQL",
        });
      }
    </script>
  </body>
</html>

Database Integration Examples

Sequelize Integration

import express from "express";
import { createServer } from "http";
import { Sequelize } from "sequelize";
import { init, setDatabaseQueryFunction } from "query-agent";

const app = express();
const server = createServer(app);
app.use(express.json());

const sequelize = new Sequelize(/* your config */);

const sequelizeQueryFunction = async (query) => {
  const [results] = await sequelize.query(query);
  return results;
};

setDatabaseQueryFunction(sequelizeQueryFunction);
init(app, server);

server.listen(3000);

Prisma Integration

import express from "express";
import { createServer } from "http";
import { PrismaClient } from "@prisma/client";
import { init, setDatabaseQueryFunction } from "query-agent";

const app = express();
const server = createServer(app);
app.use(express.json());

const prisma = new PrismaClient();

const prismaQueryFunction = async (query) => {
  return await prisma.$queryRawUnsafe(query);
};

setDatabaseQueryFunction(prismaQueryFunction);
init(app, server);

server.listen(3000);

Custom Database Integration

import express from "express";
import { createServer } from "http";
import { init, setDatabaseQueryFunction } from "query-agent";

const app = express();
const server = createServer(app);
app.use(express.json());

const customQueryFunction = async (query) => {
  // Your custom database logic
  return await yourCustomDB.execute(query);
};

setDatabaseQueryFunction(customQueryFunction);
init(app, server);

server.listen(3000);

Configuration

Environment Variables

| Variable | Description | Default | | ---------------- | ---------------------------- | ------------- | | GOOGLE_API_KEY | Google Generative AI API Key | Required | | PORT | Server port | 3000 | | NODE_ENV | Environment mode | development |

Socket.IO Events

Client → Server

• query: Send a query to the AI agent

Server → Client

• result: Query completed successfully
• error: Query failed with error
• status: Real-time status updates
• connect: Connection established
• disconnect: Connection closed

Custom Configuration

const { io, queryAgentNamespace } = init(app, server, {
  corsOptions: {
    origin: ["http://localhost:3000", "https://yourdomain.com"],
    methods: ["GET", "POST"],
    credentials: true,
  },
  socketPath: "/custom-socket-path",
  routePrefix: "/api/query-agent",
});

Development Scripts

# Build for both ESM and CJS
npm run build

# Run tests
npm test

# Run example server
npm run example

# Run Sequelize example
npm run example:sequelize

# Run Prisma example
npm run example:prisma

# Test client examples
npm run client:test

Build Process

The package uses a dual build system to support both ESM and CJS:

• ESM Build: dist/index.js - For modern Node.js applications
• CJS Build: dist/index.cjs - For CommonJS applications

The build process automatically:

1. Bundles dependencies using esbuild
2. Creates optimized builds for both module systems
3. Ensures compatibility across different Node.js versions

Architecture

┌─────────────────────────────────────────────────────────────────┐
│                    Your Express App                            │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐ │
│  │   Your Routes   │  │  Your Middleware│  │  Your Features  │ │
│  └─────────────────┘  └─────────────────┘  └─────────────────┘ │
│                              │                                  │
│  ┌─────────────────────────────────────────────────────────────┐ │
│  │              Query Agent Integration                        │ │
│  │  ┌─────────────────┐         ┌─────────────────┐          │ │
│  │  │  Express POST   │         │  Socket.IO      │          │ │
│  │  │  /query-agent   │         │  /query-agent   │          │ │
│  │  └─────────────────┘         └─────────────────┘          │ │
│  │                    ▼                                       │ │
│  │              ┌─────────────────┐                           │ │
│  │              │  AI Workflow    │                           │ │
│  │              │  (Gemini API)   │                           │ │
│  │              └─────────────────┘                           │ │
│  └─────────────────────────────────────────────────────────────┘ │
│                         ▼                                        │
│                ┌─────────────────┐                               │
│                │  Your Database  │                               │
│                │  Query Function │                               │
│                └─────────────────┘                               │
└─────────────────────────────────────────────────────────────────┘
                          ▼
                ┌─────────────────┐
                │   Your Database │
                │ (MySQL/Postgres │
                │  /SQLite/etc.)  │
                └─────────────────┘

Security Features

• ✅ Only SELECT queries allowed
• ✅ Dangerous SQL keywords blocked
• ✅ Input validation and sanitization
• ✅ Isolated namespace (/query-agent)
• ✅ Environment-based configuration
• ✅ Custom database query function control
• ✅ No conflicts with existing routes

Error Handling

The package includes comprehensive error handling for both HTTP and Socket.IO:

// HTTP Error Response
{
  success: false,
  error: "Error message",
  query: "original query",
  dbType: "MySQL"
}

// Socket.IO Error Event
socket.on('error', (data) => {
  console.error('Query failed:', data.error);
});

Migration Guide

If you're using an existing Express app, integration is simple:

Before (your existing app):

import express from "express";
import { createServer } from "http";

const app = express();
const server = createServer(app);

app.use(express.json());
app.get("/", (req, res) => res.json({ message: "Hello" }));

server.listen(3000);

After (with Query Agent):

import express from "express";
import { createServer } from "http";
import { init, setDatabaseQueryFunction } from "query-agent";

const app = express();
const server = createServer(app);

app.use(express.json());

// Your existing routes stay the same
app.get("/", (req, res) => res.json({ message: "Hello" }));

// Add Query Agent
setDatabaseQueryFunction(yourQueryFunction);
init(app, server);

server.listen(3000);

License

MIT License - see LICENSE file for details

Support

• 📧 Email: [[email protected]]
• 🐛 Issues: GitHub Issues
• 📖 Documentation: GitHub Wiki

Changelog

Version 1.0.0

• Initial release with init method integration
• Dedicated /query-agent namespace
• Flexible database integration
• Real-time status updates
• Seamless Express app integration
• Comprehensive examples and documentation