PostgreSQL MCP Server

Instructions

Implementation Reference

• tools/getSchema.ts:49-188 (handler)

  The main handler function `getSchema` that executes the tool logic: validates input, checks connection, queries information_schema.tables for tables (optionally filtered), fetches columns and constraints if requested, groups them by table, and returns structured schema information.

  export async function getSchema(
    rawParams: any
  ): McpToolResponse {
    try {
      // Validate and parse parameters
      const params = getSchemaSchema.parse(rawParams);
      
      // Check database connection status
      const connectionStatus = getConnectionStatus();
      if (connectionStatus.status !== 'connected') {
        return createDatabaseUnavailableResponse("get database schema");
      }
      
      const { schema_name, table_pattern, include_columns, include_constraints } = params;
  
      // Base query for tables
      let tablesQuery = `
        SELECT 
          table_name,
          table_schema,
          table_type
        FROM information_schema.tables
        WHERE table_schema = $1
      `;
      
      const queryParams: any[] = [schema_name];
      let paramIndex = 2;
  
      // Add table pattern filter if provided
      if (table_pattern) {
        tablesQuery += ` AND table_name LIKE $${paramIndex}`;
        queryParams.push(table_pattern);
        paramIndex++;
      }
  
      tablesQuery += ` ORDER BY table_name`;
  
      debug("Fetching schema information for schema: %s", schema_name);
      const tables = await executePostgresQuery<TableInfo>(tablesQuery, queryParams);
  
      // Initialize columns and constraints properties for all tables
      tables.forEach(table => {
        table.columns = [];
        table.constraints = [];
      });
  
      // Enhance tables with column information if requested
      if (include_columns && tables.length > 0) {
        const columnsQuery = `
          SELECT 
            table_name,
            column_name,
            data_type,
            is_nullable,
            column_default,
            character_maximum_length,
            numeric_precision,
            numeric_scale,
            ordinal_position
          FROM information_schema.columns
          WHERE table_schema = $1
          ORDER BY table_name, ordinal_position
        `;
  
        const columns = await executePostgresQuery<ColumnInfo & { table_name: string; ordinal_position: number }>(
          columnsQuery, 
          [schema_name]
        );
  
        // Group columns by table
        const columnsByTable = new Map<string, ColumnInfo[]>();
        columns.forEach(col => {
          if (!columnsByTable.has(col.table_name)) {
            columnsByTable.set(col.table_name, []);
          }
          const { table_name, ordinal_position, ...columnInfo } = col;
          columnsByTable.get(col.table_name)!.push(columnInfo);
        });
  
        // Add columns to tables
        tables.forEach(table => {
          table.columns = columnsByTable.get(table.table_name) || [];
        });
      }
  
      // Enhance tables with constraint information if requested
      if (include_constraints && tables.length > 0) {
        const constraintsQuery = `
          SELECT 
            tc.table_name,
            tc.constraint_name,
            tc.constraint_type,
            kcu.column_name,
            ccu.table_name AS foreign_table_name,
            ccu.column_name AS foreign_column_name
          FROM information_schema.table_constraints tc
          LEFT JOIN information_schema.key_column_usage kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
          LEFT JOIN information_schema.constraint_column_usage ccu
            ON tc.constraint_name = ccu.constraint_name
            AND tc.table_schema = ccu.table_schema
          WHERE tc.table_schema = $1
          ORDER BY tc.table_name, tc.constraint_name
        `;
  
        const constraints = await executePostgresQuery<ConstraintInfo & { table_name: string }>(
          constraintsQuery, 
          [schema_name]
        );
  
        // Group constraints by table
        const constraintsByTable = new Map<string, ConstraintInfo[]>();
        constraints.forEach(constraint => {
          if (!constraintsByTable.has(constraint.table_name)) {
            constraintsByTable.set(constraint.table_name, []);
          }
          const { table_name, ...constraintInfo } = constraint;
          constraintsByTable.get(constraint.table_name)!.push(constraintInfo);
        });
  
        // Add constraints to tables
        tables.forEach(table => {
          table.constraints = constraintsByTable.get(table.table_name) || [];
        });
      }
  
      const response = {
        schema: schema_name,
        table_count: tables.length,
        tables: tables,
        generated_at: new Date().toISOString(),
      };
  
      return createMcpSuccessResponse(response);
  
    } catch (error) {
      return createMcpErrorResponse("get schema", error);
    }
  }

• tools/getSchema.ts:12-21 (schema)

  Zod schema definition for the `get-schema` tool inputs: schema_name (default 'public'), optional table_pattern filter, flags for including columns and constraints.

  // Zod schema for input validation
  export const getSchemaShape: ZodRawShape = {
    schema_name: z.string().optional().default("public"),
    table_pattern: z.string().optional(),
    include_columns: z.boolean().optional().default(true),
    include_constraints: z.boolean().optional().default(false),
  };
  
  export const getSchemaSchema = z.object(getSchemaShape);

• index.ts:34-39 (registration)

  Registration of the `get-schema` tool on the MCP server, providing name, description, input schema, and handler function.

  server.tool(
    "get-schema",
    "Get database schema information including tables, columns, data types, and optionally constraints. Useful for understanding database structure.",
    getSchemaShape,
    getSchema
  );