#!/usr/bin/env node
import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import {
CallToolRequestSchema,
ListToolsRequestSchema,
} from "@modelcontextprotocol/sdk/types.js";
import sql from "mssql";
import fs from "fs";
import path from "path";
import { fileURLToPath } from "url";
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
// Load configuration
function loadConfig() {
const configPath = process.env.DB_CONFIG_PATH || path.join(__dirname, "config.json");
if (!fs.existsSync(configPath)) {
throw new Error(`Configuration file not found at ${configPath}. Please create a config.json file or set DB_CONFIG_PATH environment variable.`);
}
const configData = fs.readFileSync(configPath, "utf-8");
return JSON.parse(configData);
}
// Database connection pool
let pool = null;
async function getConnection() {
if (!pool) {
const config = loadConfig();
pool = await sql.connect(config.database);
}
return pool;
}
// Create MCP server
const server = new Server(
{
name: "boxtalk-data-mcp",
version: "1.0.0",
},
{
capabilities: {
tools: {},
},
}
);
// List available tools
server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
{
name: "get_table_data",
description: "Get paginated data from a SQL Server table. Returns a specified page of records with pagination enforced.",
inputSchema: {
type: "object",
properties: {
table: {
type: "string",
description: "The name of the table to query (can include schema, e.g., 'dbo.Users')",
},
page: {
type: "number",
description: "Page number (1-based). Defaults to 1.",
default: 1,
},
pageSize: {
type: "number",
description: "Number of records per page. Must be between 1 and 1000. Defaults to 100.",
default: 100,
},
orderBy: {
type: "string",
description: "Column name to order by. Defaults to first column if not specified.",
},
},
required: ["table"],
},
},
{
name: "get_table_count",
description: "Get the total count of records in a SQL Server table.",
inputSchema: {
type: "object",
properties: {
table: {
type: "string",
description: "The name of the table to count (can include schema, e.g., 'dbo.Users')",
},
},
required: ["table"],
},
},
{
name: "get_table_structure",
description: "Get the structure/schema of a SQL Server table including column names, data types, and constraints.",
inputSchema: {
type: "object",
properties: {
table: {
type: "string",
description: "The name of the table (can include schema, e.g., 'dbo.Users')",
},
},
required: ["table"],
},
},
{
name: "query_data",
description: "Execute a SELECT query on the SQL Server database with enforced pagination. Only SELECT queries are allowed - DDL and DML operations (CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, etc.) are rejected.",
inputSchema: {
type: "object",
properties: {
query: {
type: "string",
description: "The SELECT query to execute. Must be a SELECT statement. Do not include OFFSET/FETCH clauses as pagination is automatically applied.",
},
page: {
type: "number",
description: "Page number (1-based). Defaults to 1.",
default: 1,
},
pageSize: {
type: "number",
description: "Number of records per page. Must be between 1 and 1000. Defaults to 100.",
default: 100,
},
},
required: ["query"],
},
},
],
};
});
// Handle tool calls
server.setRequestHandler(CallToolRequestSchema, async (request) => {
const { name, arguments: args } = request.params;
try {
const connection = await getConnection();
switch (name) {
case "get_table_data": {
const { table, page = 1, pageSize = 100, orderBy } = args;
// Validate pagination parameters
if (page < 1) {
throw new Error("Page number must be at least 1");
}
if (pageSize < 1 || pageSize > 1000) {
throw new Error("Page size must be between 1 and 1000");
}
// Parse table name (handle schema.table format)
const tableParts = table.split(".");
const schema = tableParts.length > 1 ? tableParts[0] : "dbo";
const tableName = tableParts.length > 1 ? tableParts[1] : tableParts[0];
// Get column info to determine order by column if not specified
let orderByColumn = orderBy;
if (!orderByColumn) {
const columnsResult = await connection
.request()
.input("schema", sql.NVarChar, schema)
.input("table", sql.NVarChar, tableName)
.query(`
SELECT TOP 1 COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION
`);
if (columnsResult.recordset.length === 0) {
throw new Error(`Table ${table} not found`);
}
orderByColumn = columnsResult.recordset[0].COLUMN_NAME;
}
// Calculate offset
const offset = (page - 1) * pageSize;
// Execute paginated query
const result = await connection
.request()
.input("offset", sql.Int, offset)
.input("pageSize", sql.Int, pageSize)
.query(`
SELECT *
FROM ${schema}.${tableName}
ORDER BY [${orderByColumn}]
OFFSET @offset ROWS
FETCH NEXT @pageSize ROWS ONLY
`);
return {
content: [
{
type: "text",
text: JSON.stringify({
table,
page,
pageSize,
recordCount: result.recordset.length,
data: result.recordset,
}, null, 2),
},
],
};
}
case "get_table_count": {
const { table } = args;
// Parse table name
const tableParts = table.split(".");
const schema = tableParts.length > 1 ? tableParts[0] : "dbo";
const tableName = tableParts.length > 1 ? tableParts[1] : tableParts[0];
const result = await connection
.request()
.query(`SELECT COUNT(*) as count FROM ${schema}.${tableName}`);
return {
content: [
{
type: "text",
text: JSON.stringify({
table,
count: result.recordset[0].count,
}, null, 2),
},
],
};
}
case "get_table_structure": {
const { table } = args;
// Parse table name
const tableParts = table.split(".");
const schema = tableParts.length > 1 ? tableParts[0] : "dbo";
const tableName = tableParts.length > 1 ? tableParts[1] : tableParts[0];
// Get column information
const columnsResult = await connection
.request()
.input("schema", sql.NVarChar, schema)
.input("table", sql.NVarChar, tableName)
.query(`
SELECT
COLUMN_NAME as columnName,
DATA_TYPE as dataType,
CHARACTER_MAXIMUM_LENGTH as maxLength,
IS_NULLABLE as isNullable,
COLUMN_DEFAULT as defaultValue,
ORDINAL_POSITION as ordinalPosition
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table
ORDER BY ORDINAL_POSITION
`);
// Get primary key information
const pkResult = await connection
.request()
.input("schema", sql.NVarChar, schema)
.input("table", sql.NVarChar, tableName)
.query(`
SELECT COLUMN_NAME as columnName
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_SCHEMA = @schema AND TABLE_NAME = @table
`);
// Get foreign key information
const fkResult = await connection
.request()
.input("schema", sql.NVarChar, schema)
.input("table", sql.NVarChar, tableName)
.query(`
SELECT
COL_NAME(fc.parent_object_id, fc.parent_column_id) as columnName,
OBJECT_SCHEMA_NAME(fc.referenced_object_id) as referencedSchema,
OBJECT_NAME(fc.referenced_object_id) as referencedTable,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) as referencedColumn
FROM sys.foreign_key_columns fc
INNER JOIN sys.foreign_keys fk ON fc.constraint_object_id = fk.object_id
WHERE OBJECT_SCHEMA_NAME(fc.parent_object_id) = @schema
AND OBJECT_NAME(fc.parent_object_id) = @table
`);
const primaryKeys = pkResult.recordset.map(row => row.columnName);
const foreignKeys = fkResult.recordset;
return {
content: [
{
type: "text",
text: JSON.stringify({
table,
schema,
tableName,
columns: columnsResult.recordset,
primaryKeys,
foreignKeys,
}, null, 2),
},
],
};
}
case "query_data": {
const { query, page = 1, pageSize = 100 } = args;
// Validate pagination parameters
if (page < 1) {
throw new Error("Page number must be at least 1");
}
if (pageSize < 1 || pageSize > 1000) {
throw new Error("Page size must be between 1 and 1000");
}
// Validate that this is a SELECT query
const trimmedQuery = query.trim().toUpperCase();
// Check for DDL/DML keywords that should be blocked
const forbiddenKeywords = [
'CREATE', 'ALTER', 'DROP', 'TRUNCATE', 'RENAME',
'INSERT', 'UPDATE', 'DELETE', 'MERGE',
'GRANT', 'REVOKE', 'DENY',
'EXEC', 'EXECUTE', 'SP_',
'XP_', 'BACKUP', 'RESTORE'
];
// Check if query starts with SELECT or WITH (for CTEs)
const startsWithSelect = trimmedQuery.startsWith('SELECT') || trimmedQuery.startsWith('WITH');
if (!startsWithSelect) {
throw new Error("Only SELECT queries are allowed");
}
// Check for forbidden keywords in the query
for (const keyword of forbiddenKeywords) {
// Use word boundaries to avoid false positives (e.g., "SELECTED" shouldn't match "SELECT")
const regex = new RegExp(`\\b${keyword}\\b`, 'i');
if (regex.test(query)) {
throw new Error(`Query contains forbidden keyword: ${keyword}. Only SELECT queries are allowed.`);
}
}
// Remove any existing OFFSET/FETCH clauses to prevent conflicts
let cleanQuery = query.trim();
// Remove trailing semicolon if present
if (cleanQuery.endsWith(';')) {
cleanQuery = cleanQuery.slice(0, -1).trim();
}
// Remove existing OFFSET/FETCH clauses (case-insensitive)
cleanQuery = cleanQuery.replace(/\s+OFFSET\s+\d+\s+ROWS(\s+FETCH\s+NEXT\s+\d+\s+ROWS\s+ONLY)?$/i, '');
// Check if query has an ORDER BY clause
const hasOrderBy = /ORDER\s+BY/i.test(cleanQuery);
if (!hasOrderBy) {
throw new Error("Query must include an ORDER BY clause for pagination to work correctly");
}
// Calculate offset
const offset = (page - 1) * pageSize;
// Build paginated query
const paginatedQuery = `
${cleanQuery}
OFFSET ${offset} ROWS
FETCH NEXT ${pageSize} ROWS ONLY
`;
// Execute query
const result = await connection.request().query(paginatedQuery);
return {
content: [
{
type: "text",
text: JSON.stringify({
page,
pageSize,
recordCount: result.recordset.length,
data: result.recordset,
}, null, 2),
},
],
};
}
default:
throw new Error(`Unknown tool: ${name}`);
}
} catch (error) {
return {
content: [
{
type: "text",
text: `Error: ${error.message}`,
},
],
isError: true,
};
}
});
// Start server
async function main() {
const transport = new StdioServerTransport();
await server.connect(transport);
console.error("Boxtalk Data MCP Server running on stdio");
}
main().catch((error) => {
console.error("Server error:", error);
process.exit(1);
});
