Build REST API with Azure Data API Builder (DAB)

By:   |   Updated: 2024-06-03   |   Comments (2)   |   Related: > Application Development


In the modern IT world, globally available data that can be accessed in a secure, fast, and reliable way is vital for businesses to stay competitive and provide great data solutions to their customers. REST API offers a reliable, secure, and platform-agnostic method to share information via the web. Previously, we used third-party tools or coded our own solutions to create our data APIs, which could take us some time to achieve. Whether you are a developer or data engineer working with Azure databases, how can we easily create a data API for our databases without spending excessive time coding the solution or using third-party tools?


Microsoft offers a tool named Data API Builder (DAB). According to the documentation, it generates modern REST and GraphQL endpoints for your Azure databases. It can securely expose API (Application Programming Interface) endpoints so your data can be accessed using modern techniques from any platform, developer language, or device. It includes a flexible, deeply integrated policy engine, granular security controls, and integration with popular Azure databases.

DAB is open-source and can be executed for development workloads on your machines or production workloads in any cloud. It also supports multiple database engines: Azure SQL, SQL Server, Azure Database for PostgreSQL, Azure Database for MySQL, and Azure Cosmos DB for NoSQL.

This tutorial will guide you through creating your first REST API using Azure SQL database.


One of the Supported Database Engines: Azure SQL, SQL Server, Azure Database for PostgreSQL, Azure Database for MySQL, and Azure Cosmos DB for NoSQL. I will use an Azure SQL database on the free tier.

My Azure SQL database

.NET 6: This is the current version at the time of writing; it should support higher versions soon. Since it is platform agnostic, you can use Windows, MacOS, or Linux. For this tip, I will use an Azure VM with Linux. Below is the command I use on my Linux VM for .NET 6:

sudo apt-get update && \ 
sudo apt-get install -y dotnet-sdk-6.0
Install .NET 6

Installing Data API Builder

Once you have the prerequisites in place, start using DAB by installing the CLI:

dotnet tool install --global Microsoft.DataApiBuilder
installing data api builder

You can validate that it installed successfully by running this command:

dab --version
checking is properly installed

Note: If you get a "not found" error, try to reload your session. If the issue persists, try reinstalling the tool.

Creating the First REST API

DAB functions as a "web server" on the machine where it runs, meaning it serves REST API calls.

The usual flow for DAB is Init > add/update entities > start service.

It's as simple as that!

Once completed, you can perform API calls to the machine where it is running.

Now, let's go step by step with a simple example. I am using an Azure SQL database and a Linux VM.

We should first initialize our DAB using the init command. If you are using another engine, change it as needed.

The --config flag is used to specify multiple environments, such as dev, QA, and Prod. It must follow this convention: dab-config.<Your-Environment>.json

For this demonstration, my environment will be mssql: dab-config.mssql.json

dab init --config "dab-config.<Your-Environment>.json" --database-type "mssql" –-connection-string '<Your Connection String>'
dab init command

The next step is to add entities to our REST API; a simple example is adding a table.

With the -c flag, we specify the environment we want to add the entity, in our case dab-config.mssql.json

dab add <MyObjectName> -c "dab-config.<Your-Environment>.json" --source <MyDBObject> --permissions "anonymous:*"
adding an entity to dab

We can use the add or update commands as needed. You can see the full CLI reference here.

Once you are done with the configuration, you can source the dab-config.mssql.json in case you need it deployed on another server or container or to be part of your CI/CD strategy.

We can explore the .json file and make sure everything is correct. In Linux, perform a cat command:

cat dab-config.mssql.json
exploring our dab config json file

We can see the entity we added and the connection string we initialized for our configuration file are there. As stated earlier, on production environments, you need to source/copy and deploy this file; there is no need to run the init and add/update commands again.

Once the config file is ready, the last step is to run the start command so the API can be consumed:

dab start -c "dab-config.<Your-Environment>.json"
running the dab start command

If successful, you will see the input in the image above.

To consume the API, you must use the address marked in the image https://localhost:5001 or use the server IP address so it can be accessed from another location. You must configure firewalls and access rules properly, depending on the underlying infrastructure DAB is running (VM, container, on-premises).

Testing It!

We can test it on the same machine by opening another session. If the session where the DAB Start command is stopped, you will not be able to consume the API.

One of the easiest ways to test if you can access the API is via a curl command:

curl -v https://localhost:5001
Validating health in a secondary session

The -v flag indicates that we want verbose output.

If you want to make it simpler, remove the flag, and you should see only the Healthy text:

running curl without verbose

The last step in our testing is to consume the API.

Do you remember that we set up a Customer entity? We can try to call it by issuing a GET command with curl.

The basic structure to call an entity should be https://host:5001/api/<Entity>

curl -i -X GET https://localhost:5001/api/Customers
issuing a GET command with CURL

Note: If you have https issues with curl, you can use the -k flag to mark the connection as insecure.

If we check the original session, where the start command is executing, we can see the calls we made so far:

Checking session running dab, we can see the log

Using jq, we can customize the API calls to see only the relevant data we want.

For example:

curl -s https://localhost:5001/api/Customers | jq '.value[0] | {CustomerID, Email}'
using jq to filter and customize data retrieval
curl -s https://localhost:5001/api/Customers | jq -c '.value[] | select( .Phone | contains("21"))'
using jq to filter and customize data retrieval, example 2
Next Steps
  • Check Data API Builder documentation.
  • DAB can be deployed on VMs, Containers, and on-premises servers, so you can use the one you want, which may be different from this example.
  • You can use any programming language that supports REST API calls so you can start working with your API.
  • If you want to secure your connection string, you can set up environments. If you use them, remember to exclude .env files from your git repository.
  • Stay tuned for more tips where we will see more examples and configurations.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-06-03

Comments For This Article

Monday, June 3, 2024 - 12:55:49 PM - Eduardo Pivaral Back To Top (92284)
Hi Rob,

I have a part 2 article that is pending publishing where I explain about security considerations, but you have multiple options to authenticate clients:
- Using firewall & IP rules in the VM or container that is running DAB,
- In DAB itself using authorization options:
- if you use Azure API management service, you can control access from there

Monday, June 3, 2024 - 12:09:48 PM - Rob Back To Top (92283)
Very nice. We have it up and running towards an on-premises SQL-database. Should we have something in front of it to authenticate a client call or can we embed that in DAB with config files?

get free sql tips
agree to terms