Custom API using .NET SDK for SQL API at Azure Cosmos DB

• Download AzureCosmosDBAPIs.zip - 1.2 MB

Introduction

This article is continuation of my previous article Migrate Data from SQL Server to Azure Cosmos DB as most of the data which are used in this article are already populate into the DB.

In this article I would like to give high-level insights on the client-side development using .NET SDK for SQL API to perform operations at Azure Cosmos DB. Other client-side Azure Cosmos DB APIs like Table API, Mango DB API, Gremlin APIs are not covered in this article. After reading this article, I would like to have developers directly copy the code and tweak it and use it in their tasks.

Background

Azure Cosmos DB has come a long way.

• In 2010 till 2014, it was Internal Microsoft DocumentDB service
• In 2014 August, Azure DocumentDB preview was released with features like SQL grammar over schema-free JSON, Tunable throughput, indexing, consistency etc.
• In 2015 April, Azure DocumentDB General Availability was released
• From there one new features were keep on getting added like Geospatial support, Geo-replication, Partitioned collections, Wire support for MongoDB (BSON) etc.

When I talk about Azure Cosmos DB many ask me What is No-SQL, Why to consider No-SQL, What all other No-SQL technologies etc. So, let’s start with very high level of understanding. The reason is, I would to give details on the .NET API rather than making it a dry & boring by give more notes over here on SQL vs No-SQL etc.

What is No-SQL?

1^st thing which I immediately recollect is, its’s schema free. What exactly it means in the same database you can have documents, table, graph, columnar data models and more. Another key thing is, it enables virtually limitless storage and throughput (provide you have appropriate settings).

Why to consider No-SQL?

This is very tricky question which I face. Consider the amount of volume, velocity and variety of data which you want to handle. Most of the time we are happy with volume and velocity, but when we start considering about variety of data, at this point we think about going with scheme-free options which again move towards No-SQL.

What other No-SQL technologies are there?

Apart from the (Azure) Cosmos DB there are many technologies from different vendors available in the market and among them, which are in race with (Azure) Cosmos DB are Mongo DB, Cassandra, Amazon Dynamo DB, Orient DB, Arrango DB etc. Each has their own advantages and limitations. You can explore more on each of them and decide which one to choose & recommend for your requirements. I have already chosen Azure Cosmos DB because of many of its capabilities which made me and my clients happy like Elastically scalable throughput and storage, Multi-region replication, Ad hoc queries with familiar SQL syntax etc.

At a high level if I need to compare with Relation DB with No-SQL DB then below are the diffs

1. Relational DB: Data is represented in rows and columns. No-SQL DB: Data is represented in documents and properties
2. Relational DB: It is strongly-typed schema. No-SQL DB: Schema-free
3. Relational DB: Highly normalized. No-SQL DB: Typically, De-Normalized

Prerequisites

• IDE: You can have VS code or latest version VS. In this article, VS 2017 Enterprise edition has been used.
• Azure Cosmos DB emulator: Azure Cosmos DB Emulator provides a local environment that emulates the Azure Cosmos DB service for development purposes. Using the Azure Cosmos DB Emulator, you can develop and test your application locally, without creating an Azure subscription or incurring any costs. I would recommend this as this is free and it has the same functionalities which you can do at the Azure Cosmos DB at the Azure portal. When you're satisfied with how your application is working in the Azure Cosmos DB Emulator, you can switch to using an Azure Cosmos DB account in the cloud. Azure Cosmos DB emulator can be found here (https://docs.microsoft.com/en-us/azure/cosmos-db/local-emulator).
• Knowledge on c#.
• Collections and Documents at the Azure Cosmos DB. I have already created the data and if you also want to create the data please follow my article, https://www.codeproject.com/Articles/1266054/Migrate-Data-from-SQL-Server-to-Azure-Cosmos-DB

Azure Resource Model

Azure Cosmos DB Resource Model is structured like below.

• Azure Account
  • Databases
    • Collections
      • Documents
        • resource/data
        • attachments (optional)
      • SPROCs
      • TRIGGERs
      • UDFs
    • Users
      • Permissions

We would be going ahead in creation of the API’s as per the above resource model (except SPROCs, TRIGGERs and UDFs are not covered).

Azure Account creation is done at the Azure portal, we would not be considering it and more over we would be using Azure Cosmos DB emulator and VS for all the necessary API operations.

In every operation which are done at the Azure Cosmos DB there is one major factor which needs to be considered. It is called “Throughput”. Throughput is measured as, how many requests can be served for a specific period. Throughput must be provisioned to ensure that sufficient system resources are available for your Cosmos database all the time to meet or exceed the Azure Cosmos DB SLA. The cost of all database operations is normalized by Cosmos DB and is expressed in terms of Request Units (RUs). Every Azure Cosmos DB response header shows the RU charge for a request. Each API has its own set of database operations, ranging from simple point reads and writes to complex queries. Each database operation consumes system resources depending upon the complexity of the operation.

As discussed over my previous article, let me give you high-level overview of “Partitions” at Azure Cosmos DB. Azure Cosmos DB achieves elastic scaling using partitions. Partitions are physical fixed-capacity data buckets. Cosmos DB collections are composed of multiple partitions. These are logical resources for the collections. Partitions host partition keys. Partition Keys are hashed values which determines the physical partition for storing each item. When considering a partition key you need to consider what should be the boundary for the query in a transaction and no bottle necks in the performance as partitions are data buckets and if partition keys are not set correctly then traversing between the partitions would lead to performance issues.

Another major concept with regards to the performance is the replication of data which is done by Turnkey Global Distribution. After having the settings, we can also set the Consistency levels. I would leave you to explore more on it at Microsoft Docs.

Every resource in the Azure Cosmos DB has following:

• id: user-defined unique identifier. It should be string
• _rid: auto-generated resource id
• _ts: auto-generated timestamp (last updated) epoch value
• _etag: auto-generated GUID. Used for optimistic concurrency
• _self: auto-generated URI path to the resource. This is very useful when using with SQL API for Azure Cosmos DB
• _attachments: URI path suffix to the resource attachment

You can write most T-SQL Operators and Functions in the Azure Cosmos DB.  And only class which is base for creating the API is DocumentClient. We use methods in the DocumentClient class for our each and every API. Another class is UriFactory. UriFactory is a helper class to assist in creating the various Uris needed for use with the DocumentClient instance in the Azure Cosmos DB service.

At a high level, there are only 2 steps for performing any of the operations at the Azure Cosmos DB

1. Create a DocumentClient instance by providing the connection string (end point and key)
2. Invoke methods for accessing the resources by leveraging task parallel library of .NET SDK for SQL API.

Creating the Application (APIs)

Enough of theory. I would also get bored while reading above details 😉. Let’s start with creating APIs.

1. Open VS studio and create a .NET Core 2.1 Console Application. Eventhough .NET Core application is not needed but I want to use the latest technologies so using them.
    
2. Once done, name the solution name as AzureCosmosDBAPIs and project name as APIs.
    
3. Add following nuget pacakages to the project
   1. Microsoft.Azure.DocumentDB.Core
   2. Microsoft.Extensions.Configuration
   3. Microsoft.Extensions.Configuration.FileExtensions
   4. Microsoft.Extensions.Configuration.Json
4. Add application settings file. I have added applicationsetttings.json for app settings. Add two keys in it, which would have connection settings to the Azure Cosmos DB. applicationsetting.json should look like
   
   
   I have used Azure CosmosDB emulator’s endpoint and primary key values. If you want to connect to Azure Portal, then you need to set those values from the Portal.
    
5. Add a class by name AppConfig.cs. This is used to read the application settings values from the above created file. This class uses the .NET core way of reading values of application settings from the config file. AppConfig.cs should look like
   
    
6. Add class by name DatabaseOperations.cs to the csproject. This class will have all the necessary operations which can be performed on a specific Database like creation of database etc. There are 3 methods
   1. ViewDatabaseDetails: This is will provide the list of databases from the Azure Cosmos DB
   2. CreateDatabase: This would create a database at the Azure Cosmos DB. It has overloaded method which takes the database name as the parameter.
   3. DeleteDatabase: This would delete a database at the Azure Cosmos DB. It has overloaded method which takes the database name as the parameter.
   4. DatabaseOperations class with methods should look like
      
      
       
7. Add class by name CollectionOperations.cs to the csproject. This class has all the necessary operations which can be performed on a specific collection in a database like creation of collections etc.
   1. ViewCollectionsInDatabase: This is will provide the list of collections in a database from the Azure Cosmos DB.
   2. GetCollection: This will get specific collection from database at Azure Cosmos DB. Class also has another method FindDocumentCollection, this would also fetch specific collection from the database.
   3. CreateCollection: This would create a collection in a database at the Azure Cosmos DB.
   4. DeleteCollection: This would delete a collection from a database at the Azure Cosmos DB.
   5. DisplayCollectionProperties: It would show the properties of a collection in a database.
   6. CollectionOperations class should look like
      
      
       
8. Add class by name DocumentOperations.cs to the csproject. This class has all the CRUD methods which can be performed on a specific document in a collection like creation of document etc.
   1. There are multiple methods in the class for creating a document
      1. Creating a document using json string (CreateDocumentUsingJson)
      2. Creating a document using a POCO (CreateDocumentUsingPocoObject). You can extend this method by having overloade method which can take 3^rd parameter with POCO class and create the document accordingly.
   2. There are multiple methods in the class for fetching the document
      1. Fetching all the documents in the collection (GetAllDocumentsAndDisplayResults)
      2. Fetching all the documents of a specific department in a collection (GetAllDocumentsForASpecificDepartmentAndDisplayResults)
   3. UpdateEmployeeDetails: This method would update the document.
   4. Deleting of the document can be done in two ways
      1. Deleting by employee name (DeleteEmployeeByEmployeeName)
      2. Deleting by Id (DeleteEmployeeById)
   5. DocumentOperationsclass should look like
      
      .
9. Add class by name UsersAndPermissionsOperations.cs to the csproject. This class will have all the CRUD operation for users and user permission in a database and collections respectively. Users are created at the database level and User Permissions are given to collections. User is of type Microsoft.Azure.Documents.User and Microsoft.Azure.Documents.Permission.
   1. GetAllUsers will fetch all the users with in a database
   2. GetuserDetailsByUserId will fetch user details for a specific user from the database
   3. ViewUsers will be used to display information on the console. This method would call ViewUser to display the user details
   4. CreateUser would create a user in the database
   5. DeleteUser would delete user from the database
   6. ViewPermissionsForAUser will display user permission for a specific user. This method would call ViewPermission to display the user permission on the console
   7. CreatePermission would create permission for a user for a resource (collection)
   8. There are two overloaded method for deleting permissions
      1. One method would take the user object and permission Id
      2. Another method would take user object and permission object
   9. UsersAndPermissionsOperations.cs should look like
      
      
10. At the Program.cs, all the above methods have been called. I have commented out all of them. As per you need you can uncomment and verify their functionality. Program.cs should look like
    
    

Next steps for you

1. I have deliberately left off creating a new database and in that creating a new collection etc.
2. The UriFactory class can be leveraged for manipulating resources.