A friend of mine develops complex models and saves the output to multiple SQL tables to share them with stakeholders. He needs an easy and secure way to share this sensitive data.
With the Data API Builder we can generate API endpoints based on an existing SQL DB. The Data API builder is integrated in the Azure Static Web Apps offering of Azure.
You can follow Tutorial: Add an Azure SQL database connection in Azure Static Web Apps | Microsoft Learn and create an API based on an Azure SQL Database. In this article I show the finished API and discuss the advantages of generating an API.
The end-result is available under ambitious-sand-06ee7ad03.5.azurestaticapps.net/data-api/rest/Customer. If the page is down, see this screenshot.
How it looks like
We add the DB connection through the Azure Portal. This adds the required environment variable DATABASE_CONNECTION_STRING in the Static Web Apps environment.
That’s one part of the configuration. We still need the database configuration file.
The configuration file is generated through the SWA CLI.
The configuration file looks like this. See especially “connection-string” and “entities”.
{
"$schema": "https://github.com/Azure/data-api-builder/releases/download/v0.9.7/dab.draft.schema.json",
"data-source": {
"database-type": "mssql",
"connection-string": "@env('DATABASE_CONNECTION_STRING')",
"options": {
"set-session-context": false
}
},
"runtime": {
"rest": {
"enabled": true,
"path": "/rest",
"request-body-strict": true
},
"graphql": {
"enabled": true,
"path": "/graphql",
"allow-introspection": true
},
"host": {
"cors": {
"origins": ["http://localhost:4280"],
"allow-credentials": false
},
"authentication": {
"provider": "StaticWebApps"
},
"mode": "production"
}
},
"entities": {
"Customer": {
"source": "SalesLT.Customer",
"permissions": [
{
"actions": ["*"],
"role": "anonymous"
}
]
}
}
}We’re done. This was what is needed to hook the sample SQL DB to our Azure Static Web Apps.
Why even bother?
It couldn’t be simpler to expose your SQL DB data via REST endpoints. If you change the schema of your SQL DB tables, the endpoints return the new column automatically. Authentication and customization is done through configuration files what makes it very robust. From my point of view, the Data API Builder does the job of translating our structured data in consumable API endpoints.
Going further
Azure Static Web Apps offers multiple authentication mechanisms. The data API can be secured in the same way. See Authentication in Data API builder | Microsoft Learn for more info.
An Open API description document is exposed under /data-api/rest/openapi.
I will use this feature to easily expose a database through an API to a frontend or users that would like to consume my data through REST or graphQL. I’m looking forward to see your APIs in action.