database_mcp_sqlite/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::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::SqliteHandler;
15use crate::types::GetTableSchemaRequest;
16
17/// Marker type for the `get_table_schema` MCP tool.
18pub(crate) struct GetTableSchemaTool;
19
20impl GetTableSchemaTool {
21    const NAME: &'static str = "get_table_schema";
22    const TITLE: &'static str = "Get Table Schema";
23    const DESCRIPTION: &'static str = r#"Get column definitions and foreign key relationships for a table. Requires `table_name` — call `list_tables` first.
24
25<usecase>
26ALWAYS call this before writing queries to understand:
27- Column names and data types
28- Which columns are nullable, primary keys, or have defaults
29- Foreign key relationships for writing JOINs
30</usecase>
31
32<examples>
33✓ "What columns does the orders table have?" → get_table_schema(table_name="orders")
34✓ Before writing a SELECT → get_table_schema first to confirm column names
35✓ "How are users and orders related?" → check foreign keys in both tables
36</examples>
37
38<what_it_returns>
39A JSON object with table_name and columns keyed by column name, each containing type, nullable, key, default, and foreign_key info.
40</what_it_returns>"#;
41}
42
43impl ToolBase for GetTableSchemaTool {
44    type Parameter = GetTableSchemaRequest;
45    type Output = TableSchemaResponse;
46    type Error = ErrorData;
47
48    fn name() -> Cow<'static, str> {
49        Self::NAME.into()
50    }
51
52    fn title() -> Option<String> {
53        Some(Self::TITLE.into())
54    }
55
56    fn description() -> Option<Cow<'static, str>> {
57        Some(Self::DESCRIPTION.into())
58    }
59
60    fn annotations() -> Option<ToolAnnotations> {
61        Some(
62            ToolAnnotations::new()
63                .read_only(true)
64                .destructive(false)
65                .idempotent(true)
66                .open_world(false),
67        )
68    }
69}
70
71impl AsyncTool<SqliteHandler> for GetTableSchemaTool {
72    async fn invoke(handler: &SqliteHandler, params: Self::Parameter) -> Result<Self::Output, Self::Error> {
73        Ok(handler.get_table_schema(&params).await?)
74    }
75}
76
77impl SqliteHandler {
78    /// Returns column definitions with foreign key relationships.
79    ///
80    /// # Errors
81    ///
82    /// Returns [`AppError`] if validation fails or the query errors.
83    pub async fn get_table_schema(&self, request: &GetTableSchemaRequest) -> Result<TableSchemaResponse, AppError> {
84        let table = &request.table_name;
85        validate_identifier(table)?;
86
87        // 1. Get basic schema
88        let pragma_sql = format!("PRAGMA table_info({})", self.connection.quote_identifier(table));
89        let rows = self.connection.fetch(pragma_sql.as_str(), None).await?;
90
91        if rows.is_empty() {
92            return Err(AppError::TableNotFound(table.clone()));
93        }
94
95        let mut columns: HashMap<String, Value> = HashMap::new();
96        for row in &rows {
97            let col_name = row.get("name").and_then(Value::as_str).unwrap_or_default().to_owned();
98            let col_type = row.get("type").and_then(Value::as_str).unwrap_or_default().to_owned();
99            let notnull = row.get("notnull").and_then(Value::as_i64).unwrap_or(0);
100            let default = row.get("dflt_value").and_then(Value::as_str).map(str::to_owned);
101            let pk = row.get("pk").and_then(Value::as_i64).unwrap_or(0);
102            columns.insert(
103                col_name,
104                json!({
105                    "type": col_type,
106                    "nullable": notnull == 0,
107                    "key": if pk > 0 { "PRI" } else { "" },
108                    "default": default,
109                    "extra": Value::Null,
110                    "foreign_key": Value::Null,
111                }),
112            );
113        }
114
115        // 2. Get FK info via PRAGMA
116        let fk_pragma_sql = format!("PRAGMA foreign_key_list({})", self.connection.quote_identifier(table));
117        let fk_rows = self.connection.fetch(fk_pragma_sql.as_str(), None).await?;
118
119        for fk_row in &fk_rows {
120            let from_col = fk_row
121                .get("from")
122                .and_then(Value::as_str)
123                .unwrap_or_default()
124                .to_owned();
125            if let Some(col_info) = columns.get_mut(&from_col)
126                && let Some(obj) = col_info.as_object_mut()
127            {
128                let ref_table = fk_row
129                    .get("table")
130                    .and_then(Value::as_str)
131                    .unwrap_or_default()
132                    .to_owned();
133                let ref_col = fk_row.get("to").and_then(Value::as_str).unwrap_or_default().to_owned();
134                let on_update = fk_row
135                    .get("on_update")
136                    .and_then(Value::as_str)
137                    .unwrap_or_default()
138                    .to_owned();
139                let on_delete = fk_row
140                    .get("on_delete")
141                    .and_then(Value::as_str)
142                    .unwrap_or_default()
143                    .to_owned();
144                obj.insert(
145                    "foreign_key".to_string(),
146                    json!({
147                        "constraint_name": Value::Null,
148                        "referenced_table": ref_table,
149                        "referenced_column": ref_col,
150                        "on_update": on_update,
151                        "on_delete": on_delete,
152                    }),
153                );
154            }
155        }
156
157        Ok(TableSchemaResponse {
158            table_name: table.clone(),
159            columns: json!(columns),
160        })
161    }
162}
database_mcp_sqlite/tools/get_table_schema.rs

database_mcp_sqlite/tools/
get_table_schema.rs
