MCP PostgreSQL Server

Instructions

Implementation Reference

• src/index.ts:447-510 (handler)

  Main handler function for 'describe_table' tool. Validates input, queries PostgreSQL information_schema for table columns, data types, nullability, defaults, primary key status, and max length. Returns JSON-formatted results.

  private async handleDescribeTable(args: any) {
    await this.ensureConnection();
  
    if (!args.table) {
      throw new McpError(ErrorCode.InvalidParams, 'Table name is required');
    }
  
    const schema = args.schema || 'public';
  
    try {
      const result = await this.client!.query(`
        SELECT 
          c.column_name, 
          c.data_type, 
          c.is_nullable, 
          c.column_default,
          CASE 
            WHEN pk.constraint_type = 'PRIMARY KEY' THEN true 
            ELSE false 
          END AS is_primary_key,
          c.character_maximum_length
        FROM 
          information_schema.columns c
        LEFT JOIN (
          SELECT 
            tc.constraint_type, 
            kcu.column_name, 
            kcu.table_name,
            kcu.table_schema
          FROM 
            information_schema.table_constraints tc
          JOIN 
            information_schema.key_column_usage kcu
          ON 
            tc.constraint_name = kcu.constraint_name
          WHERE 
            tc.constraint_type = 'PRIMARY KEY'
        ) pk
        ON 
          c.column_name = pk.column_name
          AND c.table_name = pk.table_name
          AND c.table_schema = pk.table_schema
        WHERE 
          c.table_schema = $1 
          AND c.table_name = $2
        ORDER BY 
          c.ordinal_position
      `, [schema, args.table]);
      
      return {
        content: [
          {
            type: 'text',
            text: JSON.stringify(result.rows, null, 2),
          },
        ],
      };
    } catch (error) {
      throw new McpError(
        ErrorCode.InternalError,
        `Failed to describe table: ${getErrorMessage(error)}`
      );
    }
  }

• src/index.ts:234-251 (schema)

  Input schema definition for the 'describe_table' tool, specifying required 'table' parameter and optional 'schema'.

  {
    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'],
    },
  },

• src/index.ts:267-268 (registration)

  Dispatch/registration case in the tool request handler switch statement that routes 'describe_table' calls to the handleDescribeTable function.

  case 'describe_table':
    return await this.handleDescribeTable(request.params.arguments);

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

  Tool list registration in ListToolsRequestSchema handler, including 'describe_table' 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'],
        },
      },
    ],
  }));