database_mcp_sqlite/tools/
get_table_schema.rs

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

database_mcp_sqlite/tools/
get_table_schema.rs
