[Feature] Allow one way relationships between stored procedures and tables

[pingu2k4]

Hi,

We have a need for relationships between a stored proc and other tables.

To try and explain in a minimal way exactly what we are trying to achieve:

• We have a table of employees, and a table of roles. There is a linking table to allow for a many to many relationship between employees and roles.
• We have a datagrid in our app, and want to allow for paging. The paging offered out of the box with DAB isn't enough for our datagrid paging - it wouldn't allow us to skip to the last page, or to arbitrarily select page 10, etc.
• We have built examples of stored procedures which would allow us to handle all of our paging by passing in page size and page parameters. (It also handles sorting and filtering, though that is beyond the scope of what we need here)
• The example stored procedures we created only took into account pulling data from a single table with no joins.
• We are able to have this stored procedure return data from joins as well, it will need a change in logic but we are sure that it is doable.
• However, the response will be of varying length, depending on the number of connections across the join. With a page size of 10, we might get 10 results or we might get 50 - but still only representing 10 actual results, just with an average of 5 roles per employee in that case.
• We could use a mapper on the app end after receiving the data from DAB, and this should solve our problem...
• However, this would involve in us doing what feels like more than should be necessary, and starts to feel like DAB is becoming more of a restriction to us. We would lose a certain amount in generation of DTO's that we currently have, as we would need a different object to map to...

It feels as though a one-way relationship from stored proc to table should be able to work, so long as the --relationship.fields param is provided so that DAB knows how to link from SP to table... If this was a feature, then it would allow for far simpler stored procs, as we wouldn't be dealing with the join ourselves, or having to page based on repeated data.... and it would remove our need for mapping on the app's end too.

Here is the relevant log output when attempting to start DAB with a relationship defined on a stored proc:

Logs

dbug: Azure.DataApiBuilder.Core.Configurations.RuntimeConfigValidator[0]
      Employee_Role: gosmarter.Employees_Roles(EmployeeId) is related to one Employee: gosmarter.Employees(EmployeeId).
fail: Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization. Refer to exception for error details.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot define relationship for entity: Entity { Source = EntitySource { Object = gosmarter.sp_GetEmployees, Type = StoredProcedure, Parameters = System.Collections.Generic.Dictionary`2[System.String,System.Object], KeyFields =  }, GraphQL = EntityGraphQLOptions { Singular = GetEmployees, Plural = GetEmployees, Enabled = True, Operation = Mutation }, Rest = EntityRestOptions { Methods = Azure.DataApiBuilder.Config.ObjectModel.SupportedHttpVerb[], Path = , Enabled = True }, Permissions = Azure.DataApiBuilder.Config.ObjectModel.EntityPermission[], Mappings = , Relationships = System.Collections.Generic.Dictionary`2[System.String,Azure.DataApiBuilder.Config.ObjectModel.EntityRelationship] }
         at Azure.DataApiBuilder.Core.Configurations.RuntimeConfigValidator.ValidateRelationshipsInConfig(RuntimeConfig runtimeConfig, ISqlMetadataProvider sqlMetadataProvider)
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app)
fail: Azure.DataApiBuilder.Service.Startup[0]
      Could not initialize the engine with the runtime config file: .\staticwebapp.database.config.json
info: Microsoft.Hosting.Lifetime[0]
      Application is shutting down...
Unable to launch the Data API builder engine.
Error: Failed to start the engine.

[Benjiiim]

Hello @pingu2k4 Adding this use case in #1903 may be interesting.

[pingu2k4]

@Benjiiim Cheers, have added to that issue. :)

[FelixZY]

Are stored procedures (especially with parameters) supported at all at this point?

[seantleonard]

Are stored procedures (especially with parameters) supported at all at this point?

Stored procedures in DAB don't support relationships. They are supported for querying and mutations in GraphQL and REST see https://learn.microsoft.com/azure/data-api-builder/views-and-stored-procedures#stored-procedures

[FelixZY]

Adding a note for future googlers:

Stored procedures are not supported by all database types yet. See also #1023 , #1024 , #1500