database_mcp_postgres/tools/
get_table_schema.rs

1//! MCP tool: `get_table_schema`.
2
3use std::borrow::Cow;
4use std::collections::HashMap;
5
6use database_mcp_server::AppError;
7use database_mcp_server::types::{GetTableSchemaRequest, TableSchemaResponse};
8use database_mcp_sql::Connection as _;
9use database_mcp_sql::identifier::validate_identifier;
10use rmcp::handler::server::router::tool::{AsyncTool, ToolBase};
11use rmcp::model::{ErrorData, ToolAnnotations};
12use serde_json::{Value, json};
13
14use crate::PostgresHandler;
15
16/// Marker type for the `get_table_schema` MCP tool.
17pub(crate) struct GetTableSchemaTool;
18
19impl GetTableSchemaTool {
20    const NAME: &'static str = "get_table_schema";
21    const DESCRIPTION: &'static str = r#"Get column definitions and foreign key relationships for a table. Requires `database_name` and `table_name` — call `list_databases` and `list_tables` first.
22
23<usecase>
24ALWAYS call this before writing queries to understand:
25- Column names and data types
26- Which columns are nullable, primary keys, or have defaults
27- Foreign key relationships for writing JOINs
28</usecase>
29
30<examples>
31✓ "What columns does the orders table have?" → get_table_schema(database_name="mydb", table_name="orders")
32✓ Before writing a SELECT → get_table_schema first to confirm column names
33✓ "How are users and orders related?" → check foreign keys in both tables
34</examples>
35
36<what_it_returns>
37A JSON object with table_name and columns keyed by column name, each containing type, nullable, key, default, and foreign_key info.
38</what_it_returns>"#;
39}
40
41impl ToolBase for GetTableSchemaTool {
42    type Parameter = GetTableSchemaRequest;
43    type Output = TableSchemaResponse;
44    type Error = ErrorData;
45
46    fn name() -> Cow<'static, str> {
47        Self::NAME.into()
48    }
49
50    fn description() -> Option<Cow<'static, str>> {
51        Some(Self::DESCRIPTION.into())
52    }
53
54    fn annotations() -> Option<ToolAnnotations> {
55        Some(
56            ToolAnnotations::new()
57                .read_only(true)
58                .destructive(false)
59                .idempotent(true)
60                .open_world(false),
61        )
62    }
63}
64
65impl AsyncTool<PostgresHandler> for GetTableSchemaTool {
66    async fn invoke(handler: &PostgresHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
67        Ok(handler.get_table_schema(&params).await?)
68    }
69}
70
71impl PostgresHandler {
72    /// Returns column definitions with foreign key relationships.
73    ///
74    /// # Errors
75    ///
76    /// Returns [`AppError`] if validation fails or the query errors.
77    pub async fn get_table_schema(&self, request: &GetTableSchemaRequest) -> Result<TableSchemaResponse, AppError> {
78        let table = &request.table_name;
79        validate_identifier(table)?;
80        let db = if request.database_name.is_empty() {
81            None
82        } else {
83            Some(request.database_name.as_str())
84        };
85
86        // 1. Get basic schema
87        let schema_sql = format!(
88            "SELECT column_name, data_type, is_nullable, column_default, \
89                    character_maximum_length \
90             FROM information_schema.columns \
91             WHERE table_schema = 'public' AND table_name = '{table}' \
92             ORDER BY ordinal_position"
93        );
94        let rows = self.connection.fetch(&schema_sql, db).await?;
95
96        if rows.is_empty() {
97            return Err(AppError::TableNotFound(table.clone()));
98        }
99
100        let mut columns: HashMap<String, Value> = HashMap::new();
101        for row in &rows {
102            let col_name = row
103                .get("column_name")
104                .and_then(Value::as_str)
105                .unwrap_or_default()
106                .to_owned();
107            let data_type = row
108                .get("data_type")
109                .and_then(Value::as_str)
110                .unwrap_or_default()
111                .to_owned();
112            let nullable = row
113                .get("is_nullable")
114                .and_then(Value::as_str)
115                .unwrap_or_default()
116                .to_owned();
117            let default = row.get("column_default").and_then(Value::as_str).map(str::to_owned);
118            columns.insert(
119                col_name,
120                json!({
121                    "type": data_type,
122                    "nullable": nullable.to_uppercase() == "YES",
123                    "key": Value::Null,
124                    "default": default,
125                    "extra": Value::Null,
126                    "foreign_key": Value::Null,
127                }),
128            );
129        }
130
131        // 2. Get FK relationships
132        let fk_sql = format!(
133            "SELECT \
134                kcu.column_name, \
135                tc.constraint_name, \
136                ccu.table_name AS referenced_table, \
137                ccu.column_name AS referenced_column, \
138                rc.update_rule AS on_update, \
139                rc.delete_rule AS on_delete \
140            FROM information_schema.table_constraints tc \
141            JOIN information_schema.key_column_usage kcu \
142                ON tc.constraint_name = kcu.constraint_name \
143                AND tc.table_schema = kcu.table_schema \
144            JOIN information_schema.constraint_column_usage ccu \
145                ON ccu.constraint_name = tc.constraint_name \
146                AND ccu.table_schema = tc.table_schema \
147            JOIN information_schema.referential_constraints rc \
148                ON rc.constraint_name = tc.constraint_name \
149                AND rc.constraint_schema = tc.table_schema \
150            WHERE tc.constraint_type = 'FOREIGN KEY' \
151                AND tc.table_name = '{table}' \
152                AND tc.table_schema = 'public'"
153        );
154        let fk_rows = self.connection.fetch(&fk_sql, db).await?;
155
156        for fk_row in &fk_rows {
157            let col_name = fk_row
158                .get("column_name")
159                .and_then(Value::as_str)
160                .unwrap_or_default()
161                .to_owned();
162            if let Some(col_info) = columns.get_mut(&col_name)
163                && let Some(obj) = col_info.as_object_mut()
164            {
165                obj.insert(
166                    "foreign_key".to_string(),
167                    json!({
168                        "constraint_name": fk_row.get("constraint_name").and_then(Value::as_str),
169                        "referenced_table": fk_row.get("referenced_table").and_then(Value::as_str),
170                        "referenced_column": fk_row.get("referenced_column").and_then(Value::as_str),
171                        "on_update": fk_row.get("on_update").and_then(Value::as_str),
172                        "on_delete": fk_row.get("on_delete").and_then(Value::as_str),
173                    }),
174                );
175            }
176        }
177
178        Ok(TableSchemaResponse {
179            table_name: table.clone(),
180            columns: json!(columns),
181        })
182    }
183}
database_mcp_postgres/tools/get_table_schema.rs

database_mcp_postgres/tools/
get_table_schema.rs
