SmartDB is a universal database gateway that implements the Model Context Protocol (MCP) server interface. This gateway allows MCP-compatible clients to connect and explore different databases. Compared to similar products, SmartDB not only provides basic database connection and exploration capabilities but also adds advanced features such as OAuth 2.0 authentication , health checks, SQL optimizat
Add this skill
npx mdskills install wenb1n-dev/smartdb-mcpComprehensive multi-database MCP gateway with OAuth, health checks, and SQL optimization tools
1[](README-zh.md)2[](README.md)3[](https://mseep.ai/app/wenb1n-dev-smartdb-mcp)45<img width="1023" height="270" alt="image" src="https://github.com/user-attachments/assets/4b282174-dd45-4edb-9de8-9b14e2c59a9e" />678# SmartDB910SmartDB is a universal database gateway that implements the Model Context Protocol (MCP) server interface. This gateway allows MCP-compatible clients to connect and explore different databases.1112Compared to similar products, SmartDB not only provides basic database connection and exploration capabilities but also adds advanced features such as OAuth 2.0 authentication , health checks, SQL optimization, and index health detection, making database management and maintenance more secure and intelligent.1314<img width="1303" height="697" alt="image" src="https://github.com/user-attachments/assets/9340f85e-28b0-45f2-8cb2-768b0d1c8b5c" />151617## Currently Supported Databases18| Database | Support | Description |19|------------|---------|--------------------------|20| MySQL | √ | Supports MySQL 5.6+, MariaDB 10+ |21| PostgreSQL | √ | Supports PostgreSQL 9.6+, YMatrix |22| Oracle | √ | Oracle 12+ |23| SQL Server | √ | Microsoft SQL Server 2012+ |24| Dameng | √ | Dameng 8.0+ |2526## Tool List27| Tool Name | Description |28|-----------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|29| execute_sql | SQL execution tool that can execute ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE", "CREATE", "ALTER", "DROP", "TRUNCATE"] commands based on permission configuration |30| get_db_health | Analyzes database health status (connection status, transaction status, running status, lock detection) and outputs professional diagnostic reports and solutions |31| get_table_desc | Searches for table structures in the database based on table names, supports multi-table queries |32| get_table_index | Searches for table indexes in the database based on table names, supports multi-table queries |33| get_table_name | Database table name query tool. Used to query all table names in the database or search for corresponding table names based on Chinese table names or table descriptions |34| get_db_version | Database version query tool |35| sql_creator | SQL query generation tool that generates corresponding SQL query statements based on different database types |36| sql_optimize | A professional SQL performance optimization tool that provides expert optimization suggestions based on execution plans, table structure information, table data volume, and table indexes. |3738## Usage3940### Environment Configuration File Description41```bash42# Database configuration file path43DATABASE_CONFIG_FILE=/Volumes/SmartDB/src/config/database_config.json4445#========OAuth2========46# OAuth2 client ID47CLIENT_ID=smart_db_client_id48# OAuth2 client secret49CLIENT_SECRET=smart_db_client_secret50# Access token expiration time (minutes)51ACCESS_TOKEN_EXPIRE_MINUTES=3052# Refresh token expiration time (days)53REFRESH_TOKEN_EXPIRE_DAYS=3054# Token encryption key55TOKEN_SECRET_KEY=smart_db_token_secret56# Username57OAUTH_USER_NAME=admin58# Password59OAUTH_USER_PASSWORD=wenb1n60```61Note: If you adjust the client ID and key in the oauth configuration, please also modify the corresponding configuration in the static/config file in the previous code6263### Database Connection Configuration Description64```json65{66 "default": {67 "host": "192.168.xxx.xxx",68 "port": 3306,69 "user": "root",70 "password": "root",71 "database": "a_llm",72 "role": "readonly",73 "pool_size": 10,74 "max_overflow": 20,75 "pool_recycle": 3600,76 "pool_timeout": 30,77 "type": "mysql"78 },79 "postgresql": {80 "host": "192.168.xxx.xxx",81 "port": 5432,82 "user": "postgres",83 "password": "123456",84 "database": "postgres",85 "schema": "public",86 "role": "readonly",87 "pool_size": 5,88 "max_overflow": 10,89 "pool_recycle": 3600,90 "pool_timeout": 30,91 "type": "postgresql"92 },93 "oracle": {94 "host": "192.168.xxx.xxx",95 "port": 1521,96 "user": "U_ORACLE",97 "password": "123456",98 "database": "123456",99 "service_name": "ORCL",100 "role": "readonly",101 "pool_size": 5,102 "max_overflow": 10,103 "pool_recycle": 3600,104 "pool_timeout": 30,105 "type": "oracle"106 },107 "mssql": {108 "host": "192.168.xxx.xxx",109 "port": 1433,110 "user": "test",111 "password": "123456",112 "database": "TEST",113 "schema": "dbo",114 "role": "readonly",115 "pool_size": 5,116 "max_overflow": 10,117 "pool_recycle": 3600,118 "pool_timeout": 30,119 "type": "mssqlserver"120 }121}122```123124* Database Connection Parameter Description125126The following table details the meaning and usage of each parameter in the database connection configuration file:127128| Parameter | Required | Type | Description |129|-----------|----------|------|-------------|130| host | Yes | string | Database server address |131| port | Yes | integer | Database server port number |132| user | Yes | string | Database username |133| password | Yes | string | Database user password |134| database | Yes | string | Database name to connect to |135| role | Yes | string | User role, such as "readonly" for read-only permissions |136| pool_size | Yes | integer | Connection pool size |137| max_overflow | Yes | integer | Maximum overflow connections in connection pool |138| pool_recycle | Yes | integer | Connection pool recycle time (seconds) |139| pool_timeout | Yes | integer | Connection pool timeout time (seconds) |140| type | Yes | string | Database type, such as "mysql", "postgresql", "oracle", "mssqlserver" |141142* Additional Parameters for Specific Databases143144| Parameter | Database Type | Required | Type | Description |145|-----------|---------------|----------|------|-------------|146| schema | PostgreSQL, SQL Server | No | string | Database schema |147| service_name | Oracle | No | string | Oracle service name |148149* role permission control configuration items and corresponding database permissions: readonly (readonly), read/write (writer), administrator (admin)150```151 "readonly": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN"], # readonly permission152 "writer": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE"], # read/write permission153 "admin": ["SELECT", "SHOW", "DESCRIBE", "EXPLAIN", "INSERT", "UPDATE", "DELETE",154 "CREATE", "ALTER", "DROP", "TRUNCATE"] # administrator permission155```156157* Note158159"default" is the default database connection configuration and must be configured. Other database configurations should be added as needed.160161## pip installation and configuration162163```bash164pip install SmartDB-MCP165166Parameter explanation167--mode: transmission mode ("stdio", "sse", "streamablehttp")168--envfile path of the environment variable file169--oauth enable oauth authentication (currently only supported in "streamablehttp" mode)170171Start command:172 smartdb --envfile=/Volumes/config/.env --oauth=true173174175```176177## Docker Startup178179### Quick Start180181#### 1. Build and Start Service182183```bash184# Start service using docker-compose185docker-compose up -d186187# Check service status188docker-compose ps189190# View logs191docker-compose logs -f smartdb192```193194#### 2. Manual Image Building195196```bash197# Build image198docker build -t smartdb-mcp:latest .199200# Run container201docker run -d \202 --name smartdb-mcp-server \203 -p 3000:3000 \204 -e DATABASE_CONFIG_FILE=/app/src/config/database_config.json \205 -e CLIENT_ID=smart_db_client_id \206 -e CLIENT_SECRET=smart_db_client_secret \207 -e TOKEN_SECRET_KEY=your_secret_key \208 -v $(pwd)/src/config:/app/src/config:ro \209 -v $(pwd)/logs:/app/logs \210 smartdb-mcp:latest211```212213## Code Startup214215### Local Development Streamable Http Mode216217- Start service using uv218219Add the following content to your MCP client tools, such as cursor, cline, etc.220221MCP JSON as follows:222```json223{224 "mcpServers": {225 "smartdb": {226 "name": "smartdb",227 "type": "streamableHttp",228 "description": "",229 "isActive": true,230 "url": "http://localhost:3000/mcp/"231 }232 }233}234```235236Start command:237```bash238# Download dependencies239uv sync240241# Start242uv run -m core.server243244# Custom env file location245uv run -m core.server --envfile /path/to/.env246```247248### Local Development SSE Mode249250- Start service using uv251252Add the following content to your MCP client tools, such as cursor, cline, etc.253254MCP JSON as follows:255```json256{257 "mcpServers": {258 "smartdb": {259 "name": "smartdb",260 "description": "",261 "isActive": true,262 "url": "http://localhost:3000/sse"263 }264 }265}266```267268Start command:269```bash270# Download dependencies271uv sync272273# Start274uv run -m core.server --mode sse275276# Custom env file location277uv run -m core.server --mode sse --envfile /path/to/.env278```279280### Local Development STDIO Mode281282Add the following content to your MCP client tools, such as cursor, cline, etc.283284MCP JSON as follows:285```json286{287 "mcpServers": {288 "smartdb": {289 "name": "smartdb",290 "type": "stdio",291 "isActive": false,292 "registryUrl": "",293 "command": "uv",294 "args": [295 "--directory",296 "/Volumes/python/SmartDB/",297 "run",298 "-m",299 "core.server",300 "--mode",301 "stdio"302 ],303 "env": {304 "DATABASE_CONFIG_FILE": "/Volumes/database_config.json"305 }306 }307 }308 }309}310```311312## OAuth 2.0 Authentication Support3133141. Start authentication service. By default, it uses the built-in OAuth 2.0 password mode authentication. You can modify your own authentication service address in the env file.315```bash316uv run -m core.server --oauth=true317```3183192. Access the authentication service at http://localhost:3000/login. Default account and password are configured in the env file.320<img width="1777" height="950" alt="image" src="https://github.com/user-attachments/assets/20531bee-467f-4758-bc08-fddc086ed411" />3213223233. Copy the token and add it to the request header, for example:324<img width="1838" height="1021" alt="image" src="https://github.com/user-attachments/assets/df911d7a-d3d0-44dc-b3c6-58607ff3807d" />325326327```json328{329 "mcpServers": {330 "smartdb": {331 "name": "smartdb",332 "type": "streamableHttp",333 "description": "",334 "isActive": true,335 "url": "http://localhost:3000/mcp/",336 "headers": {337 "authorization": "bearer TOKEN_VALUE"338 }339 }340 }341}342```343344## Usage Examples3451. Query the table data of the default connection pool346<img width="1131" height="1112" alt="image" src="https://github.com/user-attachments/assets/a858a38a-c57e-47a6-8f74-2458266859ac" />3473482. Query the table data of the others connection pool349<img width="1144" height="728" alt="image" src="https://github.com/user-attachments/assets/ad619912-9aad-4e10-b4aa-b1de521390c2" />3503513. Query data from tables in other connection pools and other databases352<img width="1128" height="586" alt="image" src="https://github.com/user-attachments/assets/f110f00f-319b-4754-ae4c-32fee0d2a44d" />3533544. Query database health status355<img width="1140" height="2560" alt="image" src="https://github.com/user-attachments/assets/fa55271c-94ad-4079-a9eb-c68a8e607111" />3563575. Sql Optimize358<img width="1554" height="3613" alt="image" src="https://github.com/user-attachments/assets/58d9c835-160c-44b3-b97c-0e46830ea438" />359360361362
Full transparency — inspect the skill content before installing.