example_usage.md•6.12 kB
# Universal SQL MCP Server - Usage Examples
This document provides examples of how to configure and use the Universal SQL MCP Server with different database engines.
## Database Configuration Examples
### MySQL Configuration
Create a `.env` file for MySQL:
```env
DB_TYPE=mysql
DB_HOST=localhost
DB_PORT=3306
DB_USER=myuser
DB_PASSWORD=mypassword
DB_NAME=mydatabase
ENABLE_WRITE_OPERATIONS=true
```
### PostgreSQL Configuration
Create a `.env` file for PostgreSQL:
```env
DB_TYPE=postgresql
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=mypassword
DB_NAME=mydatabase
ENABLE_WRITE_OPERATIONS=true
```
### SQLite Configuration
Create a `.env` file for SQLite:
```env
DB_TYPE=sqlite
DB_NAME=./data/mydatabase.db
ENABLE_WRITE_OPERATIONS=true
```
### SQL Server Configuration
Create a `.env` file for SQL Server:
```env
DB_TYPE=sqlserver
DB_HOST=localhost
DB_PORT=1433
DB_USER=sa
DB_PASSWORD=MyStrongPassword123
DB_NAME=mydatabase
DB_DRIVER=ODBC Driver 17 for SQL Server
ENABLE_WRITE_OPERATIONS=true
```
## MCP Tool Usage Examples
### 1. Get Database Schema
This works identically across all database types:
```json
{
"method": "tools/call",
"params": {
"name": "get_database_schema"
}
}
```
**Response Example:**
```json
{
"success": true,
"data": [
{
"table_name": "users",
"table_comment": "User accounts table",
"engine": "MySQL",
"estimated_rows": 1500,
"columns": [
{
"name": "id",
"type": "int",
"nullable": false,
"key": "PRI",
"extra": "auto_increment"
},
{
"name": "email",
"type": "varchar",
"nullable": false,
"max_length": 255
}
],
"indexes": [
{
"name": "PRIMARY",
"unique": true,
"columns": ["id"]
}
]
}
]
}
```
### 2. Execute SELECT Queries
Works with all database types, but syntax may vary:
**MySQL/PostgreSQL/SQL Server:**
```json
{
"method": "tools/call",
"params": {
"name": "execute_sql_query",
"arguments": {
"sql_query": "SELECT id, name, email FROM users WHERE created_at > '2024-01-01' LIMIT 10"
}
}
}
```
**SQLite:**
```json
{
"method": "tools/call",
"params": {
"name": "execute_sql_query",
"arguments": {
"sql_query": "SELECT id, name, email FROM users WHERE created_at > '2024-01-01' LIMIT 10"
}
}
}
```
### 3. Execute Write Operations
#### INSERT Examples
**MySQL:**
```json
{
"method": "tools/call",
"params": {
"name": "execute_write_operation",
"arguments": {
"sql_query": "INSERT INTO users (name, email, created_at) VALUES ('John Doe', 'john@example.com', NOW())"
}
}
}
```
**PostgreSQL with RETURNING:**
```json
{
"method": "tools/call",
"params": {
"name": "execute_write_operation",
"arguments": {
"sql_query": "INSERT INTO users (name, email, created_at) VALUES ('Jane Doe', 'jane@example.com', CURRENT_TIMESTAMP) RETURNING id"
}
}
}
```
**SQLite:**
```json
{
"method": "tools/call",
"params": {
"name": "execute_write_operation",
"arguments": {
"sql_query": "INSERT INTO users (name, email, created_at) VALUES ('Bob Smith', 'bob@example.com', datetime('now'))"
}
}
}
```
**SQL Server:**
```json
{
"method": "tools/call",
"params": {
"name": "execute_write_operation",
"arguments": {
"sql_query": "INSERT INTO users (name, email, created_at) VALUES ('Alice Johnson', 'alice@example.com', GETDATE())"
}
}
}
```
#### UPDATE Examples
**All Database Types:**
```json
{
"method": "tools/call",
"params": {
"name": "execute_write_operation",
"arguments": {
"sql_query": "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = 123"
}
}
}
```
### 4. Test Database Connection
```json
{
"method": "tools/call",
"params": {
"name": "test_database_connection"
}
}
```
**Response Example:**
```json
{
"success": true,
"message": "Database connection test successful",
"database_name": "mydatabase"
}
```
## Database-Specific Features
### MySQL
- Full support for `AUTO_INCREMENT` columns
- Comprehensive index information including index types
- Table engine information (InnoDB, MyISAM, etc.)
- Table and column comments
### PostgreSQL
- Support for `SERIAL` and `BIGSERIAL` columns
- Advanced constraint information
- Schema-qualified table names
- Rich data type support
### SQLite
- Simple and fast for development
- Full-text search capabilities
- No network configuration required
- Perfect for embedded applications
### SQL Server
- Support for `IDENTITY` columns
- Advanced data types
- Windows and SQL Server authentication
- Enterprise-grade features
## Error Handling Examples
### Invalid Query Type
```json
{
"method": "tools/call",
"params": {
"name": "execute_sql_query",
"arguments": {
"sql_query": "DELETE FROM users WHERE id = 1"
}
}
}
```
**Response:**
```json
{
"success": false,
"data": [],
"row_count": 0,
"message": "Only SELECT queries are allowed for security reasons"
}
```
### Connection Error
```json
{
"success": false,
"message": "Database connection test failed: Access denied for user 'wronguser'@'localhost'",
"database_name": "mydatabase"
}
```
## Best Practices
1. **Use Environment Variables**: Never hardcode database credentials
2. **Enable Write Operations Carefully**: Only enable when necessary
3. **Test Connections**: Always test connectivity before deploying
4. **Use Appropriate Data Types**: Leverage database-specific features
5. **Monitor Logs**: Enable request logging for debugging
6. **Secure Connections**: Use SSL/TLS for production databases
## Switching Between Databases
To switch from one database to another, simply update your `.env` file:
```bash
# From MySQL to PostgreSQL
sed -i 's/DB_TYPE=mysql/DB_TYPE=postgresql/' .env
sed -i 's/DB_PORT=3306/DB_PORT=5432/' .env
# Restart the server
python main.py
```
The MCP server will automatically use the appropriate database connector and adapt its queries accordingly.