# Data Nexus MCP

A modular, secure platform for connecting to SQL and NoSQL databases via REST API, MCP (Model Context Protocol), and a Vue.js Web UI.

## Architecture

```
┌─────────────┐     ┌──────────────┐     ┌─────────────────┐
│   Web UI    │────▶│  REST API    │────▶│ core-db-command │
│  (Vue.js)   │     │  (FastAPI)   │     │  (Python lib)   │
└─────────────┘     └──────┬───────┘     └────────┬────────┘
                           │                       │
                    ┌──────▼───────┐               │
                    │  MCP Server  │───────────────┘
                    └──────┬───────┘
                           │
                    ┌──────▼───────┐
                    │  MCP Client  │──▶ AI Agents / LLMs
                    └──────────────┘
```

### Modules

| Module | Package | Description |
|--------|---------|-------------|
| **core-db-command** | `core_db_command/` | Plugin-based Python library for all database drivers |
| **rest-api-command** | `rest_api_command/` | FastAPI REST layer with OAuth2/LDAP/basic auth |
| **mcp-server** | `mcp_server/` | MCP tools: `query_database`, `get_schema`, `list_tables`, `describe_table`, `execute_sql` |
| **mcp-client** | `mcp_client/` | MCP client bridge for agent integration |
| **web-ui** | `web-ui/` | Vue 3 + Pinia + Monaco Editor query studio |
| **config** | `config/` | YAML connection definitions with `${ENV}` substitution |

## Supported Database Types

The driver registry supports **50+ database types** across 13 categories:

- **Relational:** PostgreSQL, MySQL, MSSQL, Oracle, CockroachDB, TiDB, YugabyteDB, TimescaleDB, pgvector
- **Document:** MongoDB, DocumentDB, Firestore, Couchbase (stub)
- **Key-Value:** Redis, DynamoDB, Memcached/etcd/RocksDB (stub)
- **Wide-Column:** Cassandra, ScyllaDB, Bigtable/HBase (stub)
- **Graph:** Neo4j, Neptune/JanusGraph/ArangoDB (stub)
- **Time-Series:** InfluxDB, ClickHouse, Prometheus/QuestDB (stub)
- **Vector:** Qdrant, Weaviate, Milvus, Pinecone
- **Search:** Elasticsearch, OpenSearch, Splunk/Solr (stub)
- **Warehouse:** BigQuery, Snowflake, Redshift/Databricks (stub)
- **Multi-Model:** Cosmos DB, OrientDB (stub)
- **Embedded:** SQLite, DuckDB, Realm/LMDB (stub)
- **Ledger:** QLDB/BigchainDB (stub)
- **NewSQL:** Spanner (stub)

Fully implemented drivers include PostgreSQL, MySQL, MSSQL, Oracle, MongoDB, Redis, SQLite, DuckDB, Elasticsearch, ClickHouse, Neo4j, InfluxDB, Cassandra, DynamoDB, BigQuery, Snowflake, Qdrant, Weaviate, Milvus, Pinecone, Cosmos DB, and Firestore. Stub drivers are registered and extensible.

## Quick Start

### Prerequisites

- Python 3.11+
- Node.js 20+ (for Web UI development)
- Docker & Docker Compose (optional)

### 1. Install Python dependencies

```bash
cp .env.example .env
pip install -e ".[dev]"
```

### 2. Configure connections

Edit `config/connections.yaml` and set secrets via environment variables:

```yaml
connections:
  - name: postgres_prod
    type: postgresql
    host: localhost
    port: 5432
    database: mydb
    user: readonly_user
    password: ${PG_PASSWORD}
```

### 3. Start the REST API

```bash
db-rest-api
# or: uvicorn rest_api_command.app:app --reload
```

API docs: http://localhost:8000/docs

### 4. Start the Web UI (development)

```bash
cd web-ui
cp .env.example .env
npm install
npm run dev
```

Open http://localhost:5173 — default credentials: `admin` / `changeme`

### 5. Run with Docker Compose

```bash
docker compose up -d
```

Services:
- REST API: http://localhost:8000
- Web UI: http://localhost:5173
- PostgreSQL, MySQL, MongoDB, Redis, Elasticsearch

## REST API Endpoints

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/connections` | List connections (no credentials) |
| POST | `/api/db/{name}/query` | Parameterized query |
| POST | `/api/db/{name}/sql` | Raw SQL / native command |
| GET | `/api/db/{name}/schema` | Database schema |
| GET | `/api/db/{name}/tables` | List tables/collections |
| GET | `/api/db/{name}/tables/{table}/describe` | Table structure |
| GET/POST | `/api/query-history` | Query history |

## MCP Server

Configure in `.env` or Cursor MCP `env`:

```env
MCP_REST_API_URL=http://localhost:8000
# Option A: bearer token (when REST_API_AUTH_MODE=oauth2)
MCP_REST_API_TOKEN=<jwt-from-/api/auth/token>
# Option B: username/password (works with basic auth; auto-fetches JWT if oauth2)
MCP_REST_API_USER=admin
MCP_REST_API_PASSWORD=changeme
```

Run:

```bash
db-mcp-server
```

Add to Cursor/Claude MCP config:

```json
{
  "mcpServers": {
    "data-nexus-mcp": {
      "command": "db-mcp-server",
      "cwd": "/path/to/data_nexus_mcp",
      "env": {
        "MCP_REST_API_URL": "http://localhost:8000",
        "MCP_REST_API_USER": "admin",
        "MCP_REST_API_PASSWORD": "changeme"
      }
    }
  }
}
```

Note: `REST_API_*` variables belong on the REST API process (`db-rest-api`), not in the MCP server config.

## MCP Client

```bash
db-mcp-client                    # list available tools
db-mcp-client query local_sqlite "SELECT 1"
```

## Authentication

Set `REST_API_AUTH_MODE` to one of:

- `basic` — HTTP Basic Auth (default for development)
- `oauth2` — JWT bearer tokens via `/api/auth/token`
- `ldap` — LDAP bind (requires `REST_API_LDAP_SERVER` and `REST_API_LDAP_BASE_DN`)

## Adding a New Driver

1. Create `core_db_command/drivers/mydb.py`
2. Subclass `BaseDriver` and set `driver_type`
3. Decorate with `@DriverRegistry.register`
4. Import in `core_db_command/drivers/registry_loader.py`

```python
from core_db_command.base import BaseDriver, DriverRegistry

@DriverRegistry.register
class MyDBDriver(BaseDriver):
    driver_type = "mydb"

    async def connect(self): ...
    async def disconnect(self): ...
    async def query(self, query, params=None): ...
    async def execute(self, command, params=None): ...
    async def list_tables(self, schema=None): ...
    async def describe_table(self, table, schema=None): ...
```

## Testing

```bash
pytest tests/ -v
```

## Security Notes

- Credentials are **never** returned by the REST API or MCP server
- Secrets must use `${ENV_VAR}` placeholders in YAML config
- All API endpoints require authentication
- Query input is validated and length-limited

## Project Structure

```
data-nexus-mcp/
├── core_db_command/       # Core library + drivers
├── rest_api_command/      # FastAPI REST API
├── mcp_server/            # MCP server
├── mcp_client/            # MCP client
├── web-ui/                # Vue.js frontend
├── config/                # YAML connection config
├── tests/                 # Unit tests
├── docker-compose.yml
├── Dockerfile
└── pyproject.toml
```

## License

Apache 2.0