Oracle Database MCP Server

# Oracle Database MCP Server & Client Plan ## Project Overview Create an MCP (Model Context Protocol) server that integrates with GitHub Copilot to execute SQL queries against an Oracle database. This allows LLMs within Copilot to safely run read-only queries against your Oracle DB. ### 🎯 Current Status: **Production Ready** ✅ **Major Accomplishments:** - ✅ Core MCP server fully implemented and working - ✅ Two main tools operational (query_database, get_database_schema) - ✅ Custom lightweight logging (Winston removed) - ✅ VS Code integration documented and tested - ✅ Claude Desktop integration available - ✅ Comprehensive documentation suite **Remaining Work:** - ⚠️ Formal automated testing framework (currently manual testing only) - 💡 Optional enhancements (rate limiting, query complexity analysis) --- ## Architecture ``` ┌─────────────────────────────────────────────────────────────┐ │ GitHub Copilot / LLM │ └──────────────────────┬──────────────────────────────────────┘ │ (MCP Protocol) │ ┌──────────────────────▼──────────────────────────────────────┐ │ MCP Client (VS Code / Copilot Extension) │ │ - Communicates with MCP Server via STDIO │ │ - Spawns server process & pipes stdin/stdout/stderr │ │ - Handles tool invocations │ └──────────────────────┬──────────────────────────────────────┘ │ (JSON-RPC over STDIO) │ stdin/stdout stream │ ┌──────────────────────▼──────────────────────────────────────┐ │ MCP Server Process (Node.js + TypeScript) │ │ - Implements MCP protocol (stdio transport) │ │ - Defines SQL execution tools │ │ - Handles query validation & execution │ │ - Manages error handling & response formatting │ └──────────────────────┬──────────────────────────────────────┘ │ (oracledb package) │ ┌──────────────────────▼──────────────────────────────────────┐ │ Oracle Database Client Library (oracledb) │ │ - Connection pooling │ │ - Query execution with READ-ONLY user │ └──────────────────────┬──────────────────────────────────────┘ │ (SQL over network - SELECT only) │ ┌──────────────────────▼──────────────────────────────────────┐ │ Oracle Database (READ-ONLY USER) │ │ - Executes SELECT queries only │ │ - Returns result sets │ │ - No DML operations possible at DB level │ └─────────────────────────────────────────────────────────────┘ ``` --- ## Implementation Phases ### Phase 1: Core MCP Server Implementation **Goal:** Build the MCP server with basic Oracle connectivity #### 1.1 Dependencies & Setup - [x] MCP SDK (`@modelcontextprotocol/sdk`) - [x] Oracle DB Client (`oracledb`) - [x] Environment variable management (`dotenv`) - [x] Input validation (`zod` - already included) - [x] Logging utility (custom lightweight logger - see `src/utils/logger.ts` and `docs/LOGGING.md`) - [x] Connection pooling manager **Files created:** - [x] `src/config.ts` - Configuration & environment variables - [x] `src/database/oracleConnection.ts` - Oracle DB connection management - [x] `src/server.ts` - Main MCP server implementation - [x] `.env.example` - Environment template - [x] `.gitignore` - Security (exclude .env, node_modules) #### 1.2 Database Connection Layer **Requirements:** ✅ **COMPLETED** - [x] Connection pooling for performance - [x] **READ-ONLY database user** (enforced at DB level for security) - [x] Connection string from environment variables - [x] Error handling & retry logic - [x] Connection timeout management - [x] Query timeout management - [x] Graceful shutdown of connection pool **Setup:** - [x] DBA creates read-only user on Oracle DB - [x] User has SELECT privileges on allowed tables only - [x] User has NO INSERT, UPDATE, DELETE, CREATE, DROP privileges - [x] Connection string, user, and password stored in `.env` **Interfaces implemented:** - See `src/database/types.ts` for OraclePool, OraclePoolConfig, QueryResult types - See `src/database/oracleConnection.ts` for connection management - See `src/database/queryExecutor.ts` for query execution logic --- ### Phase 2: MCP Tools Definition **Goal:** Define SQL execution tools with validation & safety ✅ **COMPLETED** #### 2.1 Tool: `query_database` ✅ **IMPLEMENTED** - **Purpose:** Execute read-only SELECT queries - **Input Schema (Zod):** - `query` (string): SQL SELECT statement - `maxRows` (number, optional): Limit results (default: 1000) - `timeout` (number, optional): Query timeout in ms - **Validation Rules:** ✅ Implemented in `src/tools/queryDatabase.ts` - [x] Basic query length limit (50,000 characters) - [x] Enforce result size limits (prevent memory exhaustion) - [x] Query timeout enforcement - [x] No injection protection needed (local client, trusted) - **Response Format:** ✅ Implemented ```typescript { success: boolean; data?: QueryResult; error?: string; } ``` #### 2.2 Tool: `get_database_schema` ✅ **IMPLEMENTED** - **Purpose:** Retrieve database schema information - **Input Schema:** ✅ Implemented in `src/tools/getSchema.ts` - `tableName?` (string): Specific table or all tables - **Returns:** Table names, column names, data types, constraints - **Implementation:** See `src/database/queryExecutor.ts` - `getSchema()` function #### 2.3 Tool: `validate_query` ⚠️ **NOT IMPLEMENTED** - **Status:** Optional feature - not currently needed - **Purpose:** Validate SQL syntax without executing - **Note:** Can be added in future if needed --- ### Phase 3: Security & Validation **Goal:** Ensure safe, efficient database access (local client only) ✅ **COMPLETED** #### 3.1 Query Safety Layer ✅ **IMPLEMENTED** - [x] Basic query validation - [x] Query result size limits (prevent memory exhaustion) - configurable via MAX_ROWS_PER_QUERY - [x] Query timeout enforcement - configurable via QUERY_TIMEOUT_MS - [x] No injection protection needed (local trusted client) - [ ] Query complexity analysis (prevent expensive queries) - **Future enhancement** - [ ] Rate limiting & throttling - **Future enhancement** #### 3.2 Logging & Auditing ✅ **IMPLEMENTED** - [x] Log all executed queries with: - [x] Timestamp - [x] Query text - [x] Execution time - [x] Result row count - [x] Success/failure status - [x] Custom lightweight logging implementation (replaced Winston) - [x] Console logging with ANSI color codes - [x] Optional file logging with daily rotation - [x] Audit logging support - **See:** `src/utils/logger.ts` and `docs/LOGGING.md` #### 3.3 Error Handling ✅ **IMPLEMENTED** - [x] Graceful error messages - [x] Connection error handling - [x] Timeout handling - [x] Resource limit handling --- ### Phase 4: Testing **Goal:** Ensure reliability & security ⚠️ **PARTIAL** #### 4.1 Unit Tests ⚠️ **Not Implemented** - [ ] Query validation tests - [ ] Connection pool tests - [ ] Error handling scenarios - [ ] Input sanitization tests - **Note:** No formal unit test framework currently in place #### 4.2 Integration Tests ✅ **MANUAL TESTING AVAILABLE** - [x] Test client available (`npm run test-client`) - [x] Logger integration tests (`test-logger.mjs`) - [x] Can connect to test Oracle instance - [x] Can execute sample queries - [x] Can test schema retrieval - [ ] Automated test suite - **Future enhancement** #### 4.3 Reliability Tests ⚠️ **Manual Only** - [x] Query timeout behavior (configurable) - [x] Result size limit enforcement (configurable) - [ ] Automated reliability tests - **Future enhancement** **Status:** Manual testing via `npm run test-client`. Formal test framework (Jest/Mocha) not yet implemented. --- ### Phase 5: GitHub Copilot Integration **Goal:** Connect the MCP server to GitHub Copilot via stdio ✅ **COMPLETED** #### 5.1 VS Code Configuration ✅ **IMPLEMENTED** - [x] Created `.vscode/mcp.json.example` configuration template - [x] Documented MCP server setup for VS Code - [x] Server communicates via **stdio (stdin/stdout)** not HTTP - [x] Configuration uses input variables for secure credentials **Configuration File:** `.vscode/mcp.json.example` ```json { "servers": { "oracleDatabase": { "type": "stdio", "command": "node", "args": ["${workspaceFolder}/dist/server.js"], "env": { "ORACLE_CONNECTION_STRING": "localhost:1521/XEPDB1", "ORACLE_USER": "readonly_user", "ORACLE_PASSWORD": "password", ... } } } } ``` **How it works:** 1. ✅ VS Code spawns the Node.js server process 2. ✅ Server listens on stdin for JSON-RPC messages 3. ✅ Server writes JSON-RPC responses to stdout 4. ✅ stderr is used for logging/debugging 5. ✅ No HTTP server needed - purely process-based communication #### 5.2 Documentation ✅ **COMPLETED** - [x] README with setup instructions - [x] Quick start guide (`docs/QUICK-START-VSCODE.md`) - [x] Detailed VS Code integration guide (`docs/VSCODE-INTEGRATION.md`) - [x] VS Code Agent Mode implementation plan (`docs/VSCODE-AGENT-MODE-PLAN.md`) - [x] Claude Desktop integration guide (`docs/CLAUDE-INTEGRATION.md`) - [x] MCP integration guide (`docs/MCP-INTEGRATION.md`) - [x] Troubleshooting guide (included in integration docs) - [x] Logging guide (`docs/LOGGING.md`) --- ## Project File Structure ### Current Implementation ✅ ``` mcp-oracle-database/ ├── src/ │ ├── server.ts # ✅ Main MCP server entry point │ ├── client.ts # ✅ Test client for validation │ ├── config.ts # ✅ Configuration & validation │ ├── database/ │ │ ├── oracleConnection.ts # ✅ Oracle DB connection pool │ │ ├── queryExecutor.ts # ✅ Query execution logic │ │ └── types.ts # ✅ Database-related types │ ├── tools/ │ │ ├── queryDatabase.ts # ✅ query_database tool │ │ ├── getSchema.ts # ✅ get_database_schema tool │ │ └── (validateQuery.ts) # ⚠️ Not implemented (optional) │ └── utils/ │ └── logger.ts # ✅ Custom lightweight logger ├── docs/ │ ├── MCP-INTEGRATION.md # ✅ MCP protocol guide │ ├── VSCODE-INTEGRATION.md # ✅ VS Code setup guide │ ├── VSCODE-AGENT-MODE-PLAN.md # ✅ Implementation details │ ├── QUICK-START-VSCODE.md # ✅ Quick start guide │ ├── CLAUDE-INTEGRATION.md # ✅ Claude Desktop guide │ ├── LOGGING.md # ✅ Logging guide │ ├── WINSTON_MIGRATION.md # ✅ Logger migration doc │ └── IMPLEMENTATION-SUMMARY.md # ✅ Deliverables overview ├── .vscode/ │ ├── mcp.json.example # ✅ MCP configuration template │ ├── tool-sets.jsonc # ✅ Tool sets configuration │ └── tasks.json # ✅ VS Code tasks ├── .env.example # ✅ Environment template ├── .env # ❌ Local env (git ignored) ├── .gitignore # ✅ Security exclusions ├── package.json # ✅ Dependencies & scripts ├── tsconfig.json # ✅ TypeScript configuration ├── PLAN.md # ✅ This file ├── README.md # ✅ User documentation ├── TESTING.md # ✅ Testing guide ├── test-logger.mjs # ✅ Logger integration test └── dist/ # ✅ Compiled output (git ignored) ``` ### Not Implemented (Future Enhancements) ``` tests/ # ⚠️ Formal test suite ├── unit/ │ ├── queryValidator.test.ts # ⚠️ Not implemented │ ├── database.test.ts # ⚠️ Not implemented │ └── logger.test.ts # ⚠️ Not implemented └── integration/ └── server.test.ts # ⚠️ Not implemented src/security/ # ⚠️ Advanced security features (optional) ├── queryValidator.ts # ⚠️ Not needed for local client ├── sqlInjectionGuard.ts # ⚠️ Not needed for local client └── rateLimiter.ts # ⚠️ Future enhancement jest.config.js # ⚠️ Not implemented ``` --- ## Dependencies ### Current Dependencies (Installed) ```json { "dependencies": { "@modelcontextprotocol/sdk": "^1.20.2", "dotenv": "^16.3.1", "oracledb": "^6.4.0", "zod": "^3.25.76" }, "devDependencies": { "@types/node": "^20.10.5", "typescript": "^5.3.3" } } ``` ### Removed Dependencies - ~~`winston`~~ - **Replaced with custom lightweight logger** (see `docs/WINSTON_MIGRATION.md`) - Removed 20+ transitive dependencies - Zero external dependencies for logging - Custom implementation in `src/utils/logger.ts` ### Future Test Dependencies (Not Yet Implemented) ```json { "devDependencies": { "jest": "^29.7.0", "@types/jest": "^29.5.11", "ts-jest": "^29.1.1" } } ``` --- ## Key Configuration Points ### Environment Variables (`.env`) ``` # Oracle Database Connection (READ-ONLY USER) ORACLE_CONNECTION_STRING=hostname:port/servicename ORACLE_USER=readonly_user ORACLE_PASSWORD=readonly_password # Connection Pool Settings ORACLE_POOL_MIN=2 ORACLE_POOL_MAX=10 # Query Settings QUERY_TIMEOUT_MS=30000 MAX_ROWS_PER_QUERY=1000 MAX_QUERY_LENGTH=10000 # Logging LOG_LEVEL=info LOG_FILE=logs/mcp-server.log # Security ENABLE_AUDIT_LOGGING=true ALLOWED_TABLES=USERS,PRODUCTS,ORDERS # Optional whitelist # Transport MCP_TRANSPORT=stdio # Uses stdin/stdout for communication ``` --- ## Implementation Checklist - [x] **Phase 1: Core MCP Server** ✅ **COMPLETED** - [x] Setup TypeScript configuration - [x] Implement database connection layer - [x] Create main server entry point - [x] Test basic MCP protocol handshake - [x] Custom lightweight logger implementation - [x] **Phase 2: MCP Tools** ✅ **COMPLETED** - [x] Implement query_database tool - [x] Implement get_database_schema tool - [ ] Implement validate_query tool (optional, not needed) - [x] Add tool schemas with Zod validation - [x] **Phase 3: Security** ✅ **COMPLETED** - [x] Query validation layer (basic) - [x] Logging & auditing (custom logger) - [x] Error handling & sanitization - [x] Read-only database user enforcement - [ ] SQL injection prevention (not needed for local trusted client) - [ ] Advanced query complexity analysis (future enhancement) - [ ] **Phase 4: Testing** ⚠️ **PARTIAL** - [ ] Automated unit tests (not implemented) - [x] Manual integration testing (test-client available) - [x] Logger integration tests - [ ] Formal test framework (future enhancement) - [x] **Phase 5: Copilot Integration** ✅ **COMPLETED** - [x] Comprehensive documentation - [x] VS Code configuration guide - [x] MCP configuration examples - [x] Quick start guide - [x] Troubleshooting guide - [x] Multiple integration guides (VS Code, Claude Desktop) --- ## Security Considerations ### Local Client Architecture - Designed for **trusted local environment** only - No injection protection needed (local, trusted user) - Read-only database user provides data protection layer - Focus is on stability, performance, and preventing resource exhaustion ### Priority Safeguards 1. **Query Timeout** - Prevent long-running/expensive queries 2. **Result Size Limits** - Prevent memory exhaustion 3. **Connection Pooling** - Efficient resource management 4. **Rate Limiting** - Prevent query flooding 5. **Logging** - Track all activity for debugging/auditing 6. **Error Handling** - Graceful failure modes --- ## Success Criteria ### Core Functionality ✅ **ACHIEVED** ✅ MCP server starts without errors ✅ Can execute SELECT queries successfully ✅ Blocks INSERT/UPDATE/DELETE/DROP operations (via read-only user) ✅ Returns results in expected format ✅ Handles errors gracefully ✅ Logs all activity ✅ Integrates with GitHub Copilot ✅ Integrates with Claude Desktop ### Documentation ✅ **ACHIEVED** ✅ Comprehensive README ✅ Quick start guide ✅ VS Code integration guide ✅ Claude Desktop integration guide ✅ MCP integration guide ✅ Logging documentation ✅ Winston migration documentation ### Security ✅ **ACHIEVED** ✅ Read-only database user enforcement ✅ Query timeout protection ✅ Result size limits ✅ Connection pooling ✅ Graceful error handling ✅ Secure credential management (input variables) ### Testing ⚠️ **PARTIAL** ✅ Manual testing available (`npm run test-client`) ✅ Logger integration tests ⚠️ Automated unit/integration test suite (future enhancement) --- ## Documentation ### Integration Guides ✅ **COMPLETE** - ✅ [MCP Integration Guide](./docs/MCP-INTEGRATION.md) - Understanding MCP protocol and tools - ✅ [VS Code Integration Guide](./docs/VSCODE-INTEGRATION.md) - Set up with GitHub Copilot - ✅ [VS Code Agent Mode Plan](./docs/VSCODE-AGENT-MODE-PLAN.md) - Implementation details - ✅ [Quick Start Guide](./docs/QUICK-START-VSCODE.md) - Get started in 3 steps - ✅ [Claude Desktop Integration Guide](./docs/CLAUDE-INTEGRATION.md) - Set up with Claude Desktop - ✅ [Logging Guide](./docs/LOGGING.md) - Logger configuration and usage - ✅ [Winston Migration](./docs/WINSTON_MIGRATION.md) - Logger migration summary - ✅ [Implementation Summary](./docs/IMPLEMENTATION-SUMMARY.md) - Deliverables overview ### Testing Documentation - ✅ Manual testing instructions in README.md - ✅ Test client available: `npm run test-client` - ⚠️ Automated test suite documentation (not yet implemented) ## References - MCP Documentation: https://modelcontextprotocol.io/ - Oracle Node.js Driver: https://node-oracledb.readthedocs.io/ - GitHub Copilot Extensions: https://docs.github.com/en/copilot