Yazdani-Supabase-MCP-Server

Yazdani-Supabase-MCP-Server

An MCP server that integrates with Supabase PostgreSQL and uses Claude 3.7 for natural language query processing, designed for integration with React Native PaveApp.

Features

• Schema Inspection: View database schemas, tables, columns, and relationships
• SQL Execution: Run SQL queries with safety controls
• Migration Management: View and manage database migrations
• Natural Language Processing: Convert natural language to SQL using Claude 3.7
• Safety Controls: Protection against destructive operations
• Mock Database Mode: Support for development and testing without a live database connection
• Auto-Fallback Mechanism: Gracefully falls back to mock data when database connection fails
• Session Pooler Support: Uses Supabase Session pooler for stable connections

Installation

Prerequisites

• Python 3.8 or higher
• Access to a Supabase PostgreSQL database (optional with mock mode)
• Anthropic API key (for Claude integration)

Setup

1. Clone the repository:

   git clone <repository-url>
   cd Yazdani-supabase-mcp-server
   

2. Install dependencies:

   pip install -r requirements.txt
   

3. Create a .env file with your credentials:

   # Supabase credentials
   SUPABASE_PROJECT_REF=your_project_ref
   SUPABASE_URL=https://your_project_ref.supabase.co
   SUPABASE_DB_PASSWORD=your_db_password
   SUPABASE_REGION=your_region
   SUPABASE_ACCESS_TOKEN=your_access_token
   SUPABASE_SERVICE_ROLE_KEY=your_service_role_key
   
   # Database connection
   POSTGRES_HOST=db.your_project_ref.supabase.co
   POSTGRES_PORT=5432
   POSTGRES_DB=postgres
   POSTGRES_USER=postgres
   SUPABASE_POOLER_HOST=aws-0-ca-central-1.pooler.supabase.com
   
   # Claude API
   ANTHROPIC_API_KEY=your_anthropic_api_key
   
   # Optional: for mock database mode
   # USE_MOCK_DB=true
   

Usage

Running the Server with Live Database

For normal operation with a Supabase database connection:

python run_server.py

Running the Server with Mock Database

For development or testing without a live database connection:

# Windows
improved_mock_server.bat

# Unix/macOS
USE_MOCK_DB=true python run_server.py

Testing Connection

To test the connection with both real and mock database:

python connection_test.py

Natural Language Testing

We've added a powerful natural language testing environment that allows you to test the MCP server with natural language queries:

# Windows - Run the full test environment (HTTP server + NL client)
run_nl_test.bat

# Manual setup
python http_server.py --port 8000   # Terminal 1
python nl_test_client.py            # Terminal 2

The natural language test client provides:

• Interactive command-line interface for testing
• Natural language to SQL conversion using Claude
• SQL execution against the Supabase database
• Formatted result display
• Command history and result saving capabilities

Sample natural language queries to try:

• "Show me all tables in the public schema"
• "Get all users who signed up in the last month"
• "What is the total number of users in the database?"

Installing as a Package

pip install -e .
yazdani-supabase-mcp-server

Integration with React Native PaveApp

Setting Up MCP Server for React Native

1. Run the server:

   • For development: Use mock mode with improved_mock_server.bat
   • For production: Ensure database credentials are correct and run run_server.py

2. Make the server accessible:

   • For local development: Ensure React Native can access your local network
   • For production: Host the server where it can be accessed by your app

3. Connect from React Native:

   • Use the MCP client libraries to connect to the server
   • Configure the client with the server's address and port

API Endpoints

The MCP server exposes the following tools that can be accessed through the MCP protocol:

• get_schemas: List all database schemas
• get_tables: List tables in a schema
• get_table_schema: Get detailed table structure
• execute_postgresql: Execute SQL statements
• retrieve_migrations: List database migrations
• generate_sql: Generate SQL from natural language

Troubleshooting

Connection Details

The server uses the Supabase Session pooler for database connections:

postgresql://postgres.{project_ref}:{password}@{pooler_host}:5432/postgres

This connection format is recommended by Supabase for applications that maintain persistent connections, especially on IPv4 networks.

Connection Issues

If you're experiencing connection issues with Supabase:

1. Verify your Supabase project is active
2. Check that your database credentials are correct in the .env file
3. Ensure your IP address is allowed in Supabase database settings
4. Verify you're using the correct pooler host for your region
5. Try using the mock database mode: USE_MOCK_DB=true
6. Run python test_pooler_connection.py to test the pooler connection specifically
7. Run python connection_test.py to run a comprehensive connection test

Mock Database Mode

The server now includes improved mock database support:

• Auto-fallback to mock data when connection fails
• Consistent interface between real and mock data
• Clear indication when mock data is being used
• Improved logging for troubleshooting

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is licensed under the MIT License - see the LICENSE file for details.