mcp_postgres/actions/
schema_alter.rs

1use crate::errors::Result as MCPResult;
2use serde_json::{Value, json};
3use tokio_postgres::Client;
4
5const MAX_IDENTIFIER_LEN: usize = 255;
6
7fn validate_identifier(val: &str, label: &str) -> Result<(), crate::errors::MCPError> {
8    if val.is_empty() || val.len() > MAX_IDENTIFIER_LEN {
9        return Err(crate::errors::MCPError::InvalidParams(format!(
10            "'{label}' must be 1-{MAX_IDENTIFIER_LEN} characters"
11        )));
12    }
13    Ok(())
14}
15
16fn qi(ident: &str) -> String {
17    crate::validation::quote_ident(ident)
18}
19
20pub async fn add_column(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
21    let table = params
22        .as_ref()
23        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
24        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
25    let column = params
26        .as_ref()
27        .and_then(|p| p.get("column").and_then(|v| v.as_str()))
28        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'column'".into()))?;
29    let data_type = params
30        .as_ref()
31        .and_then(|p| p.get("data_type").and_then(|v| v.as_str()))
32        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'data_type'".into()))?;
33    let schema = params
34        .as_ref()
35        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
36        .unwrap_or("public");
37    let not_null = params
38        .as_ref()
39        .and_then(|p| p.get("not_null").and_then(|v| v.as_bool()))
40        .unwrap_or(false);
41    let default = params
42        .as_ref()
43        .and_then(|p| p.get("default").and_then(|v| v.as_str()));
44
45    validate_identifier(table, "table")?;
46    validate_identifier(column, "column")?;
47    validate_identifier(schema, "schema")?;
48
49    let mut sql = format!(
50        "ALTER TABLE {}.{} ADD COLUMN {}",
51        qi(schema),
52        qi(table),
53        qi(column)
54    );
55    sql.push_str(&format!(" {}", data_type));
56    if let Some(d) = default {
57        sql.push_str(&format!(" DEFAULT {}", d));
58    }
59    if not_null {
60        sql.push_str(" NOT NULL");
61    }
62
63    client.execute(&sql, &[]).await?;
64    Ok(json!({ "success": true, "sql": sql }))
65}
66
67pub async fn drop_column(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
68    let table = params
69        .as_ref()
70        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
71        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
72    let column = params
73        .as_ref()
74        .and_then(|p| p.get("column").and_then(|v| v.as_str()))
75        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'column'".into()))?;
76    let schema = params
77        .as_ref()
78        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
79        .unwrap_or("public");
80    let cascade = params
81        .as_ref()
82        .and_then(|p| p.get("cascade").and_then(|v| v.as_bool()))
83        .unwrap_or(false);
84
85    validate_identifier(table, "table")?;
86    validate_identifier(column, "column")?;
87    validate_identifier(schema, "schema")?;
88
89    let mut sql = format!(
90        "ALTER TABLE {}.{} DROP COLUMN {}",
91        qi(schema),
92        qi(table),
93        qi(column)
94    );
95    if cascade {
96        sql.push_str(" CASCADE");
97    }
98
99    client.execute(&sql, &[]).await?;
100    Ok(json!({ "success": true, "sql": sql }))
101}
102
103pub async fn rename_column(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
104    let table = params
105        .as_ref()
106        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
107        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
108    let column = params
109        .as_ref()
110        .and_then(|p| p.get("column").and_then(|v| v.as_str()))
111        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'column'".into()))?;
112    let new_name = params
113        .as_ref()
114        .and_then(|p| p.get("new_name").and_then(|v| v.as_str()))
115        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'new_name'".into()))?;
116    let schema = params
117        .as_ref()
118        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
119        .unwrap_or("public");
120
121    validate_identifier(table, "table")?;
122    validate_identifier(column, "column")?;
123    validate_identifier(new_name, "new_name")?;
124    validate_identifier(schema, "schema")?;
125
126    let sql = format!(
127        "ALTER TABLE {}.{} RENAME COLUMN {} TO {}",
128        qi(schema),
129        qi(table),
130        qi(column),
131        qi(new_name)
132    );
133    client.execute(&sql, &[]).await?;
134    Ok(json!({ "success": true, "sql": sql }))
135}
136
137pub async fn alter_column_type(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
138    let table = params
139        .as_ref()
140        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
141        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
142    let column = params
143        .as_ref()
144        .and_then(|p| p.get("column").and_then(|v| v.as_str()))
145        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'column'".into()))?;
146    let data_type = params
147        .as_ref()
148        .and_then(|p| p.get("data_type").and_then(|v| v.as_str()))
149        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'data_type'".into()))?;
150    let schema = params
151        .as_ref()
152        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
153        .unwrap_or("public");
154    let using = params
155        .as_ref()
156        .and_then(|p| p.get("using").and_then(|v| v.as_str()));
157
158    validate_identifier(table, "table")?;
159    validate_identifier(column, "column")?;
160    validate_identifier(schema, "schema")?;
161
162    let mut sql = format!(
163        "ALTER TABLE {}.{} ALTER COLUMN {} TYPE {}",
164        qi(schema),
165        qi(table),
166        qi(column),
167        data_type
168    );
169    if let Some(expr) = using {
170        sql.push_str(&format!(" USING {}", expr));
171    }
172
173    client.execute(&sql, &[]).await?;
174    Ok(json!({ "success": true, "sql": sql }))
175}
176
177pub async fn rename_table(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
178    let table = params
179        .as_ref()
180        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
181        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
182    let new_name = params
183        .as_ref()
184        .and_then(|p| p.get("new_name").and_then(|v| v.as_str()))
185        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'new_name'".into()))?;
186    let schema = params
187        .as_ref()
188        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
189        .unwrap_or("public");
190
191    validate_identifier(table, "table")?;
192    validate_identifier(new_name, "new_name")?;
193    validate_identifier(schema, "schema")?;
194
195    let sql = format!(
196        "ALTER TABLE {}.{} RENAME TO {}",
197        qi(schema),
198        qi(table),
199        qi(new_name)
200    );
201    client.execute(&sql, &[]).await?;
202    Ok(json!({ "success": true, "sql": sql }))
203}
204
205pub async fn rename_index(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
206    let index = params
207        .as_ref()
208        .and_then(|p| p.get("index").and_then(|v| v.as_str()))
209        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'index'".into()))?;
210    let new_name = params
211        .as_ref()
212        .and_then(|p| p.get("new_name").and_then(|v| v.as_str()))
213        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'new_name'".into()))?;
214    let schema = params
215        .as_ref()
216        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
217        .unwrap_or("public");
218
219    validate_identifier(index, "index")?;
220    validate_identifier(new_name, "new_name")?;
221    validate_identifier(schema, "schema")?;
222
223    let sql = format!(
224        "ALTER INDEX {}.{} RENAME TO {}",
225        qi(schema),
226        qi(index),
227        qi(new_name)
228    );
229    client.execute(&sql, &[]).await?;
230    Ok(json!({ "success": true, "sql": sql }))
231}
232
233pub async fn rename_schema(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
234    let schema = params
235        .as_ref()
236        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
237        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'schema'".into()))?;
238    let new_name = params
239        .as_ref()
240        .and_then(|p| p.get("new_name").and_then(|v| v.as_str()))
241        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'new_name'".into()))?;
242
243    validate_identifier(schema, "schema")?;
244    validate_identifier(new_name, "new_name")?;
245
246    let sql = format!("ALTER SCHEMA {} RENAME TO {}", qi(schema), qi(new_name));
247    client.execute(&sql, &[]).await?;
248    Ok(json!({ "success": true, "sql": sql }))
249}
250
251pub async fn add_foreign_key(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
252    let table = params
253        .as_ref()
254        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
255        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
256    let columns = params
257        .as_ref()
258        .and_then(|p| p.get("columns").and_then(|v| v.as_str()))
259        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'columns'".into()))?;
260    let ref_table = params
261        .as_ref()
262        .and_then(|p| p.get("ref_table").and_then(|v| v.as_str()))
263        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'ref_table'".into()))?;
264    let ref_columns = params
265        .as_ref()
266        .and_then(|p| p.get("ref_columns").and_then(|v| v.as_str()))
267        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'ref_columns'".into()))?;
268    let schema = params
269        .as_ref()
270        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
271        .unwrap_or("public");
272    let constraint_name = params
273        .as_ref()
274        .and_then(|p| p.get("constraint_name").and_then(|v| v.as_str()));
275    let on_delete = params
276        .as_ref()
277        .and_then(|p| p.get("on_delete").and_then(|v| v.as_str()));
278    let on_update = params
279        .as_ref()
280        .and_then(|p| p.get("on_update").and_then(|v| v.as_str()));
281
282    validate_identifier(table, "table")?;
283    validate_identifier(ref_table, "ref_table")?;
284    validate_identifier(schema, "schema")?;
285
286    let mut sql = format!("ALTER TABLE {}.{} ADD", qi(schema), qi(table));
287    if let Some(cname) = constraint_name {
288        sql.push_str(&format!(" CONSTRAINT {}", qi(cname)));
289    }
290    sql.push_str(&format!(
291        " FOREIGN KEY ({}) REFERENCES {}.{} ({})",
292        columns,
293        qi(schema),
294        qi(ref_table),
295        ref_columns
296    ));
297    if let Some(od) = on_delete {
298        sql.push_str(&format!(" ON DELETE {}", od));
299    }
300    if let Some(ou) = on_update {
301        sql.push_str(&format!(" ON UPDATE {}", ou));
302    }
303
304    client.execute(&sql, &[]).await?;
305    Ok(json!({ "success": true, "sql": sql }))
306}
307
308pub async fn drop_foreign_key(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
309    let table = params
310        .as_ref()
311        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
312        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
313    let constraint = params
314        .as_ref()
315        .and_then(|p| p.get("constraint").and_then(|v| v.as_str()))
316        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'constraint'".into()))?;
317    let schema = params
318        .as_ref()
319        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
320        .unwrap_or("public");
321    let cascade = params
322        .as_ref()
323        .and_then(|p| p.get("cascade").and_then(|v| v.as_bool()))
324        .unwrap_or(false);
325
326    validate_identifier(table, "table")?;
327    validate_identifier(constraint, "constraint")?;
328    validate_identifier(schema, "schema")?;
329
330    let mut sql = format!(
331        "ALTER TABLE {}.{} DROP CONSTRAINT {}",
332        qi(schema),
333        qi(table),
334        qi(constraint)
335    );
336    if cascade {
337        sql.push_str(" CASCADE");
338    }
339
340    client.execute(&sql, &[]).await?;
341    Ok(json!({ "success": true, "sql": sql }))
342}
343
344pub async fn add_unique_constraint(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
345    let table = params
346        .as_ref()
347        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
348        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
349    let columns = params
350        .as_ref()
351        .and_then(|p| p.get("columns").and_then(|v| v.as_str()))
352        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'columns'".into()))?;
353    let schema = params
354        .as_ref()
355        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
356        .unwrap_or("public");
357    let constraint_name = params
358        .as_ref()
359        .and_then(|p| p.get("constraint_name").and_then(|v| v.as_str()));
360
361    validate_identifier(table, "table")?;
362    validate_identifier(schema, "schema")?;
363
364    let mut sql = format!("ALTER TABLE {}.{} ADD", qi(schema), qi(table));
365    if let Some(cname) = constraint_name {
366        validate_identifier(cname, "constraint_name")?;
367        sql.push_str(&format!(" CONSTRAINT {}", qi(cname)));
368    }
369    sql.push_str(&format!(" UNIQUE ({})", columns));
370
371    client.execute(&sql, &[]).await?;
372    Ok(json!({ "success": true, "sql": sql }))
373}
374
375pub async fn drop_constraint(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
376    let table = params
377        .as_ref()
378        .and_then(|p| p.get("table").and_then(|v| v.as_str()))
379        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'table'".into()))?;
380    let constraint = params
381        .as_ref()
382        .and_then(|p| p.get("constraint").and_then(|v| v.as_str()))
383        .ok_or_else(|| crate::errors::MCPError::InvalidParams("Missing 'constraint'".into()))?;
384    let schema = params
385        .as_ref()
386        .and_then(|p| p.get("schema").and_then(|v| v.as_str()))
387        .unwrap_or("public");
388    let cascade = params
389        .as_ref()
390        .and_then(|p| p.get("cascade").and_then(|v| v.as_bool()))
391        .unwrap_or(false);
392
393    validate_identifier(table, "table")?;
394    validate_identifier(constraint, "constraint")?;
395    validate_identifier(schema, "schema")?;
396
397    let mut sql = format!(
398        "ALTER TABLE {}.{} DROP CONSTRAINT {}",
399        qi(schema),
400        qi(table),
401        qi(constraint)
402    );
403    if cascade {
404        sql.push_str(" CASCADE");
405    }
406
407    client.execute(&sql, &[]).await?;
408    Ok(json!({ "success": true, "sql": sql }))
409}
mcp_postgres/actions/schema_alter.rs

mcp_postgres/actions/
schema_alter.rs
