Knitting Data API builder together and how I tested it

In my previous posts about #DataAPIbuilder I showed you how I created an environment for it. I used the Bikestores sample code which you can download from SQLServerTutorials. The source and explanation is here.

I have installed that database in a VM on my desktop. That is running SQL2019 and the full stuff is downloaded from the Microsoft site.

After that I configured my Visual Studio and tried to install the Nuget Package for DataApiBuilder. That failed in the beginning, but I could overcome by downloading it from NuGet Gallery | Microsoft.DataApiBuilder 0.7.6 On that page there is a nice Download button. When done, you can just install it.

Then, my adventure started, also because I was not following Microsoft Learn for this, but I must admit that I looked at the information there quite a lot. Another nice source of information is the Youtube videos: Video 1 video 2 Video 3 Video 4

But I wanted to do it a bit different. As some know, I am an Database Platform Engineer and I am working mainly on the platform. When we deploy a VM with SQL, that is completely finished and ready for production data. A build does consist of a lot of steps, including updating inventories and reading and writing from Active Directory. As a Microsoft team we nearly only use Powershell for this. At this moment we use tools like dbatools, SQLServer modules, ReportServer modules and Ansible (sorry for this). Next to these standard Powershell modules we use Powershell Universal from Ironman Software. Also for endpoints on databases. That is where I think this module will fit in better.

This module creates endpoints on tables, stored procedures and views. You configure it by modifying a JSON file and you can add the security measures you need.

When I look at what I have created so far:

For the sake of testing i did not add authorization/authentication (both important parts of security), but authorization is described here and authentication here. This shows that is possible and how you should implement this. It is not hard to do. Just read carefully. While this is written for AzureAD, it should work for an on prem Active Directory also.

I have been testing a lot recently and I must admit that I like how easy it is to test stuff. From Visual Studio I used the postman and Thunder client to test the API request. BUt I was more impressed by the insomnia app. It does API but also GraphQL. The latter is an interesting thing also.

As stated, I do like PowerShell. I created a script to validate if the API’s are working:

<pre class="wp-block-code">
$baseURI = "https://localhost:5001/api"


# Retrieve an order from the past...
#(Invoke-RestMethod -uri "$($baseURI)/orderbook/order_id/218").value

# Insert some staff
# Pidcock (manager)
# Poels (manager)
# Brian Bønk Rueløkke
# Johan Ludvig Brattås (Sales Person)

$Pidcock = @{
  "first_name"= "Tom"
  "last_name"= "Pidcock"
  "email"= "tom@ppikes.com"
  "phone"= "03425698742"
  "active"= "1"
  "store_id"= 7
  "manager_id" = 1
  } | convertto-json

$Poel = @{
  "first_name"= "Mathieu"
  "last_name"= "vd Poel"
  "email"= "Mathieu@ppikes.com"
  "phone"= "069876543"
  "active"= "1"
  "store_id"= 8
  "manager_id"  = 1 
} | convertto-json


$Pidcock = (Invoke-RestMethod -Uri  "$($baseURI)/salesstaff" -Method post -Body $Pidcock -ContentType "application/json").value.staff_id
$poel = (Invoke-RestMethod -Uri  "$($baseURI)/salesstaff" -Method post -Body $Poel -ContentType "application/json").value.staff_id


# Add Johan for the Oslo 
$Johan = @{
  "first_name"= "Johan"
  "last_name"= "Ludvig Brattås"
  "email"= "Johan@ppikes.com"
  "phone"= "0695558889943"
  "active"= "1"
  "store_id"= 9
  "manager_id" = $Poel

} | convertto-json


$Johan = (Invoke-RestMethod -Uri  "$($baseURI)/salesstaff" -Method post -Body $Johan -ContentType "application/json").value.staff_id
 Add Johan
$brian = @{
  "first_name"= "Brian"
  "last_name"= "Bønk Rueløkke"
  "email"= "Brian@ppikes.com"
  "phone"= "01234567890"
  "active"= "1"
  "store_id"= 8
  "manager_id" = $Pidcock

} | convertto-json

$Brian = = (Invoke-RestMethod -Uri  "$($baseURI)/salesstaff" -Method post -Body $brian -ContentType "application/json").value.staff_id


#Lets Create an order; Johan is going to sell something to me. Tasks
#Insert me as a customer

$data = @{
  "first_name"= "Rob"
  "last_name"= "Litjens"
  "phone"= "0612345678"
  "email"= "rob@dontemail.me"
  "street"= "Downingstreet 11"
  "city"= "Wijchen"
  "state"= "Netherlands"
  "zip_code"= "9876AA"
} | convertto-json

$CustID = (Invoke-RestMethod -Uri  "$($baseURI)/salescustomers" -Method post -Body $data -ContentType "application/json").value.Customer_id
 # create the order

$ordate = (Get-Date -Format “yyyy-MM-dd hh:mm:ss”).ToString()
$shdate = get-date ((Get-Date).adddays(4))  -Format “yyyy-MM-dd hh:mm:ss”.ToString()
$redate = get-date ((Get-Date).adddays(8))  -Format “yyyy-MM-dd hh:mm:ss”.ToString()
$Order= @{
  "customer_id" = 1452
  "order_status"= 4
  "order_date"= $ordate
  "required_date"= $redate
  "shipped_date"= $shdate
  "store_id"= 9
  "staff_id"= $johan
} | convertto-json



$orderid = (Invoke-RestMethod -Uri  "$($baseURI)/salesorders" -Method post -Body $Order -ContentType "application/json").value.order_id

$orderbook = @{
  "order_id"= $orderid
} |convertto-json
$orderbook =  (Invoke-RestMethod -Uri  "$($baseURI)/orderbook/order_id/$orderid"  -Method post -Body $Orderbook -ContentType "application/json").value


$Sales = @{
    "Year" = "2018"
} |ConvertTo-Json
Invoke-RestMethod -Uri  "$($baseURI)/GetSalesData" -Method post -Body $Sales -ContentType "application/json"

This works like a charm. All results are clearly written. Id’s can be used and output of the storedprocedure is there. That makes it possible to use this on premise also despite we need to work on the authentication and authorization.

Explicity, I have no shares or whatever in this Open Source product, but it offers quite some good and usefull stuff. Also on purpose I did not use any static website or whatever to show things. Automation has no GUI in most cases.

Going through the changes I made, it is relatively simple to put this in a CICD pipeline.

As you can see, up till here there was no Azure involved. Everything is running on NUC… uh on premise.

For the next few days I will focus on setting up my presentation on this and probably investigate a bit of something correlated

Thank you Davide Mauri to gain my interest in this product

• DataAPIBuilder
• Devops