Saying goodbye to writing CRUD? OR Data API Builder and Azure cloud
Victor Karabedyants
MSDP in Software Engineering, CTO, MBA, Cloud Manager at Sitecore | AI Engineer | Azure Solutions Architect | Azure Administrator | Azure Security Engineer | Azure Developer | Azure Data Engineer and Devops| CKA
Problem.
How often do we write APIs for the database? - very often, and how often do we repeat, copy, and paste code from our previous projects to write new CRUD? The answer is obvious, this is a task that is constantly faced by every developer. So why waste time, Microsoft thought and decided to make its open-source implementation of the API for databases, which helps to solve this problem!
Data API Builder is a tool that allows developers to quickly and easily create APIs for accessing data. Instead of writing tons of routine code for each CRUD operation, Data API Builder automates this process by providing developers with ready-made API builders that can be configured using a simple interface. This allows you to focus on the business logic of the application, and not on the details of the implementation of data access. In addition to the REST API, the application also supports GrapSQL.
So you probably have a question, “Which databases does this tool support?”, the answer to that question is given below:
You can also familiarize yourself with all the possibilities of this tool at the link:
In this article, I want to show you how to install and configure Data API Builder on your Windows machine and how to deploy it in the Azure cloud and use it as a tool to help you quickly implement CRUD for your database.
We will start our adventure by installing Data API Builder in the Azure cloud, for this, we will use Azure Container Apps and Azure SQL Database. I will write a general work plan for what we will do in the Azure cloud.
Create a managed identity with role-based access control permissions
Deploy Azure SQL with the sample AdventureWorksLT dataset
Stage the container image in Azure Container Registry
Deploy Azure Container App with the Data API builder container image
First, we need to log in and open Azure Cloud Shell. After that, set all the environment variables, for example, like this:
API_CONTAINER_NAME="api$SUFFIX" CONTAINER_ENV_NAME="env$SUFFIX" LOCATION="westus"
Next, we need to create the environment and the container itself
az containerapp env create \?
??--resource-group $RESOURCE_GROUP_NAME \
??--name $CONTAINER_ENV_NAME \
??--logs-destination none \
??--location $LOCATION
az containerapp create \?
??--resource-group $RESOURCE_GROUP_NAME \
??--environment $CONTAINER_ENV_NAME \
??--name $API_CONTAINER_NAME \
??--image "mcr.microsoft.com/azure-databases/data-api-builder" \
??--ingress "external" \
??--target-port "5000" \
??--system-assigned
In order not to duplicate the official documentation, you can find more detailed instructions for deploying Azure Container Apps, configuring the database, and environment variables, and launching the application at the following link:?
The instructions also describe how to create a test database and how to give access to it, for example:
az sql server create \
--resource-group $RESOURCE_GROUP_NAME \
--name $SQL_SERVER_NAME \
--location $LOCATION \
--enable-ad-only-auth \
--external-admin-principal-type "User" \
--external-admin-name $API_CONTAINER_NAME \
--external-admin-sid $MANAGED_IDENTITY_PRINCIPAL_ID
az sql server firewall-rule create \
--resource-group $RESOURCE_GROUP_NAME \
--server $SQL_SERVER_NAME \
--name "AllowAzure" \
--start-ip-address "0.0.0.0" \
--end-ip-address "0.0.0.0"
az sql db create \
--resource-group $RESOURCE_GROUP_NAME \
--server $SQL_SERVER_NAME \
--name "adventureworks" \
--sample-name "AdventureWorksLT"
Also for our example, we need to build the container and add it to the container registry:
FROM mcr.microsoft.com/dotnet/sdk:6.0-cbl-mariner2.0 AS build
WORKDIR /config
RUN dotnet new tool-manifest
RUN dotnet tool install Microsoft.DataApiBuilder
RUN dotnet tool run dab -- init --database-type "mssql" --connection-string "@env('DATABASE_CONNECTION_STRING')"
RUN dotnet tool run dab -- add Product --source "SalesLT.Product" --permissions "anonymous:read"
COPY --from=build /config /App
az acr build \
??--registry $CONTAINER_REGISTRY_NAME \
??--image adventureworkslt-dab:latest \
??--image adventureworkslt-dab:{{.Run.ID}} \
??--file Dockerfile \.
What the container assembly looks like:
After that, we can go to the API and retrieve the products from the database that we have connected:
After spending only 15 minutes, we got the REST API that can be used by us to access data in the database, also using Azure container apps, we can quickly scale the application in case of increased load.?
The second part of the article will be devoted to the installation and configuration of the DATA API Builder on the local machine for this we will usually use Docker, and in this part, we will also pay attention to the configuration of this service so that it is possible to flexibly configure what data we want to give through the API. Let's start.?
For the test database, we will also use a docker container, with SQL 2022,?
docker pull mcr.microsoft.com/mssql/server:2022-latest
领英推荐
Let's start the container with our login and password:
docker run \
????--name mssql \
????--publish 1433:1433 \
????--detach \
????--env "ACCEPT_EULA=Y" \
????--env "MSSQL_SA_PASSWORD=<your-password>" \
After that, we need to add test data, you can use any tool convenient for you and connect using the connection string:
Server=localhost,1433;User Id=sa;Password=<your-password>;TrustServerCertificate=true;
We add test data:
IF NOT EXISTS(SELECT name FROM sys.databases WHERE name = 'Library')
BEGIN
????CREATE DATABASE Library;
END
GO
USE Library
DROP TABLE IF EXISTS dbo.Books;
CREATE TABLE dbo.Books
(
id int NOT NULL PRIMARY KEY,
title nvarchar(1000) NOT NULL,
[year] int null,
[pages] int null
)
GO
INSERT INTO dbo.Books VALUES
(1000, 'Practical Azure SQL Database for Modern Developers', 2020, 326),
(1001, 'SQL Server 2019 Revealed: Including Big Data Clusters and Machine Learning', 2019, 444),
(1002, 'Azure SQL Revealed: A Guide to the Cloud for SQL Server Professionals', 2020, 528),
(1003, 'SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability', 2022, 506)
GO
Now let's prepare our configuration file for the DATA API Builder, as you can see, we have very simply granted read access to the anonymous table Books:
{
??"$schema": "https://github.com/Azure/data-api-builder/releases/latest/download/dab.draft.schema.json",
??"data-source": {
"database-type": "mssql",
"connection-string": "Server=host.docker.internal\\mssql,1433;Initial Catalog=Library;User Id=sa;Password=<your-password>;TrustServerCertificate=true;"
??},
??"entities": {
"book": {
?? "source": "dbo.Books",
?? "permissions": [
???? {
?????? "actions": [
???????? "read"
?????? ],
?????? "role": "anonymous"
???? }
?? ]
}
??}
}
Now we are ready to launch our app!
docker run \
--name dab \
--publish 5000:5000 \
--detach \
--mount type=bind,source=$(pwd)/dab-config.json,target=/App/dab-config.json,readonly \
If you now go to the address? https://localhost:5000/api/book? we will get a list of books:
{
??"value": [
????{
??????"id": 1000,
??????"title": "Practical Azure SQL Database for Modern Developers",
??????"year": 2020,
??????"pages": 326
????},
????{
??????"id": 1001,
??????"title": "SQL Server 2019 Revealed: Including Big Data Clusters and Machine Learning",
??????"year": 2019,
??????"pages": 444
????},
As a result, we have successfully considered the transition from the classic CRUD approach to using tools such as Data API Builder in the Azure cloud environment. We highlighted the benefits of this approach, focusing on development speed, data security, and scalability.
The final step was testing the ability to run our application both locally and in a Docker environment. Running locally gives developers the ability to quickly test and improve the application, while using Docker allows the application to be deployed in a standardized environment, ensuring portability and scalability. Next, we looked at creating a configuration file that defines how REST and GraphQL endpoints map to actual data. This step is important to ensure convenient access to data through the created APIs.
In general, the considered aspects demonstrate the advantages of using modern tools and technologies for software development, which allows the creation of effective, secure, and scalable applications, both locally and in the regional environment.
We also have a channel on Azure in Telegram, join the community? - https://t.me/azureuacommunity