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

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

Problem

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?

Solution

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.

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

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

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 (
    CategoryID INT PRIMARY KEY IDENTITY(1,1),
    CategoryName NVARCHAR(50)
);

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

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

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:

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

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:

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:

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

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:

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.

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:

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

And access an entity:

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.

About the author

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