Expose CosmosDB data through APIM

CosmosDB is a distributed DB offering from Azure supporting relational & non-relational data. In this post, I will explain how to quickly build simple Rest APIs on APIM to expose CosmosDB data.

Let’s assume our CosmosDB stores the master address list for an imaginary country. The attributes of an address record are its unique id, city, state & zip code. When viewed as a json, the structure would be as below:

{
   "id": 12345,
   "state": "state",
   "city": "city",
   "postalCode": 900102
}

Our goal is to build a GET API which can take the postal code as query parameter, search the DB and return the results. If we had a relational DB, the API would execute a simple SQL query like select * from addresses where postalCode=900102. Fortunately, CosmosDB also exposes a HTTP POST rest endpoint which accepts SQL queries in the request payload, executes it and returns the response. The below postman capture of the document search endpoint, shows the minimal headers required to access the API.

And the request payload would be:

{
  "query": "select * from c where c.zipCode = @zipCode",
  "parameters": [{
     "name": "@zipCode",
     "value": "900102"
  }]
}

Though a good feature, in its current style, the API is not meaningful and there would be no visibility on the query being executed. Typically, we would have a service layer which would connect to the DB and expose a more meaningful GET endpoint. Instead, we will leverage the APIM’s inbuilt policies to quickly build a GET endpoint which does the same.

On APIM, the first step would be to register an address API. The next step would be to register a GET operation with blank target under address API for our use case. So, now we have an endpoint similar to below:

https://{your APIM instance name}.azure-api.net/addresses/search?zipCode=?

The next steps would be to use the APIM’s inbuilt policy, send-request and return-resonse to make a call to Cosmos DB’s REST API.

Invoking the Cosmos DB’s REST APIs, does need the authorization & x-ms-date headers to be set in the request.

To include the x-ms-date header we can use the set-variable policy from APIM. Since the expected date format is RFC 1123, we can leverage the inbuilt “R” pattern as below:

<set-variable name="utcNow" value="@(DateTime.UtcNow.ToString("R"))" />

Next, the authorization header needs to be set based on the above captured UTC time. The set-variable policy can be leveraged again as below and the generated value can be stored into a variable (in this case authToken).

<set-variable name="authToken" value="@{
// The key from Cosmos DB. Ideally would be retrieved from AKV
var hmacSha256 = new System.Security.Cryptography.HMACSHA256 { 
   Key = Convert.FromBase64String("****************") };
// All search operations would be post
var verb = "post";
// All search operations are against the docs resource always
var resourceType = "docs";
// Format - dbs/{db name}/colls/{collection name}
var resourceId = "dbs/master-db/colls/addresses";
// The above captured UTC time, converted to lower case
var requestTime = ((string)context.Variables["utcNow"]).ToLower();
string payLoad = String.Format(
    "{0}\n{1}\n{2}\n{3}\n{4}\n",
    verb,
    resourceType,
    resourceId,
    requestTime,
    "");
byte[] hashPayLoad = hmacSha256.ComputeHash(
     System.Text.Encoding.UTF8.GetBytes(payLoad));
// the final signature to be set in the authorization field
string signature = Convert.ToBase64String(hashPayLoad);
return String.Format(
    "type={0}&ver={1}&sig={2}",
    "master",
    "1.0",
    signature
  );
}"/>

The next step would be to use the above 2 variables and construct a HTTP POST request which would carry the SQL query to the Cosmos DB REST API. (Typically, the above 2 steps would be common at the API level and only the HTTP send request policy & return response policy would be coded under each operation).

<send-request mode="new" response-variable-name="dbSearchResults" timeout="1" ignore-error="true">
<set-url>{cosmos db URL}</set-url>
  <set-method>POST</set-method>
  <set-header name="x-ms-documentdb-isquery" exists-action="override">
     <value>true</value>
  </set-header>
  <set-header name="Content-Type" exists-action="override>
     <value>application/query+json</value>
  </set-header>
  <set-header name="x-ms-version" exists-action="override">.  
     <value>2018-12-31</value>
  </set-header>
  <set-header name="x-ms-date" exists-action="override">.  
     <value>@((string)context.Variables["utcNow"])</value>
  </set-header>
  <set-header name="Authorization" exists-action="override">
     <value>@((string)context.Variables["authToken"])</value>
  </set-header>
<!-- The only piece of code specific to each operation would be below -->
<set-body>@{
var zipCode = context.Request.Url.Query["zipCode"][0]; //ugly
var parameters = new JArray(
  new JObject(
    new JProperty("name", "@zipCode"),
    new JProperty("value", zipCode))
  );
return new JObject(
   new JProperty("query","select * from c where c.zipCode = @zipCode"),
   new JProperty("parameters", parameters)
  ).ToString();
}</set-body>
</send-request>

Leveraging the Cosmos DB’s inbuilt REST APIs eliminates the additional overhead of developing and maintaining services tightly coupled to such master data.

Having APIM in front, allows the APIs to be truly extensible as the only change needed would be to introduce a new operation and code the Cosmos DB specific SQL logic into a policy. Also, it becomes easy to switch to access tokens based authorization as APIM natively supports AAD OAuth flows.