Advanced Features of DAB (Data API Builder) to Build a REST API

By:   |   Updated: 2024-07-09   |   Comments (1)   |   Related: > Application Development


In the previous tip on Data API Builder (DAB) for SQL Server, we discussed how REST APIs provide a secure and platform-agnostic method to share database information using REST or GRAPHQL and how DAB simplifies the process of creating data APIs without the need for extensive coding or third-party tools.

What can we do if we want POST operations? Is it possible to achieve? What other options do we have if we want to implement Data API solutions in our production environments?


As we already discussed, Microsoft offers a tool named DAB (Data API Builder) that generates modern REST and GraphQL endpoints for your Azure Databases. As of May 2024, it also supports .Net 8.

This tip will provide guidance on more advanced features of DAB and how to set up a production-ready environment for it.

Setting Up the Example

The demonstration requires one of the following supported database engines: Azure SQL, SQL Server, Azure Database for PostgreSQL, Azure Database for MySQL, and Azure Cosmos DB for NoSQL. As shown below, I will use an Azure SQL database on the free tier.

My Azure SQL database

I have configured an Azure VM with Ubuntu, but you can use other OS/VM as you need.

Connection String Security

In real-world applications, we do not want to save our connection strings in plain text in JSON configuration files. How can we add an extra layer of security using a configuration file?

DAB supports the use of Environment Variables, where connection strings can be stored, allowing this file to be called from the JSON configuration file. Let me show you an example.

First, we create a file named .env and put the connection string inside the file:

echo "my-connection-string=\"Server=tcp:<yourserver>;Initial Catalog=eduardo_db;Persist Security Info=False;User ID=<user>;Password=<password>;\"" > .env

We use the following command to validate the contents of the file:

cat .env
adding environment variable

NOTE: Remember to update your .gitignore file to exclude this .env file so the credentials are not sourced on the code.

Once the .env file has been created, we init DAB with the following command:

dab init --config "dab-config.mssql.json" --database-type "mssql" --connection-string "@env('my-connection-string')"

After we execute the command, we can validate the file using this command:

cat dab-config.mssql.json
Initialization of DAB using environment variable

That is all! We have successfully configured DAB to use an environment variable. Now, we add the catalog tables (as we learned in the previous tip) and start as usual.

POST Commands

We have only tested GET commands. DAB also supports POST commands, which allow us to insert data into the tables. How can we achieve this?

First, we will add a stored procedure to our catalog to handle inserting the information.

I have a category table with this structure:

CREATE TABLE Categories (
    CategoryName NVARCHAR(50)

Then, I create a stored procedure to insert the data into the table:

    @CategoryName NVARCHAR(50)
    IF @CategoryName IS NULL OR LEN(TRIM(@CategoryName)) = 0
        RAISERROR('CategoryName cannot be null or empty', 16, 1);
    IF EXISTS (SELECT 1 FROM Categories WHERE CategoryName = @CategoryName)
        RAISERROR('CategoryName already exists', 16, 1);
    INSERT INTO Categories (CategoryName) VALUES (@CategoryName);
    SELECT 'Category inserted successfully' AS MessageText;

You can have a stored procedure that performs the CRUD for the table, but for simplicity, I will insert new records.

The next step is to add the entity to our DAB:

dab add AddCategory -c "dab-config.mssql.json" --source.type "stored-procedure" --source [dbo].[InsertCategory] --permissions "anonymous:execute" --source.params "CategoryName:s"

Notice that we have added the --source.type flag to specify that it is a stored procedure and –source.params to specify the parameters we will pass.

You can read more about DAB and stored procedures: Stored procedures.

Then we start DAB again with dab start:

dab start -c "dab-config.mssql.json"

Once it is running, we can test a POST operation. While DAB is running, open a new ssh session and run the following CURL command:

curl -k -X POST https://localhost:5001/api/AddCategory -H "Content-Type: application/json" -d '{"CategoryName": "Food"}'

We see that the insert was performed successfully:

POST operation successful

Remember that we checked for duplicates in our stored procedure? What if we try to insert the same category?

failed POST operation

We get a 500 error. But, if we check the session where DAB is running, we see that the exception was caught in the DAB log:

DAB log for actual exception

We can modify the stored procedure to not throw errors and just messages for these types of validations, but that is up to you.

For now, let's try to insert some more categories:

inserting more categories using POST

Now, check the table; we will see the values were added successfully:

TSQL query to validate categories were inserted

Public URL Access

At this point, you have already noticed that the DAB is only running on localhost on ports 5000 and 5001, by default.

You can validate it when running dab start and see where the app is listening:

default listening address for DAB

Is it possible to expose this API over the internet or change the ports?

Yes, you can do it with a few configurations and firewall permissions.

First, you need to make sure your firewall, VM, on-premises machine, and container have the proper port rules to allow inbound access to the ports you plan to use. This step can vary depending on what you are using. Since I am using an Azure VM, I only need to add an inbound rule.

Remember: This is an example, and I am allowing all traffic. You must be more careful and only allow the minimum required access.

configuring firewall rules

DAB runs over ASP Net core, so you must modify the ASPNETCORE_URLS property. Here is an article on multiple ways to change it.

For this example, I am using an Ubuntu VM. Therefore, I need to stop the DAB service and run the following command in the machine, where you specify the IP address and port where you want to listen for http and https:

export ASPNETCORE_URLS="http://*:5000;https://*:5001"

The asterisk * denotes that we will listen from anywhere on the VM on that port, meaning that we can access the API using localhost, private/public IP addresses, hostname, etc. You can narrow it down according to your needs.

After running this, we can start DAB again. We can see that now we are listening from anywhere:

starting DAB on another listening address

Now that we have made those changes, I can use any web browser to access the API using the public IP address:

accessing URL via web browser

And access an entity:

consuming api using browser

We have successfully published the API over the internet.

Again, remember that you need to be careful with the permissions you grant and always ensure to add multiple layers of security:

  • Database users with only the required permissions.
  • CRUD operations included in stored procedures with validation rules, returning custom error messages.
  • VM permissions must be required to run DAB (no root or admin privileges).
  • Minimum firewall rules allowing only specific hosts to access the API.
  • If you need to publish the API publicly, you can use other Cloud API services or functions to add an extra layer of security.

In the next tip, we will discuss an example of using DAB with a web app and using it with another Azure service.

Next Steps
  • You can check Data API Builder documentation.
  • DAB can be deployed on VMs, containers, and on-premises servers. Use as needed.
  • You can use any programming language that supports REST API calls so you can start working with your API.
  • 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-07-09

Comments For This Article

Wednesday, July 10, 2024 - 3:47:45 AM - Khan Zain Back To Top (92375)
This article by Eduardo Pivaral provides a comprehensive guide on advanced features of Microsoft's Data API Builder (DAB) for SQL Server. It's impressive how it details the setup and configuration of a production-ready environment, including secure management of connection strings using environment variables. The step-by-step instructions for implementing POST operations via stored procedures add practical value, and the demonstration of exposing APIs over the internet is particularly useful for real-world applications. The emphasis on security practices, such as firewall configurations and minimal privilege assignments, shows a thorough understanding of production environment needs. Overall, this article is a valuable resource for anyone looking to leverage DAB for building robust data APIs.

get free sql tips
agree to terms