Claude MCP実践ガイド：社内データベース連携で問い合わせ対応を70%高速化する戦略

mkdir mcp-db-server
cd mcp-db-server
npm init -y
npm install @modelcontextprotocol/sdk tedious dotenv
npm install -D @types/node typescript tsx

import { Server } from "@modelcontextprotocol/sdk/server/index.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import { Connection, Request } from "tedious";

const server = new Server(
  {
    name: "company-db-server",
    version: "1.0.0",
  },
  {
    capabilities: {
      tools: {},
    },
  }
);

// データベース接続設定
const config = {
  server: process.env.DB_SERVER!,
  authentication: {
    type: "default",
    options: {
      userName: process.env.DB_USER!,
      password: process.env.DB_PASSWORD!,
    },
  },
  options: {
    database: process.env.DB_NAME!,
    encrypt: true,
  },
};

// ツールの登録
server.setRequestHandler("tools/list", async () => {
  return {
    tools: [
      {
        name: "query_logs",
        description: "エラーログを検索します",
        inputSchema: {
          type: "object",
          properties: {
            start_date: { type: "string", description: "開始日（YYYY-MM-DD）" },
            end_date: { type: "string", description: "終了日（YYYY-MM-DD）" },
            level: { type: "string", description: "ログレベル（ERROR/WARN/INFO）" },
          },
          required: ["start_date", "end_date"],
        },
      },
    ],
  };
});

// ツールの実行ハンドラ
server.setRequestHandler("tools/call", async (request) => {
  if (request.params.name === "query_logs") {
    const { start_date, end_date, level } = request.params.arguments;
    const results = await executeQuery(start_date, end_date, level);
    return { content: [{ type: "text", text: JSON.stringify(results, null, 2) }] };
  }
  throw new Error("Unknown tool: " + request.params.name);
});

async function executeQuery(startDate: string, endDate: string, level?: string): Promise {
  return new Promise((resolve, reject) => {
    const connection = new Connection(config);
    const results: any[] = [];

    connection.on("connect", (err) => {
      if (err) {
        reject(err);
        return;
      }

      let sql = "SELECT * FROM ErrorLogs WHERE timestamp BETWEEN @start AND @end";
      if (level) sql += " AND level = @level";

      const request = new Request(sql, (err) => {
        if (err) reject(err);
        else resolve(results);
      });

      request.addParameter("start", "VarChar", startDate);
      request.addParameter("end", "VarChar", endDate);
      if (level) request.addParameter("level", "VarChar", level);

      request.on("row", (columns) => {
        const row: any = {};
        columns.forEach((column) => {
          row[column.metadata.colName] = column.value;
        });
        results.push(row);
      });

      connection.execSql(request);
    });

    connection.connect();
  });
}

async function main() {
  const transport = new StdioServerTransport();
  await server.connect(transport);
}

main().catch(console.error);

{
  "mcpServers": {
    "company-db": {
      "command": "node",
      "args": ["/path/to/mcp-db-server/dist/index.js"],
      "env": {
        "DB_SERVER": "your-server.database.windows.net",
        "DB_USER": "admin",
        "DB_PASSWORD": "********",
        "DB_NAME": "production"
      }
    }
  }
}

-- 専用ユーザーの作成
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';

-- 特定テーブルへの読み取り権限のみ付与
GRANT SELECT ON ErrorLogs TO mcp_readonly;
GRANT SELECT ON AccessLogs TO mcp_readonly;
GRANT SELECT ON PerformanceMetrics TO mcp_readonly;

-- 個人情報テーブルへのアクセスは明示的に拒否
REVOKE ALL ON Users FROM mcp_readonly;

const ALLOWED_TABLES = ["ErrorLogs", "AccessLogs", "PerformanceMetrics"];
const FORBIDDEN_KEYWORDS = ["DROP", "DELETE", "UPDATE", "INSERT", "TRUNCATE"];

function validateQuery(tableName: string, operation: string): boolean {
  if (!ALLOWED_TABLES.includes(tableName)) {
    throw new Error("Table " + tableName + " is not accessible");
  }
  
  if (FORBIDDEN_KEYWORDS.some(keyword => operation.toUpperCase().includes(keyword))) {
    throw new Error("Operation contains forbidden keyword");
  }
  
  return true;
}

async function logAccess(user: string, tool: string, params: any, result: any) {
  const logEntry = {
    timestamp: new Date().toISOString(),
    user,
    tool,
    params,
    resultRowCount: Array.isArray(result) ? result.length : 0,
    ipAddress: process.env.USER_IP,
  };
  
  await fs.appendFile("audit.log", JSON.stringify(logEntry) + "\n");
}

server.setRequestHandler("tools/call", async (request) => {
  if (request.params.name === "natural_query") {
    const { question } = request.params.arguments;
    
    // Claudeに最適なSQLを提案させる（実際にはプロンプト内で実施）
    const suggestedSQL = await generateSQL(question);
    
    // SQLを検証してから実行
    validateQuery(suggestedSQL);
    const results = await executeSQL(suggestedSQL);
    
    return { 
      content: [
        { type: "text", text: "実行したSQL: " + suggestedSQL },
        { type: "text", text: JSON.stringify(results, null, 2) }
      ] 
    };
  }
});

const TOOL_SQL_MAP: Record = {
  get_error_summary: "SELECT level, COUNT(*) as count, DATE(timestamp) as date FROM ErrorLogs WHERE timestamp BETWEEN @start AND @end GROUP BY level, DATE(timestamp) ORDER BY date DESC",
  find_slow_queries: "SELECT query, duration_ms, timestamp FROM QueryLogs WHERE duration_ms > @threshold ORDER BY duration_ms DESC LIMIT 50",
};

よくある質問テンプレート

エラーログの確認
「昨日から今日までのERRORレベルのログを件数順に表示して」

パフォーマンス分析
「過去1週間で実行時間が1秒を超えたクエリのTOP10を教えて」

ユーザー行動分析
「特定ユーザー（ID: 12345）の過去3日間のアクセス履歴を時系列で表示して」