Content
# db-reader-mcp
**Read-only database MCP server for safe production data querying**
db-reader-mcp is a Model Context Protocol (MCP) server that enables AI assistants to safely query databases with **100% read-only** guarantees. Perfect for production environments where data safety is critical.
## Features
- **100% Read-Only**: Multiple layers of protection ensure no data can be modified
- **Multi-Database Support**: 14 databases across multiple categories
| Category | Databases |
|----------|-----------|
| Relational | PostgreSQL, MySQL, MariaDB, SQLite |
| Enterprise | Oracle, SQL Server |
| Chinese Databases | KingbaseES, openGauss, TiDB, Dameng |
| Analytical | ClickHouse |
| NoSQL | Redis, MongoDB, Elasticsearch |
- **HTTP Transport**: Streamable HTTP server for remote access
- **Security First**: SQL injection protection, table blocking, sensitive column masking, query validation
- **Performance**: Query caching, concurrent query limits, query timeout, slow query logging
- **Monitoring**: Health check with connection pool stats, audit logging
- **Easy Configuration**: Simple YAML configuration with environment variable support
## Quick Start
### Installation with uv (Recommended)
```bash
# Install base dependencies
uv pip install -e ".[dev]"
# Install with optional database support
uv pip install -e ".[dev,oracle]" # + Oracle support
uv pip install -e ".[dev,sqlserver]" # + SQL Server support
uv pip install -e ".[dev,clickhouse]" # + ClickHouse support
uv pip install -e ".[dev,kingbase]" # + KingbaseES support
uv pip install -e ".[dev,opengauss]" # + openGauss support (uses psycopg2)
uv pip install -e ".[dev,tidb]" # + TiDB support (uses pymysql)
uv pip install -e ".[dev,mariadb]" # + MariaDB support (uses pymysql)
uv pip install -e ".[dev,dameng]" # + Dameng support
uv pip install -e ".[dev,redis]" # + Redis support
uv pip install -e ".[dev,mongodb]" # + MongoDB support
uv pip install -e ".[dev,elasticsearch]" # + Elasticsearch support
```
### Installation with pip
```bash
pip install -e ".[dev]"
# With optional dependencies
pip install -e ".[dev,redis,mongodb]"
```
### Configuration
1. Create a configuration file `config/config.yaml`:
```yaml
databases:
# SQL Database
- name: my_database
type: postgresql
connection:
host: localhost
port: 5432
database: myapp
user: readonly_user
password: ${DB_PASSWORD}
# Redis
- name: cache
type: redis
connection:
host: localhost
port: 6379
db: 0
# Oracle (using service_name)
- name: oracle_db
type: oracle
connection:
host: localhost
port: 1521
service_name: ORCL
user: readonly_user
password: ${ORACLE_PASSWORD}
# ClickHouse
- name: analytics
type: clickhouse
connection:
host: localhost
port: 9000
database: default
user: default
password: ${CLICKHOUSE_PASSWORD}
# protocol: native # or 'http' for port 8123
# KingbaseES (金仓数据库)
- name: kingbase_db
type: kingbase
connection:
host: localhost
port: 54321
database: myapp
user: readonly_user
password: ${KINGBASE_PASSWORD}
# driver: ksycopg2 # use ksycopg2 for native KingbaseES driver
# SQL Server
- name: sqlserver_db
type: sqlserver
connection:
host: localhost
port: 1433
database: myapp
user: readonly_user
password: ${SQLSERVER_PASSWORD}
# driver: pymssql # or 'pyodbc'
# openGauss (Huawei)
- name: opengauss_db
type: opengauss
connection:
host: localhost
port: 5432
database: myapp
user: readonly_user
password: ${OPENGAUSS_PASSWORD}
# TiDB (PingCAP)
- name: tidb_db
type: tidb
connection:
host: localhost
port: 4000
database: myapp
user: readonly_user
password: ${TIDB_PASSWORD}
# MariaDB
- name: mariadb_db
type: mariadb
connection:
host: localhost
port: 3306
database: myapp
user: readonly_user
password: ${MARIADB_PASSWORD}
# MongoDB
- name: docs
type: mongodb
connection:
host: localhost
port: 27017
database: myapp
# Elasticsearch
- name: search
type: elasticsearch
connection:
host: localhost
port: 9200
# scheme: http # or 'https'
# user: elastic
# password: ${ES_PASSWORD}
# api_key: ${ES_API_KEY} # alternative to user/password
# Security settings
security:
blocked_tables:
- users_password
- api_keys
sensitive_columns:
- table: users
columns:
- name: email
mask_strategy: partial
- name: phone
mask_strategy: partial
# Performance limits
limits:
max_rows_per_query: 1000
query_timeout_seconds: 30
max_concurrent_queries: 10
enable_query_cache: true
cache_ttl_seconds: 300
# Logging
logging:
audit_enabled: true
slow_query_threshold_ms: 1000
```
2. Set environment variables:
```bash
export DB_PASSWORD="your_password"
export DB_MCP_CONFIG_PATH="config/config.yaml"
```
### Usage with MCP Clients
#### Start the HTTP Server
```bash
# Start server (default: 0.0.0.0:3001)
python -m db_reader_mcp.server
# Or with custom host/port
export MCP_HOST="0.0.0.0"
export MCP_PORT="3001"
python -m db_reader_mcp.server
# Test health check
curl http://localhost:3001/health
```
#### MCP Client Configuration
Add to your MCP client configuration (e.g., Cherry Studio, Cline, etc.):
**Cherry Studio (streamableHttp):**
```json
{
"mcpServers": {
"db-reader": {
"type": "streamableHttp",
"url": "http://localhost:3001/mcp",
"headers": {
"Content-Type": "application/json"
}
}
}
}
```
**Other MCP Clients:**
```json
{
"mcpServers": {
"db-reader": {
"url": "http://localhost:3001/mcp",
"transport": "http"
}
}
}
```
## Available Tools
### SQL Database Tools
#### 1. query
Execute read-only SQL queries:
```
query(sql="SELECT * FROM users LIMIT 10", database="my_database")
```
#### 2. list_tables
List all tables in the database:
```
list_tables(database="my_database")
```
#### 3. describe_table
Get detailed table structure:
```
describe_table(table_name="users")
```
#### 4. get_schema
Get complete database schema overview:
```
get_schema(database="my_database")
```
#### 5. table_stats
Get table statistics (row count, size, indexes):
```
table_stats(table_name="users")
```
#### 6. sample_data
Get sample data from a table:
```
sample_data(table_name="users", limit=5)
```
### Redis Tools
#### 7. redis_command
Execute read-only Redis commands:
```
redis_command(command="GET", args=["key"], database="cache")
redis_command(command="HGETALL", args=["hash_key"])
redis_command(command="KEYS", args=["pattern*"])
```
Allowed commands: GET, MGET, STRLEN, HGET, HGETALL, HKEYS, HVALS, HLEN, HEXISTS, LRANGE, LLEN, LINDEX, SMEMBERS, SCARD, SISMEMBER, ZRANGE, ZRANGEBYSCORE, ZCARD, ZSCORE, ZRANK, KEYS, EXISTS, TYPE, TTL, PTTL, SCAN, DBSIZE, INFO
### MongoDB Tools
#### 8. mongo_query
Execute read-only MongoDB operations:
```
mongo_query(operation="find", collection="users", query={"active": true}, limit=10)
mongo_query(operation="count_documents", collection="orders", query={"status": "pending"})
mongo_query(operation="aggregate", collection="sales", pipeline=[{"$group": {"_id": "$category", "total": {"$sum": "$amount"}}}])
mongo_query(operation="distinct", collection="products", field="category")
mongo_query(operation="list_collection_names")
```
Allowed operations: find, find_one, count_documents, estimated_document_count, distinct, aggregate, list_collection_names
### Elasticsearch Tools
#### 9. es_query
Execute read-only Elasticsearch operations:
```
es_query(operation="search", index="logs", query={"match": {"message": "error"}}, limit=10)
es_query(operation="get", index="products", doc_id="123")
es_query(operation="count", index="events", query={"range": {"timestamp": {"gte": "now-1d"}}})
es_query(operation="list_indices")
es_query(operation="get_mapping", index="users")
es_query(operation="search", index="sales", body={"size": 0, "aggs": {"total": {"sum": {"field": "amount"}}}})
```
Allowed operations: search, get, count, list_indices, get_mapping, get_settings, exists
## Security
- **SQL Validation**: Blocks all write operations (INSERT, UPDATE, DELETE, DROP, etc.)
- **Table Blocking**: Configure tables that should never be accessible
- **Sensitive Column Masking**: Automatically mask sensitive data (email, phone, etc.)
- Strategies: `null`, `partial`, `hash:N`, `truncate:N`, `first_n:N`, `last_n:N`, `random`
- **Read-Only User**: Use database users with SELECT-only permissions
- **Query Limits**: Automatic row limits (max_rows_per_query) and timeout protection
- **NoSQL Command Whitelist**: Only read-only Redis commands and MongoDB operations allowed
## Monitoring
### Health Check
```bash
curl http://localhost:3001/health
```
Returns:
```json
{
"service": "db-reader-mcp",
"status": "healthy",
"databases": {
"my_database": {
"status": "healthy",
"type": "postgresql",
"message": "Connection OK",
"pool": {
"size": 5,
"checked_in": 5,
"checked_out": 0,
"overflow": 0
}
}
},
"total": 1,
"healthy_count": 1
}
```
### Audit Logging
All queries are logged with:
- Timestamp, database, SQL/command
- Success/failure status
- Row count, execution time
- Slow query tagging (`[SLOW]` for queries exceeding threshold)
## Database Setup
### PostgreSQL
```sql
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
```
### MySQL
```sql
CREATE USER 'mcp_readonly'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON myapp.* TO 'mcp_readonly'@'%';
```
### Oracle
```sql
CREATE USER mcp_readonly IDENTIFIED BY "secure_password";
GRANT CREATE SESSION TO mcp_readonly;
GRANT SELECT ANY TABLE TO mcp_readonly;
-- Or grant on specific tables:
-- GRANT SELECT ON schema.table_name TO mcp_readonly;
```
### ClickHouse
```sql
CREATE USER mcp_readonly IDENTIFIED BY 'secure_password';
GRANT SELECT ON *.* TO mcp_readonly;
-- Or grant on specific database:
-- GRANT SELECT ON analytics.* TO mcp_readonly;
```
### KingbaseES (金仓数据库)
```sql
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
```
### Dameng (DM7/DM8)
```sql
CREATE USER mcp_readonly IDENTIFIED BY "secure_password";
GRANT SELECT ANY TABLE TO mcp_readonly;
```
### SQL Server
```sql
CREATE LOGIN mcp_readonly WITH PASSWORD = 'secure_password';
CREATE USER mcp_readonly FOR LOGIN mcp_readonly;
GRANT SELECT ON SCHEMA::dbo TO mcp_readonly;
-- Or grant on specific tables:
-- GRANT SELECT ON dbo.table_name TO mcp_readonly;
```
### openGauss (Huawei)
```sql
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE myapp TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
```
### TiDB (PingCAP)
```sql
CREATE USER 'mcp_readonly'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON myapp.* TO 'mcp_readonly'@'%';
```
### MariaDB
```sql
CREATE USER 'mcp_readonly'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON myapp.* TO 'mcp_readonly'@'%';
```
## Development
### Setup Development Environment with uv
```bash
# Create virtual environment (if not exists)
uv venv
# Activate virtual environment
source .venv/bin/activate # On Windows: .venv\Scripts\activate
# Install dependencies
uv pip install -e ".[dev]"
```
### Run Tests
```bash
# Run all tests
uv run pytest
# Run specific test file
uv run pytest tests/unit/test_validator.py -v
# Run with coverage
uv run pytest --cov=src/db_reader_mcp --cov-report=html
```
### Code Quality
```bash
# Linting
uv run ruff check .
# Format code
uv run ruff format .
# Type checking
uv run mypy src/
```
### Run Server Locally
```bash
# Using uv
uv run python -m db_reader_mcp.server
# Or after activating venv
python -m db_reader_mcp.server
```
## Project Structure
```
db-reader-mcp/
├── src/db_reader_mcp/
│ ├── server.py # MCP server entry point
│ ├── config/
│ │ └── loader.py # Configuration loader
│ ├── database/
│ │ ├── base.py # DatabaseAdapter abstract base class
│ │ ├── registry.py # AdapterRegistry (type routing)
│ │ ├── coordinator.py # DatabaseCoordinator (cache, timeout, audit)
│ │ └── adapters/
│ │ ├── redis.py # Redis adapter (read-only)
│ │ ├── mongodb.py # MongoDB adapter (read-only)
│ │ ├── elasticsearch.py # Elasticsearch adapter (read-only)
│ │ └── sql/
│ │ ├── base.py # SQLAdapter base (SQLAlchemy)
│ │ ├── postgresql.py
│ │ ├── mysql.py
│ │ ├── sqlite.py
│ │ ├── oracle.py
│ │ ├── clickhouse.py
│ │ ├── kingbase.py
│ │ ├── sqlserver.py
│ │ ├── opengauss.py
│ │ ├── tidb.py
│ │ ├── mariadb.py
│ │ └── dameng.py
│ ├── security/
│ │ ├── validator.py # SQL security validator
│ │ └── masker.py # Sensitive data masker
│ ├── logging/
│ │ └── audit.py # Audit logger
│ ├── utils/
│ │ ├── concurrency.py # Concurrent query limiter
│ │ └── timeout.py # Query timeout handler
│ └── tools/
│ ├── query.py # Query execution tool
│ ├── schema.py # Schema inspection tools
│ ├── redis_tool.py # Redis command tool
│ ├── mongo_tool.py # MongoDB query tool
│ └── es_tool.py # Elasticsearch query tool
├── tests/
│ ├── unit/ # Unit tests
│ └── integration/ # Integration tests
├── config/
│ └── config.example.yaml # Configuration example
└── pyproject.toml # Project configuration
```
## License
MIT License - see LICENSE file for details
## Contributing
Contributions welcome! Please read CONTRIBUTING.md for guidelines.
## Support
- Issues: https://github.com/puppet4/db-reader-mcp/issues
