Seamless API Development with Data API Builder for your Static Web Apps

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

frustrated person who forgot to buy something from the store

 

Have you ever walked into a store, did some shopping, and headed back home only to find that you forgot to buy 60% of the items you needed to get?

I struggle with this very same issue and so I decided to create the ‘Smart Shopping Planner’, an application that will help me keep track of the items I need to buy and that I can easily update before and after shopping.

 

Smart Shopping Planner is a React application built using JavaScript, that stores data in an Azure SQL database. In this blog, I want to show you how incredibly easy it was to provide REST endpoints (you can work with GraphQL too) to connect the app to the database.

smart shopping planner teaser

What is Data API Builder?

Data API builder (an open-source project) is a modern tool that provides REST and GraphQL endpoints connecting to your Azure databases from your application.

 

What is Static Web Apps Database connections?

Data API Builder can be executed on-premises or online via the Static Web Apps database connection feature. With Static Web Apps database connection, you can easily convert your Azure Databases into consumable endpoints using the swa cli and implement built in authorization using Static Web Apps authentication.

 

Pre-requisites

1. VS Code
2. Have .NET 6 SDK installed!
3. Azure Static Web Apps CLI
4. An Azure Subscription

Check out this step-by-step guide to setting up your project using Data API Builder via Static Web Apps connections, connecting to an Azure SQL Database.

 

Step 1: Create an Azure SQL Database

Navigate to https://portal.azure.com/#create/Microsoft.SQLDatabase and use an account with an azure subscription to create a SQL Database

 

create azure sql database

 

Under ‘Compute + storage’, click Configure database and choose a basic plan of 2GB storage which will be enough for this project.

 

sql compute config

If you don't have a SQL Server, you will be prompted to create one, with authentication options to choose from :backhand_index_pointing_down:. Select SQL authentication and set an admin and password.

 

create sql server

 

Once created, open the SQL Server and set public access network to 'selected networks', then click the + add your client IPv4 address button to access the resource.

 

sql server networking config

 

Copy the SQL Database connection string and paste it for re-use at a later step.

 

copy connection string

 

You want your database to hold the following data:

• Item ID (system generated)
• Item Category
• Item Name
• Item Quantity
• Item Description
• Item Unit Price
• Item Total Price

On your SQL Database, open the Query editor (preview) and login with your SQL credentials. Run the following SQL query to create a table that will store our shopping items.

 

 

CREATE TABLE ShoppingItems ( id INT IDENTITY(1,1) PRIMARY KEY, category VARCHAR(100), name VARCHAR(100), quantity INT, description VARCHAR(200), unitPrice DECIMAL(10, 2), totalPrice DECIMAL(10, 2) );

 

 

create table in db

 

Step 2: Prepare your Frontend.

To build this project, you can use this template with the ready frontend. To use: -

1. Visit this repo and fork it to create a duplicate repo in your account.
2. Clone the project to your local computer
3. Run npm install on your terminal
4. Run npm start to load the application on your browser

Step 3: Initialize Data API Builder using Static Web Apps Database connections

Open terminal on VS Code and install Azure Static Web Apps CLI using the following command:

 

npm install -D /static-web-apps-cli

 

To initialize Data API Builder, run the following command, which will create a folder called swa-db-configuration with a config file to hold details such as Azure database to connect to, login credentials etcetera

 

swa db init --database-type mssql --connection-string "@env('DATABASE_CONNECTION_STRING')”

 

 

initialize dab

 

Create a .env file and add an environment variable called DATABASE_CONNECTION_STRING, then assign your connection string as the value. Remember to add your password on the connection string

 

You now want Data API Builder to create an endpoint accessible via data-api/rest/Item on an entity ‘Item’, which will access your table, and specify permission i.e. An anonymous user can perform all CRUD operations from the database. Implement this by adding the following code to the database config file inside entities

 

"Item": { "source": "dbo.ShoppingItems", "permissions": [ { "actions": ["*"], "role": "anonymous" } ] }

 

 

Run the following command on your terminal to start the server on port 3000 and to start the Azure Static Web App emulator on port 4280

 

 

swa start http://localhost:3000 --run “cd src && npm start” --data-api-location swa-db-connections

 

 

 

The fetchData function in the Itemlist component runs a fetch request to the endpoint (http://localhost:4280/data-api/rest/Item) and retrieves the items in your database.

 

const fetchData = async (disableLoadState) => { if(!disableLoadState) setIsLoading(true); try { const response = await fetch('/data-api/rest/Item'); if (!response.ok) { throw new Error(response.statusText); } const data = await response.json(); setItems(data.value); } catch (error) { setError(error); } setIsLoading(false); };

 

 

The createItemRequest() function in the CreateItem component runs a POST request to the same endpoint and adds the data submitted to the database. We pass in 'X-MS-API-ROLE': 'admin' in the header to specify that only a user with admin role is allowed to add an item to the database.

 

const createItemRequest = async () => { try { const response = await fetch('/data-api/rest/Item', { method: 'POST', headers: { 'Content-Type': 'application/json', 'X-MS-API-ROLE' : 'admin', }, body: JSON.stringify({ category, name, quantity, description }) }); const data = await response.json(); if (response.ok){ } else { throw new Error(data.error.message); } } catch (error) { console.error(error); } }

 

Step 4: Deploy

Lastly, create a Static Web App on the Azure Portal here - https://portal.azure.com/#create/Microsoft.StaticApp 

 

• Subscription: <Select your subscription>
• Resource group: <Select an existing resource group, or create a new one>
• Name: <Give your static web app a name>
• Hosting plan: <Choose free>
• Region: <Select a region closest to you>
• Source: <Choose GitHub>
• GitHub account: <Connect to your GitHub account>
• Organization: <Choose your GitHub org>
• Repository: <Select repository with the frontend>
• Branch: <Select the branch with your final code>
• Build presents: <Choose react>

Click Review + Create

 

After the app is deployed, you will notice that data in your database is not yet being displayed. To connect the static web app to your database, select the Database connection (preview) feature and ‘Link existing database’

 

database connections on swa

 

Fill in the database details and click Link

 

link database to your swa

 

The data in your Azure SQL database should now be accessible from your Static Web App via database connections using Data API Builder.

Note

You would consider having only specified roles such as admin to update, create and delete items from your database. Watch this video by Thomas showing you exactly how to configure permissions and use Static Web Apps Authentication.

 

Further reading & resources

1. Data API builder documentation | Microsoft Learn
2. https://youtu.be/gCrBSSOezSQ