[Bug]: Issue adding Stored Procedures (Azure Cosmos DB / NoSQL / GraphQL)

[jacksorjacksor]

What happened?

Following the instructions here, we've run into issues registering a stored procedure with DAB.

Within Azure Cosmos DB, NoSQL, there's a container called "Persons"
Attached to the container is a Stored Process called "DefaultStoredProc"

We've the following command to register the Stored Proc:

dab add DefaultStoredProc --source Persons.DefaultStoredProc --source.type "stored-procedure" --source.params "searchType:s" --permissions "anonymous:execute" --rest.methods "get" --graphql.operation "query" -c "[locationOfConfigFile].json"

That command populates the config file with the below:

"DefaultStoredProc": {
      "source": {
        "object": "Persons.DefaultStoredProc",
        "type": "stored-procedure",
        "parameters": {
          "searchType": "s"
        }
      },
      "graphql": {
        "enabled": true,
        "operation": "query",
        "type": {
          "singular": "DefaultStoredProc",
          "plural": "DefaultStoredProcs"
        }
      },
      "rest": {
        "enabled": false,
        "methods": [
          "post"
        ]
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "execute"
            }
          ]
        }
      ]
    }

Nothing is created in the schema.gql file.

When we run this, using dab start (or swa start) we get the following error - System.Collections.Generic.KeyNotFoundException: The given key 'DefaultStoredProc' was not present in the dictionary.

Any and all advice greatly appreciated!

TY, Rich

Version

0.9.7

What database are you using?

CosmosDB NoSQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

GraphQL

Relevant log output

$ dab start -c .\staticwebapp.database.config.json

Information: Microsoft.DataApiBuilder 0.9.7+e560142426d1c080b9fd7b7fabff51a276f6bf61
Information: User provided config file: .\staticwebapp.database.config.json
Loading config file from .\staticwebapp.database.config.json.
Information: Loaded config file: .\staticwebapp.database.config.json
Information: Setting default minimum LogLevel: Error for Production mode.
Starting the runtime engine...
Loading config file from .\staticwebapp.database.config.json.
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[63]
      User profile is available. Using 'C:\Users\jacks\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
fail: Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization. Refer to exception for error details.
      System.Collections.Generic.KeyNotFoundException: The given key 'DefaultStoredProc' was not present in the dictionary.
         at Azure.DataApiBuilder.Core.Configurations.RuntimeConfigValidator.ValidateStoredProceduresInConfig(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.

Code of Conduct

• I agree to follow this project's Code of Conduct

[severussundar]

Hey @jacksorjacksor , in this discussion I had suggested using stored procedures thinking that you were using a relational database like SQL Server/Azure SQL.

Data API builder supports stored procedures only for MSSQL.

[jacksorjacksor]

Hi @severussundar - ah, huge apologies, you're right, I didn't clarify in the discussion that we were using Cosmos DB / NoSQL. Sorry about that, I should have been more clear.

I would probably recommend a note be added to the documentation to highlight this. I've added a note as part of PR 11

[sajeetharan]

@jacksorjacksor Thanks for reporting this issue, I am from the Cosmos DB product management team, would like to understand the use case here. In general, we do not recommend using Stored Procedures

[jacksorjacksor]

Hi @sajeetharan, thanks for your time and for getting back to me.

We're using Cosmos DB (NoSQL) for a project and so we're getting used to how we should be calculating various measures / using the data.

A use case would be that we've got a container Example, and within that container there's a list of Items. We'd like to know the best way of getting the count of Items (sample dataset below).

From our research it appeared that a stored procedure would be the best way to achieve this, but we're very happy to be told otherwise!

Thanks again!

Rich

Sample data set for reference - container called "Example":

{ 
"id":123,
"parentNode":{
    "items":[
        {"name":"item1"},
        {"name":"item2"},
        {"name":"item3"}
     ]
}}