Skip to content

Commit d6404b3

Browse files
souvikghosh04Jerry NixonranishanRubenCerna2079Copilot
authored
[MCP] Adding delete_record (#2889)
## Why make this change? ### Closes on - delete_record: #2830 ## What is this change? This PR implements built-int tool `delete_record` as part of built-in tools to support delete operation on a table entity. - deletes one record at a time - Entity name and Keys should be specified - Keys supports having a single primary key or composite key (refer sample request below) - Operation is performed based on permissions as configured in dab-config - Success or Failure message response is generated on execution of the delete operation ## How was this tested? Functional testing using Insomnia client by running DAB in localhost and local SQL DB database - MCP endpoint: http://localhost:5000/mcp - JSON payload (details below) - Querying and validating data in local database ## Sample Request(s) Delete by single Primary Key ``` POST: http://localhost:5000/mcp { "jsonrpc": "2.0", "id": 2, "method": "tools/call", "params": { "name": "delete_record", "arguments": { "entity": "Book", "keys": { "id": 5009 } } } } ``` Delete by a composite key ``` POST http://localhost:5000/mcp { "jsonrpc": "2.0", "id": 2, "method": "tools/call", "params": { "name": "delete_record", "arguments": { "entity": "Stock", "keys": { "categoryid": 10, "pieceid": 1 } } } } ``` --------- Co-authored-by: Jerry Nixon <[email protected]> Co-authored-by: Rahul Nishant <[email protected]> Co-authored-by: RubenCerna2079 <[email protected]> Co-authored-by: Copilot <[email protected]> Co-authored-by: Aniruddh Munde <[email protected]> Co-authored-by: Ruben Cerna <[email protected]> Co-authored-by: Anusha Kolan <[email protected]>
1 parent a86996d commit d6404b3

5 files changed

Lines changed: 725 additions & 0 deletions

File tree

‎src/Azure.DataApiBuilder.Mcp/BuiltInTools/DeleteRecordTool.cs‎

Lines changed: 346 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,346 @@
1+
// Copyright (c) Microsoft Corporation.
2+
// Licensed under the MIT License.
3+
4+
using System.Data.Common;
5+
using System.Text.Json;
6+
using Azure.DataApiBuilder.Auth;
7+
using Azure.DataApiBuilder.Config.DatabasePrimitives;
8+
using Azure.DataApiBuilder.Config.ObjectModel;
9+
using Azure.DataApiBuilder.Core.Configurations;
10+
using Azure.DataApiBuilder.Core.Models;
11+
using Azure.DataApiBuilder.Core.Resolvers;
12+
using Azure.DataApiBuilder.Core.Resolvers.Factories;
13+
using Azure.DataApiBuilder.Core.Services;
14+
using Azure.DataApiBuilder.Core.Services.MetadataProviders;
15+
using Azure.DataApiBuilder.Mcp.Model;
16+
using Azure.DataApiBuilder.Mcp.Utils;
17+
using Azure.DataApiBuilder.Service.Exceptions;
18+
using Microsoft.AspNetCore.Http;
19+
using Microsoft.AspNetCore.Mvc;
20+
using Microsoft.Data.SqlClient;
21+
using Microsoft.Extensions.DependencyInjection;
22+
using Microsoft.Extensions.Logging;
23+
using ModelContextProtocol.Protocol;
24+
using static Azure.DataApiBuilder.Mcp.Model.McpEnums;
25+
26+
namespace Azure.DataApiBuilder.Mcp.BuiltInTools
27+
{
28+
/// <summary>
29+
/// Tool to delete records from a table/view entity configured in DAB.
30+
/// Supports both simple and composite primary keys.
31+
/// </summary>
32+
public class DeleteRecordTool : IMcpTool
33+
{
34+
/// <summary>
35+
/// Gets the type of the tool, which is BuiltIn for this implementation.
36+
/// </summary>
37+
public ToolType ToolType { get; } = ToolType.BuiltIn;
38+
39+
/// <summary>
40+
/// Gets the metadata for the delete-record tool, including its name, description, and input schema.
41+
/// </summary>
42+
public Tool GetToolMetadata()
43+
{
44+
return new Tool
45+
{
46+
Name = "delete_record",
47+
Description = "Deletes a record from a table based on primary key or composite key",
48+
InputSchema = JsonSerializer.Deserialize<JsonElement>(
49+
@"{
50+
""type"": ""object"",
51+
""properties"": {
52+
""entity"": {
53+
""type"": ""string"",
54+
""description"": ""The name of the entity (table) as configured in dab-config. Required.""
55+
},
56+
""keys"": {
57+
""type"": ""object"",
58+
""description"": ""Primary key values to identify the record to delete. For composite keys, provide all key columns as properties. Required.""
59+
}
60+
},
61+
""required"": [""entity"", ""keys""]
62+
}"
63+
)
64+
};
65+
}
66+
67+
/// <summary>
68+
/// Executes the delete-record tool, deleting an existing record in the specified entity using provided keys.
69+
/// </summary>
70+
public async Task<CallToolResult> ExecuteAsync(
71+
JsonDocument? arguments,
72+
IServiceProvider serviceProvider,
73+
CancellationToken cancellationToken = default)
74+
{
75+
ILogger<DeleteRecordTool>? logger = serviceProvider.GetService<ILogger<DeleteRecordTool>>();
76+
77+
try
78+
{
79+
// Cancellation check at the start
80+
cancellationToken.ThrowIfCancellationRequested();
81+
82+
// 1) Resolve required services & configuration
83+
RuntimeConfigProvider runtimeConfigProvider = serviceProvider.GetRequiredService<RuntimeConfigProvider>();
84+
RuntimeConfig config = runtimeConfigProvider.GetConfig();
85+
86+
// 2) Check if the tool is enabled in configuration before proceeding
87+
if (config.McpDmlTools?.DeleteRecord != true)
88+
{
89+
return McpResponseBuilder.BuildErrorResult(
90+
"ToolDisabled",
91+
$"The {this.GetToolMetadata().Name} tool is disabled in the configuration.",
92+
logger);
93+
}
94+
95+
// 3) Parsing & basic argument validation
96+
if (arguments is null)
97+
{
98+
return McpResponseBuilder.BuildErrorResult("InvalidArguments", "No arguments provided.", logger);
99+
}
100+
101+
if (!McpArgumentParser.TryParseEntityAndKeys(arguments.RootElement, out string entityName, out Dictionary<string, object?> keys, out string parseError))
102+
{
103+
return McpResponseBuilder.BuildErrorResult("InvalidArguments", parseError, logger);
104+
}
105+
106+
IMetadataProviderFactory metadataProviderFactory = serviceProvider.GetRequiredService<IMetadataProviderFactory>();
107+
IMutationEngineFactory mutationEngineFactory = serviceProvider.GetRequiredService<IMutationEngineFactory>();
108+
109+
// 4) Resolve metadata for entity existence check
110+
string dataSourceName;
111+
ISqlMetadataProvider sqlMetadataProvider;
112+
113+
try
114+
{
115+
dataSourceName = config.GetDataSourceNameFromEntityName(entityName);
116+
sqlMetadataProvider = metadataProviderFactory.GetMetadataProvider(dataSourceName);
117+
}
118+
catch (Exception)
119+
{
120+
return McpResponseBuilder.BuildErrorResult("EntityNotFound", $"Entity '{entityName}' is not defined in the configuration.", logger);
121+
}
122+
123+
if (!sqlMetadataProvider.EntityToDatabaseObject.TryGetValue(entityName, out DatabaseObject? dbObject) || dbObject is null)
124+
{
125+
return McpResponseBuilder.BuildErrorResult("EntityNotFound", $"Entity '{entityName}' is not defined in the configuration.", logger);
126+
}
127+
128+
// Validate it's a table or view
129+
if (dbObject.SourceType != EntitySourceType.Table && dbObject.SourceType != EntitySourceType.View)
130+
{
131+
return McpResponseBuilder.BuildErrorResult("InvalidEntity", $"Entity '{entityName}' is not a table or view. Use 'execute-entity' for stored procedures.", logger);
132+
}
133+
134+
// 5) Authorization
135+
IAuthorizationResolver authResolver = serviceProvider.GetRequiredService<IAuthorizationResolver>();
136+
IHttpContextAccessor httpContextAccessor = serviceProvider.GetRequiredService<IHttpContextAccessor>();
137+
HttpContext? httpContext = httpContextAccessor.HttpContext;
138+
139+
if (!McpAuthorizationHelper.ValidateRoleContext(httpContext, authResolver, out string roleError))
140+
{
141+
return McpResponseBuilder.BuildErrorResult("PermissionDenied", $"Permission denied: {roleError}", logger);
142+
}
143+
144+
if (!McpAuthorizationHelper.TryResolveAuthorizedRole(
145+
httpContext!,
146+
authResolver,
147+
entityName,
148+
EntityActionOperation.Delete,
149+
out string? effectiveRole,
150+
out string authError))
151+
{
152+
return McpResponseBuilder.BuildErrorResult("PermissionDenied", $"Permission denied: {authError}", logger);
153+
}
154+
155+
// 6) Build and validate Delete context
156+
RequestValidator requestValidator = new(metadataProviderFactory, runtimeConfigProvider);
157+
158+
DeleteRequestContext context = new(
159+
entityName: entityName,
160+
dbo: dbObject,
161+
isList: false);
162+
163+
foreach (KeyValuePair<string, object?> kvp in keys)
164+
{
165+
if (kvp.Value is null)
166+
{
167+
return McpResponseBuilder.BuildErrorResult("InvalidArguments", $"Primary key value for '{kvp.Key}' cannot be null.", logger);
168+
}
169+
170+
context.PrimaryKeyValuePairs[kvp.Key] = kvp.Value;
171+
}
172+
173+
requestValidator.ValidatePrimaryKey(context);
174+
175+
// 7) Execute
176+
DatabaseType dbType = config.GetDataSourceFromDataSourceName(dataSourceName).DatabaseType;
177+
IMutationEngine mutationEngine = mutationEngineFactory.GetMutationEngine(dbType);
178+
179+
IActionResult? mutationResult = null;
180+
try
181+
{
182+
// Cancellation check before executing
183+
cancellationToken.ThrowIfCancellationRequested();
184+
mutationResult = await mutationEngine.ExecuteAsync(context).ConfigureAwait(false);
185+
}
186+
catch (DataApiBuilderException dabEx)
187+
{
188+
// Handle specific DAB exceptions
189+
logger?.LogError(dabEx, "Data API Builder error deleting record from {Entity}", entityName);
190+
191+
string message = dabEx.Message;
192+
193+
// Check for specific error patterns
194+
if (message.Contains("Could not find item with", StringComparison.OrdinalIgnoreCase))
195+
{
196+
string keyDetails = McpJsonHelper.FormatKeyDetails(keys);
197+
return McpResponseBuilder.BuildErrorResult(
198+
"RecordNotFound",
199+
$"No record found with the specified primary key: {keyDetails}",
200+
logger);
201+
}
202+
else if (message.Contains("violates foreign key constraint", StringComparison.OrdinalIgnoreCase) ||
203+
message.Contains("REFERENCE constraint", StringComparison.OrdinalIgnoreCase))
204+
{
205+
return McpResponseBuilder.BuildErrorResult(
206+
"ConstraintViolation",
207+
"Cannot delete record due to foreign key constraint. Other records depend on this record.",
208+
logger);
209+
}
210+
else if (message.Contains("permission", StringComparison.OrdinalIgnoreCase) ||
211+
message.Contains("authorization", StringComparison.OrdinalIgnoreCase))
212+
{
213+
return McpResponseBuilder.BuildErrorResult(
214+
"PermissionDenied",
215+
"You do not have permission to delete this record.",
216+
logger);
217+
}
218+
else if (message.Contains("invalid", StringComparison.OrdinalIgnoreCase) &&
219+
message.Contains("type", StringComparison.OrdinalIgnoreCase))
220+
{
221+
return McpResponseBuilder.BuildErrorResult(
222+
"InvalidArguments",
223+
"Invalid data type for one or more key values.",
224+
logger);
225+
}
226+
227+
// For any other DAB exceptions, return the message as-is
228+
return McpResponseBuilder.BuildErrorResult(
229+
"DataApiBuilderError",
230+
dabEx.Message,
231+
logger);
232+
}
233+
catch (SqlException sqlEx)
234+
{
235+
// Handle SQL Server specific errors
236+
logger?.LogError(sqlEx, "SQL Server error deleting record from {Entity}", entityName);
237+
string errorMessage = sqlEx.Number switch
238+
{
239+
547 => "Cannot delete record due to foreign key constraint. Other records depend on this record.",
240+
2627 or 2601 => "Cannot delete record due to unique constraint violation.",
241+
229 or 262 => $"Permission denied to delete from table '{dbObject.FullName}'.",
242+
208 => $"Table '{dbObject.FullName}' not found in the database.",
243+
_ => $"Database error: {sqlEx.Message}"
244+
};
245+
return McpResponseBuilder.BuildErrorResult("DatabaseError", errorMessage, logger);
246+
}
247+
catch (DbException dbEx)
248+
{
249+
// Handle generic database exceptions (works for PostgreSQL, MySQL, etc.)
250+
logger?.LogError(dbEx, "Database error deleting record from {Entity}", entityName);
251+
252+
// Check for common patterns in error messages
253+
string errorMsg = dbEx.Message.ToLowerInvariant();
254+
if (errorMsg.Contains("foreign key") || errorMsg.Contains("constraint"))
255+
{
256+
return McpResponseBuilder.BuildErrorResult(
257+
"ConstraintViolation",
258+
"Cannot delete record due to foreign key constraint. Other records depend on this record.",
259+
logger);
260+
}
261+
else if (errorMsg.Contains("not found") || errorMsg.Contains("does not exist"))
262+
{
263+
return McpResponseBuilder.BuildErrorResult(
264+
"RecordNotFound",
265+
"No record found with the specified primary key.",
266+
logger);
267+
}
268+
269+
return McpResponseBuilder.BuildErrorResult("DatabaseError", $"Database error: {dbEx.Message}", logger);
270+
}
271+
catch (InvalidOperationException ioEx) when (ioEx.Message.Contains("connection", StringComparison.OrdinalIgnoreCase))
272+
{
273+
// Handle connection-related issues
274+
logger?.LogError(ioEx, "Database connection error");
275+
return McpResponseBuilder.BuildErrorResult("ConnectionError", "Failed to connect to the database.", logger);
276+
}
277+
catch (TimeoutException timeoutEx)
278+
{
279+
// Handle query timeout
280+
logger?.LogError(timeoutEx, "Delete operation timeout for {Entity}", entityName);
281+
return McpResponseBuilder.BuildErrorResult("TimeoutError", "The delete operation timed out.", logger);
282+
}
283+
catch (Exception ex)
284+
{
285+
string errorMsg = ex.Message ?? string.Empty;
286+
287+
if (errorMsg.Contains("Could not find", StringComparison.OrdinalIgnoreCase) ||
288+
errorMsg.Contains("record not found", StringComparison.OrdinalIgnoreCase))
289+
{
290+
string keyDetails = McpJsonHelper.FormatKeyDetails(keys);
291+
return McpResponseBuilder.BuildErrorResult(
292+
"RecordNotFound",
293+
$"No entity found with the given key {keyDetails}.",
294+
logger);
295+
}
296+
else
297+
{
298+
// Re-throw unexpected exceptions
299+
throw;
300+
}
301+
}
302+
303+
// 8) Build response
304+
// Based on SqlMutationEngine, delete operations typically return NoContentResult
305+
// We build a success response with just the operation details
306+
Dictionary<string, object?> responseData = new()
307+
{
308+
["entity"] = entityName,
309+
["keyDetails"] = McpJsonHelper.FormatKeyDetails(keys),
310+
["message"] = "Record deleted successfully"
311+
};
312+
313+
// If the mutation result is OkObjectResult (which would be unusual for delete),
314+
// include the result value directly without re-serialization
315+
if (mutationResult is OkObjectResult okObjectResult && okObjectResult.Value is not null)
316+
{
317+
responseData["result"] = okObjectResult.Value;
318+
}
319+
320+
return McpResponseBuilder.BuildSuccessResult(
321+
responseData,
322+
logger,
323+
$"DeleteRecordTool success for entity {entityName}."
324+
);
325+
}
326+
catch (OperationCanceledException)
327+
{
328+
return McpResponseBuilder.BuildErrorResult("OperationCanceled", "The delete operation was canceled.", logger);
329+
}
330+
catch (ArgumentException argEx)
331+
{
332+
return McpResponseBuilder.BuildErrorResult("InvalidArguments", argEx.Message, logger);
333+
}
334+
catch (Exception ex)
335+
{
336+
ILogger<DeleteRecordTool>? innerLogger = serviceProvider.GetService<ILogger<DeleteRecordTool>>();
337+
innerLogger?.LogError(ex, "Unexpected error in DeleteRecordTool.");
338+
339+
return McpResponseBuilder.BuildErrorResult(
340+
"UnexpectedError",
341+
"An unexpected error occurred during the delete operation.",
342+
logger);
343+
}
344+
}
345+
}
346+
}

0 commit comments

Comments
 (0)