Exploring APIs and using Views or Stored Procedures

API’s are usefull if you can read documentation over them, or if you can explore them.

All decent API solutions deliver you something like a Swagger interface. Such an interface is helpfull if you want to find out which API is delivering you the data you want. Swagger is included in DataAPI Builder. You will find it under:

HTTP http://localhost:5000/swagger/index.html
HTTPS https://localhost:5001/swagger/index.html
OPENAPI https://localhost:5001/api/openapi
SWAGGER https://localhost:5001/swagger

I encourage you NOT to use the HTTP one. HTTP seems dead. There is no protection for you.

When you want to test how an API is acting, most people will start using Postman. But going through some of the stuff I read I found an interesting Extension for testing your API’s. It s called Thunder Client and you can install it straight from MarketPlace. It works exactly the same as Postman. So looks like an alternative.

For me, both tools in VSCode will work, when used in combination with the Swagger output

One of the demos, Using Blazor and DataAPI BUilder, showed some tools/website things to look at GraphQL. I will do that later.

Now that we know how we can look at the API’s, it is time to see if there is an easy way to add a table for usage by the API. As you may have seen in the images, I have added sales.customers as salescustomers. I have done this by executing this command:

dab add salescustomers --source sales.customers --permissions "anonymous:<em>" 
dab add salesorders --source sales.orders --permissions "anonymous:</em>"
--AND
dab add salesitems --source sales.order_items --permissions "anonymous:*"

That reflects in a change in the dab-config.json file:

"salescustomers": {
  "source": "sales.customers",
  "permissions": [
    {
      "role": "anonymous",
      "actions": [
        "*"
      ]
    }
  ]
}

I do get curious about how graphQL is working. I found that people are using insomnia for it. I downloaded the app from their website and created an account on their website. It is a bummer that for my purpose, I only get 15 days of free trial. It would be nice if I can use that tool a few months longer. That is not valid anymore after my demo on August 10.. But I need to test.

My first impression is that it is a nice tool Configuration seems easy. After creating the account you get something like this.

But better, watch the video.

At this moment I have only added Direct table access. In a production environment you should not do this. You should use Stored procedures to access your data and give permissions on the stored procedures only. DAB can do this.

For now, I have created a view called orderbook with this definition:

You can add the view to the API by the following command:

dab add OrderBook --source dbo.View_Orderbook --source.type View --source.key-fields "order_id","store_id" --permissions "anonymous:read"

It looks similar to adding a table to the configuration. The –source.key-fields must have a field. This field is used to select one row of the view. I would have expected to add a second field, but that causes errors. If you look on how it is written in the json file you would think it is possible. Also the plural

Based on this view i have created some stored procedures with parameters…

"orderbook": {
    "source": {
        "type": "view",
        "object": "dbo.View_Orderbook",
        "key-fields": [ <<"order_id" << assumes a collection of fields
        ] <<
        },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            "read"
          ]
        }
      ]
      }

Now that I have a view, I do will create stored procedure to give the sum of sales in a year per Staffemployee or per shop and some cross usage of it.

The comamnd to add a Stored Procedure is simple”:

dab add --source --source.type "stored-procedure" source.params "" --permissions "anonymous:execute" --rest.methods "" --graphql.operation "query"

In my situation i should use this because of the three params:

dab add GetSalesData --source dbo.csp_SalesData --source.type "stored-procedure" source.params "EmployeeID:","ShopID:","Year:" --permissions "anonymous:execute" --rest.methods "get" --graphql.operation "query"

Comparing both: We lack the parameters in the json… Is it my fault because I add three parameters? I dont think so. Examining the Microsoft sample on learn shows that they have one parameter which is overwritten for this API. We dont have that. We just allow three parameters. So.. Lets see if DAB START runs…

No, it does not run. Going through the runtime stuff.. I see this:

Azure.DataApiBuilder.Service.Startup[0]
Unable to complete runtime initialization. Refer to exception for error details.
Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Invalid request. Missing required procedure parameters: employeeID, ShopID, Year for entity: GetSalesData

That seems to be true.. I do not see them in the Config file… Lets be naughty and add them manual. I have added

    "parameters": {
      "employeeID": "",
      "ShopID":"",
      "Year": ""
    }

and this seems to work. Lets look at the Swagger page if it understands what I want…

So no… It does not understand my Stored Proc. I will see if I can dive a bit more into it, for now I leave this as is…

As you might have noticed, I have created all endpoints with Anonymous access. That is not how it should be. In the next Blog I will tell you how you can grant permissions to this and see what that means for you or your code.

• DataAPIBuilder
• Devops