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:

Running the Server with Mock Database

For development or testing without a live database connection:

Testing Connection

To test the connection with both real and mock database:

Natural Language Testing

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

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

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:

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.