PostgreSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:122-167 (handler)

  The handler function for the 'list_tables' tool. It executes a SQL query to fetch table names and types from the 'information_schema.tables' where table_schema is 'public', formats the results as a list, and returns them in the MCP response format. Uses the shared 'executeQuery' helper and handles errors.

  async () => {
    try {
      const query = `
        SELECT table_name, table_type 
        FROM information_schema.tables 
        WHERE table_schema = 'public' 
        ORDER BY table_name;
      `;
      
      const tables = await executeQuery(query);
      
      if (tables.length === 0) {
        return {
          content: [
            {
              type: "text",
              text: "No tables found in the public schema.",
            },
          ],
        };
      }
  
      const tableList = tables
        .map(table => `- ${table.table_name} (${table.table_type})`)
        .join("\n");
  
      return {
        content: [
          {
            type: "text",
            text: `Tables in database:\n${tableList}`,
          },
        ],
      };
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : "Unknown error";
      return {
        content: [
          {
            type: "text",
            text: `Error listing tables: ${errorMessage}`,
          },
        ],
      };
    }
  }

• src/index.ts:118-168 (registration)

  Registers the 'list_tables' tool with the MCP server using server.tool(). The tool has no input parameters (empty schema), a description, and references the handler function.

  server.tool(
    "list_tables",
    "List all tables in the current database",
    {},
    async () => {
      try {
        const query = `
          SELECT table_name, table_type 
          FROM information_schema.tables 
          WHERE table_schema = 'public' 
          ORDER BY table_name;
        `;
        
        const tables = await executeQuery(query);
        
        if (tables.length === 0) {
          return {
            content: [
              {
                type: "text",
                text: "No tables found in the public schema.",
              },
            ],
          };
        }
  
        const tableList = tables
          .map(table => `- ${table.table_name} (${table.table_type})`)
          .join("\n");
  
        return {
          content: [
            {
              type: "text",
              text: `Tables in database:\n${tableList}`,
            },
          ],
        };
      } catch (error) {
        const errorMessage = error instanceof Error ? error.message : "Unknown error";
        return {
          content: [
            {
              type: "text",
              text: `Error listing tables: ${errorMessage}`,
            },
          ],
        };
      }
    }
  );

• src/index.ts:121-121 (schema)

  Input schema for the 'list_tables' tool, which is empty indicating no parameters are required.

  {},

• src/index.ts:43-62 (helper)

  Shared helper function used by 'list_tables' (and other tools) to safely execute read-only SQL queries after connection check and safety validation.

  async function executeQuery(query: string, params: any[] = []): Promise<any[]> {
    const client = await getDbConnection();
    
    // Basic safety checks for read-only operations
    const normalizedQuery = query.trim().toLowerCase();
    const readOnlyPrefixes = ['select', 'show', 'describe', 'explain', 'with'];
    const isReadOnly = readOnlyPrefixes.some(prefix => normalizedQuery.startsWith(prefix));
    
    if (!isReadOnly) {
      throw new Error("Only read-only queries (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH) are allowed for security.");
    }
    
    try {
      const result = await client.query(query, params);
      return result.rows;
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : "Unknown error occurred";
      throw new Error(`Query execution failed: ${errorMessage}`);
    }
  }