LINE Bot MCP Server

ai_query_mssql

Convert natural language requests into safe read-only MSSQL SELECT queries, validate them against schema and table restrictions, execute, and return results for LINE Bot integration.

Instructions

Generate a safe read-only MSSQL SELECT from instruction via Gemini, validate, execute, and return rows.

Input Schema

TableJSON Schema

Name	Required	Description
`instruction`	Yes	Natural language request to query MSSQL (Thai/English)
`maxRows`	No	Max rows to return
`allowedSchemas`	No	Optional whitelist of schemas (e.g., ['dbo','sales'])
`allowedTables`	No	Optional whitelist of fully qualified tables (e.g., ['dbo.customer_dummy'])

Implementation Reference

src/tools/aiQueryMssql.ts:48-180 (handler)

Main handler: Uses Gemini to generate safe read-only MSSQL SELECT query from natural language instruction, validates schema/table allowlists and read-only nature, executes query, limits rows, and returns structured results.

async ({ instruction, maxRows, allowedSchemas, allowedTables }) => {
  const apiKey = process.env.GEMINI_API_KEY || process.env.GOOGLE_API_KEY;
  if (!apiKey)
    return createErrorResponse(
      "Please set GEMINI_API_KEY (or GOOGLE_API_KEY)",
    );

  // Optionally fetch top tables/columns to guide the LLM
  let schemaSnippet = "";
  try {
    const tablesRes = await queryReadOnly(
      "SELECT TOP 50 s.name AS schema_name, t.name AS table_name FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.is_ms_shipped = 0 ORDER BY s.name, t.name",
    );
    const tables = tablesRes.rows || [];
    schemaSnippet +=
      `Tables (top 50)\n` +
      tables.map(r => `- ${r.schema_name}.${r.table_name}`).join("\n");
  } catch {
    // ignore
  }

  function buildPrompt() {
    return (
      `You are a SQL assistant for Microsoft SQL Server.\n` +
      `Task: Convert the user's instruction into ONE safe SELECT/WITH query.\n` +
      `Rules:\n` +
      `- OUTPUT STRICT JSON only: {"sql":"...","params":{},"limit":N}. No markdown, no explanation.\n` +
      `- Only SELECT or WITH; single statement; NO DML/DDL (INSERT/UPDATE/DELETE/ALTER/DROP).\n` +
      `- Use fully qualified table names if possible (e.g., dbo.table).\n` +
      `- If schemas/tables are provided as allowlists, only use those.\n` +
      `- Limit rows to ${maxRows} (or fewer) using TOP for MSSQL if not specified.\n` +
      (allowedSchemas?.length
        ? `Allowed schemas: ${allowedSchemas.join(", ")}\n`
        : ``) +
      (allowedTables?.length
        ? `Allowed tables: ${allowedTables.join(", ")}\n`
        : ``) +
      (schemaSnippet ? `\nSchema snippet:\n${schemaSnippet}\n` : ``) +
      `\nInstruction: ${instruction}`
    );
  }

  async function callGemini(): Promise<string> {
    const endpoint = `https://generativelanguage.googleapis.com/v1/models/${encodeURIComponent(
      "gemini-2.0-flash",
    )}:generateContent`;
    const body = {
      contents: [{ role: "user", parts: [{ text: buildPrompt() }] }],
    };
    const res = await fetch(endpoint, {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        "X-goog-api-key": apiKey,
      },
      body: JSON.stringify(body),
    });
    if (!res.ok) throw new Error(`Gemini HTTP ${res.status}`);
    const data = (await res.json()) as GenerateContentResponse;
    const raw =
      data?.candidates?.[0]?.content?.parts
        ?.map(p => p.text || "")
        .join("") || "";
    if (!raw) throw new Error("Empty response from model");
    const fence = raw.match(/```(?:json)?\s*([\s\S]*?)```/i);
    let jsonText = fence ? fence[1].trim() : raw;
    if (!fence) {
      const s = raw.indexOf("{");
      const e = raw.lastIndexOf("}");
      if (s !== -1 && e !== -1 && e > s) jsonText = raw.slice(s, e + 1);
    }
    return jsonText;
  }

  try {
    const jsonText = await callGemini();
    let plan: {
      sql?: string;
      params?: Record<string, any>;
      limit?: number;
    };
    try {
      plan = JSON.parse(jsonText);
    } catch (e: any) {
      return createErrorResponse(
        `Failed to parse SQL plan JSON: ${e?.message || e}`,
      );
    }
    if (!plan?.sql)
      return createErrorResponse("Model did not provide sql");
    let sql = plan.sql.trim();
    // Basic safety
    if (!isReadOnlySelect(sql))
      return createErrorResponse(
        "Only read-only SELECT/WITH queries are allowed",
      );
    if (/[;]/.test(sql))
      return createErrorResponse("Multiple statements are not allowed");
    // Whitelist checks
    if (allowedSchemas?.length) {
      const ok = allowedSchemas.some(s =>
        new RegExp(`\\b${s}\\.`, "i").test(sql),
      );
      if (!ok)
        return createErrorResponse("Query references disallowed schema");
    }
    if (allowedTables?.length) {
      const ok = allowedTables.some(t =>
        new RegExp(`\\b${t}\\b`, "i").test(sql),
      );
      if (!ok)
        return createErrorResponse("Query references disallowed table");
    }
    // Enforce TOP limit for MSSQL if missing
    if (/^select\s+/i.test(sql) && !/^select\s+top\s+\d+/i.test(sql)) {
      sql = sql.replace(/^select\s+/i, `SELECT TOP ${maxRows} `);
    }

    const result = await queryReadOnly(sql, plan.params || {});
    const rows = result.rows?.slice(0, maxRows) || [];
    return createSuccessResponse({
      sql,
      params: plan.params || {},
      columns: result.columns,
      rowCount: result.rows?.length || 0,
      rows,
    });
  } catch (e: any) {
    return createErrorResponse(
      `ai_query_mssql failed: ${e?.message || e}`,
    );
  }
},

src/tools/aiQueryMssql.ts:17-47 (schema)

Input schema definitions using Zod for instruction (string), maxRows (number, default 10), allowedSchemas and allowedTables (optional string arrays).

const instructionSchema = z
  .string()
  .min(1)
  .describe("Natural language request to query MSSQL (Thai/English)");
const maxRowsSchema = z
  .number()
  .int()
  .min(1)
  .max(200)
  .default(10)
  .describe("Max rows to return");
const allowedSchemasSchema = z
  .array(z.string())
  .optional()
  .describe("Optional whitelist of schemas (e.g., ['dbo','sales'])");
const allowedTablesSchema = z
  .array(z.string())
  .optional()
  .describe(
    "Optional whitelist of fully qualified tables (e.g., ['dbo.customer_dummy'])",
  );

server.tool(
  "ai_query_mssql",
  "Generate a safe read-only MSSQL SELECT from instruction via Gemini, validate, execute, and return rows.",
  {
    instruction: instructionSchema,
    maxRows: maxRowsSchema,
    allowedSchemas: allowedSchemasSchema,
    allowedTables: allowedTablesSchema,
  },

src/tools/aiQueryMssql.ts:15-182 (registration)

The AiQueryMssql class's register method calls server.tool to register the tool with its name, description, input schema, and handler function.

export default class AiQueryMssql extends AbstractTool {
  register(server: McpServer) {
    const instructionSchema = z
      .string()
      .min(1)
      .describe("Natural language request to query MSSQL (Thai/English)");
    const maxRowsSchema = z
      .number()
      .int()
      .min(1)
      .max(200)
      .default(10)
      .describe("Max rows to return");
    const allowedSchemasSchema = z
      .array(z.string())
      .optional()
      .describe("Optional whitelist of schemas (e.g., ['dbo','sales'])");
    const allowedTablesSchema = z
      .array(z.string())
      .optional()
      .describe(
        "Optional whitelist of fully qualified tables (e.g., ['dbo.customer_dummy'])",
      );

    server.tool(
      "ai_query_mssql",
      "Generate a safe read-only MSSQL SELECT from instruction via Gemini, validate, execute, and return rows.",
      {
        instruction: instructionSchema,
        maxRows: maxRowsSchema,
        allowedSchemas: allowedSchemasSchema,
        allowedTables: allowedTablesSchema,
      },
      async ({ instruction, maxRows, allowedSchemas, allowedTables }) => {
        const apiKey = process.env.GEMINI_API_KEY || process.env.GOOGLE_API_KEY;
        if (!apiKey)
          return createErrorResponse(
            "Please set GEMINI_API_KEY (or GOOGLE_API_KEY)",
          );

        // Optionally fetch top tables/columns to guide the LLM
        let schemaSnippet = "";
        try {
          const tablesRes = await queryReadOnly(
            "SELECT TOP 50 s.name AS schema_name, t.name AS table_name FROM sys.tables t JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.is_ms_shipped = 0 ORDER BY s.name, t.name",
          );
          const tables = tablesRes.rows || [];
          schemaSnippet +=
            `Tables (top 50)\n` +
            tables.map(r => `- ${r.schema_name}.${r.table_name}`).join("\n");
        } catch {
          // ignore
        }

        function buildPrompt() {
          return (
            `You are a SQL assistant for Microsoft SQL Server.\n` +
            `Task: Convert the user's instruction into ONE safe SELECT/WITH query.\n` +
            `Rules:\n` +
            `- OUTPUT STRICT JSON only: {"sql":"...","params":{},"limit":N}. No markdown, no explanation.\n` +
            `- Only SELECT or WITH; single statement; NO DML/DDL (INSERT/UPDATE/DELETE/ALTER/DROP).\n` +
            `- Use fully qualified table names if possible (e.g., dbo.table).\n` +
            `- If schemas/tables are provided as allowlists, only use those.\n` +
            `- Limit rows to ${maxRows} (or fewer) using TOP for MSSQL if not specified.\n` +
            (allowedSchemas?.length
              ? `Allowed schemas: ${allowedSchemas.join(", ")}\n`
              : ``) +
            (allowedTables?.length
              ? `Allowed tables: ${allowedTables.join(", ")}\n`
              : ``) +
            (schemaSnippet ? `\nSchema snippet:\n${schemaSnippet}\n` : ``) +
            `\nInstruction: ${instruction}`
          );
        }

        async function callGemini(): Promise<string> {
          const endpoint = `https://generativelanguage.googleapis.com/v1/models/${encodeURIComponent(
            "gemini-2.0-flash",
          )}:generateContent`;
          const body = {
            contents: [{ role: "user", parts: [{ text: buildPrompt() }] }],
          };
          const res = await fetch(endpoint, {
            method: "POST",
            headers: {
              "Content-Type": "application/json",
              "X-goog-api-key": apiKey,
            },
            body: JSON.stringify(body),
          });
          if (!res.ok) throw new Error(`Gemini HTTP ${res.status}`);
          const data = (await res.json()) as GenerateContentResponse;
          const raw =
            data?.candidates?.[0]?.content?.parts
              ?.map(p => p.text || "")
              .join("") || "";
          if (!raw) throw new Error("Empty response from model");
          const fence = raw.match(/```(?:json)?\s*([\s\S]*?)```/i);
          let jsonText = fence ? fence[1].trim() : raw;
          if (!fence) {
            const s = raw.indexOf("{");
            const e = raw.lastIndexOf("}");
            if (s !== -1 && e !== -1 && e > s) jsonText = raw.slice(s, e + 1);
          }
          return jsonText;
        }

        try {
          const jsonText = await callGemini();
          let plan: {
            sql?: string;
            params?: Record<string, any>;
            limit?: number;
          };
          try {
            plan = JSON.parse(jsonText);
          } catch (e: any) {
            return createErrorResponse(
              `Failed to parse SQL plan JSON: ${e?.message || e}`,
            );
          }
          if (!plan?.sql)
            return createErrorResponse("Model did not provide sql");
          let sql = plan.sql.trim();
          // Basic safety
          if (!isReadOnlySelect(sql))
            return createErrorResponse(
              "Only read-only SELECT/WITH queries are allowed",
            );
          if (/[;]/.test(sql))
            return createErrorResponse("Multiple statements are not allowed");
          // Whitelist checks
          if (allowedSchemas?.length) {
            const ok = allowedSchemas.some(s =>
              new RegExp(`\\b${s}\\.`, "i").test(sql),
            );
            if (!ok)
              return createErrorResponse("Query references disallowed schema");
          }
          if (allowedTables?.length) {
            const ok = allowedTables.some(t =>
              new RegExp(`\\b${t}\\b`, "i").test(sql),
            );
            if (!ok)
              return createErrorResponse("Query references disallowed table");
          }
          // Enforce TOP limit for MSSQL if missing
          if (/^select\s+/i.test(sql) && !/^select\s+top\s+\d+/i.test(sql)) {
            sql = sql.replace(/^select\s+/i, `SELECT TOP ${maxRows} `);
          }

          const result = await queryReadOnly(sql, plan.params || {});
          const rows = result.rows?.slice(0, maxRows) || [];
          return createSuccessResponse({
            sql,
            params: plan.params || {},
            columns: result.columns,
            rowCount: result.rows?.length || 0,
            rows,
          });
        } catch (e: any) {
          return createErrorResponse(
            `ai_query_mssql failed: ${e?.message || e}`,
          );
        }
      },
    );
  }

src/index.ts:41-78 (registration)

Imports AiQueryMssql and instantiates it to call register(server), thereby registering the tool on the main MCP server instance.

import AiQueryMssql from "./tools/aiQueryMssql.js";

// Load environment variables from .env if present (no external deps)
loadEnvFromDotenv();

const server = new McpServer({
  name: "line-bot",
  version: LINE_BOT_MCP_SERVER_VERSION,
});

const channelAccessToken = process.env.CHANNEL_ACCESS_TOKEN || "";
const destinationId = process.env.DESTINATION_USER_ID || "";

const messagingApiClient = new line.messagingApi.MessagingApiClient({
  channelAccessToken: channelAccessToken,
  defaultHeaders: {
    "User-Agent": USER_AGENT,
  },
});

new PushTextMessage(messagingApiClient, destinationId).register(server);
new PushFlexMessage(messagingApiClient, destinationId).register(server);
new BroadcastTextMessage(messagingApiClient).register(server);
new BroadcastFlexMessage(messagingApiClient).register(server);
new GetProfile(messagingApiClient, destinationId).register(server);
new GetMessageQuota(messagingApiClient).register(server);
new GetRichMenuList(messagingApiClient).register(server);
new DeleteRichMenu(messagingApiClient).register(server);
new SetRichMenuDefault(messagingApiClient).register(server);
new CancelRichMenuDefault(messagingApiClient).register(server);
new PushGeminiText(messagingApiClient, destinationId).register(server);
new PushGeminiFlex(messagingApiClient, destinationId).register(server);
new GeminiCommand(messagingApiClient, destinationId).register(server);
new PushMessages(messagingApiClient, destinationId).register(server);
new BroadcastMessages(messagingApiClient).register(server);
new QueryMssql().register(server);
new ExportMssqlKnowledge().register(server);
new AiQueryMssql().register(server);

Tool Definition Quality

A4.4/5.0

Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries the full burden and does well by disclosing key behavioral traits: it's safe and read-only, uses Gemini for query generation, includes validation, executes the query, and returns rows. However, it lacks details on error handling, rate limits, or specific validation rules, leaving some gaps in behavioral context.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is highly concise and front-loaded in a single sentence that efficiently covers purpose, process, and constraints. Every word earns its place, with no redundant or vague phrasing, making it easy to parse quickly.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the tool's complexity (natural language to SQL conversion with safety validation) and lack of annotations and output schema, the description is reasonably complete. It covers the core functionality and safety aspects but could benefit from mentioning output format or error cases. However, it adequately addresses the main use case without being overly verbose.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so the schema fully documents all parameters. The description adds minimal semantic value beyond the schema, only implying that 'instruction' is natural language and the tool is read-only. It doesn't provide additional context on parameter interactions or usage examples, meeting the baseline for high schema coverage.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the tool's purpose with specific verbs ('generate', 'validate', 'execute', 'return') and resource ('MSSQL SELECT'), and distinguishes it from sibling 'query_mssql' by emphasizing safe read-only operation via Gemini. It explicitly mentions the natural language input and the read-only constraint.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines5/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides explicit usage guidance: it specifies when to use this tool (for safe read-only queries from natural language instructions via Gemini) and implicitly when not to use it (for non-SELECT operations or direct SQL execution, as suggested by the sibling 'query_mssql'). It clearly differentiates from alternatives by highlighting the Gemini-mediated approach.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Install Server

Other Tools

Latest Blog Posts

Lightport: Open-Sourcing Glama's AI Gateway
By punkpeye on April 27, 2026.
open source
OpenAI
Tool Definition Quality Score (TDQS)
By punkpeye on April 3, 2026.
mcp
The Hackers Who Tracked My Sleep Cycle
By punkpeye on March 26, 2026.
security

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/tndfame/mcp_management'

If you have feedback or need assistance with the MCP directory API, please join our Discord server