example-integration.js

/**
 * Example Integration - How to use SQL Server MCP from your application
 * 
 * This example demonstrates how to integrate with the SQL Server MCP
 * via the REST API wrapper.
 * 
 * Prerequisites:
 * 1. Start MCP server: MCP_TRANSPORT=http node dist/index.js
 * 2. Start REST wrapper: node rest-wrapper.js
 * 3. Run this example: node example-integration.js
 */

const axios = require('axios');

const API_BASE_URL = 'http://localhost:4000/api';

/**
 * Database Client Class
 * A simple wrapper to make API calls easier
 */
class DatabaseClient {
  constructor(baseUrl = API_BASE_URL) {
    this.baseUrl = baseUrl;
  }

  async request(method, endpoint, data = null) {
    try {
      const config = {
        method,
        url: `${this.baseUrl}${endpoint}`,
        headers: { 'Content-Type': 'application/json' }
      };
      if (data) config.data = data;
      
      const response = await axios(config);
      return response.data;
    } catch (error) {
      console.error(`❌ Error calling ${method} ${endpoint}:`, error.response?.data || error.message);
      throw error;
    }
  }

  async getDatabases() {
    return this.request('GET', '/databases');
  }

  async getTables(schema = 'dbo') {
    return this.request('GET', '/tables', null, { params: { schema } });
  }

  async describeTable(tableName, schema = 'dbo') {
    return this.request('GET', `/tables/${tableName}`, null, { params: { schema } });
  }

  async getTableRelationships(tableName, schema = 'dbo') {
    return this.request('GET', `/tables/${tableName}/relationships`, null, { params: { schema } });
  }

  async searchTables(keyword) {
    return this.request('GET', '/search', null, { params: { keyword } });
  }

  async executeQuery(sqlQuery) {
    return this.request('POST', '/query', { sqlQuery });
  }

  async clearCache() {
    return this.request('DELETE', '/cache');
  }

  async getTools() {
    return this.request('GET', '/tools');
  }

  async healthCheck() {
    return this.request('GET', '/health');
  }
}

/**
 * Main Example
 */
async function main() {
  console.log('╔══════════════════════════════════════════════════════════════╗');
  console.log('║   SQL Server MCP Integration Example                          ║');
  console.log('╚══════════════════════════════════════════════════════════════╝\n');

  const client = new DatabaseClient();

  try {
    // 1. Health Check
    console.log('📡 Step 1: Checking server health...');
    const health = await client.healthCheck();
    console.log(`   ✅ Status: ${health.status}`);
    console.log(`   📦 MCP Server: ${health.mcpServer}`);
    console.log(`   🏷️  Server: ${health.serverInfo.name} v${health.serverInfo.version}\n`);

    // 2. List Databases
    console.log('📋 Step 2: Listing databases...');
    const dbResult = await client.getDatabases();
    console.log(`   ✅ Found ${dbResult.count} database(s):`);
    dbResult.data.forEach(db => console.log(`      - ${db.name}`));
    console.log();

    // 3. List Tables
    console.log('📋 Step 3: Listing tables...');
    const tablesResult = await client.getTables();
    console.log(`   ✅ Found ${tablesResult.count} table(s) in schema '${tablesResult.schema}':`);
    tablesResult.data.slice(0, 5).forEach(t => 
      console.log(`      - [${t.TABLE_SCHEMA}].${t.TABLE_NAME}`)
    );
    if (tablesResult.count > 5) {
      console.log(`      ... and ${tablesResult.count - 5} more`);
    }
    console.log();

    // 4. Describe Users Table
    console.log('📋 Step 4: Describing Users table...');
    const usersTable = await client.describeTable('Users');
    console.log(`   ✅ Table 'Users' has ${usersTable.columns} columns:`);
    usersTable.data.slice(0, 5).forEach(col => {
      const nullable = col.IS_NULLABLE === 'YES' ? '(nullable)' : '(not null)';
      console.log(`      - ${col.COLUMN_NAME}: ${col.DATA_TYPE} ${nullable}`);
    });
    if (usersTable.columns > 5) {
      console.log(`      ... and ${usersTable.columns - 5} more`);
    }
    console.log();

    // 5. Get Table Relationships
    console.log('📋 Step 5: Getting Users table relationships...');
    const relationships = await client.getTableRelationships('Users');
    console.log(`   ✅ Found ${relationships.count} relationship(s):`);
    relationships.data.forEach(rel => {
      console.log(`      - ${rel.TableName}.${rel.ColumnName} → ${rel.ReferencedTable}.${rel.ReferencedColumn}`);
      console.log(`        (${rel.ForeignKeyName})`);
    });
    console.log();

    // 6. Search Tables
    console.log('📋 Step 6: Searching tables with keyword "Role"...');
    const searchResult = await client.searchTables('Role');
    console.log(`   ✅ Found ${searchResult.count} table(s):`);
    searchResult.data.forEach(t => 
      console.log(`      - [${t.TABLE_SCHEMA}].${t.TABLE_NAME}`)
    );
    console.log();

    // 7. Execute Queries
    console.log('📋 Step 7: Executing SQL queries...');
    
    console.log('   Query 1: Count users');
    const countQuery = await client.executeQuery('SELECT COUNT(*) as total FROM Users');
    console.log(`   ✅ Result: ${countQuery.data[0].total} user(s)\n`);

    console.log('   Query 2: Get user permissions');
    const permQuery = await client.executeQuery(`
      SELECT TOP 3 p.Name as PermissionName, p.Description 
      FROM Permissions p
      ORDER BY p.Name
    `);
    console.log(`   ✅ Result: Found ${permQuery.rows} permission(s):`);
    permQuery.data.forEach(p => {
      console.log(`      - ${p.PermissionName}: ${p.Description}`);
    });
    console.log();

    // 8. List Available Tools
    console.log('📋 Step 8: Listing available MCP tools...');
    const toolsResult = await client.getTools();
    console.log(`   ✅ Available: ${toolsResult.count} tool(s):`);
    toolsResult.tools.forEach(tool => {
      console.log(`      - ${tool.name}`);
      console.log(`        ${tool.description}`);
    });
    console.log();

    // Summary
    console.log('╔══════════════════════════════════════════════════════════════╗');
    console.log('║   ✅ Integration Test Completed Successfully!                 ║');
    console.log('╠══════════════════════════════════════════════════════════════╣');
    console.log('║   You can now integrate this into your application!          ║');
    console.log('║                                                                ║');
    console.log('║   Quick Reference:                                              ║');
    console.log('║   - client.getDatabases()           List databases              ║');
    console.log('║   - client.getTables()              List tables                 ║');
    console.log('║   - client.describeTable(name)      Table structure             ║');
    console.log('║   - client.searchTables(keyword)    Search tables               ║');
    console.log('║   - client.executeQuery(sql)        Execute query               ║');
    console.log('╚══════════════════════════════════════════════════════════════╝');

  } catch (error) {
    console.error('\n❌ Integration test failed!');
    console.error('Make sure both MCP server and REST wrapper are running:');
    console.error('  Terminal 1: MCP_TRANSPORT=http node dist/index.js');
    console.error('  Terminal 2: node rest-wrapper.js');
    console.error('  Terminal 3: node example-integration.js');
  }
}

// Run the example
if (require.main === module) {
  main().catch(console.error);
}

module.exports = { DatabaseClient };