Announcing Azure Static Web Apps database connections 🎉: Access your database directly from your static web app without backend code

[thomasgauvin]

Hi everyone!

We are excited to announce the launch of database connections, a new feature for Azure Static Web Apps that makes it easier for you to build your Static Web Apps relying on database access, allowing you to focus on building your application rather than focusing on backend implementation and hosting details.

With database connections, you can make CRUD requests to the built-in REST and GraphQL endpoints for your database entities at the /data-api route. You can configure these database connections to add role-based access control with built-in integration with Static Web Apps’ authentication seamlessly, and can also retrieve the relationship graph of your database with GraphQL queries.

Database connections can be enabled on your Static Web Apps for free during public preview, and operate on a serverless model. This feature supports Azure SQL, Azure Cosmos DB for NoSQL, Azure Database for MySQL (Flexible Server), and Azure Database for PostgreSQL (Flexible Server, Single Server).

Check out the announcement here: https://aka.ms/swa/db/announcement
Check out the docs here: https://aka.ms/swa/db/docs

We're planning to release some content to show you how to get started, making sure to check out https://aka.ms/swa/collections/dab!

How it works

Database connections works by extracting the schema from your database and generating REST and GraphQL endpoints for each of your specified database entities. You can specify which entities, along with their RBAC permissions and their relationships, in the configuration folder. This staticwebapp.database.config.json configuration folder must be in the swa-db-connections folder at the root of your repository. For full details on configuring your database connections, check out the docs!

Getting started

Local development

In order to develop your applications locally making use of database connections, you need to use the SWA CLI. The SWA CLI will emulate the runtime of Azure Static Web Apps locally so that you can develop against database connections locally. When running SWA CLI’s start command with the --data-api-location parameter, SWA CLI will allow you to make REST or GraphQL requests to the /data-api path of the SWA CLI server. You can also use SWA CLI’s local authentication emulation to develop with database connections’ role-based access control.

Adding a database connection to your Azure Static Web Apps resource

Adding a database connection to your Static Web Apps resource can be done via the Azure Portal. When you connect your database, you will provide your database details along with your authentication mechanism. This information will be collected and will override the authentication that your provided in your configuration file. No additional environment variables are needed, and your deployed Static Web Apps will match your SWA CLI local development behavior.

Let us know what you think!

We're constantly seeking feedback to improve database connections and the Static Web Apps experience. Feel free to share your experience in this thread and we will answer them as soon as we can! All feedback will help us shape the future iterations of this feature.

[yysun]

Can data-api call stored procedures?

[Nehmiabm]

Yes
https://github.com/Azure/data-api-builder/blob/main/docs/views-and-stored-procedures.md#stored-procedures

[thomasgauvin]

Absolutely! We use the underlying Data API Builder, which supports stored procedures!

[ChristopherHaws]

I am curious, how are database connections supposed to work when using pull request preview environments? How can we make it so a new database gets created, seeded, and cleaned up with each PR?

[thomasgauvin]

Hey Christopher! For now, Static Web Apps requires you to link an existing database, so you would need to either have a dev/test database, and you would also need to link that in the Portal/via Az CLI for new environments.

[RayMaAU]

Can we use a configuration/environment file to specify which database should be connected (linked) for the preview PR environments? So, we don't need to go to Azure Portal every time after the preview env is deployed, and manually link a db to it, thus to make the CI/CD pipeline can be more automated. In the swa-db-connections staticwebapp.database.config.json file there is a host.mode "production"/"development", what is it for? Can we use it to auto link a db?

[thomasgauvin]

Thanks for the feedback @RayMaAU, this is good feedback and we will look into providing a way for these to be auto-connected upon PRs.

Regarding the production/development, this allows you to indicate that you want a 'development' tracing for local development, and also enables Swagger/Banana Cake Pop viewers for GraphQL and REST endpoints. It is overridden with 'production' whenever you are hosting on Static Web Apps.

[zebleck]

Hey!

We're currently building an Azure Static Web App with Azure SQL Database and Azure AD B2C for authentication. We're trying to implement Row-Level Security based on the authenticated user's ID and roles.

We're stuck on how to pass the authenticated user's info to the Azure SQL Database while using Azure Data API Builder.

Any guidance on setting up this kind of authorization? Or is there some other preferred way that doesn't use Row-Level Security?

[ryanrandhawa]

Any plans to allow for managed identity authentication for preview environments? Working in an environment which does not allow SQL authentication for Azure SQL.

[jkauflin]

Can you specify multiple databases at the same time? I see some documentation that says yes, and I see a "data-source-files" array in the schema, but I can't find any examples or documentation that actually shows how to configure for multiple databases. I tried putting a file name in the data-source-files, but it doesn't seem to work:

"data-source": {
"database-type": "cosmosdb_nosql",
"connection-string": "@env('DATABASE_CONNECTION_STRING')",
"options": {
"database": "MediaGalleryDB",
"schema": "staticwebapp.database.schema.gql"
}
},
"data-source-files": [
"additionaldb.json"
],
"runtime": {