Connecting to a database with Azure Static Web Apps (preview)

Article
03/15/2023

The Azure Static Web Apps database connection feature allows you to access a database from your static web app without writing custom server-side code.

Once you create a connection between your web application and database, you can manipulate data with full support for CRUD operations, built-in authorization, and relationships.

Based on the Data API builder, Azure Static Web Apps takes REST and GraphQL requests and converts them to database queries.

Features supported by database connections include:

Feature	Description
Integrated security	Built-in integration with Azure Static Web Apps authentication and authorization security model. The same role-based security used to secure routes is available for API endpoints.
Full CRUD-based operations	Refer to the tutorials for Azure Cosmos DB, Azure SQL, MySQL, or PostgreSQL for an example on how to manipulate data in your application.
Supports SQL and NoSQL	You can use relational and document databases as your application's database.
Serverless architecture	Connections scale from 0 to 1 worker (during preview).
Database relationships	Supported only via the GraphQL endpoint.
CLI support	Develop locally with the Static Web Apps CLI. Use the `--data-api-location` option to handle requests to data APIs in development just as they're handled in the cloud.

Supported databases

The following table shows support for different relational and NoSQL databases.

Name	Type	Description	REST	GraphQL
Azure Cosmos DB	Standard	Globally distributed database platform for both NoSQL and relational databases of any scale. In addition to the standard configuration, a `gql` schema file is required for GraphQL endpoints.		✔
Azure SQL	Standard	Family of managed, secure, and intelligent products that use the SQL Server database engine in the Azure cloud.	✔	✔
Azure Database for MySQL	Flex	Relational database service in the Microsoft cloud based on the MySQL Community Edition	✔	✔
Azure Database for PostgreSQL	Flex	Fully managed PostgreSQL database-as-a-service that handles mission-critical workloads with predictable performance and dynamic scalability.	✔	✔
Azure Database for PostgreSQL (single)	Single	Fully managed PostgreSQL database.	✔	✔

You can use the following connection types for database access:

Connection string
User-assigned Managed Identity
System-assigned Managed Identity

Endpoint location

Access to data endpoints are available off the /data-api path.

The following table shows you how requests route to different parts of a static web app:

Path	Description
`example.com/api/*`	API functions
`example.com/data-api/*`	Database connection endpoints that support REST and GraphQL requests.
`example.com/*`	Static content

When you configure database connections on your website, you can configure the REST or GraphQL suffix of the /data-api/* route. The /data-api prefix is a convention of Static Web Apps and can't be changed.

Configuration

There are two steps to configuring a database connection in Static Web Apps. You need to connect your database to your static web app in the Azure portal, and update your database connections configuration file.

See Database connection configuration in Azure Static Web Apps for more detail.

Local development

The Azure Static Web Apps CLI (SWA CLI) includes support for working with database connections during local development.

The CLI activates the local /data-api endpoint, and proxies requests from port 4280 to the appropriate port for database access.

Here's an example command that starts the SWA CLI with a database connection:

swa start ./src --data-api-location swa-db-connections

This command starts the SWA CLI in the src directory. The --data-api-location option tells the CLI that a folder named swa-db-connections holds the staticwebapp.database.config.json file.

Note

In development, if you use a connection string to authenticate, use the env() function to read a connection string from an environment variable. The string passed in to the env function must be surrounded by quotes.

Role-based security

When you define an entity in the staticwebapp.database.config.json file, you can specify a list of roles required to access an entity endpoint.

The following configuration fragment requires the admin role to access all actions (create, read, update, delete) on the orders entity.

{
...
"entities": { 
  "Orders": { 
    "source": "dbo.Orders", 
    "permissions": [ 
      { 
        "actions": ["*"], 
        "role": "admin" 
      }
    ]
 }
}
...
}

When you make calls to an endpoint that requires a role, the following conditions are required:

The current user must be authenticated.
The current user must be a member of the required role.
The REST or GraphQL request must include a header with the key of X-MS-API-ROLE and a value of the role name matching what's listed in the entity configuration rules.

For instance, the following snippet shows how to pass the admin role in a request header.
```
{
  method: "POST",
  headers: { 
    "Content-Type": "application/json",
    "X-MS-API-ROLE": "admin"
  },
  body: JSON.stringify(requestPayload)
}
```

Constraints

Databases must be accessible by Azure's infrastructure.
During public preview, database connections scale from 0 to 1 database worker.

Next steps

Configure your database

Share via