Supabase Mcp

Supabase MCP: How to Use Supabase MCP Servers

In the rapidly evolving landscape of AI-assisted development, the integration of database technologies with large language models represents a significant advancement in how applications store, retrieve, and manipulate data. Supabase MCP (Model Context Protocol) servers establish a secure bridge between conversational AI models and Supabase's comprehensive PostgreSQL-based platform, enabling AI assistants to execute queries, manipulate data, manage authentication, and interact with storage—all through natural language interfaces. This technical implementation transforms AI assistants from simple text generators into powerful database operators capable of performing complex database operations while maintaining appropriate security controls and access policies.

Introduction

The Model Context Protocol provides a standardized framework for connecting AI models with external tools and data sources. Supabase MCP servers implement this protocol to create a secure, authenticated interface between AI assistants and Supabase's PostgreSQL database ecosystem. By leveraging Supabase's comprehensive REST and JavaScript APIs, MCP servers enable language models to programmatically access and manipulate database records, execute SQL queries, manage users and authentication, handle storage operations, and utilize serverless functions—all while maintaining robust security controls and proper row-level security policies.

https://github.com/supabase-community/supabase-mcp (opens in a new tab)

Technical Architecture of Supabase MCP

MCP Protocol Foundation

Supabase MCP operates within the Model Context Protocol architecture, which defines several key components:

  1. Transport Layer:

    • STDIO (Standard Input/Output): Direct process communication
    • SSE (Server-Sent Events): HTTP-based asynchronous communication
    • WebSocket: Bidirectional real-time communication

  2. Resource Types:

    • Prompts: Templated database operation patterns
    • Tools: Executable Supabase operations
    • Resources: Database schema and record information

  3. Serialization Format: JSON for structured data exchange between client and server

Supabase MCP Architecture

The Supabase MCP server implements a multi-layered architecture optimized for database operations:

supabase-mcp/
├── src/
│   ├── auth/
│   │   ├── credential-manager.ts   # Supabase credentials management
│   │   ├── policy-validator.ts     # RLS policy validation
│   │   └── jwt-handler.ts          # JWT token management
│   ├── services/
│   │   ├── database-service.ts     # PostgreSQL operations
│   │   ├── auth-service.ts         # Auth operations
│   │   ├── storage-service.ts      # Storage operations
│   │   └── functions-service.ts    # Edge Functions operations
│   ├── tools/
│   │   ├── query-tools.ts          # Database querying tools
│   │   ├── mutation-tools.ts       # Data manipulation tools
│   │   ├── schema-tools.ts         # Schema inspection tools
│   │   └── auth-tools.ts           # User management tools
│   ├── resources/
│   │   ├── schema-resources.ts     # Database schema resources
│   │   └── tables-resources.ts     # Table metadata resources
│   └── server.ts                   # MCP server implementation
├── config/
│   └── supabase-config.ts          # Configuration schema
└── package.json                    # Dependencies and scripts

The core technical components include:

  1. Supabase Client Manager: Manages authenticated Supabase client instances
  2. SQL Query Parser: Validates and sanitizes SQL queries for security
  3. Policy Enforcer: Ensures operations comply with Supabase RLS policies
  4. Tool Implementations: Translates MCP commands to Supabase API calls
  5. Schema Inspector: Introspects and caches database schema information

Setup and Installation

Prerequisites

To implement Supabase MCP, ensure you have:

  1. Node.js 16+ environment
  2. Supabase project with API credentials
  3. Properly configured Row Level Security policies
  4. An MCP-compatible client (e.g., Claude Desktop, Cursor, VS Code)

Authentication Configuration

Before installation, obtain the required Supabase credentials:

  1. Create or access your Supabase project at https://supabase.com (opens in a new tab)
  2. Navigate to Project Settings > API
  3. Copy the anon public key and URL
  4. For admin operations, obtain the service_role key (use with caution)

Installation Methods

Option 1: Using npm

npm install -g supabase-mcp

Option 2: Using Smithery

npx -y @smithery/cli install supabase-mcp --client claude

Option 3: Manual Installation from Source

git clone https://github.com/supabase-community/supabase-mcp
cd supabase-mcp
npm install
npm run build

Configuration

The Supabase MCP server requires specific configuration parameters:

  1. API Credentials:

    • SUPABASE_URL: Your Supabase project URL
    • SUPABASE_ANON_KEY: Public anon key (for limited operations)
    • SUPABASE_SERVICE_ROLE_KEY: Admin key (optional, for privileged operations)

  2. Security Controls:

    • SUPABASE_ALLOW_RAW_SQL: Enable/disable raw SQL execution (true/false)
    • SUPABASE_MAX_ROWS: Maximum rows returned from any query
    • SUPABASE_ALLOWED_TABLES: Comma-separated allowlist of accessible tables

  3. Operational Settings:

    • SUPABASE_SCHEMA_CACHE_TTL: Schema cache duration in seconds
    • SUPABASE_REQUEST_TIMEOUT: API request timeout in milliseconds
    • SUPABASE_USE_REALTIME: Enable/disable realtime subscription features

Example configuration in supabase-config.json:

{
  "api": {
    "url": "https://your-project-id.supabase.co",
    "anonKey": "your-anon-key",
    "serviceRoleKey": "your-service-role-key",
    "timeout": 30000
  },
  "security": {
    "allowRawSQL": false,
    "maxRows": 1000,
    "allowedTables": ["public.users", "public.posts", "public.comments"],
    "allowedSchemas": ["public", "storage"]
  },
  "features": {
    "enableAuth": true,
    "enableStorage": true,
    "enableFunctions": true,
    "enableRealtime": false
  },
  "performance": {
    "schemaCacheTTL": 3600,
    "useTransactions": true
  }
}

Integration with MCP Clients

Claude Desktop Integration

To integrate with Claude Desktop, edit the configuration file:

  • macOS: ~/Library/Application\ Support/Claude/claude_desktop_config.json
  • Windows: %APPDATA%/Claude/claude_desktop_config.json

Add the following configuration:

{
  "mcpServers": {
    "supabase": {
      "command": "npx",
      "args": ["-y", "supabase-mcp"],
      "env": {
        "SUPABASE_URL": "https://your-project-id.supabase.co",
        "SUPABASE_ANON_KEY": "your-anon-key",
        "SUPABASE_ALLOWED_TABLES": "public.users,public.posts,public.comments"
      }
    }
  }
}

VS Code Integration

For VS Code with GitHub Copilot, add to settings.json:

{
  "github.copilot.chat.mcpServers": [
    {
      "name": "supabase",
      "command": "npx",
      "args": ["-y", "supabase-mcp"],
      "env": {
        "SUPABASE_URL": "https://your-project-id.supabase.co",
        "SUPABASE_ANON_KEY": "your-anon-key"
      }
    }
  ]
}

Core Functionality and Technical Usage

Available Tools

The Supabase MCP server exposes several specialized tools across Supabase's feature set:

1. Database Operations

  • supabase_query: Executes parameterized queries

    interface QueryOptions {
  table: string;
  select?: string;
  filters?: {
    column: string;
    operator: 'eq' | 'neq' | 'gt' | 'gte' | 'lt' | 'lte' | 'like' | 'ilike' | 'in' | 'is';
    value: any;
  }[];
  order?: {
    column: string;
    ascending?: boolean;
  };
  limit?: number;
  offset?: number;
  count?: 'exact' | 'planned' | 'estimated';
}

  • supabase_insert: Inserts records

    interface InsertOptions {
  table: string;
  records: Record<string, any>[];
  onConflict?: string;
  returning?: string;
}

  • supabase_update: Updates records

    interface UpdateOptions {
  table: string;
  data: Record<string, any>;
  filters: {
    column: string;
    operator: 'eq' | 'neq' | 'gt' | 'gte' | 'lt' | 'lte';
    value: any;
  }[];
  returning?: string;
}

  • supabase_delete: Deletes records

    interface DeleteOptions {
  table: string;
  filters: {
    column: string;
    operator: 'eq' | 'neq' | 'gt' | 'gte' | 'lt' | 'lte';
    value: any;
  }[];
  returning?: string;
}

  • supabase_execute_sql: Executes raw SQL (if enabled)

    interface ExecuteSqlOptions {
  sql: string;
  params?: any[];
}

2. Schema Operations

  • supabase_get_tables: Lists tables with metadata

    interface GetTablesOptions {
  schema?: string;
  includeColumns?: boolean;
  includeRelationships?: boolean;
  includePolicies?: boolean;
}

  • supabase_get_table_definition: Gets detailed table structure

    interface GetTableDefinitionOptions {
  table: string;
  schema?: string;
}

3. Authentication Operations

  • supabase_signup_user: Creates a new user

    interface SignupUserOptions {
  email: string;
  password: string;
  metadata?: Record<string, any>;
  autoConfirm?: boolean;
}

  • supabase_login_user: Authenticates a user

    interface LoginUserOptions {
  email: string;
  password: string;
}

  • supabase_get_user: Retrieves user information

    interface GetUserOptions {
  userId?: string;
  email?: string;
}

4. Storage Operations

  • supabase_list_buckets: Lists storage buckets

    interface ListBucketsOptions {
  includeSizes?: boolean;
}

  • supabase_list_files: Lists files in a bucket

    interface ListFilesOptions {
  bucket: string;
  path?: string;
  limit?: number;
  offset?: number;
  sortBy?: {
    column: 'name' | 'created_at' | 'updated_at' | 'size';
    order: 'asc' | 'desc';
  };
}

  • supabase_get_file_url: Generates file URLs

    interface GetFileUrlOptions {
  bucket: string;
  path: string;
  transform?: {
    width?: number;
    height?: number;
    resize?: 'cover' | 'contain' | 'fill';
    format?: 'origin' | 'webp' | 'jpg' | 'png' | 'avif';
    quality?: number;
  };
  download?: boolean;
  expiresIn?: number;
}

Technical Usage Patterns

Multi-step Database Operations

// Query users with specific criteria
const activeUsers = await tools.supabase_query({
  table: "users",
  select: "id, username, email, last_login_at",
  filters: [
    { column: "is_active", operator: "eq", value: true },
    { column: "last_login_at", operator: "gte", value: "2023-01-01T00:00:00Z" }
  ],
  order: {
    column: "last_login_at",
    ascending: false
  },
  limit: 10
});
 
// Get post counts for these users
const userIds = activeUsers.data.map(user => user.id);
 
const postCounts = await tools.supabase_execute_sql({
  sql: `
    SELECT user_id, COUNT(*) as post_count 
    FROM posts 
    WHERE user_id IN (${userIds.map((_, i) => `$${i+1}`).join(',')})
    GROUP BY user_id
  `,
  params: userIds
});
 
// Create a summary table with combined data
const summaryRecords = activeUsers.data.map(user => {
  const userPostCount = postCounts.data.find(row => row.user_id === user.id);
  return {
    user_id: user.id,
    username: user.username,
    email: user.email,
    last_login_at: user.last_login_at,
    post_count: userPostCount ? userPostCount.post_count : 0,
    report_generated_at: new Date().toISOString()
  };
});
 
await tools.supabase_insert({
  table: "user_activity_reports",
  records: summaryRecords,
  onConflict: "user_id",
  returning: "id"
});

User Registration and Profile Setup

// Register a new user
const newUser = await tools.supabase_signup_user({
  email: "user@example.com",
  password: "securePassword123",
  metadata: {
    full_name: "John Doe",
    signup_source: "website"
  }
});
 
// Create a user profile
await tools.supabase_insert({
  table: "profiles",
  records: [{
    id: newUser.user.id,
    username: "johndoe",
    bio: "New user",
    avatar_url: null,
    created_at: new Date().toISOString()
  }]
});
 
// Upload a default avatar
const avatarUpload = await tools.supabase_upload_file({
  bucket: "avatars",
  path: `${newUser.user.id}/avatar.png`,
  file: defaultAvatarBuffer, // A Buffer containing image data
  contentType: "image/png"
});
 
// Update profile with avatar URL
await tools.supabase_update({
  table: "profiles",
  data: {
    avatar_url: avatarUpload.path
  },
  filters: [
    { column: "id", operator: "eq", value: newUser.user.id }
  ]
});
 
// Generate a public URL for the avatar
const avatarUrl = await tools.supabase_get_file_url({
  bucket: "avatars",
  path: `${newUser.user.id}/avatar.png`,
  transform: {
    width: 150,
    height: 150,
    resize: "cover",
    format: "webp",
    quality: 80
  }
});

Advanced Implementation Considerations

Row-Level Security Integration

Supabase MCP respects PostgreSQL's Row-Level Security policies:

class RlsPolicyEnforcer {
  async validateOperation(
    operation: DatabaseOperation,
    table: string,
    user: User | null
  ): Promise<boolean> {
    // Get table RLS policies
    const policies = await this.getTablePolicies(table);
    
    if (!policies.length) {
      // If no policies, table requires admin rights
      return this.hasAdminAccess();
    }
    
    // Find applicable policies for this operation
    const applicablePolicies = policies.filter(policy => 
      policy.command === operation || policy.command === 'ALL'
    );
    
    if (!applicablePolicies.length) {
      return false; // No applicable policies
    }
    
    // Check user roles against policy roles
    // This is simplified; actual RLS is enforced by Postgres
    if (user) {
      const userRoles = await this.getUserRoles(user.id);
      const hasAccess = applicablePolicies.some(policy => 
        !policy.roles.length || // Public policy
        policy.roles.some(role => userRoles.includes(role))
      );
      
      return hasAccess;
    }
    
    // For anonymous access, check for public policies
    return applicablePolicies.some(policy => policy.roles.length === 0);
  }
}

Query Sanitization and Validation

To prevent SQL injection and ensure query safety:

class QueryValidator {
  validateSqlQuery(sql: string): { isValid: boolean, error?: string } {
    // Check for disallowed SQL statements
    const disallowedPatterns = [
      /DROP\s+/i,
      /ALTER\s+/i,
      /TRUNCATE\s+/i,
      /GRANT\s+/i,
      /REVOKE\s+/i,
      /CONNECT\s+/i,
      /CREATE\s+(DATABASE|SCHEMA|ROLE)/i,
      /pg_/i, // Prevent access to system tables
      /information_schema/i,
    ];
    
    for (const pattern of disallowedPatterns) {
      if (pattern.test(sql)) {
        return {
          isValid: false,
          error: `Query contains disallowed SQL pattern: ${pattern.toString()}`
        };
      }
    }
    
    // Validate table access
    const tableMatches = sql.match(/FROM\s+([a-zA-Z0-9_\.,"]+)/gi);
    if (tableMatches) {
      for (const match of tableMatches) {
        const tableRef = match.replace(/FROM\s+/i, '').trim();
        if (!this.isTableAllowed(tableRef)) {
          return {
            isValid: false,
            error: `Access to table ${tableRef} is not allowed`
          };
        }
      }
    }
    
    return { isValid: true };
  }
  
  private isTableAllowed(tableRef: string): boolean {
    // Implementation to check against allowed tables
    return true; // Simplified
  }
}

Troubleshooting Common Technical Issues

Authentication Issues

If experiencing authentication problems:

  1. Verify API credentials validity in the Supabase dashboard
  2. Check for JWT token expiration (default is 3600 seconds)
  3. Ensure proper Row-Level Security policies are in place
async function troubleshootAuth(): Promise<AuthDiagnostics> {
  // Test anonymous access
  try {
    const { data: anonData, error: anonError } = await supabase
      .from('public_table')
      .select('count(*)')
      .limit(1);
      
    // Test authenticated access
    const { data: authData, error: authError } = await supabaseAdmin
      .from('restricted_table')
      .select('count(*)')
      .limit(1);
      
    return {
      anonAccessWorking: !anonError,
      anonError: anonError?.message,
      authAccessWorking: !authError,
      authError: authError?.message,
      jwt: await getDecodedJwtPayload()
    };
  } catch (error) {
    return {
      error: error.message,
      connectionWorking: false
    };
  }
}

Query Performance Optimization

For handling large datasets efficiently:

class QueryOptimizer {
  optimizeQuery(options: QueryOptions): QueryOptions {
    const optimized = { ...options };
    
    // Add pagination if not specified
    if (!optimized.limit && !optimized.offset) {
      optimized.limit = 100; // Default page size
    }
    
    // Add selective fields if not specified
    if (!optimized.select) {
      // Get recommended fields from schema (e.g., no large text/blob fields)
      optimized.select = this.getRecommendedFields(optimized.table);
    }
    
    // Add index-based ordering if available
    if (!optimized.order && this.hasIdColumn(optimized.table)) {
      optimized.order = {
        column: 'id',
        ascending: false
      };
    }
    
    return optimized;
  }
  
  private getRecommendedFields(table: string): string {
    // Implementation to get optimized field selection
    return '*'; // Simplified
  }
  
  private hasIdColumn(table: string): boolean {
    // Check if table has id column
    return true; // Simplified
  }
}

Connection Pooling

To efficiently manage database connections:

class SupabaseClientPool {
  private clients: Map<string, SupabaseClient> = new Map();
  private maxClients: number = 10;
  private cleanupInterval: NodeJS.Timeout;
  
  constructor() {
    // Periodically clean up inactive clients
    this.cleanupInterval = setInterval(() => this.cleanup(), 5 * 60 * 1000);
  }
  
  getClient(key: string = 'default'): SupabaseClient {
    if (this.clients.has(key)) {
      const client = this.clients.get(key)!;
      client.lastUsed = Date.now();
      return client.instance;
    }
    
    // Create new client if under limit
    if (this.clients.size < this.maxClients) {
      const newClient = {
        instance: this.createClient(),
        lastUsed: Date.now()
      };
      
      this.clients.set(key, newClient);
      return newClient.instance;
    }
    
    // Reuse least recently used client if at limit
    const oldestKey = [...this.clients.entries()]
      .sort((a, b) => a[1].lastUsed - b[1].lastUsed)[0][0];
      
    const recycledClient = {
      instance: this.createClient(),
      lastUsed: Date.now()
    };
    
    this.clients.delete(oldestKey);
    this.clients.set(key, recycledClient);
    return recycledClient.instance;
  }
  
  private cleanup() {
    const now = Date.now();
    const expiryMs = 10 * 60 * 1000; // 10 minutes
    
    for (const [key, client] of this.clients.entries()) {
      if (now - client.lastUsed > expiryMs) {
        this.clients.delete(key);
      }
    }
  }
  
  private createClient(): SupabaseClient {
    // Create new Supabase client
    return createClient(SUPABASE_URL, SUPABASE_KEY);
  }
}

Conclusion

Supabase MCP servers represent a powerful technical bridge between conversational AI and PostgreSQL database operations through Supabase's comprehensive platform. By implementing the Model Context Protocol with Supabase's extensive API ecosystem, these servers enable AI assistants to perform sophisticated database operations, user authentication, storage management, and more—all while maintaining proper security controls and respecting row-level security policies.

This implementation establishes a foundation for building secure, scalable database operations that can be triggered through natural language interaction. As both MCP and Supabase continue to evolve, we can expect further advancements in performance optimization, security features, and integration capabilities.

For developers seeking to extend their AI systems with database capabilities, Supabase MCP offers a standardized, secure approach that abstracts the complexity of PostgreSQL while providing AI assistants with powerful tools to interact with your application's data layer. By following the technical implementation details outlined in this article, developers can quickly set up a secure, efficient bridge between their AI assistants and their Supabase data.

Stripe McpUnity Mcp