PostgreSQL MCP Server

Instructions

Implementation Reference

• src/index.ts:177-290 (handler)

  The handler function that implements the logic for the 'describe_table' tool. It queries PostgreSQL information_schema for columns, primary keys, and foreign keys, then formats a detailed textual description of the table structure.

  async ({ tableName }) => {
    try {
      // Get column information
      const columnsQuery = `
        SELECT 
          column_name,
          data_type,
          is_nullable,
          column_default,
          character_maximum_length,
          numeric_precision,
          numeric_scale
        FROM information_schema.columns 
        WHERE table_schema = 'public' AND table_name = $1
        ORDER BY ordinal_position;
      `;
      
      const columns = await executeQuery(columnsQuery, [tableName]);
      
      if (columns.length === 0) {
        return {
          content: [
            {
              type: "text",
              text: `Table '${tableName}' not found or has no columns.`,
            },
          ],
        };
      }
  
      // Get primary key information
      const pkQuery = `
        SELECT column_name
        FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu 
          ON tc.constraint_name = kcu.constraint_name
        WHERE tc.table_schema = 'public' 
          AND tc.table_name = $1 
          AND tc.constraint_type = 'PRIMARY KEY'
        ORDER BY kcu.ordinal_position;
      `;
      
      const primaryKeys = await executeQuery(pkQuery, [tableName]);
  
      // Get foreign key information
      const fkQuery = `
        SELECT 
          kcu.column_name,
          ccu.table_name AS referenced_table,
          ccu.column_name AS referenced_column
        FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu 
          ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage ccu 
          ON ccu.constraint_name = tc.constraint_name
        WHERE tc.table_schema = 'public' 
          AND tc.table_name = $1 
          AND tc.constraint_type = 'FOREIGN KEY';
      `;
      
      const foreignKeys = await executeQuery(fkQuery, [tableName]);
  
      // Format response
      let response = `Table: ${tableName}\n\nColumns:\n`;
      
      columns.forEach(col => {
        const isPk = primaryKeys.some(pk => pk.column_name === col.column_name);
        const fk = foreignKeys.find(fk => fk.column_name === col.column_name);
        
        response += `- ${col.column_name}: ${col.data_type}`;
        
        if (col.character_maximum_length) {
          response += `(${col.character_maximum_length})`;
        } else if (col.numeric_precision) {
          response += `(${col.numeric_precision}${col.numeric_scale ? `,${col.numeric_scale}` : ''})`;
        }
        
        response += ` ${col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'}`;
        
        if (col.column_default) {
          response += ` DEFAULT ${col.column_default}`;
        }
        
        if (isPk) {
          response += ' [PRIMARY KEY]';
        }
        
        if (fk) {
          response += ` [FK -> ${fk.referenced_table}.${fk.referenced_column}]`;
        }
        
        response += '\n';
      });
  
      return {
        content: [
          {
            type: "text",
            text: response,
          },
        ],
      };
    } catch (error) {
      const errorMessage = error instanceof Error ? error.message : "Unknown error";
      return {
        content: [
          {
            type: "text",
            text: `Error describing table '${tableName}': ${errorMessage}`,
          },
        ],
      };
    }
  }

• src/index.ts:174-176 (schema)

  The Zod input schema defining the 'tableName' parameter for the 'describe_table' tool.

  {
    tableName: z.string().describe("Name of the table to describe"),
  },

• src/index.ts:171-291 (registration)

  The registration of the 'describe_table' tool using server.tool(), including name, description, input schema, and handler reference.

  server.tool(
    "describe_table",
    "Get detailed information about a table's structure, columns, and constraints",
    {
      tableName: z.string().describe("Name of the table to describe"),
    },
    async ({ tableName }) => {
      try {
        // Get column information
        const columnsQuery = `
          SELECT 
            column_name,
            data_type,
            is_nullable,
            column_default,
            character_maximum_length,
            numeric_precision,
            numeric_scale
          FROM information_schema.columns 
          WHERE table_schema = 'public' AND table_name = $1
          ORDER BY ordinal_position;
        `;
        
        const columns = await executeQuery(columnsQuery, [tableName]);
        
        if (columns.length === 0) {
          return {
            content: [
              {
                type: "text",
                text: `Table '${tableName}' not found or has no columns.`,
              },
            ],
          };
        }
  
        // Get primary key information
        const pkQuery = `
          SELECT column_name
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage kcu 
            ON tc.constraint_name = kcu.constraint_name
          WHERE tc.table_schema = 'public' 
            AND tc.table_name = $1 
            AND tc.constraint_type = 'PRIMARY KEY'
          ORDER BY kcu.ordinal_position;
        `;
        
        const primaryKeys = await executeQuery(pkQuery, [tableName]);
  
        // Get foreign key information
        const fkQuery = `
          SELECT 
            kcu.column_name,
            ccu.table_name AS referenced_table,
            ccu.column_name AS referenced_column
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage kcu 
            ON tc.constraint_name = kcu.constraint_name
          JOIN information_schema.constraint_column_usage ccu 
            ON ccu.constraint_name = tc.constraint_name
          WHERE tc.table_schema = 'public' 
            AND tc.table_name = $1 
            AND tc.constraint_type = 'FOREIGN KEY';
        `;
        
        const foreignKeys = await executeQuery(fkQuery, [tableName]);
  
        // Format response
        let response = `Table: ${tableName}\n\nColumns:\n`;
        
        columns.forEach(col => {
          const isPk = primaryKeys.some(pk => pk.column_name === col.column_name);
          const fk = foreignKeys.find(fk => fk.column_name === col.column_name);
          
          response += `- ${col.column_name}: ${col.data_type}`;
          
          if (col.character_maximum_length) {
            response += `(${col.character_maximum_length})`;
          } else if (col.numeric_precision) {
            response += `(${col.numeric_precision}${col.numeric_scale ? `,${col.numeric_scale}` : ''})`;
          }
          
          response += ` ${col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'}`;
          
          if (col.column_default) {
            response += ` DEFAULT ${col.column_default}`;
          }
          
          if (isPk) {
            response += ' [PRIMARY KEY]';
          }
          
          if (fk) {
            response += ` [FK -> ${fk.referenced_table}.${fk.referenced_column}]`;
          }
          
          response += '\n';
        });
  
        return {
          content: [
            {
              type: "text",
              text: response,
            },
          ],
        };
      } catch (error) {
        const errorMessage = error instanceof Error ? error.message : "Unknown error";
        return {
          content: [
            {
              type: "text",
              text: `Error describing table '${tableName}': ${errorMessage}`,
            },
          ],
        };
      }
    }
  );

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

  Helper function 'executeQuery' used by the 'describe_table' handler to safely execute read-only SQL queries against the connected PostgreSQL database.

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