Introduction

In today’s data-driven landscape, managing and leveraging APIs effectively is crucial for Data-driven organizations.

Azure Data API Builder emerges as a powerful tool designed to streamline API management, offering robust data integration, security, and performance monitoring features.

This blog provides a comprehensive technical deep dive into Azure Data API Builder, exploring its capabilities, setup process, and best practices for optimal use.

On May 15^th, the Data API builder becomes Generally Available.

What is Azure Data API Builder?

Azure Data API Builder is a managed service that simplifies the creation, management, and deployment of APIs. It enables seamless data integration from various sources, allowing IT professionals to build scalable and secure APIs efficiently.

Core Features:

• Seamless integration with Azure-based data sources
• Rapid API Development
• Automated API generation
• Support of REST and GraphQL endpoints
• Security and Access Control
• Scalability and Performance
• Provides insights into API performance and usage

Use Cases and Benefits:

• Enterprise Application
• Microservices Architecture
• MVP or POC – prototype development
• Leveraging Machine Learning using APIs
• Use of API data in Data pipelines

Architecture

The architecture of Data API Builder consists of –

1. Source data
2. Configuration file
3. Data API Builder Runtime
4. REST and GraphQL methods
5. REST and GraphQL endpoints

Setting Up Azure Data API Builder

There are various ways to set up DAB – Local and Azure services. For simplicity, in this blog, we will see how to set it up locally using an MS SQL Server container containing AdventureWorks database backup restored.

Prerequisites

1. Docker Desktop
2. .Net 8.0
3. SQL Server Management Studio or Azure Data Studio
4. Sample demo database – AdventureWorks database restored.
5. Postman

Install the Data API builder CLI

Install the Microsoft.DataApiBuilder package from NuGet as a .NET tool.

dotnet tool install --global Microsoft.DataApiBuilder

Setup Local Database

For the simipliticy of this demo, I have created a docker image with MS SQL Server 2022 containing AdventureWorks2022 and AdventureWorksDW2022 database backup restored to be ready for consumption.

docker run -d --name sql-server -p 1433:1433 dominicvivek06/sql-server

The default “sa” password is “sqldev&data2024”

Verify successful connections by connecting to the local database. I am using Azure Data Studio

Check for the two databases – AdventureWorks2022 and AdventureWorksDW2022 database.

Setup Configuration files

First, we need to initialize the configuration file –

dab init --database-type "mssql" --host-mode "Development" --connection-string "Server=localhost,1433;User Id=sa;Database=AdventureWorks2022;Password=sqldev&data2024;TrustServerCertificate=True;Encrypt=True;"

This will create a baseline configuration – dab-config.json

Let’s start by adding our first entity – AddressType to the configuration using dab utility with add parameter.

dab add AddressType --source "Person.AddressType" --permissions "anonymous:*"

Let’s add a view as well. Note – adding table and view are different.

dab add vProductAndDescription --source Production.vProductAndDescription --source.type View --source.key-fields "ProductID" --permissions "anonymous:read"

Start DAB

Now, let’s start the DAB (note – start the dab in a separate terminal and don’t terminate)

dab start

dab start
Information: Microsoft.DataApiBuilder 1.1.7
Information: Config not provided. Trying to get default config based on DAB_ENVIRONMENT...
Information: Environment variable DAB_ENVIRONMENT is (null)
Loading config file from dab-config.json.
Information: Loaded config file: dab-config.json
Information: Setting default minimum LogLevel: Debug for Development mode.
Starting the runtime engine...
Loading config file from dab-config.json.
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[63]
      User profile is available. Using 'C:\Users\domin\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
info: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      [AddressType] REST path: /api/AddressType
info: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      [vProductAndDescription] REST path: /api/vProductAndDescription
dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
       Executing query: SELECT STE.type_desc FROM sys.triggers ST inner join sys.trigger_events STE On ST.object_id = STE.object_id AND ST.parent_id = object_id(@param0 + '.' + @param1) WHERE ST.is_disabled = 0;
dbug: Azure.DataApiBuilder.Core.Resolvers.IQueryExecutor[0]
       Executing query: SELECT ifsc.column_name from sys.columns as sc INNER JOIN information_schema.columns as ifsc ON (sc.is_computed = 1 or ifsc.data_type = 'timestamp') AND sc.object_id = object_id(@param0+'.'+@param1) and ifsc.table_name = @param1 AND ifsc.table_schema = @param0 and ifsc.column_name = sc.name;
dbug: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      Logging primary key information for entity: AddressType.
dbug: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      Primary key column name: AddressTypeID
      Primary key mapped name: AddressTypeID
      Type: Int32
      IsNullable: False
      IsAutoGenerated: True
dbug: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      Logging primary key information for entity: vProductAndDescription.
dbug: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0]
      Primary key column name: ProductID
      Primary key mapped name: ProductID
      Type: Int32
      IsNullable: False
      IsAutoGenerated: False
info: Azure.DataApiBuilder.Service.Startup[0]
      Successfully completed runtime initialization.
info: Microsoft.Hosting.Lifetime[14]
      Now listening on: http://localhost:5000
info: Microsoft.Hosting.Lifetime[0]
      Application started. Press Ctrl+C to shut down.
info: Microsoft.Hosting.Lifetime[0]
      Hosting environment: Production
info: Microsoft.Hosting.Lifetime[0]
      Content root path: D:\devopsdemo\sql

Look for – “Now listening on: http://localhost:5000” in the startup log. If you see this message, then it means DAB has started successfully.

API Documentation

One of the capabilities of DAB is that it also produces Swagger API docs.

The Swagger API can be accessed by

 https://localhost:5000/swagger

REST API Endpoints

The API endpoint is available at http://localhost:5000/

Verify the status of the API endpoint. The “status”: “Healthy” indicates a successful DAB configuration.

Let’s view our added table entity – AddressType via API

http://localhost:5000/api/AddressType

Next, let’s view our added view – vProductAndDescription

http://localhost:5000/api/vProductAndDescription

Both the listing of entity and view were successful. Now, let’s pass the parameters to verify the API. For that, we provide an ID to the API endpoint.

http://localhost:5000/api/AddressType/AddressTypeID/2

GraphQL Endpoint

We can also access the GraphQL endpoint via – http://localhost:5000/graphql

Select “Browse Schema” to view the schema of the API’s.

We can start browsing for a GraphQL endpoint –

Getting a particular ID also works in GraphQL

Cleaning Docker

1. Stop the “dab start” window by pressing Ctrl+C.
2. Get the docker container by running docker ps

3. use docker stop to stop the container

PS D:\devopsdemo\sql> docker stop d6a5317d9b92
d6a5317d9b92

4. Remove the docker image

PS D:\devopsdemo\sql> docker images
REPOSITORY                      TAG       IMAGE ID       CREATED         SIZE
dominicvivek06/sql-server       latest    c543867b9033   28 hours ago    2.81GB
grafana/grafana                 latest    f9095e2f0444   2 weeks ago     443MB
prom/prometheus                 latest    ecb74a3b23a9   3 weeks ago     272MB
containerwatch/containerwatch   1.0.0     800c8dc20716   10 months ago   219MB


PS D:\devopsdemo\sql> docker rmi  c543867b9033 -f
Untagged: dominicvivek06/sql-server:latest
Untagged: dominicvivek06/sql-server@sha256:29a4976c2c7251860a17f800c1d02deb478eabb3a229b676c8b2d4d7c6c2cad4
Deleted: sha256:c543867b90337175154f1585fce03be0610d476938782294e5b90b85c3cc17d1
PS D:\devopsdemo\sql> 

Full Documentation

For full documentation, visit https://learn.microsoft.com/en-us/azure/data-api-builder/

Conclusion

Azure Data API Builder is a powerful tool that simplifies creating, managing, and securing APIs, offering robust data integration, security, and performance monitoring features. By following the setup process outlined in this blog, you can leverage Azure Data API Builder to optimize your API management and unlock the full potential of your data assets. Whether integrating data from multiple sources, securing sensitive information, or scaling your applications, Azure Data API Builder provides the tools and capabilities you need to succeed in the digital era.

Hope you enjoyed this blog post. Post comments or questions regarding Azure Data API Builder.