MCP PostgreSQL Server

Instructions

Implementation Reference

• src/index.ts:318-351 (handler)

  The handler function that executes the 'query' tool logic: validates input, ensures DB connection, runs SELECT query with parameters, returns result rows as JSON.

  private async handleQuery(args: any) {
    await this.ensureConnection();
  
    if (!args.sql) {
      throw new McpError(ErrorCode.InvalidParams, 'SQL query is required');
    }
  
    if (!args.sql.trim().toUpperCase().startsWith('SELECT')) {
      throw new McpError(
        ErrorCode.InvalidParams,
        'Only SELECT queries are allowed with query tool'
      );
    }
  
    try {
      // Convert ? parameters to $1, $2, etc. if needed
      const sql = args.sql.includes('?') ? convertToNamedParams(args.sql) : args.sql;
      const result = await this.client!.query(sql, args.params || []);
      
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(result.rows, null, 2),
          },
        ],
      };
    } catch (error) {
      throw new McpError(
        ErrorCode.InternalError,
        `Query execution failed: ${getErrorMessage(error)}`
      );
    }
  }

• src/index.ts:172-188 (schema)

  Input schema definition for the 'query' tool, specifying 'sql' as required string and optional 'params' array.

  inputSchema: {
    type: 'object',
    properties: {
      sql: {
        type: 'string',
        description: 'SQL SELECT query (use $1, $2, etc. for parameters)',
      },
      params: {
        type: 'array',
        items: {
          type: ['string', 'number', 'boolean', 'null'],
        },
        description: 'Query parameters (optional)',
      },
    },
    required: ['sql'],
  },

• src/index.ts:169-189 (registration)

  Registration of the 'query' tool in the ListTools handler, including name, description, and schema.

  {
    name: 'query',
    description: 'Execute a SELECT query',
    inputSchema: {
      type: 'object',
      properties: {
        sql: {
          type: 'string',
          description: 'SQL SELECT query (use $1, $2, etc. for parameters)',
        },
        params: {
          type: 'array',
          items: {
            type: ['string', 'number', 'boolean', 'null'],
          },
          description: 'Query parameters (optional)',
        },
      },
      required: ['sql'],
    },
  },

• src/index.ts:44-47 (helper)

  Utility function used by the query handler to convert ? placeholders to PostgreSQL $1, $2, etc. parameters.

  function convertToNamedParams(query: string): string {
    let paramIndex = 0;
    return query.replace(/\?/g, () => `$${++paramIndex}`);
  }

• src/index.ts:259-260 (registration)

  Dispatch/registration in the CallToolRequest handler switch statement routing 'query' calls to handleQuery.

  case 'query':
    return await this.handleQuery(request.params.arguments);