mcp-oracle-database

Database MCP Server

A Model Context Protocol (MCP) server that enables GitHub Copilot and other LLMs to execute read-only SQL queries against Oracle databases.

📦 Installation

From npm (Recommended)

npm install -g mcp-oracle-database

Or install locally in your project:

npm install mcp-oracle-database

From Source

git clone https://github.com/tannerpace/my-mcp.git
cd my-mcp
npm install && npm run build

�🚀 Quick Start with VS Code

If installed via npm:

• Configure VS Code MCP settings

Create .vscode/mcp.json:

{
  "servers": {
    "oracleDatabase": {
      "type": "stdio",
      "command": "mcp-database-server",
      "env": {
        "ORACLE_CONNECTION_STRING": "localhost:1521/XEPDB1",
        "ORACLE_USER": "your_readonly_user",
        "ORACLE_PASSWORD": "your_password",
        "ORACLE_POOL_MIN": "2",
        "ORACLE_POOL_MAX": "10",
        "QUERY_TIMEOUT_MS": "30000",
        "MAX_ROWS_PER_QUERY": "1000"
      }
    }
  }
}

• Reload VS Code and ask Copilot:

"What tables are in the database?"

If running from source:

# 1. Build the server
npm install && npm run build

# 2. Configure VS Code
cp .vscode/mcp.json.example .vscode/mcp.json

# 3. Start Oracle database (if using Docker)
docker start oracle-xe

# 4. Reload VS Code and ask Copilot:
"What tables are in the database?"

See Quick Start Guide for detailed setup.

Features

• 🔒 Read-only access - Uses a dedicated read-only database user for security
• 📡 stdio transport - Communicates via standard input/output (no HTTP server needed)
• ⚡ Connection pooling - Efficient Oracle connection management
• 📊 Schema introspection - Query table and column information
• 📝 Audit logging - All queries are logged with execution metrics
• ⏱️ Timeout protection - Prevents long-running queries
• 🛡️ Result limits - Configurable row limits to prevent memory issues

Architecture

GitHub Copilot
      ↓ (MCP Protocol)
  MCP Client (spawns process)
      ↓ (JSON-RPC over stdio)
  MCP Server (Node.js)
      ↓ (oracledb)
  Oracle DB (read-only user)

Prerequisites

• Node.js v18 or higher
• Oracle Database with a read-only user created
  • Running locally (Docker recommended)
  • Or accessible remote instance

Note: This project uses the node-oracledb package in Thin Mode, which means no Oracle Instant Client installation is required! The pure JavaScript driver connects directly to Oracle Database, just like Python's oracledb library.

Optional: Running Oracle Database Locally with Docker

If you need a local Oracle database for development:

macOS (using Colima):

# Start Colima (Docker runtime for macOS)
colima start

# Pull and run Oracle XE container
docker run -d \
  --name oracle-xe \
  -p 1521:1521 \
  -p 5500:5500 \
  -e ORACLE_PWD=OraclePwd123 \
  container-registry.oracle.com/database/express:latest

# Wait for database to be ready (takes 1-2 minutes)
docker logs -f oracle-xe

# Start/stop the database later
docker start oracle-xe
docker stop oracle-xe

Linux/Other:

# Same docker commands as above, just ensure Docker is running
docker ps

The database will be available at:

• Connection: localhost:1521/XEPDB1
• SYS password: OraclePwd123
• Web UI: http://localhost:5500/em

Setup

1. Clone and Install Dependencies

git clone <repository-url>
cd my-mcp
npm install

2. Create Read-Only Database User

Connect to your Oracle database as a DBA and run:

-- Create read-only user
CREATE USER readonly_user IDENTIFIED BY secure_password;

-- Grant connect and read-only privileges
GRANT CONNECT TO readonly_user;
GRANT SELECT ANY TABLE TO readonly_user;

-- Or grant access to specific tables only:
GRANT SELECT ON schema.table1 TO readonly_user;
GRANT SELECT ON schema.table2 TO readonly_user;

3. Configure Environment Variables

Copy the example environment file:

cp .env.example .env

Edit .env and set your Oracle connection details:

# Oracle Database Connection (READ-ONLY USER)
ORACLE_CONNECTION_STRING=hostname:1521/servicename
ORACLE_USER=readonly_user
ORACLE_PASSWORD=secure_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=50000

# Logging
LOG_LEVEL=info
ENABLE_AUDIT_LOGGING=true

4. Build the Server

npm run build

5. Configure GitHub Copilot / MCP Client

Create or update your MCP client configuration file:

VS Code (cline_mcp_config.json or similar):

{
  "mcpServers": {
    "oracle-db": {
      "command": "node",
      "args": ["/absolute/path/to/my-mcp/dist/server.js"],
      "env": {
        "ORACLE_CONNECTION_STRING": "hostname:1521/servicename",
        "ORACLE_USER": "readonly_user",
        "ORACLE_PASSWORD": "secure_password"
      }
    }
  }
}

Or use environment variables from your shell:

{
  "mcpServers": {
    "oracle-db": {
      "command": "node",
      "args": ["/absolute/path/to/my-mcp/dist/server.js"]
    }
  }
}

Usage

Once configured, the MCP server provides two tools to GitHub Copilot:

Testing with the Built-in Client

Before integrating with Copilot, you can test the server locally:

# Make sure you have .env configured with valid Oracle credentials
npm run build
npm run test-client

This will:

• Start the MCP server
• Connect to it via the test client
• List available tools
• Get database schema (list all tables)
• Disconnect and shut down

Edit src/client.ts to customize the test queries.

Using with GitHub Copilot

Once configured, the MCP server provides two tools to GitHub Copilot:

1. query_database

Execute read-only SQL queries:

User: "Show me the top 10 customers by revenue"
Copilot: [calls query_database with SQL query]

Parameters:

• query (required): SQL SELECT statement
• maxRows (optional): Maximum rows to return
• timeout (optional): Query timeout in milliseconds

Example:

{
  "query": "SELECT customer_name, SUM(revenue) as total FROM customers GROUP BY customer_name ORDER BY total DESC",
  "maxRows": 10
}

2. get_database_schema

Get schema information:

User: "What columns are in the CUSTOMERS table?"
Copilot: [calls get_database_schema with tableName="CUSTOMERS"]

Parameters:

• tableName (optional): Specific table name, or omit to list all tables

Example Prompts for Copilot

• "List all tables in the database"
• "Show me the schema of the ORDERS table"
• "How many active users do we have?"
• "What are the top 5 products by sales this month?"
• "Show me recent transactions for customer ID 12345"

Development

Project Structure

my-mcp/
├── src/
│   ├── server.ts              # Main MCP server entry point
│   ├── client.ts              # Test client for local testing
│   ├── config.ts              # Configuration with Zod validation
│   ├── database/
│   │   ├── types.ts           # TypeScript types
│   │   ├── oracleConnection.ts # Connection pool manager
│   │   └── queryExecutor.ts   # Query execution logic
│   ├── tools/
│   │   ├── queryDatabase.ts   # query_database tool
│   │   └── getSchema.ts       # get_database_schema tool
│   └── logging/
│       └── logger.ts          # Winston-based logging
├── dist/                      # Compiled JavaScript (generated)
├── .env                       # Environment variables (git ignored)
├── .env.example               # Environment template
└── package.json

Scripts

npm run build       # Compile TypeScript
npm run dev         # Watch mode compilation
npm run clean       # Remove dist folder
npm run typecheck   # Type check without compiling
npm start           # Run the server (after building)
npm run test-client # Run test client to verify server works

Logging

All queries and events are logged in JSON format. Logs go to stdout/stderr:

{
  "level": "info",
  "message": "Query executed successfully",
  "timestamp": "2025-10-24T10:30:00.000Z",
  "audit": true,
  "query": "SELECT * FROM customers WHERE...",
  "rowCount": 42,
  "executionTime": 156
}

Set LOG_LEVEL=debug in .env for more verbose logging.

Security Considerations

• Read-Only User - Database user has only SELECT privileges
• Local Client - Designed for trusted local use only
• No Injection Protection - Trust the LLM to generate valid queries
• Query Limits - Row count and timeout limits prevent resource exhaustion
• Audit Logging - All queries logged for review

Troubleshooting

Docker/Colima Issues (macOS)

Docker not running:

# Check if Colima is running
colima status

# Start Colima if needed
colima start

# Verify Docker works
docker ps

Database won't start:

# Check container status
docker ps -a | grep oracle

# View logs
docker logs oracle-xe

# Restart if needed
docker restart oracle-xe

Connection Failed

Error: ORA-12545: Connect failed because target host or object does not exist

Solutions:

• Check your ORACLE_CONNECTION_STRING format: hostname:port/servicename
• For local Docker: use localhost:1521/XEPDB1
• Verify database is running: docker ps | grep oracle

Permission Denied

Error: ORA-00942: table or view does not exist

Solution: Grant SELECT privileges to your read-only user on the required tables.

Database Not Ready

If the test client fails immediately after starting the database:

• Wait 1-2 minutes for Oracle to fully initialize
• Check health status: docker ps should show (healthy)
• Watch startup logs: docker logs -f oracle-xe

Thin Mode vs Thick Mode

This project uses Thin Mode (pure JavaScript, no Oracle Client needed). If you encounter issues and want to use Thick Mode:

• Install Oracle Instant Client
• Add to your code: oracledb.initOracleClient() before creating the pool

For most use cases, Thin Mode is simpler and works great!

Documentation

📚 Integration Guides:

• MCP Integration Guide - Learn about MCP protocol, tools, and how it works
• VS Code Integration Guide - Set up with GitHub Copilot (includes custom instructions)
• Claude Desktop Integration Guide - Set up with Claude Desktop
• Quick Start Guide - Get started with VS Code in 3 steps ⚡
• VS Code Agent Mode Plan - Implementation details and troubleshooting

📊 Test Results:

• Test Results - Comprehensive test results with example queries

📝 Custom Instructions:

• .github/copilot-instructions.md - Project-wide Copilot instructions
• .github/instructions/ - Language-specific coding guidelines

License

ISC

Contributing

Contributions welcome! Please open an issue or pull request.