Unlock Seamless Data Access: Introducing Azure Static Web Apps Database Connections

Microsoft

Dec 20, 2023

Are you tired of spending countless hours crafting custom APIs for your web applications? Imagine a world where you can effortlessly host your static web apps and data APIs without writing extensive backend code.

Learn about the Azure Static Web Apps Database Connections feature - a game-changer that bridges the gap between your frontend and database, all while keeping complexity at bay. In this blog post, we’ll explore how this cutting-edge feature empowers you to connect directly to your database from your static sites. Say goodbye to the traditional backend hustle and hello to a streamlined experience.

Buckle up and watch this Open at Microsoft Episode where Jerry dives into the magic of Data API Builder and static web apps database connections feature and unleash the potential of your web applications!

Try it Yourself!

Scenario

Wouldn’t you like to have an organized way of managing your shopping list? This project allows you to add shopping items as soon as you remember them to give you a compiled shopping list the next time you go out for shopping and include an estimated budget.

Pre-requisites

.NET 6 or later (for running DAB locally)
An Azure Subscription

Create a database.

You can work with different Azure databases based on your preference including Azure SQL, Azure Cosmos DB, Azure Database PostgreSQL and Azure MySQL Database.

I’ll create an Azure SQL database with Basic Compute + Storage with will give me up to 2 GB Storage, configure SQL authentication with a table called ShoppingItems that holds the data as shown on the image below. Copy the connection string to use later.

Prepare your frontend.

You can create a react application using this GitHub template with sample code for the shopping planner.

Remember to add a .env file and add your database connection string in a variable called my-connection-string

Once your frontend is ready, run

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

to install Static Web Apps CLI

Install & configure Data API Builder

Run

dotnet tool install -g Microsoft.DataApiBuilder

to install Data API builder using dotnet tool.

Next, use Static Web Apps CLI to generate a dab configuration file by running

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

The configuration file will be created in a folder swa-db-connections

Now, we will add an entity to expose our table as REST or GraphQL endpoints by running

dab add Item -c "staticwebapp.database.config.json" --source dbo.ShoppingItems --permissions "anonymous:*"

where -c specifies the name of the configuration file (default is dab-config.json) and --source specifies the table.

Confirm the entity has been added in your dab config file.

Push to GitHub.

Deploy your web app (and API).

Install the Azure Static Web Apps extension on VS Code to deploy your application on a Static Web App on Azure. Static web apps will automatically set up GitHub actions for a CI/CD pipeline and this way, your live app will automatically be updated every time you push changes on GitHub.

Open your Static web application on the Azure portal and go to Database connection (preview) under Settings.