PostgreSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:301-362 (handler)

  The handler function for the 'execute_query' tool. It processes the input query and limit, adds a LIMIT if necessary for SELECT queries, executes the query using the helper executeQuery, formats the results as a markdown table, and returns them. Includes error handling.

  async ({ query, limit }) => {
    try {
      const maxLimit = limit || 100;
      
      // Add LIMIT clause if not present and it's a SELECT query
      let finalQuery = query.trim();
      const normalizedQuery = finalQuery.toLowerCase();
      
      if (normalizedQuery.startsWith('select') && !normalizedQuery.includes('limit')) {
        finalQuery += ` LIMIT ${maxLimit}`;
      }
      
      const rows = await executeQuery(finalQuery);
      
      if (rows.length === 0) {
        return {
          content: [
            {
              type: "text",
              text: "Query executed successfully. No rows returned.",
            },
          ],
        };
      }
  
      // Format results as a table
      const headers = Object.keys(rows[0]);
      let result = `Query Results (${rows.length} rows):\n\n`;
      
      // Add headers
      result += headers.join(' | ') + '\n';
      result += headers.map(() => '---').join(' | ') + '\n';
      
      // Add rows
      rows.forEach(row => {
        const values = headers.map(header => {
          const value = row[header];
          return value === null ? 'NULL' : String(value);
        });
        result += values.join(' | ') + '\n';
      });
  
      return {
        content: [
          {
            type: "text",
            text: result,
          },
        ],
      };
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : "Unknown error";
      return {
        content: [
          {
            type: "text",
            text: `Error executing query: ${errorMessage}`,
          },
        ],
      };
    }
  }

• src/index.ts:297-300 (schema)

  Zod schema defining the input parameters for the 'execute_query' tool: required 'query' string and optional 'limit' number.

  {
    query: z.string().describe("SQL query to execute (read-only operations only)"),
    limit: z.number().describe("Maximum number of rows to return (default: 100)").optional(),
  },

• src/index.ts:294-363 (registration)

  Registration of the 'execute_query' tool using server.tool(), including name, description, input schema, and handler function.

  server.tool(
    "execute_query",
    "Execute a read-only SQL query (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH statements only)",
    {
      query: z.string().describe("SQL query to execute (read-only operations only)"),
      limit: z.number().describe("Maximum number of rows to return (default: 100)").optional(),
    },
    async ({ query, limit }) => {
      try {
        const maxLimit = limit || 100;
        
        // Add LIMIT clause if not present and it's a SELECT query
        let finalQuery = query.trim();
        const normalizedQuery = finalQuery.toLowerCase();
        
        if (normalizedQuery.startsWith('select') && !normalizedQuery.includes('limit')) {
          finalQuery += ` LIMIT ${maxLimit}`;
        }
        
        const rows = await executeQuery(finalQuery);
        
        if (rows.length === 0) {
          return {
            content: [
              {
                type: "text",
                text: "Query executed successfully. No rows returned.",
              },
            ],
          };
        }
  
        // Format results as a table
        const headers = Object.keys(rows[0]);
        let result = `Query Results (${rows.length} rows):\n\n`;
        
        // Add headers
        result += headers.join(' | ') + '\n';
        result += headers.map(() => '---').join(' | ') + '\n';
        
        // Add rows
        rows.forEach(row => {
          const values = headers.map(header => {
            const value = row[header];
            return value === null ? 'NULL' : String(value);
          });
          result += values.join(' | ') + '\n';
        });
  
        return {
          content: [
            {
              type: "text",
              text: result,
            },
          ],
        };
      } catch (error) {
        const errorMessage = error instanceof Error ? error.message : "Unknown error";
        return {
          content: [
            {
              type: "text",
              text: `Error executing query: ${errorMessage}`,
            },
          ],
        };
      }
    }
  );

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

  Helper function 'executeQuery' that performs the actual database query execution with read-only safety checks. Used by the 'execute_query' handler and other tools.

  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}`);
    }
  }