Skip to content

Commit 1dd0882

Browse files
aaronburtleAniruddh25
aaronburtle
and
Aniruddh25
authored
Add keyless REST PUT/PATCH support for entities with auto-generated primary keys (#3150)
## Why make this change? Closes #2663 One test change to help the pipeline pass relates to #2992 ## What is this change? ## Add keyless REST PUT/PATCH support for entities with auto-generated primary keys We now support keyless PUT/PATCH requests so long as the keys that are missing are auto-generated by the entity in question. If the URL is keyless, and the request body is keyless, then all of the keys needed for the request must be auto-generated. If the request body contains keys, then we allow the request only if the request body contains all non auto-generated keys. So for example, if the entity has a composite key, then it would be allowed to omit any auto-generated keys from that composite key, but the non auto-generated keys must be included. This is handled with new validation logic, which will now account for keyless PUT/PATCH, simply validating that any non auto-generated keys are included. * In `OpenApiDocumentor`, keyless `PUT` and `PATCH` operations are now documented for entities with auto-generated primary keys on the base entity path. These use the `_NoAutoPK` request body schema and only advertise `201 Created` responses. A missing `AddQueryParameters` helper method was also added. * Stored procedure entities are unaffected. * Mutation engine is modified to detect the truly keyless operation in case of Upsert/UpsertIncremental to then trigger the Insert workflow instead otherwise the SQL statement will be incorrectly generated. ## How was this tested? We modify existing tests to match the new behavior, and then add a test for keyless PUT/PATCH. ## Sample Request(s) ``` PUT /api/Book { "title": "My New Book", "publisher_id": 1234 } PATCH /api/Book { "title": "Another New Book", "publisher_id": 5678 } ``` --------- Co-authored-by: Aniruddh Munde <[email protected]>
1 parent b2310d0 commit 1dd0882

14 files changed

Lines changed: 411 additions & 34 deletions

‎src/Core/Resolvers/SqlMutationEngine.cs‎

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -541,6 +541,84 @@ await queryExecutor.ExecuteQueryAsync(
541541
{
542542
if (context.OperationType is EntityActionOperation.Upsert || context.OperationType is EntityActionOperation.UpsertIncremental)
543543
{
544+
// When no primary key values are provided (empty PrimaryKeyValuePairs),
545+
// there is no row to look up for update. The upsert degenerates to a
546+
// pure INSERT - execute it via the insert path so the mutation engine
547+
// generates a correct INSERT statement instead of an UPDATE with an
548+
// empty WHERE clause (WHERE 1 = 1) that would match every row.
549+
if (context.PrimaryKeyValuePairs.Count == 0)
550+
{
551+
DbResultSetRow? insertResultRow = null;
552+
553+
try
554+
{
555+
using (TransactionScope transactionScope = ConstructTransactionScopeBasedOnDbType(sqlMetadataProvider))
556+
{
557+
insertResultRow =
558+
await PerformMutationOperation(
559+
entityName: context.EntityName,
560+
operationType: EntityActionOperation.Insert,
561+
parameters: parameters,
562+
sqlMetadataProvider: sqlMetadataProvider);
563+
564+
if (insertResultRow is null)
565+
{
566+
throw new DataApiBuilderException(
567+
message: "An unexpected error occurred while trying to execute the query.",
568+
statusCode: HttpStatusCode.InternalServerError,
569+
subStatusCode: DataApiBuilderException.SubStatusCodes.UnexpectedError);
570+
}
571+
572+
if (insertResultRow.Columns.Count == 0)
573+
{
574+
throw new DataApiBuilderException(
575+
message: "Could not insert row with given values.",
576+
statusCode: HttpStatusCode.Forbidden,
577+
subStatusCode: DataApiBuilderException.SubStatusCodes.DatabasePolicyFailure);
578+
}
579+
580+
if (isDatabasePolicyDefinedForReadAction)
581+
{
582+
FindRequestContext findRequestContext = ConstructFindRequestContext(context, insertResultRow, roleName, sqlMetadataProvider);
583+
IQueryEngine queryEngine = _queryEngineFactory.GetQueryEngine(sqlMetadataProvider.GetDatabaseType());
584+
selectOperationResponse = await queryEngine.ExecuteAsync(findRequestContext);
585+
}
586+
587+
transactionScope.Complete();
588+
}
589+
}
590+
catch (TransactionException)
591+
{
592+
throw _dabExceptionWithTransactionErrorMessage;
593+
}
594+
595+
if (isReadPermissionConfiguredForRole && !isDatabasePolicyDefinedForReadAction)
596+
{
597+
IEnumerable<string> allowedExposedColumns = _authorizationResolver.GetAllowedExposedColumns(context.EntityName, roleName, EntityActionOperation.Read);
598+
foreach (string columnInResponse in insertResultRow.Columns.Keys)
599+
{
600+
if (!allowedExposedColumns.Contains(columnInResponse))
601+
{
602+
insertResultRow.Columns.Remove(columnInResponse);
603+
}
604+
}
605+
}
606+
607+
string pkRouteForLocationHeader = isReadPermissionConfiguredForRole
608+
? SqlResponseHelpers.ConstructPrimaryKeyRoute(context, insertResultRow.Columns, sqlMetadataProvider)
609+
: string.Empty;
610+
611+
return SqlResponseHelpers.ConstructCreatedResultResponse(
612+
insertResultRow.Columns,
613+
selectOperationResponse,
614+
pkRouteForLocationHeader,
615+
isReadPermissionConfiguredForRole,
616+
isDatabasePolicyDefinedForReadAction,
617+
context.OperationType,
618+
GetBaseRouteFromConfig(_runtimeConfigProvider.GetConfig()),
619+
GetHttpContext());
620+
}
621+
544622
DbResultSet? upsertOperationResult;
545623
DbResultSetRow upsertOperationResultSetRow;
546624

‎src/Core/Services/OpenAPI/OpenApiDocumentor.cs‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,7 @@ public class OpenApiDocumentor : IOpenApiDocumentor
4343
private const string GETONE_DESCRIPTION = "Returns an entity.";
4444
private const string POST_DESCRIPTION = "Create entity.";
4545
private const string PUT_DESCRIPTION = "Replace or create entity.";
46+
private const string PUT_PATCH_KEYLESS_DESCRIPTION = "Create entity (keyless). For entities with auto-generated primary keys, creates a new record without requiring the key in the URL.";
4647
private const string PATCH_DESCRIPTION = "Update or create entity.";
4748
private const string DELETE_DESCRIPTION = "Delete entity.";
4849
private const string SP_EXECUTE_DESCRIPTION = "Executes a stored procedure.";
@@ -502,6 +503,31 @@ private Dictionary<OperationType, OpenApiOperation> CreateOperations(
502503
openApiPathItemOperations.Add(OperationType.Post, postOperation);
503504
}
504505

506+
// For entities with auto-generated primary keys, add keyless PUT and PATCH operations.
507+
// These routes allow creating records without specifying the primary key in the URL,
508+
// which is useful for entities with identity/auto-generated keys.
509+
if (DoesSourceContainAutogeneratedPrimaryKey(sourceDefinition))
510+
{
511+
string keylessBodySchemaReferenceId = $"{entityName}_NoAutoPK";
512+
bool keylessRequestBodyRequired = IsRequestBodyRequired(sourceDefinition, considerPrimaryKeys: true);
513+
514+
if (configuredRestOperations[OperationType.Put])
515+
{
516+
OpenApiOperation putKeylessOperation = CreateBaseOperation(description: PUT_PATCH_KEYLESS_DESCRIPTION, tags: tags);
517+
putKeylessOperation.RequestBody = CreateOpenApiRequestBodyPayload(keylessBodySchemaReferenceId, keylessRequestBodyRequired);
518+
putKeylessOperation.Responses.Add(HttpStatusCode.Created.ToString("D"), CreateOpenApiResponse(description: nameof(HttpStatusCode.Created), responseObjectSchemaName: entityName));
519+
openApiPathItemOperations.Add(OperationType.Put, putKeylessOperation);
520+
}
521+
522+
if (configuredRestOperations[OperationType.Patch])
523+
{
524+
OpenApiOperation patchKeylessOperation = CreateBaseOperation(description: PUT_PATCH_KEYLESS_DESCRIPTION, tags: tags);
525+
patchKeylessOperation.RequestBody = CreateOpenApiRequestBodyPayload(keylessBodySchemaReferenceId, keylessRequestBodyRequired);
526+
patchKeylessOperation.Responses.Add(HttpStatusCode.Created.ToString("D"), CreateOpenApiResponse(description: nameof(HttpStatusCode.Created), responseObjectSchemaName: entityName));
527+
openApiPathItemOperations.Add(OperationType.Patch, patchKeylessOperation);
528+
}
529+
}
530+
505531
return openApiPathItemOperations;
506532
}
507533
}

‎src/Core/Services/RequestValidator.cs‎

Lines changed: 42 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -348,8 +348,12 @@ public void ValidateInsertRequestContext(InsertRequestContext insertRequestCtx)
348348
/// and vice versa.
349349
/// </summary>
350350
/// <param name="upsertRequestCtx">Upsert Request context containing the request body.</param>
351+
/// <param name="primaryKeyInUrl">When true the primary key was provided in the URL route
352+
/// and PK columns in the body are skipped (original behaviour). When false the primary key
353+
/// is expected in the request body, so non-auto-generated PK columns must be present and
354+
/// the full composite key (if applicable) must be supplied.</param>
351355
/// <exception cref="DataApiBuilderException"></exception>
352-
public void ValidateUpsertRequestContext(UpsertRequestContext upsertRequestCtx)
356+
public void ValidateUpsertRequestContext(UpsertRequestContext upsertRequestCtx, bool isPrimaryKeyInUrl = true)
353357
{
354358
ISqlMetadataProvider sqlMetadataProvider = GetSqlMetadataProvider(upsertRequestCtx.EntityName);
355359
IEnumerable<string> fieldsInRequestBody = upsertRequestCtx.FieldValuePairsInBody.Keys;
@@ -385,13 +389,45 @@ public void ValidateUpsertRequestContext(UpsertRequestContext upsertRequestCtx)
385389
unValidatedFields.Remove(exposedName!);
386390
}
387391

388-
// Primary Key(s) should not be present in the request body. We do not fail a request
389-
// if a PK is autogenerated here, because an UPSERT request may only need to update a
390-
// record. If an insert occurs on a table with autogenerated primary key,
391-
// a database error will be returned.
392+
// When the primary key is provided in the URL route, skip PK columns in body validation.
393+
// When the primary key is NOT in the URL (body-based PK), we need to validate that
394+
// all non-auto-generated PK columns are present in the body to form a complete key.
392395
if (sourceDefinition.PrimaryKey.Contains(column.Key))
393396
{
394-
continue;
397+
if (isPrimaryKeyInUrl)
398+
{
399+
continue;
400+
}
401+
else
402+
{
403+
// Body-based PK: non-auto-generated PK columns MUST be present.
404+
// Auto-generated PK columns are skipped — they cannot be supplied by the caller.
405+
if (column.Value.IsAutoGenerated)
406+
{
407+
continue;
408+
}
409+
410+
if (!fieldsInRequestBody.Contains(exposedName))
411+
{
412+
throw new DataApiBuilderException(
413+
message: $"Invalid request body. Missing field in body: {exposedName}.",
414+
statusCode: HttpStatusCode.BadRequest,
415+
subStatusCode: DataApiBuilderException.SubStatusCodes.BadRequest);
416+
}
417+
418+
// PK value must not be null for non-nullable PK columns.
419+
if (!column.Value.IsNullable &&
420+
upsertRequestCtx.FieldValuePairsInBody[exposedName!] is null)
421+
{
422+
throw new DataApiBuilderException(
423+
message: $"Invalid value for field {exposedName} in request body.",
424+
statusCode: HttpStatusCode.BadRequest,
425+
subStatusCode: DataApiBuilderException.SubStatusCodes.BadRequest);
426+
}
427+
428+
unValidatedFields.Remove(exposedName!);
429+
continue;
430+
}
395431
}
396432

397433
// Request body must have value defined for included non-nullable columns
@@ -488,7 +524,6 @@ public void ValidateEntity(string entityName)
488524
/// Tries to get the table definition for the given entity from the Metadata provider.
489525
/// </summary>
490526
/// <param name="entityName">Target entity name.</param>
491-
/// enables referencing DB schema.</param>
492527
/// <exception cref="DataApiBuilderException"></exception>
493528

494529
private static SourceDefinition TryGetSourceDefinition(string entityName, ISqlMetadataProvider sqlMetadataProvider)

‎src/Core/Services/RestService.cs‎

Lines changed: 28 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -70,24 +70,25 @@ RequestValidator requestValidator
7070
ISqlMetadataProvider sqlMetadataProvider = _sqlMetadataProviderFactory.GetMetadataProvider(dataSourceName);
7171
DatabaseObject dbObject = sqlMetadataProvider.EntityToDatabaseObject[entityName];
7272

73-
if (dbObject.SourceType is not EntitySourceType.StoredProcedure)
74-
{
75-
await AuthorizationCheckForRequirementAsync(resource: entityName, requirement: new EntityRoleOperationPermissionsRequirement());
76-
}
77-
else
78-
{
79-
await AuthorizationCheckForRequirementAsync(resource: entityName, requirement: new StoredProcedurePermissionsRequirement());
80-
}
81-
8273
QueryString? query = GetHttpContext().Request.QueryString;
8374
string queryString = query is null ? string.Empty : GetHttpContext().Request.QueryString.ToString();
8475

76+
// Read the request body early so it can be used for downstream processing.
8577
string requestBody = string.Empty;
8678
using (StreamReader reader = new(GetHttpContext().Request.Body))
8779
{
8880
requestBody = await reader.ReadToEndAsync();
8981
}
9082

83+
if (dbObject.SourceType is not EntitySourceType.StoredProcedure)
84+
{
85+
await AuthorizationCheckForRequirementAsync(resource: entityName, requirement: new EntityRoleOperationPermissionsRequirement());
86+
}
87+
else
88+
{
89+
await AuthorizationCheckForRequirementAsync(resource: entityName, requirement: new StoredProcedurePermissionsRequirement());
90+
}
91+
9192
RestRequestContext context;
9293

9394
// If request has resolved to a stored procedure entity, initialize and validate appropriate request context
@@ -144,7 +145,21 @@ RequestValidator requestValidator
144145
case EntityActionOperation.UpdateIncremental:
145146
case EntityActionOperation.Upsert:
146147
case EntityActionOperation.UpsertIncremental:
147-
RequestValidator.ValidatePrimaryKeyRouteAndQueryStringInURL(operationType, primaryKeyRoute);
148+
// For Upsert/UpsertIncremental, a keyless URL is allowed. When the
149+
// primary key route is absent, ValidateUpsertRequestContext checks that
150+
// the body contains all non-auto-generated PK columns so the mutation
151+
// engine can resolve the target row (or insert a new one).
152+
// Update/UpdateIncremental always require the PK in the URL.
153+
if (!string.IsNullOrEmpty(primaryKeyRoute))
154+
{
155+
RequestValidator.ValidatePrimaryKeyRouteAndQueryStringInURL(operationType, primaryKeyRoute);
156+
}
157+
else if (operationType is not EntityActionOperation.Upsert and
158+
not EntityActionOperation.UpsertIncremental)
159+
{
160+
RequestValidator.ValidatePrimaryKeyRouteAndQueryStringInURL(operationType, primaryKeyRoute);
161+
}
162+
148163
JsonElement upsertPayloadRoot = RequestValidator.ValidateAndParseRequestBody(requestBody);
149164
context = new UpsertRequestContext(
150165
entityName,
@@ -153,7 +168,9 @@ RequestValidator requestValidator
153168
operationType);
154169
if (context.DatabaseObject.SourceType is EntitySourceType.Table)
155170
{
156-
_requestValidator.ValidateUpsertRequestContext((UpsertRequestContext)context);
171+
_requestValidator.ValidateUpsertRequestContext(
172+
(UpsertRequestContext)context,
173+
isPrimaryKeyInUrl: !string.IsNullOrEmpty(primaryKeyRoute));
157174
}
158175

159176
break;

‎src/Service.Tests/OpenApiDocumentor/DocumentVerbosityTests.cs‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ public class DocumentVerbosityTests
2323
private const string UNEXPECTED_CONTENTS_ERROR = "Unexpected number of response objects to validate.";
2424

2525
/// <summary>
26-
/// Validates that for the Book entity, 7 response object schemas generated by OpenApiDocumentor
26+
/// Validates that for the Book entity, 9 response object schemas generated by OpenApiDocumentor
2727
/// contain a 'type' property with value 'object'.
2828
///
2929
/// Two paths:
@@ -32,9 +32,9 @@ public class DocumentVerbosityTests
3232
/// - Validate responses that return result contents:
3333
/// GET (200), PUT (200, 201), PATCH (200, 201)
3434
/// - "/Books"
35-
/// - 2 operations GET(all) POST
35+
/// - 4 operations GET(all) POST PUT(keyless) PATCH(keyless)
3636
/// - Validate responses that return result contents:
37-
/// GET (200), POST (201)
37+
/// GET (200), POST (201), PUT keyless (201), PATCH keyless (201)
3838
/// </summary>
3939
[TestMethod]
4040
public async Task ResponseObjectSchemaIncludesTypeProperty()
@@ -71,10 +71,10 @@ public async Task ResponseObjectSchemaIncludesTypeProperty()
7171
.Select(pair => pair.Value)
7272
.ToList();
7373

74-
// Validate that 7 response object schemas contain a 'type' property with value 'object'
75-
// Test summary describes all 7 expected responses.
74+
// Validate that 9 response object schemas contain a 'type' property with value 'object'
75+
// Test summary describes all 9 expected responses.
7676
Assert.IsTrue(
77-
condition: responses.Count == 7,
77+
condition: responses.Count == 9,
7878
message: UNEXPECTED_CONTENTS_ERROR);
7979

8080
foreach (OpenApiResponse response in responses)

‎src/Service.Tests/OpenApiDocumentor/ParameterValidationTests.cs‎

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -117,8 +117,13 @@ public async Task TestQueryParametersExcludedFromNonReadOperationsOnTablesAndVie
117117
OpenApiPathItem pathWithouId = openApiDocument.Paths[$"/{entityName}"];
118118
Assert.IsTrue(pathWithouId.Operations.ContainsKey(OperationType.Post));
119119
Assert.IsFalse(pathWithouId.Operations[OperationType.Post].Parameters.Any(param => param.In is ParameterLocation.Query));
120-
Assert.IsFalse(pathWithouId.Operations.ContainsKey(OperationType.Put));
121-
Assert.IsFalse(pathWithouId.Operations.ContainsKey(OperationType.Patch));
120+
121+
// With keyless PUT/PATCH support, PUT and PATCH operations are present on the base path
122+
// for entities with auto-generated primary keys. Validate they don't have query parameters.
123+
Assert.IsTrue(pathWithouId.Operations.ContainsKey(OperationType.Put));
124+
Assert.IsFalse(pathWithouId.Operations[OperationType.Put].Parameters.Any(param => param.In is ParameterLocation.Query));
125+
Assert.IsTrue(pathWithouId.Operations.ContainsKey(OperationType.Patch));
126+
Assert.IsFalse(pathWithouId.Operations[OperationType.Patch].Parameters.Any(param => param.In is ParameterLocation.Query));
122127
Assert.IsFalse(pathWithouId.Operations.ContainsKey(OperationType.Delete));
123128

124129
// Assert that Query Parameters Excluded From NonReadOperations for path with id.

0 commit comments

Comments
 (0)