Self-Hosted Supabase MCP Server

Instructions

Implementation Reference

• src/tools/get_database_connections.ts:36-64 (handler)

  The main handler function for the get_database_connections tool. It executes a SQL query on pg_stat_activity to retrieve details of active client backend database connections, casts certain fields to text, filters backend_type, orders by backend_start, and processes the result using shared utilities.

  execute: async (input: GetDbConnectionsInput, context: ToolContext) => {
      const client = context.selfhostedClient;
  
      // Query pg_stat_activity
      // Note: Access to pg_stat_activity might require superuser or specific grants.
      const getConnectionsSql = `
          SELECT
              pid,
              datname,
              usename,
              application_name,
              client_addr::text, -- Cast inet to text
              backend_start::text, -- Cast timestamp to text
              state,
              query
          FROM
              pg_stat_activity
          WHERE
              backend_type = 'client backend' -- Exclude background workers, etc.
              -- Optionally filter out self?
              -- AND pid != pg_backend_pid()
          ORDER BY
              backend_start
      `;
  
      const result = await executeSqlWithFallback(client, getConnectionsSql, true);
  
      return handleSqlResponse(result, GetDbConnectionsOutputSchema);
  },

• src/tools/get_database_connections.ts:7-28 (schema)

  Zod schemas for input (empty object), output (array of connection objects with fields like datname, usename, etc.), and static MCP input JSON schema (empty object).

  const GetDbConnectionsOutputSchema = z.array(z.object({
      datname: z.string().nullable().describe('Database name'),
      usename: z.string().nullable().describe('User name'),
      application_name: z.string().nullable().describe('Application name (e.g., PostgREST, psql)'),
      client_addr: z.string().nullable().describe('Client IP address'),
      backend_start: z.string().nullable().describe('Time when the backend process started'),
      state: z.string().nullable().describe('Current connection state (e.g., active, idle)'),
      query: z.string().nullable().describe('Last or current query being executed'),
      pid: z.number().describe('Process ID of the backend'),
  }));
  
  // Input schema (allow filtering by user or database later if needed)
  const GetDbConnectionsInputSchema = z.object({});
  type GetDbConnectionsInput = z.infer<typeof GetDbConnectionsInputSchema>;
  
  // Static JSON Schema for MCP capabilities
  const mcpInputSchema = {
      type: 'object',
      properties: {},
      required: [],
  };

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

  Import statement that brings in the getDatabaseConnectionsTool from its implementation file.

  import { getDatabaseConnectionsTool } from './tools/get_database_connections.js';

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

  Registration of the tool in the availableTools object, which is later used to populate the MCP server capabilities and handle tool calls.

  [getDatabaseConnectionsTool.name]: getDatabaseConnectionsTool as AppTool,