PostgreSQL MCP Server

Instructions

Implementation Reference

• tools/getTableInfo.ts:33-201 (handler)

  The primary handler function `getTableInfo` that executes SQL queries against PostgreSQL's information_schema and pg catalogs to fetch comprehensive table metadata including columns, constraints, indexes, and optional size/row statistics. Handles validation, sanitization, connection checks, and error responses.
  export async function getTableInfo( rawParams: any ): McpToolResponse { try { // Validate and parse parameters const params = getTableInfoSchema.parse(rawParams); // Check database connection status const connectionStatus = getConnectionStatus(); if (connectionStatus.status !== 'connected') { return createDatabaseUnavailableResponse("get table information"); } const { table, schema_name, include_statistics } = params; // Validate identifiers const sanitizedTable = sanitizeIdentifier(table); const sanitizedSchema = sanitizeIdentifier(schema_name); // Get basic table information const tableInfoQuery = ` SELECT t.table_name, t.table_schema, t.table_type, obj_description(c.oid) as table_comment FROM information_schema.tables t LEFT JOIN pg_class c ON c.relname = t.table_name LEFT JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.table_schema WHERE t.table_schema = $1 AND t.table_name = $2 `; const tableInfo = await executePostgresQuery(tableInfoQuery, [sanitizedSchema, sanitizedTable]); if (tableInfo.length === 0) { throw new Error(`Table ${sanitizedSchema}.${sanitizedTable} not found`); } // Get detailed column information const columnsQuery = ` SELECT c.column_name, c.data_type, c.is_nullable, c.column_default, c.character_maximum_length, c.numeric_precision, c.numeric_scale, c.ordinal_position, col_description(pgc.oid, c.ordinal_position) as column_comment FROM information_schema.columns c LEFT JOIN pg_class pgc ON pgc.relname = c.table_name LEFT JOIN pg_namespace pgn ON pgn.oid = pgc.relnamespace AND pgn.nspname = c.table_schema WHERE c.table_schema = $1 AND c.table_name = $2 ORDER BY c.ordinal_position `; const columns = await executePostgresQuery(columnsQuery, [sanitizedSchema, sanitizedTable]); // Get constraints const constraintsQuery = ` SELECT tc.constraint_name, tc.constraint_type, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name, rc.match_option, rc.update_rule, rc.delete_rule 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 LEFT JOIN information_schema.referential_constraints rc ON tc.constraint_name = rc.constraint_name AND tc.table_schema = rc.constraint_schema WHERE tc.table_schema = $1 AND tc.table_name = $2 ORDER BY tc.constraint_type, tc.constraint_name `; const constraints = await executePostgresQuery(constraintsQuery, [sanitizedSchema, sanitizedTable]); // Get indexes const indexesQuery = ` SELECT i.relname as index_name, a.attname as column_name, ix.indisunique as is_unique, ix.indisprimary as is_primary, am.amname as index_type FROM pg_class i JOIN pg_index ix ON i.oid = ix.indexrelid JOIN pg_class t ON t.oid = ix.indrelid JOIN pg_namespace n ON n.oid = t.relnamespace JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) JOIN pg_am am ON i.relam = am.oid WHERE n.nspname = $1 AND t.relname = $2 ORDER BY i.relname, a.attnum `; const indexes = await executePostgresQuery(indexesQuery, [sanitizedSchema, sanitizedTable]); let statistics: TableStatistics | undefined; // Get table statistics if requested if (include_statistics) { const statsQuery = ` SELECT schemaname, tablename, attname, n_distinct, correlation FROM pg_stats WHERE schemaname = $1 AND tablename = $2 `; const sizeQuery = ` SELECT pg_stat_get_live_tuples(c.oid) as estimated_row_count, pg_total_relation_size(c.oid) as total_size_bytes, pg_size_pretty(pg_total_relation_size(c.oid)) as total_size_pretty, pg_relation_size(c.oid) as table_size_bytes, pg_size_pretty(pg_relation_size(c.oid)) as table_size_pretty, pg_total_relation_size(c.oid) - pg_relation_size(c.oid) as index_size_bytes, pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid)) as index_size_pretty FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = $1 AND c.relname = $2 `; try { const sizeResult = await executePostgresQuery(sizeQuery, [sanitizedSchema, sanitizedTable]); if (sizeResult.length > 0) { const rawStats = sizeResult[0]; statistics = { estimated_row_count: parseInt(rawStats.estimated_row_count) || 0, table_size_bytes: parseInt(rawStats.table_size_bytes) || 0, table_size_pretty: rawStats.table_size_pretty, index_size_bytes: parseInt(rawStats.index_size_bytes) || 0, index_size_pretty: rawStats.index_size_pretty, total_size_bytes: parseInt(rawStats.total_size_bytes) || 0, total_size_pretty: rawStats.total_size_pretty, }; } } catch (error) { debug("Failed to get table statistics: %o", error); } } const response = { table: tableInfo[0], columns: columns, constraints: constraints, indexes: indexes, ...(statistics && { statistics }), generated_at: new Date().toISOString(), }; return createMcpSuccessResponse(response); } catch (error) { return createMcpErrorResponse("get table info", error); } }
• tools/getTableInfo.ts:14-20 (schema)

  Zod schema defining input parameters for the tool: `table` (required string), `schema_name` (optional string, defaults to 'public'), `include_statistics` (optional boolean, defaults to true). Used for validation in the handler.
  export const getTableInfoShape: ZodRawShape = { table: z.string().min(1, "Table name is required"), schema_name: z.string().optional().default("public"), include_statistics: z.boolean().optional().default(true), }; export const getTableInfoSchema = z.object(getTableInfoShape);
• index.ts:69-74 (registration)

  Tool registration in the MCP server using `server.tool(name, description, inputSchema, handlerFunction)`, linking to the schema and handler from tools/getTableInfo.ts.
  server.tool( "get-table-info", "Get detailed information about a specific table including columns, constraints, indexes, and optionally statistics like row count and size.", getTableInfoShape, getTableInfo );