MCP Oracle Server (HTTP Transport)

A production-grade Node.js Model Context Protocol (MCP) server for Oracle Database with HTTP transport for Telnyx AI Agent compatibility. This server exposes MCP tools over HTTP/JSON-RPC, enabling integration with Telnyx and other HTTP-based MCP clients.

Features

• ✅ HTTP-based MCP Protocol: Full HTTP/JSON-RPC implementation (not STDIO)

• ✅ Telnyx Compatible: Designed for Telnyx AI Agent MCP integration

• ✅ Oracle Database Integration: Connection pooling with oracledb driver

• ✅ API Key Authentication: Secure /mcp endpoint with API key validation

• ✅ MCP Tools:

  • runQuery: Execute SQL queries with bind parameters

  • listTables: List all tables in the database

  • getSchema: Get detailed table schema information

  • nl2sql: Convert natural language to SQL using external service

• ✅ Web Server: Express server with /health, /ready, /metrics, /webhook/telnyx

• ✅ Production Ready: Logging, error handling, connection pooling, graceful shutdown

• ✅ Dockerized: Fully containerized with Docker Compose

Project Structure

mcp-oracle/
├── src/
│   ├── server.js          # Main entrypoint: HTTP server + MCP setup
│   ├── oracle.js          # Oracle connection pool management
│   ├── auth.js            # API key authentication middleware
│   ├── web.js             # Express app and routes
│   ├── mcpTransport.js    # HTTP transport wrapper for MCP
│   ├── logger.js          # Winston logger with file rotation
│   └── tools/
│       ├── runQuery.js     # Execute SQL queries
│       ├── listTables.js   # List database tables
│       ├── getSchema.js    # Get table schema
│       └── nl2sql.js       # Natural language to SQL
├── tests/
│   ├── integration.test.sh # Integration test script
│   └── lint-setup.md       # Linting setup guide
├── package.json
├── Dockerfile
├── docker-compose.yml
├── .env.example
└── README.md

Prerequisites

• Node.js 20+ (for local development)

• Docker and Docker Compose (for containerized deployment)

• Oracle Database (accessible via network)

• Oracle Instant Client (handled automatically in Docker)

Quick Start

1. Setup Environment

# Copy environment file
cp .env.example .env

# Edit .env with your Oracle credentials
nano .env

Required environment variables:

• ORACLE_USER: Oracle database username

• ORACLE_PASS: Oracle database password

• ORACLE_CONN: Connection string (format: host:port/service)

• MCP_API_KEY: API key for /mcp endpoint authentication (recommended)

2. Run with Docker Compose

# Build and start all services
docker-compose up --build

# Run in detached mode
docker-compose up -d

# View logs
docker-compose logs -f mcp-oracle

3. Run Locally (Development)

# Install dependencies
npm install

# Set environment variables (or use .env file)
export ORACLE_USER=your_user
export ORACLE_PASS=your_password
export ORACLE_CONN=host:1521/XEPDB1
export MCP_API_KEY=your_api_key

# Start the server
npm start

Configuration

Environment Variables

See .env.example for all available configuration options:

Required:

• ORACLE_USER: Oracle database username

• ORACLE_PASS: Oracle database password

• ORACLE_CONN: Oracle connection string (format: host:port/service)

Recommended:

• MCP_API_KEY: API key for /mcp endpoint (if not set, allows unauthenticated requests in dev mode)

Optional:

• PORT: HTTP server port (default: 3000)

• NL2SQL_URL: URL of NL2SQL service (default: http://nl2sql-service:8500/query)

• LOG_LEVEL: Logging level (default: info)

• CORS_ORIGIN: CORS origin (default: *)

• MAX_REQUEST_SIZE: Maximum request size (default: 10mb)

Oracle Connection Pool

Configure pool settings via environment variables:

• ORACLE_POOL_MIN: Minimum pool size (default: 2)

• ORACLE_POOL_MAX: Maximum pool size (default: 10)

• ORACLE_POOL_INCREMENT: Pool increment (default: 1)

• ORACLE_POOL_TIMEOUT: Pool timeout in seconds (default: 60)

API Endpoints

MCP Endpoint

POST /mcp

Main MCP protocol endpoint. Accepts JSON-RPC 2.0 requests.

Authentication:

• Header: x-mcp-api-key: <your-api-key>

• Or: Authorization: Bearer <your-api-key>

Request Format:

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/list",
  "params": {}
}

Response Format:

{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "tools": [...]
  }
}

Health & Monitoring

• GET /health: Basic health check

• GET /ready: Readiness check (verifies DB pool)

• GET /metrics: Prometheus-formatted metrics

• POST /webhook/telnyx: Telnyx webhook handler

MCP Tools

1. runQuery

Execute a SQL query against the Oracle database.

Request:

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "runQuery",
    "arguments": {
      "sql": "SELECT * FROM employees WHERE department_id = :dept_id",
      "binds": { "dept_id": 10 },
      "maxRows": 100
    }
  }
}

Response:

{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "content": [{
      "type": "text",
      "text": "{\"success\":true,\"data\":{\"rows\":[...],\"rowCount\":5}}"
    }]
  }
}

2. listTables

List all tables in the database.

Request:

{
  "jsonrpc": "2.0",
  "id": 2,
  "method": "tools/call",
  "params": {
    "name": "listTables",
    "arguments": {
      "schema": "HR"
    }
  }
}

3. getSchema

Get detailed schema information for a table.

Request:

{
  "jsonrpc": "2.0",
  "id": 3,
  "method": "tools/call",
  "params": {
    "name": "getSchema",
    "arguments": {
      "tableName": "employees",
      "schema": "HR"
    }
  }
}

4. nl2sql

Convert natural language query to SQL.

Request:

{
  "jsonrpc": "2.0",
  "id": 4,
  "method": "tools/call",
  "params": {
    "name": "nl2sql",
    "arguments": {
      "query": "Show me all customers from New York"
    }
  }
}

Telnyx Configuration

Setting up Telnyx AI Agent

1. Configure MCP URL:

   • In Telnyx AI Agent settings, set the MCP URL to: https://<your-host>:<port>/mcp

   • Example: https://mcp.example.com:3000/mcp

2. Configure API Key:

   • In Telnyx AI Agent MCP configuration, set the API key to match your MCP_API_KEY environment variable

   • The agent should send requests with header: x-mcp-api-key: <your-api-key>

3. Example Telnyx Configuration JSON:

{
  "mcp": {
    "url": "https://your-server.com:3000/mcp",
    "apiKey": "your-secure-api-key-here",
    "transport": "http"
  }
}

Testing Telnyx Integration

# Test MCP endpoint with API key
curl -X POST http://localhost:3000/mcp \
  -H "Content-Type: application/json" \
  -H "x-mcp-api-key: your-api-key" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/list",
    "params": {}
  }'

Example curl Commands

List Available Tools

curl -X POST http://localhost:3000/mcp \
  -H "Content-Type: application/json" \
  -H "x-mcp-api-key: changeme" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/list",
    "params": {}
  }'

Execute a Query

curl -X POST http://localhost:3000/mcp \
  -H "Content-Type: application/json" \
  -H "x-mcp-api-key: changeme" \
  -d '{
    "jsonrpc": "2.0",
    "id": 2,
    "method": "tools/call",
    "params": {
      "name": "runQuery",
      "arguments": {
        "sql": "SELECT table_name FROM user_tables WHERE ROWNUM <= 5"
      }
    }
  }'

Health Check

curl http://localhost:3000/health

Telnyx Webhook

curl -X POST http://localhost:3000/webhook/telnyx \
  -H "Content-Type: application/json" \
  -d '{
    "event_type": "message.received",
    "data": {
      "from": "+1234567890",
      "to": "+0987654321",
      "text": "Hello"
    }
  }'

Running Tests

Integration Tests

The project includes a bash integration test script:

# Make script executable
chmod +x tests/integration.test.sh

# Run tests (defaults to http://localhost:3000)
./tests/integration.test.sh

# Run with custom URL and API key
BASE_URL=http://your-server:3000 MCP_API_KEY=your-key ./tests/integration.test.sh

The test script validates:

• Health endpoint

• Readiness endpoint

• Metrics endpoint

• MCP authentication

• MCP tools/list

• MCP tools/call (listTables)

Docker Services

mcp-oracle

The main MCP server container:

• Runs HTTP server on port 3000

• Exposes MCP endpoint at /mcp

• Mounts logs directory

• Connects to Oracle database

nl2sql-service

FastAPI service for natural language to SQL conversion:

• Placeholder implementation included

• Exposes API on port 8500

• Replace with your actual NL2SQL service by updating the image in docker-compose.yml

oracle-db (Optional)

Local Oracle database for testing:

• Uses gvenzl/oracle-free image

• Exposes port 1521

• Data persisted in Docker volume

• Commented out by default - uncomment in docker-compose.yml if needed

Logging

Logs are written to:

• Console: Structured JSON logs with timestamps

• Files: Rotating log files in logs/ directory

  • Format: mcp-oracle-YYYY-MM-DD.log

  • Max size: 20MB per file

  • Retention: 14 days

Security

API Key Authentication

The /mcp endpoint requires API key authentication:

• Set MCP_API_KEY environment variable

• Send requests with header: x-mcp-api-key: <key> or Authorization: Bearer <key>

• If MCP_API_KEY is not set, requests are allowed but a warning is logged (development mode)

TLS/HTTPS

For production, enable TLS:

1. Option 1: Application-level TLS (not recommended)

   • Set TLS_CERT and TLS_KEY environment variables

   • Update server code to use HTTPS

2. Option 2: Reverse Proxy (recommended)

   • Use nginx, traefik, or similar

   • Handle TLS termination at the proxy

   • Forward requests to the application on port 3000

Request Limits

• Maximum request size: 10MB (configurable via MAX_REQUEST_SIZE)

• Request timeout: 30 seconds (configurable via MCP_REQUEST_TIMEOUT)

• CORS: Configurable via CORS_ORIGIN

Troubleshooting

Oracle Connection Issues

1. Check connection string format: host:port/service

2. Verify network connectivity: telnet host 1521

3. Check Oracle Instant Client: Ensure it's installed in Docker

4. Review logs: Check logs/ directory for detailed error messages

MCP Endpoint Issues

1. Authentication errors: Verify MCP_API_KEY matches in request header

2. Timeout errors: Increase MCP_REQUEST_TIMEOUT if queries are slow

3. Connection refused: Ensure service is running and port is exposed

NL2SQL Service Issues

1. Verify service is running: curl http://nl2sql-service:8500/health

2. Check network: Ensure services are on the same Docker network

3. Review timeout: Default is 30 seconds

Design Notes / Choices

This section documents key design decisions and tradeoffs made during implementation:

1. HTTP Transport Implementation

Decision: Implemented custom HTTP transport wrapper instead of using SDK's STDIO transport.

Rationale:

• The MCP SDK (@modelcontextprotocol/sdk) primarily supports STDIO transport

• Telnyx and other HTTP-based clients require HTTP/JSON-RPC endpoints

• Created a wrapper that routes JSON-RPC requests to MCP Server's internal request handlers

• This allows the server to work with HTTP clients while maintaining compatibility with MCP protocol

Tradeoff:

• Requires manual routing of requests to handlers

• May need updates if SDK adds native HTTP transport in the future

• Benefits: Works with Telnyx and any HTTP client

2. API Key Authentication Pattern

Decision: Simple API key authentication via header, with development mode fallback.

Rationale:

• Telnyx requires API key-based authentication

• Supports both x-mcp-api-key header and Authorization: Bearer for flexibility

• If MCP_API_KEY is not set, allows requests but logs warning (useful for local development)

• Simple to implement and understand

Tradeoff:

• Not as secure as JWT with expiration, but sufficient for API-to-API communication

• Can be enhanced with JWT support in the future (stub provided in auth.js)

3. Request Timeout Handling

Decision: 30-second default timeout with configurable value.

Rationale:

• Prevents hanging requests from consuming resources

• SQL queries can be slow, but 30 seconds is reasonable for most cases

• Configurable via MCP_REQUEST_TIMEOUT for different use cases

• Returns proper JSON-RPC error response on timeout

Tradeoff:

• May timeout on very large/complex queries

• Users can increase timeout or optimize queries

4. Streaming Support

Decision: Implemented request-response pattern without streaming.

Rationale:

• MCP SDK's streaming is designed for STDIO

• HTTP request-response is simpler and more compatible

• Large result sets can be paginated using maxRows parameter

• Can be enhanced with Server-Sent Events (SSE) or WebSockets if needed

Tradeoff:

• Large result sets must be paginated

• No real-time streaming of results

• Benefits: Simpler implementation, better compatibility

5. Error Handling and Response Format

Decision: All errors return JSON-RPC 2.0 compliant responses with proper error codes.

Rationale:

• Maintains JSON-RPC 2.0 protocol compliance

• Provides structured error information

• Tool errors are wrapped in MCP content format

• Database errors are caught and returned as JSON

Tradeoff:

• Error information is nested (JSON-RPC error → MCP content → tool error)

• Benefits: Protocol compliance, structured errors, easier debugging

License

MIT

Contributing

Contributions welcome! Please ensure:

• Code follows existing style

• All tools return consistent JSON responses

• Error handling is comprehensive

• Logging is appropriate

• Tests are updated

Support

For issues and questions:

1. Check logs in logs/ directory

2. Review Docker Compose logs: docker-compose logs

3. Verify environment variables

4. Test Oracle connectivity independently

5. Run integration tests: ./tests/integration.test.sh