Content
# Database Assistant MCP Server
A read-only MCP (Model Context Protocol) server for database exploration and querying. Connect any MCP-compatible client (AWS Kiro, Claude Desktop, etc.) to your PostgreSQL or MySQL database and explore schemas, run queries, generate SQL from natural language, and export results as CSV.
## Features
- **Schema Discovery** — List databases, schemas, tables, columns, constraints, indexes, and foreign key relationships
- **Safe Query Execution** — AST-based SQL validation ensures only read-only queries run
- **Natural Language to SQL** — Describe what you want in plain English and get a validated SQL query
- **CSV Export** — Export any query result to a CSV file
- **Query Explanation** — Run EXPLAIN ANALYZE to understand query performance
- **Pagination** — Large results are paginated (100 rows/page) automatically
- **Audit Logging** — Every query is logged with timestamp, execution time, and row count
## Tools
| Tool | Description |
|------|-------------|
| `list_databases` | List all databases on the server |
| `list_schemas` | List schemas (filtered by allowlist) |
| `list_tables` | Tables in a schema with types and row counts |
| `describe_table` | Columns, types, constraints, and indexes |
| `describe_relationships` | Foreign key tree (incoming + outgoing) |
| `sample_data` | Quick N-row preview of a table |
| `validate_sql` | Check if a query is safe before running |
| `run_sql` | Execute a validated read-only query |
| `explain_query` | EXPLAIN ANALYZE with execution plan |
| `generate_sql` | Natural language → SQL (LLM-powered) |
| `export_csv` | Execute query and save results as CSV |
## Security
This server enforces strict read-only access:
- **AST-based validation** — SQL is parsed into an abstract syntax tree using [sqlglot](https://github.com/tobymao/sqlglot), not regex
- **Blocked operations** — INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CREATE, GRANT, REVOKE, COPY, CALL
- **Single-statement only** — Multi-statement queries (`;` separated) are rejected
- **Dangerous function blocking** — `pg_read_file`, `lo_export`, `dblink`, `LOAD_FILE`, etc.
- **Automatic LIMIT** — Queries without a LIMIT get one injected (default: 1000 rows max)
- **Query timeout** — Enforced at the database level (default: 10 seconds)
- **Schema allowlist** — Restrict access to specific schemas only
- **Generated SQL re-validation** — LLM-generated queries pass through the same validator
## Prerequisites
- Python 3.10+
- [uv](https://docs.astral.sh/uv/) package manager
- PostgreSQL or MySQL database
## Setup
1. **Clone and install dependencies:**
```bash
cd /path/to/RDS
uv sync
```
2. **Configure environment:**
```bash
cp .env.example .env
# Edit .env with your database credentials
```
3. **Seed a test database (optional):**
```bash
psql -U postgres -f seed.sql
```
This creates an `ecommerce` database with 50 tables and ~25,000 rows across customers, products, orders, analytics, support, and more.
## Configuration
Set these environment variables (via `.env` file or system environment):
| Variable | Required | Default | Description |
|----------|----------|---------|-------------|
| `DB_HOST` | Yes | — | Database hostname or RDS endpoint |
| `DB_PORT` | No | `5432` | Database port |
| `DB_NAME` | Yes | — | Database name |
| `DB_USER` | Yes | — | Database user (use a read-only user) |
| `DB_PASSWORD` | Yes | — | Database password |
| `DB_TYPE` | No | `postgresql` | `postgresql` or `mysql` |
| `ALLOWED_SCHEMAS` | No | (all) | Comma-separated schema allowlist |
| `QUERY_TIMEOUT` | No | `10` | Max query execution time (seconds) |
| `MAX_ROWS` | No | `1000` | Maximum rows returned per query |
| `CSV_EXPORT_DIR` | No | `/tmp/db_exports` | Directory for CSV exports |
| `OPENAI_BASE_URL` | No | — | LLM API endpoint (for `generate_sql`) |
| `OPENAI_API_KEY` | No | — | LLM API key (for `generate_sql`) |
| `MODEL` | No | `bedrock.claude-sonnet-4-6` | LLM model ID |
## Usage
### With AWS Kiro
Add to your Kiro MCP configuration:
```json
{
"mcpServers": {
"database-assistant": {
"command": "/opt/homebrew/bin/uv",
"args": ["run", "--directory", "/path/to/database_mcp_server", "mcp_server.py"],
"env": {
"DB_HOST": "your-rds-endpoint.rds.amazonaws.com",
"DB_PORT": "5432",
"DB_NAME": "ecommerce",
"DB_USER": "readonly_user",
"DB_PASSWORD": "your_password",
"DB_TYPE": "postgresql",
"ALLOWED_SCHEMAS": "store"
}
}
}
}
```
### With Claude Desktop
Add to `claude_desktop_config.json`:
```json
{
"mcpServers": {
"database-assistant": {
"command": "uv",
"args": ["run", "--directory", "/path/to/RDS", "mcp_server.py"]
}
}
}
```
### With MCP Inspector (for testing)
```bash
uv run mcp dev mcp_server.py
```
### Direct stdio (for development)
```bash
uv run mcp_server.py
```
## Example Workflows
### Explore a database schema
```
User: What tables are in this database?
→ list_tables(schema="store")
User: Tell me about the orders table
→ describe_table(table="store.orders")
User: What relates to orders?
→ describe_relationships(table="store.orders")
```
### Query with natural language
```
User: Show me the top 10 customers by revenue this year
→ generate_sql(question="top 10 customers by total order revenue in 2024")
→ validate_sql(query="SELECT ...")
→ run_sql(query="SELECT ...")
```
### Export data
```
User: Export all orders from last month as CSV
→ generate_sql(question="all orders created in the last 30 days with customer name and total")
→ export_csv(query="SELECT ...")
→ Returns: /tmp/db_exports/export_20240715_143022_a1b2c3d4.csv
```
## Architecture
```
MCP Client (Kiro / Claude Desktop)
│
▼ (stdio)
┌─────────────────────────────┐
│ mcp_server.py │ FastMCP server, 11 tools
├─────────────────────────────┤
│ sql/validator.py │ AST-based safety validation (sqlglot)
│ sql/generator.py │ NL→SQL via OpenAI-compatible LLM
│ db/schema.py │ Schema introspection service
│ db/connection.py │ Async connection pool (asyncpg/aiomysql)
│ export/csv_export.py │ CSV file generation
│ config.py │ Environment-based configuration
└─────────────────────────────┘
│
▼
PostgreSQL / MySQL / AWS RDS
```
## Project Structure
```
.
├── mcp_server.py # MCP server entry point with tool definitions
├── config.py # Configuration from environment variables
├── db/
│ ├── connection.py # Async connection manager with pooling
│ └── schema.py # Schema introspection service
├── sql/
│ ├── validator.py # SQL validation (sqlglot AST)
│ └── generator.py # LLM-based SQL generation
├── export/
│ └── csv_export.py # CSV export utility
├── seed.sql # Sample database (50 tables, 25k+ rows)
├── pyproject.toml # Dependencies and project metadata
├── .env.example # Environment variable template
└── .gitignore
```
## Creating a Read-Only Database User
For production use, create a dedicated read-only user:
```sql
-- PostgreSQL
CREATE USER readonly_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE ecommerce TO readonly_user;
GRANT USAGE ON SCHEMA store TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA store TO readonly_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA store GRANT SELECT ON TABLES TO readonly_user;
```
```sql
-- MySQL
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON ecommerce.* TO 'readonly_user'@'%';
FLUSH PRIVILEGES;
```
## Dependencies
- [mcp](https://pypi.org/project/mcp/) — Model Context Protocol SDK
- [sqlglot](https://github.com/tobymao/sqlglot) — SQL parser for validation
- [asyncpg](https://github.com/MagicStack/asyncpg) — Async PostgreSQL driver
- [aiomysql](https://github.com/aio-libs/aiomysql) — Async MySQL driver
- [openai](https://github.com/openai/openai-python) — LLM API client (for SQL generation)
- [python-dotenv](https://github.com/theskumar/python-dotenv) — Environment variable loading
- [pydantic](https://github.com/pydantic/pydantic) — Data validation
## License
MIT
