A Comprehensive Guide to Getting Started with Data API Builder for Azure SQL Database or SQL Server

This article is contributed. See the original author and article here.

Getting started with Data API builder for Azure SQL Database or SQL Server

What is Data API builder?

Data API builder for Azure Databases provides modern REST and GraphQL endpoints to your Azure Databases. Database objects can be safely exposed via REST or GraphQL endpoints with Data API builder, allowing your data to be accessed using modern approaches on any platform, language, or device. Granular security is ensured by an integrated and configurable policy engine; integration with Azure SQL,

SQL Server, PostgreSQL, MySQL, and Cosmos DB provides developers with unprecedented productivity.

Data API Builder Features

REST operations like CRUD ( POST, GET, PUT, PATCH, DELETE) , filtering, sorting and pagination.

GraphQL operations like queries and mutation, queries and mutations.

Support authentication via OAuth2/JWT.

Role-based authorization using received claims.

Allow collections, tables, views and stored procedures to be accessed via REST and GraphQL.

Full integration with Static Web Apps via Database Connection feature when running in Azure.

Easy development via dedicated CLI.

Open Source, you can always contribute

Our Focus.

In this blog, I’m going to walk you through a simple process to get started with Data API Builder with Microsoft SQL Database deployed on Azure. This can also be done using Azure Cosmos Db, Azure Database for PostgreSQL and Azure MySQL Database.

Prerequisites.

Basic understanding of Rest CRUD operations like POST and GET

Basic understanding of a relational database like Microsoft SQL or MYSQL

Willingness to follow along and learn as the above two prerequisites are not a must.

Requirements to get started.

Please click the below links to download .Net 6 and Vs Code editor, you can search Live Preview and Thunder client in the Vs code Extension Tab and install them.

Azure subscription, if you’re a student redeem Azure for students else create a free account

.NET 6.0

VS Code

VS code Extension Live Preview (from Microsoft) or live server

Vs code Extension Thunder Client

Insomnia for testing GraghQL request

Procedure

Provision and deploy Microsoft SQL on Azure

Create a Data API builder.

Test our API with Thunder client & Insomnia

Link our Azure SQL DB via Data API Builder to our simple HTML Page to view our Data.

Provision and deploy Microsoft SQL on Azure

Visit https://portal.azure.com/ and sign in.

Search for SQL databases

Click create to start provisioning your service.

Select your subscription, create a resource group, enter a database name and select elastic pool no for this is just a demo project.

Create your SQL server and select a location.

For authentication I have selected both SQL and Azure Active directory, but SQL alone is enough for authenticating this project. Add sever admin and password and keep the safe because we will need them later.

After creating the SQL server, select locally – redundant backup for demo purposes.

For these options, let’s go with the defaults.

For these options, let’s go with the defaults.

Create tags to identify your services on your resource group and create Review + create

Click go to resources after deployment is complete

On the overview you will see all the essential details of our SQL Database and SQL Server.

Click on the Query editor and sign in with either SQL Server auth or Azure Active Directory

You can view your database name below

Click new query and add this code below to create two tables (Brand & Category) then click run to execute these queries.

Add this insert statements to insert data in our database.

click run to execute and check the message below ‘query succeeded’.

refresh to view the newly created tables.

You can easily view tables available in our database

Run the below SQL Statement to confirm data inside our table.

On the connection string copy ADO.NET(SQL authentication) we will use it late.(NB: do not expose your connection string with password, I will do so for demo purposes)

Create a Data API builder.

Back to our windows open windows PowerShell and type dotnet, if the below message doesn’t appear. Install .NET 6.0

Run the below command to install Data API Builder locally.

Confirm installation was successful by running the below command.

Test our API with Thunder client & Insomnia

Open any folder you want to work on with VS Code then open your terminal. Run dab init –database-type “mssql” –connection-string “enter-your-connection-string-we-copied-in-the-above-steps”. Its good practice to use .env file on your root folder and call it on our connection string ie dab init –database-type “mssql” –connection-string “@env(‘my-connection-string’)”

The command generates a config file called dab-config.json looking like this:

Data-source will directly enable us to specify the database we are using and connection-string.The runtime will specify that our Data API will be consumed by both Rest endpoint is made available at the path `/api/` and GraphQL endpoint is available at `/graphql`. Under host we specify the mode of our data API either production or development, cors enables us to set origins example if yourdormain.com is your client app which will be sending request to your API you should add it under origins. Under authentication you can specify how you want to be authenticated.

Add the below code for the brand category entities. This entities maps directly to your tables on Azure SQL database, wo do this by specifying the source. Permissions lets use specify the actions, “*” means all operations Create Edit Read Delete are enabled. With role you can assign people roles to do this actions above, anonymous will let anyone do all the actions.

To start run the code below.

If everything is okey you should see the below message on the terminal, with URL https://localhost:5001 and http://localhost:5000. You can use either but I prefer https://localhost:5001 (because of the SSL certificate)

launch your Thunder client VS code extension( if on windows use CTRL + SHIFT + R). create a new request, select GET and https://localhost:5001/api/category. Click send and you should get a response from our deployed Azure SQL database.

Change the URL to https://localhost:5001/api/Brand. To fetch all brands in the database, click send.

The GET verb also supports several query parameters (also case sensitive) that allow you to manipulate and refine the requested data:
- $orderby: return items in the specified order
- $first: the top n items to return
- $filter: expression to filter the returned items
- $select: list of field names to be returned

The query parameters select will Only select CategoryID field after you click send.

The query parameters `filter` and `eq` will return CategoryName equal to Category x after you click send.

Data API offers validations, example you can’t just send categoryName as the only field because our table requires both categoryName and categoryID. An error will be returned.

REST Endpoint POST: create a new category after you add the json object below with that data and click send.

REST Endpoint POST: create a new category after you add the json object below with that data and click send.

REST Endpoint PUT: will update a category after you add the json object below with that data and click send.

Whenever you need to access a single item, you can get the item you want through a GET request by specifying its primary key example CategoryID

To delete a record by specifying its primary key example CategoryID and set the HTPP request to delete, and click send.

GraphQL endpoint is available at `/graphql`, set the HTPP request to post and provide the schema below and click send. For this example we need REST client like Postman or Insomnia to query. I have used Insomnia.

This is how we filter using the filter method.

This is how we orderBy descending or ascending.

Link our Azure SQL DB via Data API Builder to our simple HTML Page to view our Data.

For this demo am using a simple html page with jQuery and bootstrap. is helping me to make the HTTP request to our API server using fetch, get the response and append it to our html page dynamically.

Step one – add this code in index.html. and right click on the code to open with live server. Copy the URL displayed after the html page loads.

Step two – Paste the URL below on the dab-config.json on origins. Save your code. On your terminal `CTRL + C` to terminate the Data API server and start it again using `dab start`.