Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/list_tables.ts:35-68 (handler)

  The handler function that executes a PostgreSQL query to list all accessible tables, excluding system and internal Supabase schemas, using utility functions for SQL execution and response handling.

  execute: async (input: ListTablesInput, context: ToolContext) => {
      const client = context.selfhostedClient;
  
      // SQL query to get tables from pg_catalog and information_schema
      // Excludes system schemas like pg_catalog, information_schema, and Supabase internal schemas
      const listTablesSql = `
          SELECT
              n.nspname as schema,
              c.relname as name,
              pgd.description as comment
          FROM
              pg_catalog.pg_class c
          JOIN
              pg_catalog.pg_namespace n ON n.oid = c.relnamespace
          LEFT JOIN
              pg_catalog.pg_description pgd ON pgd.objoid = c.oid AND pgd.objsubid = 0
          WHERE
              c.relkind = 'r' -- r = ordinary table
              AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
              AND n.nspname NOT LIKE 'pg_temp_%'
              AND n.nspname NOT LIKE 'pg_toast_temp_%'
               -- Exclude Supabase internal schemas
              AND n.nspname NOT IN ('auth', 'storage', 'extensions', 'graphql', 'graphql_public', 'pgbouncer', 'realtime', 'supabase_functions', 'supabase_migrations', '_realtime')
              AND has_schema_privilege(n.oid, 'USAGE')
              AND has_table_privilege(c.oid, 'SELECT')
          ORDER BY
              n.nspname,
              c.relname
      `;
  
      const result = await executeSqlWithFallback(client, listTablesSql, true);
  
      return handleSqlResponse(result, ListTablesOutputSchema); // Use a helper to handle response/errors
  },

• src/tools/list_tables.ts:7-11 (schema)

  Zod schema defining the output structure: an array of objects containing schema name, table name, and optional comment.

  const ListTablesOutputSchema = z.array(z.object({
      schema: z.string(),
      name: z.string(),
      comment: z.string().nullable().optional(), // Add comment if available
  }));

• src/tools/list_tables.ts:14-17 (schema)

  Zod schema for input parameters, which is an empty object since no inputs are required for this tool.

  const ListTablesInputSchema = z.object({ // No specific input needed for listing tables
      // Optional: add schema filter later if needed
      // schema: z.string().optional().describe('Filter tables by schema name.'),
  });

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

  Import statement bringing the listTablesTool into the main index file.

  import { listTablesTool } from './tools/list_tables.js';

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

  Registration of the listTablesTool in the availableTools object, which is used to populate the MCP server's tool capabilities.

  [listTablesTool.name]: listTablesTool as AppTool,