Building Static Web Apps with database connections: Best Practices
Published Mar 23 2023 01:54 PM 7,976 Views

Today, many web applications depend on custom API backends to have access to databases. This approach allows you to have full flexibility over your database calls but building out such a backend can be tedious and complex, especially when considering the edge cases of a data-access backend such as authorization and relationships. With Static Web Apps’ database connections feature, and its underlying open-source project Data API Builder, you now have more options to build out database-backed web applications. In this blog article, we cover the considerations of choosing between these various options.

 

A quick introduction to the Static Web Apps database connections feature

 

As announced last week, Static Web Apps database connections is a new feature to connect and use databases directly from your static sites. When you connect a database, Static Web Apps will provision a `/data-api` endpoint that will allow you to make REST or GraphQL requests to retrieve or edit your database entities, directly from your frontend static site with no backend required.  For more details on the feature, check out our documentation and getting started guides.

 

thomasgauvinmsft_0-1679603458440.png

 

 

Now that we’ve covered what Static Web Apps database connections is, let’s consider when to use database connections for accessing databases from your Static Web Apps.

 

Static Web Apps database connections or managed functions? 

 

Many Static Web Apps depend on backend APIs to access their database contents. This often involves creating separate endpoints for each entity, and separate handlers for various actions (create, update  delete, etc.). As the application progresses, these endpoints need to be updated to cover authorization for various user types and expose entity relationships. Later on, when you have to improve the performance of your applications, you may need to optimize queries requesting only certain entity fields, or even adding this granularity to related entities. Each time you want to add functionality to your application, you have to make changes to your data access backend. Furthermore, when building your own backend, you have to consider how you host your backend, and how to coordinate changes across your backend and frontend.

 

This is precisely what Static Web Apps’ database connections seeks to solve. When you connect a database to your Static Web App, you get a provisioned `/data-api` endpoint to make REST and/or GraphQL requests to the database entities you choose to expose. This endpoint comes out-of-the-box with support for request query parameters, authorization with Static Web Apps authentication integration, and granular relationships access via GraphQL. Essentially, the database connections feature enables you to focus on building your web application with optimized data access APIs, without having to build such a backend yourself and having to host it.

 

For instance, let's look at the database connections configuration snippet detailed below (this is a snippet from the Library Content Management System sample:(

 

{
...
    "entities": {
      "Author": {
        "source": "dbo.authors",
        "permissions": [
          {
            "actions": ["*"],
            "role": "admin"
          },
          {
            "actions": ["read"],
            "role": "anonymous"
          }
        ],
        "relationships": {
          "books": {
            "cardinality": "many",
            "target.entity": "Book"
          }
        }
      },
      "Book": {
        "source": "dbo.books",
        "permissions": [
          {
            "actions": ["*"],
            "role": "admin"
          },
          {
            "actions": ["read"],
            "role": "anonymous"
          }
        ]
      }
...
}

 

In this, we specify 2 entities, "Book" and "Author" that we want to expose via our database connections. With this configuration, Static Web Apps provides you with /data-api/rest/Author and /data-api/rest/Book REST endpoints that accept POST/PUT/GET/DELETE requests for logged-in users, and only accepts GET requests for anonymous users. It also provides you with GraphQL queries and mutations with the same authorization, and allows you to query your entities with their relationships as well. As we can see, these few lines allow us to configure our database connections with authorization and relationships. With database connections, we can focus on building our applications, rather than having to manually build out and maintain a data-access backend.

 

When considering Static Web Apps database connections and database-access with API backends, it’s not an either or; it’s an and

 

Static Web Apps database connections and managed functions can both be used to build out your web application together. The advantage of having access to both tools is that they complement each other well. When you want to have access to a new database entity, rather than having to build out a series of API endpoints, you can leverage database connections to provide access to that entity with all features enabled on day 1. Conversely, if you need more flexibility such as having to pre-process or post-process your data, you can write out custom backend endpoints to process the data before saving it to a database.  

 

To that end, Static Web Apps database connections can be used for both existing applications and new projects. For existing applications, adding new functionality to your application that would require new database access can be done via database connections. Similarly, for new applications, much of the CRUD access can be built out via database connections, and backend functionality that requires more processing can be done with backends.

 

Static Web Apps database connections or Data API builder?

 

When considering Static Web Apps’ database connections feature, you may also encounter the Data API builder open-source project and wonder which tool is best for your use case. Static Web Apps actually uses Data API Builder under the hood to provide its database connections feature, so this is a natural question to have. Note that you don’t need to know about or use Data API builder if you are only using Static Web Apps’ database connections, though you may be interested to know how Static Web Apps database connections works “under-the-hood”.

 

An architectural diagram of Static Web Apps featuring managed functions and database connectionsAn architectural diagram of Static Web Apps featuring managed functions and database connections

 

Data API builder is a Microsoft open-source project that allows you to create REST and GraphQL endpoints from your database. On one hand, when you use Data API builder, you can have access to more flexibility related to the configuration of your data API. On the other hand, you are responsible for managing and hosting the Data API builder engine. This involves hosting this engine via containers and configuring the scaling of these containers within your architecture. You also have to consider doing this for each of your environments and applications.

 

A diagram showing how Data API builder provides REST and GraphQL endpoints from database schema and configurationA diagram showing how Data API builder provides REST and GraphQL endpoints from database schema and configuration

 

Static Web Apps’ database connections feature was built to allow you to access your database entities directly from your static sites, and abstract away the need to configure and host Data API builder on your own. This means that for each of your Static Web Apps environments, you essentially get a serverless Data API builder that is managed for you and that processes your requests. This allows you to really focus on building your applications with your database data.  

 

If you are building with Static Web Apps and require database access, the database connections feature abstracts away the provisioning of underlying resources and allows you to simply make calls to the `/data-api` endpoint. Doing so allows you to benefit from a seamless integration with your Static Web Apps features, your preview environments, and your local development experience with the SWA CLI. And it’s simple to enable on your Static Web Apps, with no additional hosting or configuration needed. However, if you are building a mobile application, or a different type of web application, using the Data API builder engine directly may be a good option for you so that you can benefit from all the built-in functionality of the engine. This will involve hosting, provisioning and configuring the Data API builder to be accessible from your applications.

 

Conclusion

 

In sum, Static Web Apps’ database connections feature, Data API builder, and manual database-access backends are all great options for enabling access to your database to your applications, and each have their strengths. Static Web Apps’ database connections is a serverless managed option that allows you to connect to a database from your static site just by accessing the `/data-api` endpoint, without needing to build out or host a backend. Data API builder is an engine that allows you to query your databases with REST and GraphQL, and must be manually hosted to use with your deployed applications. Writing a custom backend allows you to have the most flexibility over your data and apply any pre- or post-processing of that data. By using these approaches together, you can increase your productivity and build out secure and optimized database-access applications.

 

 

 

 

6 Comments
Co-Authors
Version history
Last update:
‎Mar 23 2023 02:02 PM
Updated by: