Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/execute_sql.ts:36-43 (handler)

  The main execute handler for the 'execute_sql' tool. It retrieves the database client from context, logs the query preview, executes the SQL using a fallback mechanism, and processes/validates the response.

  execute: async (input: ExecuteSqlInput, context: ToolContext) => {
      const client = context.selfhostedClient;
  
      console.error(`Executing SQL (readOnly: ${input.read_only}): ${input.sql.substring(0, 100)}...`);
      
      const result = await executeSqlWithFallback(client, input.sql, input.read_only);
      return handleSqlResponse(result, ExecuteSqlOutputSchema);
  },

• src/tools/execute_sql.ts:8-17 (schema)

  Zod schemas defining the input (sql query and optional read_only flag) and output (array of rows) for the execute_sql tool.

  const ExecuteSqlInputSchema = z.object({
      sql: z.string().describe('The SQL query to execute.'),
      read_only: z.boolean().optional().default(false).describe('Hint for the RPC function whether the query is read-only (best effort).'),
      // Future enhancement: Add option to force direct connection?
      // use_direct_connection: z.boolean().optional().default(false).describe('Attempt to use direct DB connection instead of RPC.'),
  });
  type ExecuteSqlInput = z.infer<typeof ExecuteSqlInputSchema>;
  
  // Output schema - expects an array of results (rows)
  const ExecuteSqlOutputSchema = z.array(z.unknown()).describe('The array of rows returned by the SQL query.');

• src/index.ts:104-104 (registration)

  Registers the executeSqlTool in the availableTools object, which is used to populate MCP capabilities and handle tool calls in the server.

  [executeSqlTool.name]: executeSqlTool as AppTool,

• src/tools/utils.ts:64-78 (helper)

  Utility function that implements the fallback logic: prefers direct PostgreSQL connection via pg if DATABASE_URL available, otherwise uses the RPC function via Supabase client.

  export async function executeSqlWithFallback(
      client: SelfhostedSupabaseClient, 
      sql: string, 
      readOnly: boolean = true
  ): Promise<SqlExecutionResult> {
      // Try direct database connection first (bypasses JWT authentication)
      if (client.isPgAvailable()) {
          console.info('Using direct database connection (bypassing JWT)...');
          return await client.executeSqlWithPg(sql);
      }
      
      // Fallback to RPC if direct connection not available
      console.info('Falling back to RPC method...');
      return await client.executeSqlViaRpc(sql, readOnly);
  } 

• src/tools/utils.ts:20-35 (helper)

  Utility function to process SQL execution results: checks for errors and validates successful row data against the output schema, throwing descriptive errors if issues occur.

  export function handleSqlResponse<T>(result: SqlExecutionResult, schema: z.ZodSchema<T>): T {
      // Check if the result contains an error
      if ('error' in result) {
          throw new Error(`SQL Error (${result.error.code}): ${result.error.message}`);
      }
  
      // Validate the result against the schema
      try {
          return schema.parse(result);
      } catch (validationError) {
          if (validationError instanceof z.ZodError) {
              throw new Error(`Schema validation failed: ${validationError.errors.map(e => `${e.path.join('.')}: ${e.message}`).join(', ')}`);
          }
          throw new Error(`Unexpected validation error: ${validationError}`);
      }
  }