Skip to main content
Glama
jerichosequitin

Metabase

by jerichosequitin
OverviewSchemaRelated ServersScoreDiscussions
TypeScript

retrieve

Fetch detailed information about Metabase models including cards, dashboards, tables, and databases using specific IDs. Supports batch retrieval and pagination for large datasets.

Instructions

Fetch additional details for supported models (Cards, Dashboards, Tables, Databases, Collections, Fields). Supports multiple IDs (max 50 per request) with intelligent concurrent processing and optimized caching. Includes table pagination for large databases exceeding token limits.

Input Schema

TableJSON Schema
NameRequiredDescriptionDefault
modelYesType of model to retrieve. Only one model type allowed per request.
idsYesArray of IDs to retrieve (1-50 IDs per request). All IDs must be positive integers. For larger datasets, make multiple requests.
table_offsetNoStarting offset for table pagination (database model only). Use with table_limit for paginating through large databases that exceed token limits.
table_limitNoMaximum number of tables to return per page (database model only). Maximum 100 tables per page. Use with table_offset for pagination.

Implementation Reference

  • src/handlers/retrieve/index.ts:31-459 (handler)
    Core implementation of the 'retrieve' tool handler. Validates input parameters (model, ids, pagination), fetches data concurrently from Metabase API with caching, applies model-specific optimizations, handles errors per ID, and returns structured response with usage guidance.
    export async function handleRetrieve(
  request: CallToolRequest,
  requestId: string,
  apiClient: MetabaseApiClient,
  logDebug: (message: string, data?: unknown) => void,
  logInfo: (message: string, data?: unknown) => void,
  logWarn: (message: string, data?: unknown, error?: Error) => void,
  logError: (message: string, data?: unknown) => void
) {
  const { model, ids, table_offset, table_limit } = request.params?.arguments || {};

  // Validate required parameters
  if (!model) {
    logWarn('Missing model parameter in retrieve request', { requestId });
    throw ValidationErrorFactory.invalidParameter(
      'model',
      model,
      'Must be one of: card, dashboard, table, database, collection, field'
    );
  }

  if (!ids || !Array.isArray(ids) || ids.length === 0) {
    logWarn('Missing or invalid ids parameter in retrieve request', { requestId });
    throw ValidationErrorFactory.invalidParameter(
      'ids',
      ids,
      'Must be a non-empty array of numbers'
    );
  }

  // Validate model type with case insensitive handling
  const supportedModels = [
    'card',
    'dashboard',
    'table',
    'database',
    'collection',
    'field',
  ] as const;

  const validatedModel = validateEnumValue(model, supportedModels, 'model', requestId, logWarn);

  // Validate maximum number of IDs based on model type
  const maxIds = validatedModel === 'database' ? MAX_DATABASE_IDS_PER_REQUEST : MAX_IDS_PER_REQUEST;
  if (ids.length > maxIds) {
    logWarn(
      `Too many IDs requested: ${ids.length}. Maximum allowed for ${validatedModel}: ${maxIds}`,
      {
        requestId,
      }
    );
    throw ValidationErrorFactory.invalidParameter(
      'ids',
      `${ids.length} items`,
      validatedModel === 'database'
        ? `Maximum allowed: ${maxIds} databases per request due to large metadata. For more databases, please make multiple requests.`
        : `Maximum allowed: ${maxIds} per request. For larger datasets, please make multiple requests.`
    );
  }

  // Validate all IDs are positive integers
  const numericIds: number[] = [];
  for (const id of ids) {
    validatePositiveInteger(id, 'id', requestId, logWarn);
    numericIds.push(id as number);
  }

  // Validate pagination parameters for database model
  let paginationOffset = 0;
  let paginationLimit: number | undefined = undefined;

  if (validatedModel === 'database') {
    if (table_offset !== undefined) {
      paginationOffset = parseAndValidateNonNegativeInteger(
        table_offset,
        'table_offset',
        requestId,
        logWarn
      );
    }

    if (table_limit !== undefined) {
      paginationLimit = parseAndValidatePositiveInteger(
        table_limit,
        'table_limit',
        requestId,
        logWarn
      );

      if (paginationLimit > 100) {
        logWarn('table_limit too large, maximum is 100', {
          requestId,
          table_limit: paginationLimit,
        });
        throw ValidationErrorFactory.invalidParameter(
          'table_limit',
          `${paginationLimit}`,
          'Maximum allowed: 100 tables per page'
        );
      }
    }
  } else if (table_offset !== undefined || table_limit !== undefined) {
    logWarn('table_offset and table_limit are only valid for database model', { requestId });
    throw ValidationErrorFactory.invalidParameter(
      'table_offset/table_limit',
      'provided for non-database model',
      'table_offset and table_limit parameters are only supported for the database model'
    );
  }

  logDebug(`Retrieving ${validatedModel} details for IDs: ${numericIds.join(', ')}`);

  try {
    const results: any[] = [];
    const errors: Array<{
      id: number;
      error: string;
    }> = [];
    let apiHits = 0;
    let cacheHits = 0;

    // Intelligent concurrency control based on request size and server protection
    // - Small requests (≤3): Full concurrency for minimal latency
    // - Medium requests (4-20): Moderate batching for balanced performance
    // - Large requests (21-50): Conservative batching to prevent server overload
    const CONCURRENT_LIMIT =
      numericIds.length <= CONCURRENCY_LIMITS.SMALL_REQUEST_THRESHOLD
        ? numericIds.length
        : numericIds.length <= CONCURRENCY_LIMITS.MEDIUM_REQUEST_THRESHOLD
          ? CONCURRENCY_LIMITS.MEDIUM_BATCH_SIZE
          : CONCURRENCY_LIMITS.LARGE_BATCH_SIZE;

    // Determine optimization level based on request size to manage token usage
    const optimizationLevel =
      numericIds.length >= OPTIMIZATION_THRESHOLDS.ULTRA_MINIMAL_THRESHOLD
        ? OptimizationLevel.ULTRA_MINIMAL
        : numericIds.length >= OPTIMIZATION_THRESHOLDS.AGGRESSIVE_OPTIMIZATION_THRESHOLD
          ? OptimizationLevel.AGGRESSIVE
          : OptimizationLevel.STANDARD;

    logDebug(
      `Processing ${numericIds.length} ${validatedModel}(s) with concurrency limit: ${CONCURRENT_LIMIT}, optimization level: ${optimizationLevel}`
    );

    // Process requests concurrently with controlled concurrency to balance performance and server load
    const processId = async (id: number) => {
      try {
        let response: CachedResponse<any>;

        switch (validatedModel) {
          case 'card':
            response = await apiClient.getCard(id);
            break;
          case 'dashboard':
            response = await apiClient.getDashboard(id);
            break;
          case 'table':
            response = await apiClient.getTable(id);
            break;
          case 'database':
            response = await apiClient.getDatabase(id);
            break;
          case 'collection': {
            // For collections, get both metadata and items like resources do
            const [collectionResponse, itemsResponse] = await Promise.all([
              apiClient.getCollection(id),
              apiClient.getCollectionItems(id),
            ]);

            // Combine the collection metadata with its items
            const collectionWithItems = {
              ...collectionResponse.data,
              items: itemsResponse.data || [],
            };

            response = {
              data: collectionWithItems,
              source: collectionResponse.source, // Use the source from the main collection call
              fetchTime: collectionResponse.fetchTime + itemsResponse.fetchTime,
            };
            break;
          }
          case 'field':
            response = await apiClient.getField(id);
            break;
        }

        // Track cache vs API hits accurately
        if (response.source === 'cache') {
          cacheHits++;
        } else {
          apiHits++;
        }

        // Save raw structure for documentation if enabled
        saveRawStructure(validatedModel, response.data, SAVE_RAW_STRUCTURES);

        let result: any;

        // Optimize responses to reduce token usage
        if (validatedModel === 'card') {
          result = optimizeCardResponse(
            {
              id,
              ...response.data,
            },
            optimizationLevel
          );
        } else if (validatedModel === 'dashboard') {
          result = optimizeDashboardResponse(
            {
              id,
              ...response.data,
            },
            optimizationLevel
          );
        } else if (validatedModel === 'table') {
          result = optimizeTableResponse(
            {
              id,
              ...response.data,
            },
            optimizationLevel
          );
        } else if (validatedModel === 'database') {
          result = optimizeDatabaseResponse(
            {
              id,
              ...response.data,
            },
            optimizationLevel,
            paginationOffset,
            paginationLimit
          );
        } else if (validatedModel === 'collection') {
          result = optimizeCollectionResponse(
            {
              id,
              ...response.data,
            },
            optimizationLevel
          );
        } else if (validatedModel === 'field') {
          result = optimizeFieldResponse(
            {
              id,
              ...response.data,
            },
            optimizationLevel
          );
        } else {
          result = {
            id,
            ...response.data,
            retrieved_at: new Date().toISOString(),
          };
        }

        logDebug(`Successfully retrieved ${validatedModel} ${id} from ${response.source}`);
        return { success: true, id, result };
      } catch (error: any) {
        const errorMessage = error?.message || error?.data?.message || 'Unknown error';
        logWarn(`Failed to retrieve ${validatedModel} ${id}: ${errorMessage}`, { requestId });
        return { success: false, id, error: errorMessage };
      }
    };

    // Process IDs in batches to control concurrency
    const processBatch = async (batch: number[]) => {
      return Promise.allSettled(batch.map(processId));
    };

    // Split IDs into batches and process them
    const batches: number[][] = [];
    for (let i = 0; i < numericIds.length; i += CONCURRENT_LIMIT) {
      batches.push(numericIds.slice(i, i + CONCURRENT_LIMIT));
    }

    // Process all batches sequentially, but items within each batch concurrently
    for (const batch of batches) {
      const batchResults = await processBatch(batch);

      batchResults.forEach(result => {
        if (result.status === 'fulfilled') {
          const { success, id, result: itemResult, error } = result.value;
          if (success) {
            results.push(itemResult);
          } else {
            errors.push({ id, error });
          }
        } else {
          // This shouldn't happen with our current implementation, but handle it gracefully
          logWarn(`Unexpected batch processing error: ${result.reason}`, { requestId });
        }
      });
    }

    const successCount = results.length;
    const errorCount = errors.length;

    // Create data source information
    const dataSource = {
      cache: cacheHits,
      api: apiHits,
    };
    const primarySource = cacheHits > apiHits ? 'cache' : apiHits > cacheHits ? 'api' : 'mixed';

    // Handle scenario where all requests failed
    if (successCount === 0 && errorCount > 0) {
      const idsText =
        numericIds.length === 1 ? `ID ${numericIds[0]}` : `IDs ${numericIds.join(', ')}`;
      throw new McpError(
        ErrorCode.InternalError,
        `Failed to retrieve ${validatedModel}(s) ${idsText}: ${errors[0].error}`
      );
    }

    // Log warning if failure rate is high (>50%)
    if (errorCount > 0 && errorCount / numericIds.length > 0.5) {
      logWarn(
        `High failure rate in retrieve operation: ${errorCount}/${numericIds.length} ${validatedModel}(s) failed`,
        { requestId, errors }
      );
    }

    // Create response object
    const response: any = {
      model: validatedModel,
      total_requested: numericIds.length,
      successful_retrievals: successCount,
      failed_retrievals: errorCount,
      source: dataSource,
      results: results,
    };

    // Add errors if any occurred
    if (errors.length > 0) {
      response.errors = errors;
    }

    // Add usage guidance based on model type
    switch (validatedModel) {
      case 'card':
        response.usage_guidance =
          'Use the database_id and dataset_query.native.query with execute_query to run queries. You can modify the SQL as needed. Response is optimized to include only essential fields for better performance.';
        break;
      case 'dashboard':
        response.usage_guidance =
          'Dashboard data includes optimized layout, cards, and parameters. Use retrieve or execute_query with card database_id and dataset_query.native.query from dashcards[].card to run queries. Response is optimized to exclude heavy metadata for better performance.';
        break;
      case 'table':
        response.usage_guidance =
          'Table metadata includes optimized column information, data types, and relationships. Use fields[] array to understand table schema and construct queries. Response excludes heavy fingerprint statistics for better performance.';
        break;
      case 'database':
        if (paginationLimit !== undefined) {
          response.usage_guidance =
            'Database details include paginated table information. Use table_offset and table_limit parameters for pagination when dealing with large databases that exceed token limits. Use tables[] array to see available tables, then retrieve with model="table" for detailed table metadata.';
        } else {
          response.usage_guidance =
            'Database details include optimized connection info and available tables. Use tables[] array to see all tables, then retrieve with model="table" for detailed table metadata. For large databases exceeding token limits, use table_offset and table_limit parameters for pagination.';
        }
        break;
      case 'collection':
        response.usage_guidance =
          'Collection details include organizational structure, metadata, and items within the collection. Items are organized by type (cards, dashboards, collections, other) for easy navigation. Collections work like folders to organize your Metabase items. Use the items array to see what content is available in this collection.';
        break;
      case 'field':
        response.usage_guidance =
          'Field metadata includes data type, constraints, and relationships. Use this information when constructing queries or understanding table structure. Response is heavily optimized to exclude nested database features and detailed fingerprint data for better performance.';
        break;
    }

    const logMessage =
      errorCount > 0
        ? `Retrieved ${successCount}/${numericIds.length} ${validatedModel}s (${errorCount} errors, source: ${primarySource})`
        : `Successfully retrieved ${successCount} ${validatedModel}s (source: ${primarySource})`;

    logInfo(logMessage);

    // Monitor response size for token usage optimization feedback
    const responseText = formatJson(response);
    const responseSizeChars = responseText.length;
    const estimatedTokens = Math.ceil(responseSizeChars / 4); // Rough estimation: ~4 chars per token

    // Log warnings for large responses
    if (estimatedTokens > 20000) {
      logWarn(
        `Large response detected: ~${estimatedTokens} tokens (${responseSizeChars} chars) for ${numericIds.length} ${validatedModel}(s). Consider using smaller batch sizes for better performance.`,
        {
          requestId,
          responseSize: responseSizeChars,
          estimatedTokens,
          optimizationLevel,
          itemCount: numericIds.length,
        }
      );
    } else if (estimatedTokens > 15000) {
      logDebug(
        `Moderate response size: ~${estimatedTokens} tokens (${responseSizeChars} chars) for ${numericIds.length} ${validatedModel}(s)`,
        {
          requestId,
          responseSize: responseSizeChars,
          estimatedTokens,
          optimizationLevel,
        }
      );
    }

    return {
      content: [
        {
          type: 'text',
          text: responseText,
        },
      ],
    };
  } catch (error: any) {
    throw handleApiError(
      error,
      {
        operation: `Retrieve ${validatedModel} details`,
        resourceType: validatedModel,
        resourceId: numericIds.join(', '),
      },
      logError
    );
  }
}
  • src/server.ts:248-291 (schema)
    Input schema definition for the 'retrieve' tool, including parameters model (enum), ids (array 1-50), and optional pagination for databases.
    name: 'retrieve',
description:
  'Fetch additional details for supported models (Cards, Dashboards, Tables, Databases, Collections, Fields). Supports multiple IDs (max 50 per request) with intelligent concurrent processing and optimized caching. Includes table pagination for large databases exceeding token limits.',
annotations: {
  readOnlyHint: true,
  destructiveHint: false,
  idempotentHint: true,
  openWorldHint: true,
},
inputSchema: {
  type: 'object',
  properties: {
    model: {
      type: 'string',
      enum: ['card', 'dashboard', 'table', 'database', 'collection', 'field'],
      description:
        'Type of model to retrieve. Only one model type allowed per request.',
    },
    ids: {
      type: 'array',
      items: {
        type: 'number',
      },
      description:
        'Array of IDs to retrieve (1-50 IDs per request). All IDs must be positive integers. For larger datasets, make multiple requests.',
      minItems: 1,
      maxItems: 50,
    },
    table_offset: {
      type: 'number',
      description:
        'Starting offset for table pagination (database model only). Use with table_limit for paginating through large databases that exceed token limits.',
      minimum: 0,
    },
    table_limit: {
      type: 'number',
      description:
        'Maximum number of tables to return per page (database model only). Maximum 100 tables per page. Use with table_offset for pagination.',
      minimum: 1,
      maximum: 100,
    },
  },
  required: ['model', 'ids'],
},
  • src/server.ts:567-578 (registration)
    Registration of the 'retrieve' handler in the CallToolRequest switch statement.
    case 'retrieve':
  return safeCall(() =>
    handleRetrieve(
      request,
      requestId,
      this.apiClient,
      this.logDebug.bind(this),
      this.logInfo.bind(this),
      this.logWarn.bind(this),
      this.logError.bind(this)
    )
  );
  • src/handlers/index.ts:6-6 (registration)
    Export of handleRetrieve from the handlers index, imported into server.ts.
    export { handleRetrieve } from './retrieve/index.js';
  • src/handlers/retrieve/types.ts:1-25 (helper)
    Type definitions and constants used by the retrieve handler for validation limits, concurrency, and optimization thresholds.
    // Supported model types for the retrieve command
export type SupportedModel = 'card' | 'dashboard' | 'table' | 'database' | 'collection' | 'field';

// Rate limiting and performance constants
export const MAX_IDS_PER_REQUEST = 50; // Maximum IDs per request to prevent abuse and ensure reasonable response times

// Database-specific limits (databases can have massive metadata)
export const MAX_DATABASE_IDS_PER_REQUEST = 2; // Maximum database IDs per request due to large table/field metadata

export const CONCURRENCY_LIMITS = {
  SMALL_REQUEST_THRESHOLD: 3, // ≤3 IDs: Full concurrency for minimal latency
  MEDIUM_REQUEST_THRESHOLD: 20, // 4-20 IDs: Moderate batching for balanced performance
  MEDIUM_BATCH_SIZE: 8, // Concurrent requests for medium batches
  LARGE_BATCH_SIZE: 5, // Conservative batching for large requests (21-50)
};

// Optimization thresholds for token usage management
export const OPTIMIZATION_THRESHOLDS = {
  AGGRESSIVE_OPTIMIZATION_THRESHOLD: 10, // ≥10 IDs: Enable aggressive optimization to prevent >25k token responses
  ULTRA_MINIMAL_THRESHOLD: 25, // ≥25 IDs: Ultra-minimal response for very large datasets
};

// Flag to enable saving raw response structures for documentation
export const SAVE_RAW_STRUCTURES = false; // Set to true when you want to capture raw structures
Install Server

Other Tools

Latest Blog Posts

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/jerichosequitin/Metabase'

If you have feedback or need assistance with the MCP directory API, please join our Discord server