Database Assistant 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, 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 package manager
• PostgreSQL or MySQL database

Setup

1. Clone and install dependencies:

cd /path/to/RDS
uv sync

2. Configure environment:

cp .env.example .env
# Edit .env with your database credentials

3. Seed a test database (optional):

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:

{
  "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:

{
  "mcpServers": {
    "database-assistant": {
      "command": "uv",
      "args": ["run", "--directory", "/path/to/RDS", "mcp_server.py"]
    }
  }
}

With MCP Inspector (for testing)

uv run mcp dev mcp_server.py

Direct stdio (for development)

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:

-- 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;

-- MySQL
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON ecommerce.* TO 'readonly_user'@'%';
FLUSH PRIVILEGES;

Dependencies

• mcp — Model Context Protocol SDK
• sqlglot — SQL parser for validation
• asyncpg — Async PostgreSQL driver
• aiomysql — Async MySQL driver
• openai — LLM API client (for SQL generation)
• python-dotenv — Environment variable loading
• pydantic — Data validation

License

MIT