dbmcp_postgres/tools/
get_table_schema.rs

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

dbmcp_postgres/tools/
get_table_schema.rs
