MCP server for inspecting MySQL and PostgreSQL database schemas and structure
A powerful Model Context Protocol (MCP) server for inspecting MySQL and PostgreSQL database schemas, relationships, and structure. This tool provides AI assistants with comprehensive database introspection capabilities while maintaining strict read-only access for security.
- Multi-Database Support: Connect to MySQL and PostgreSQL databases simultaneously
- SQL Query Analysis: Analyze query performance with EXPLAIN and get optimization recommendations
- Read-Only Database Inspection: Secure schema exploration without modification risks
- Comprehensive Schema Analysis: Detailed table, column, index, and relationship information
- Foreign Key Relationship Mapping: Understand data relationships across tables
- Index Performance Analysis: Identify optimization opportunities
- Security-First Design: Query validation, input sanitization, and audit logging
- Cross-Database Awareness: Analyze relationships across multiple database instances
- Node.js 18 or higher
- MySQL 5.7+ or PostgreSQL 12+ (or compatible databases)
- Network access to target database(s)
- Valid database credentials with SELECT permissions
bash
npm install -g mcp-database-inspector
`$3
bash
git clone https://github.com/kokorolx/mcp-mysql-inspector.git
cd mcp-mysql-inspector
npm install
npm run build
`$3
#### Roo Configuration
Create
:
`json
{
"servers": {
"database-inspector": {
"command": "npx",
"args": [
"mcp-database-inspector",
"mysql://dev:password@localhost:3306/ecommerce",
"postgresql://dev:password@localhost:5432/analytics"
]
}
}
}
`#### Claude Desktop Configuration
Add to
:
`json
{
"mcpServers": {
"database-inspector": {
"command": "npx",
"args": [
"-y",
"mcp-database-inspector",
"mysql://username:password@localhost:3306/database1",
"postgresql://username:password@localhost:5432/database2"
]
}
}
}
`🛠 Available Tools
$3
Lists all connected databases with connection status and type (MySQL/PostgreSQL).$3
Lists all tables in a specified database with metadata.$3
Get complete table schema including columns, types, constraints, and metadata.
Supports both single-table and multi-table inspection via the (string) or tables (string[]) parameter.Parameters:
-
database (string, required): Name of the database.
- table (string, optional): Name of a single table to inspect.
- tables (string[], optional): Array of table names to inspect (multi-table mode).Examples:
`json
{ "database": "mydb", "table": "users" }
{ "database": "mydb", "tables": ["users", "orders", "products"] }
`$3
Get foreign key relationships for one or more tables, or the entire database.
Supports both single-table and multi-table inspection.Parameters:
-
(string, required): Name of the database.
- table (string, optional): Name of a single table to analyze.
- tables (string[], optional): Array of table names to analyze (multi-table mode).Examples:
`json
{ "database": "mydb", "table": "orders" }
{ "database": "mydb", "tables": ["orders", "order_items"] }
`$3
Get detailed index information for one or more tables.
Supports both single-table and multi-table inspection.Parameters:
-
(string, required): Name of the database.
- table (string, optional): Name of a single table to analyze.
- tables (string[], optional): Array of table names to analyze (multi-table mode).Examples:
`json
{ "database": "mydb", "table": "products" }
{ "database": "mydb", "tables": ["products", "categories"] }
`$3
Analyze SQL query performance using and get optimization recommendations.Parameters:
-
database (string, required): Name of the database to run the analysis against.
- query (string, required): The SQL query to analyze.Example:
`json
{
"database": "mydb",
"query": "SELECT * FROM users WHERE email = 'test@example.com'"
}
`Response includes:
- Query cost estimation
- Execution plan operations
- Potential performance issues (e.g., full table scans)
- Actionable recommendations
$3
Execute safe, read-only SQL queries with automatic validation and row limits.Parameters:
-
(string, required): Name of the database.
- query (string, required): The SQL query to execute.
- limit (number, optional): Maximum rows to return (default: 1000, max: 10000).Example:
`json
{
"database": "mydb",
"query": "SELECT id, name FROM users WHERE active = true",
"limit": 100
}
`$3
Execute safe SELECT-only SQL queries with enhanced security validation. This tool enforces stricter security by only allowing SELECT statements with automatic row limits.Parameters:
-
(string, required): Name of the database.
- query (string, required): The SELECT SQL query to execute.
- limit (number, optional): Maximum rows to return (default: 1000, max: 1000).Example:
`json
{
"database": "mydb",
"query": "SELECT id, name FROM users WHERE active = true",
"limit": 100
}
`$3
Query INFORMATION_SCHEMA tables with filters and limits.Parameters:
-
(string, required): Name of the database.
- table (string, required): INFORMATION_SCHEMA table (COLUMNS, TABLES, or ROUTINES).
- filters (object, optional): Key-value filters for WHERE clause.
- limit (number, optional): Maximum rows to return (default: 100, max: 1000).🔒 Security Features
$3
- Whitelist-Only Approach: Only SELECT, SHOW, DESCRIBE, EXPLAIN queries allowed
- SQL Injection Prevention: Multi-layer validation and parameter binding
- Row Limits: Automatic limits to prevent resource exhaustion
- Timeout Protection: Query timeouts enforced$3
- URL Validation: Comprehensive connection string validation
- Identifier Sanitization: Database identifier format validation
- Parameter Sanitization: Input cleaning and null-byte removal$3
- Comprehensive Logging: All operations logged with configurable levels
- Sensitive Data Masking: Credentials automatically redacted from logs
- Performance Monitoring: Query timing and resource usage tracking⚙️ Configuration
$3
| Variable | Description | Default | Example |
|----------|-------------|---------|----------|
|
LOG_LEVEL | Logging level (error, warn, info, debug, trace) | info | LOG_LEVEL=debug |$3
MySQL:
`
mysql://username:password@hostname:port/database?ssl=true
PostgreSQL:
postgresql://username:password@hostname:port/database?ssl=true
postgres://username:password@hostname:port/database
Supported Options:
-
- Enable/disable SSL connections🚨 Troubleshooting
$3
#### Connection Failures
`bash
Test MySQL connection
mysql -h hostname -u username -p database_nameTest PostgreSQL connection
psql -h hostname -U username -d database_nameCheck network connectivity
telnet hostname 3306 # MySQL
telnet hostname 5432 # PostgreSQL
`#### Permission Errors
MySQL:
sql
GRANT SELECT ON database_name.* TO 'username'@'%';
GRANT SELECT ON INFORMATION_SCHEMA.* TO 'username'@'%';
`PostgreSQL:
sql
GRANT CONNECT ON DATABASE database_name TO username;
GRANT USAGE ON SCHEMA public TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
`$3
Enable detailed logging:
bash
LOG_LEVEL=debug npx mcp-database-inspector "mysql://..." "postgresql://..."
``This project is licensed under the MIT License - see the LICENSE file for details.