Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/list_extensions.ts:25-57 (handler)

  The complete implementation of the 'list_extensions' tool handler, which executes a SQL query against pg_extension to list installed extensions (excluding plpgsql), processes the result with helpers, and returns validated output.

  export const listExtensionsTool = {
      name: 'list_extensions',
      description: 'Lists all installed PostgreSQL extensions in the database.',
      inputSchema: ListExtensionsInputSchema,
      mcpInputSchema: mcpInputSchema,
      outputSchema: ListExtensionsOutputSchema,
      execute: async (input: ListExtensionsInput, context: ToolContext) => {
          const client = context.selfhostedClient;
  
          // SQL based on pg_extension
          const listExtensionsSql = `
              SELECT
                  pe.extname AS name,
                  pn.nspname AS schema,
                  pe.extversion AS version,
                  pd.description
              FROM
                  pg_catalog.pg_extension pe
              LEFT JOIN
                  pg_catalog.pg_namespace pn ON pn.oid = pe.extnamespace
              LEFT JOIN
                  pg_catalog.pg_description pd ON pd.objoid = pe.oid AND pd.classoid = 'pg_catalog.pg_extension'::regclass
              WHERE
                  pe.extname != 'plpgsql' -- Exclude the default plpgsql extension
              ORDER BY
                  pe.extname
          `;
  
          const result = await executeSqlWithFallback(client, listExtensionsSql, true);
  
          return handleSqlResponse(result, ListExtensionsOutputSchema);
      },
  }; 

• src/tools/list_extensions.ts:7-16 (schema)

  Zod schemas defining the input (empty object) and output structure (array of objects with name, schema, version, and optional description) for the list_extensions tool.

  const ListExtensionsOutputSchema = z.array(z.object({
      name: z.string(),
      schema: z.string(),
      version: z.string(),
      description: z.string().nullable().optional(),
  }));
  
  // Input schema (none needed for this tool)
  const ListExtensionsInputSchema = z.object({});
  type ListExtensionsInput = z.infer<typeof ListExtensionsInputSchema>;

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

  Registration of the listExtensionsTool into the availableTools object, which is used to populate the MCP server's tool capabilities and handlers.

  [listTablesTool.name]: listTablesTool as AppTool,
  [listExtensionsTool.name]: listExtensionsTool as AppTool,
  [listMigrationsTool.name]: listMigrationsTool as AppTool,

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

  Import statement that brings the listExtensionsTool into the main index file for registration.

  import { listExtensionsTool } from './tools/list_extensions.js';