MCP PostgreSQL Server

Instructions

Implementation Reference

• src/index.ts:353-390 (handler)

  The handler function that implements the core logic for the 'execute' tool. It ensures database connection, validates non-SELECT SQL, converts parameters if needed, executes the query, and returns rowCount and command.

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

• src/index.ts:190-210 (schema)

  The schema definition for the 'execute' tool as returned by listTools, specifying input schema with required 'sql' and optional 'params'.

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

• src/index.ts:261-262 (registration)

  Registration/dispatch of the 'execute' tool handler in the CallToolRequestSchema switch statement.

  case 'execute':
    return await this.handleExecute(request.params.arguments);

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

  Helper utility to convert SQL ? placeholders to PostgreSQL positional parameters $1, $2, etc., used in the execute handler.

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

• src/index.ts:137-253 (registration)

  The listTools handler that registers/declares the 'execute' tool with its schema.

  this.server.setRequestHandler(ListToolsRequestSchema, async () => ({
    tools: [
      {
        name: 'connect_db',
        description: 'Connect to PostgreSQL database. NOTE: Default connection exists - only use when requested or if other commands fail',
        inputSchema: {
          type: 'object',
          properties: {
            host: {
              type: 'string',
              description: 'Database host',
            },
            port: {
              type: 'number',
              description: 'Database port (default: 5432)',
            },
            user: {
              type: 'string',
              description: 'Database user',
            },
            password: {
              type: 'string',
              description: 'Database password',
            },
            database: {
              type: 'string',
              description: 'Database name',
            },
          },
          required: ['host', 'user', 'password', 'database'],
        },
      },
      {
        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'],
        },
      },
      {
        name: 'execute',
        description: 'Execute an INSERT, UPDATE, or DELETE query',
        inputSchema: {
          type: 'object',
          properties: {
            sql: {
              type: 'string',
              description: 'SQL query (INSERT, UPDATE, DELETE) (use $1, $2, etc. for parameters)',
            },
            params: {
              type: 'array',
              items: {
                type: ['string', 'number', 'boolean', 'null'],
              },
              description: 'Query parameters (optional)',
            },
          },
          required: ['sql'],
        },
      },
      {
        name: 'list_schemas',
        description: 'List all schemas in the database',
        inputSchema: {
          type: 'object',
          properties: {},
          required: [],
        },
      },
      {
        name: 'list_tables',
        description: 'List tables in the database',
        inputSchema: {
          type: 'object',
          properties: {
            schema: {
              type: 'string',
              description: 'Schema name (default: public)',
            },
          },
          required: [],
        },
      },
      {
        name: 'describe_table',
        description: 'Get table structure',
        inputSchema: {
          type: 'object',
          properties: {
            table: {
              type: 'string',
              description: 'Table name',
            },
            schema: {
              type: 'string',
              description: 'Schema name (default: public)',
            },
          },
          required: ['table'],
        },
      },
    ],
  }));