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
- VS Code
- Have .NET 6 SDK installed!
- Azure Static Web Apps CLI
- 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 👇. 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: -
- Visit this repo and fork it to create a duplicate repo in your account.
- Clone the project to your local computer
- Run npm install
Step 3: Initialize Data API Builder using Static Web Apps Database connections
To confirm if you have static web apps CLI installed, open terminal on VS Code and type swa -- version. If you don't get a version number back, run the following command to install Azure Static Web Apps CLI:
npm install -g /static-web-apps-cli
Install Data API Builder
To install data api builder, you need dotnet tools. To confirm if you have .NET, (Note: Data API Builder requires .NET Core 6 to run) type dotnet --list-sdks. If you don't get a version and path to the dotnet sdk, install data api builder using the following command:
dotnet tool install -g Microsoft.DataApiBuilder
Type dab --version to confirm whether you have succesfully installed data api builder.
Add your Database Connection String
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
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
You now want Data API Builder to create an endpoint accessible via data-api/api/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 “npm start” --data-api-location swa-db-connections
The fetchData function in the App.js component runs a fetch request to the endpoint (http://localhost:4280/data-api/api/Item) and retrieves the items in your database.
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.
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
Microsoft