How souhar-dya/mcp-db-server works
Project type and purpose
• This is a backend microservice (“mcp-db-server”) exposing relational databases (PostgreSQL/MySQL) to AI agents over REST. It accepts natural-language queries, converts them to SQL via a transformer model, executes read-only SQL, and returns JSON.
File structure
• Top-level artifacts:
• app/
– server.py: FastAPI app and endpoint definitions
– nl_to_sql.py: NL→SQL conversion module (HuggingFace model)
– db.py: Async SQLAlchemy database connection, query validation, and result limiting
• mcp_server.py: CLI entrypoint (alias to app.server)
• Dockerfile & docker-compose.yml: Container & multi-service orchestration (including sample DB)
• test_suite.py: end-to-end tests
• CI workflows under .github/workflows
• Controller layer (FastAPI endpoints)
• Domain logic (NL→SQL, validation)
• Data layer (async ORM)
Extract architecture hints from the README
• RESTful API with endpoints (/health, /mcp/list_tables, /mcp/describe, /mcp/query, /mcp/tables/{name}/sample)
• “AI agent friendly” design, containerized for Docker Compose or Kubernetes
• Security: read-only SQL enforcement, injection protection, row limits
• External integrations: HuggingFace transformers, SQLAlchemy, FastAPI
Main components
a. Clients / AI Agents
• External AI clients or CURL scripts calling the REST endpoints
b. API Layer (FastAPI container)
• Endpoint handlers (server.py)
• OpenAPI/Swagger UI
c. NL→SQL Engine
• Transformer model from HuggingFace (nl_to_sql.py)
d. Validation & Throttling
• Query validator (db.py)
• Row-limit enforcement
e. Database Layer
• Async SQLAlchemy + asyncpg / PyMySQL connecting to PostgreSQL/MySQL
f. Dev/Test/CI Infrastructure
• Docker Compose service definitions
• GitHub Actions pipelines
• Kubernetes manifests (optional)
Relationships and data flow
• API Layer → NL→SQL Engine (in-process function call)
• NL→SQL Engine → Validation Module → Database Layer (async SQL execution)
• Database Layer → API Layer (JSON results) → Clients
Architectural patterns
• Layered architecture (Controller → Business Logic → Persistence)
• Single container microservice pattern
• Read-only façade over relational data for AI agents
• Use of asynchronous I/O throughout (FastAPI + async DB drivers)
Technologies & frameworks
• FastAPI (HTTP server, dependency injection)
• HuggingFace Transformers (NL→SQL)
• SQLAlchemy (ORM + query building)
• asyncpg / PyMySQL (async drivers)
• Docker & Docker Compose (containerization)
• GitHub Actions (CI/CD)
• Kubernetes (optional deployment)