example_usage.md

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:

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:

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:

DB_TYPE=sqlite
DB_NAME=./data/mydatabase.db
ENABLE_WRITE_OPERATIONS=true

SQL Server Configuration

Create a .env file for SQL Server:

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:

{
  "method": "tools/call",
  "params": {
    "name": "get_database_schema"
  }
}

Response Example:

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

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

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

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

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

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

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

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

{
  "method": "tools/call",
  "params": {
    "name": "test_database_connection"
  }
}

Response Example:

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

{
  "method": "tools/call",
  "params": {
    "name": "execute_sql_query",
    "arguments": {
      "sql_query": "DELETE FROM users WHERE id = 1"
    }
  }
}

Response:

{
  "success": false,
  "data": [],
  "row_count": 0,
  "message": "Only SELECT queries are allowed for security reasons"
}

Connection Error

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

# 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.