Muhammad-Idzhans/mcp-server

# SQL Environment Variables
MSSQL_HOST=hans-server.database.windows.net, hans-server.database.windows.net, hans-server.database.windows.net
MSSQL_PORT=1433, 1433, 1433
MSSQL_USER=IdzhansKhairi, IdzhansKhairi, IdzhansKhairi
MSSQL_PASSWORD=iloveEnfrasys@123, iloveEnfrasys@123, iloveEnfrasys@123
MSSQL_DB=mssql-mcp, coffee_database, pastry_database

{
  "db": "mysql",                            // Database Type
  "sql": "SELECT * FROM orders LIMIT 10;"   // SQL queries based on db type
}

// Get all table
{
  "db": "pg",
  "sql": "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name"
}

// List all tables with columns + contents
{
  "db": "pg",
  "sql": "SELECT c.table_name, c.column_name, c.data_type FROM information_schema.columns c JOIN information_schema.tables t ON c.table_name = t.table_name WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE' ORDER BY c.table_name, c.ordinal_position;"
}

// List contents of a specific table with its columns - "SELECT * FROM <table-name> LIMIT 10;"
{
  "db": "pg",
  "sql": "SELECT * FROM books LIMIT 10;"
}

// Get all table
{
  "db": "mysql",
  "sql": "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME"
}

// List all tables with columns + contents
{
  "db": "mysql",
  "sql": "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME, ORDINAL_POSITION;"
}

// List contents of a specific table with its columns - "SELECT * FROM <table-name> LIMIT 10;"
{
  "db": "mysql",
  "sql": "SELECT * FROM orders LIMIT 10;"
}

// Get all table
{
  "db": "mssql",
  "sql": "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME"
}

// List all tables with columns + contents
{
  "db": "mssql",
  "sql": "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME, ORDINAL_POSITION;"
}

// List contents of a specific table with its columns - "SELECT TOP 10 * FROM <table-name>;"
{
  "db": "mssql",
  "sql": "SELECT TOP 10 * FROM Doctors;"
}

// Get all table
{
  "db": "oracle",
  "sql": "SELECT DISTINCT table_name FROM user_tab_columns WHERE table_name NOT LIKE 'ROLLING$%' AND table_name NOT LIKE 'SCHEDULER_%' -- AND UPPER(table_name) NOT IN (<your excludedOracleTables uppercased>) ORDER BY table_name"
}

// List all tables with columns + contents
{
  "db": "oracle",
  "sql": "SELECT table_name, column_name, data_type FROM user_tab_columns ORDER BY table_name, column_id"
}

// List contents of a specific table with its columns
{
  "db": "oracle",
  "sql": "SELECT * FROM COURSES"
}

npm ci

npm run build

if exist srcpkg rmdir /s /q srcpkg

mkdir srcpkg

xcopy src srcpkg\src\ /E /I /Y
copy package.json srcpkg\
copy package-lock.json srcpkg\ >NUL 2>&1
copy tsconfig.json srcpkg\ >NUL 2>&1
copy dbs.yaml srcpkg\ >NUL 2>&1
copy policies.yaml srcpkg\ >NUL 2>&1
if exist src\tools\sql\templates xcopy src\tools\sql\templates srcpkg\src\tools\sql\templates\ /E /I /Y

if exist artifact-src.zip del /f /q artifact-src.zip
tar -a -c -f artifact-src.zip -C srcpkg .

az login

az webapp config set -g <resource-group> -n <web-app-name> --linux-fx-version "NODE|20-lts"

az webapp config appsettings set -g <resource-group> -n <web-app-name> --settings SCM_DO_BUILD_DURING_DEPLOYMENT=true NPM_CONFIG_PRODUCTION=false

az webapp deploy -g <resource-group> -n <web-app-name> --src-path artifact-src.zip

az webapp log config -g <resource-group> -n <web-app-name> --application-logging filesystem --docker-container-logging filesystem --level information

az webapp log tail -g <resource-group> -n <web-app-name>

az webapp show -g <resource-group> -n <web-app-name> --query outboundIpAddresses -o tsv

{
  "openapi": "3.0.1",
  "info": {
    "title": "MCP SQL Server API",
    "version": "1.0.0",
    "description": "REST API wrapper for MCP SQL server endpoints."
  },
  "servers": [
    {
      "url": "https://<web-app-link>"
    }
  ],
  "paths": {
    "/dbs": {
      "get": {
        "summary": "List all databases",
        "operationId": "listDatabases",
        "responses": {
          "200": {
            "description": "Successful response",
            "content": {
              "application/json": {
                "schema": {
                  "type": "array",
                  "items": {
                    "type": "string"
                  }
                }
              }
            }
          }
        }
      }
    },
    "/dbs/aliases": {
      "get": {
        "summary": "List all database aliases",
        "operationId": "listDatabaseAliases",
        "responses": {
          "200": {
            "description": "Successful response",
            "content": {
              "application/json": {
                "schema": {
                  "type": "array",
                  "items": {
                    "type": "string"
                  }
                }
              }
            }
          }
        }
      }
    },
    "/dbs/types": {
      "get": {
        "summary": "List all SQL database types/engines/dialect",
        "operationId": "listDatabaseTypes",
        "responses": {
          "200": {
            "description": "Successful response",
            "content": {
              "application/json": {
                "schema": {
                  "type": "array",
                  "items": {
                    "type": "string"
                  }
                }
              }
            }
          }
        }
      }
    },
    "/dbs/list-by-type": {
      "get": {
        "summary": "List all SQL database names available by types/engines/dialect",
        "operationId": "listDatabaseByTypes",
        "responses": {
          "200": {
            "description": "Successful response",
            "content": {
              "application/json": {
                "schema": {
                  "type": "array",
                  "items": {
                    "type": "string"
                  }
                }
              }
            }
          }
        }
      }
    },
    "/sql/query": {
      "post": {
        "summary": "Execute SQL query against a database",
        "operationId": "executeSqlQuery",
        "requestBody": {
          "required": true,
          "content": {
            "application/json": {
              "schema": {
                "type": "object",
                "properties": {
                  "db": {
                    "type": "string",
                    "description": "The database name to run the query on based on user input"
                  },
                  "type": {
                    "type": "string",
                    "enum": [
                      "mysql",
                      "mssql",
                      "oracle",
                      "pg"
                    ],
                    "description": "The type of database engine/dialect"
                  },
                  "sql": {
                    "type": "string",
                    "description": "The SQL query to execute based on the database type"
                  }
                },
                "required": [
                  "db",
                  "type",
                  "sql"
                ]
              },
              "example": {
                "db": "pastry_database",
                "type": "mssql",
                "sql": "SELECT TOP 10 * FROM PastryOrders;"
              }
            }
          }
        },
        "responses": {
          "200": {
            "description": "Query executed successfully",
            "content": {
              "application/json": {
                "schema": {
                  "type": "object",
                  "properties": {
                    "rows": {
                      "type": "array",
                      "items": {
                        "type": "object"
                      }
                    },
                    "message": {
                      "type": "string"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}