OpenAPI for your Azure SQL database
A recent and exciting feature of Data API builder (you may have learned about Data API builder from my previous article, as I used it to quickly make a stored procedure and a table available as REST endpoint to easily integrate with OpenAI) is the compatibility with OpenAPI specifications and Swagger. This was a highly demanded feature, and it is impressive to see it in action. You can quickly transform your database tables, views or stored procedures into standard, modern, scalable, REST endpoints that are accessible to everyone.
For this example, in just a few minutes, I converted the AdventureWorksLT sample database into a REST service that you can access and use – yes, you heard me, enjoy it! – at this link: https://dm-dab-awlt.azurewebsites.net/swagger
The database can now be used easily with any modern frontend framework, be it React, Vue.JS, Svelte, Blazor or anything that is able to make a REST call, and easily query the data with a simple (just using plan Javascript here):
var result = await fetch('https://dm-dab-awlt.azurewebsites.net/api/Customer/CustomerID/5')
var body = await result.json()
And you also have pagination, sorting, filtering and field selection capabilities, not to mention support for authentication and authorization (in fact if you try to do anything other than a GET you’ll get a 403). Pretty impressive if you ask me!
“Hold on a second!” – you might say – “I’ve just read recently somewhere that having all tables exposed is bad-bad-bad! Why are you doing this?”. I’m very familiar with that post that recently went viral, that really shows a nightmarish situation:
This post highlights a crucial point: choose the appropriate tool for each task. Data manipulation should not be done inefficiently in the client or the backend. Let the database handle it. Relational databases (which nowadays always go beyond the relational model) can do the work for you in optimal ways. Azure SQL Database can do things that looks like magic to optimize data manipulation in ways you’ll never be able to do yourself (for example, figuring out in real time the best JOIN strategy via the newly introduced Adaptive Join feature), as it would be extremely expensive and absolutely impractical, to move all data out of the database and then do the join. Just ike the tweet says.
Make sure that you do the right thing, and use views and stored procedure as needed, and expose those instead of all the tables. But as an example, having all the tables to play with is just fine for this playground, and allows you to get confident with Data API builder.
If you want to install it in your own subscription, to play with the Data API builder configuration file and check out all the features we packed into Data API builder, here’s the GitHub repo with the deployment code that can help you to get started:
https://github.com/azure-samples/dab-adventureworks-lt
Now, just have fun!
Light
Dark
4 comments
This is really nice and something I’ve been looking for a long time.
As far as filtering, pagination, field selection- can you confirm if this is the OData specification? It looks like it, but it doesn’t use the word OData anywhere. It would really suck if you guys invented a similar-but-incompatible variant of OData.
It is similar to OData to make it easier for people to use it, but it is not really OData as it doesn’t provide all the features that OData offers. We followed the idea that REST should be simple and straightforward, while GraphQL will provide more flexiblity (for example relationship navigation) where needed. I didn’t mention it in my post as I was fociusing on OpenAPI, but Data API builder comes also with GraphQL support.
Having said that, if OData is a key feature to be supported for you, please post the idea here: https://github.com/Azure/data-api-builder/discussions/categories/ideas 🙂 Thanks!
How do the Azure hosting costs of DAB compare with a manually created WebAPI deployed as a web app?
Am I reading the docs right when it says DAB can be deployed as a static web app? Does that mean there’s no code running server side (other than the DB Connection calls)? If so, that blows my mind!
Hey Daniel,
There are two ways to deploy DAB. The first one is deploying it in a container, in which case you’ll pay for the container, just like if you would have deployed your own code.
The other option is to use DAB within Static Web Apps (SWA), so that you don’t have even to bother about deploying the container :). SWA offers a free tier, and DAB follows that too. So if you want to start to use DAB right away and the SWA free tier suits your needs, you can start for free to use it (and remember that there is also an *free* Azure SQL DB tier that has been recently announced: https://devblogs.microsoft.com/azure-sql/new-azure-sql-database-free-offer/)
> Does that mean there’s no code running server side
In either cases there is code running on the server side. SWA just make it easier for you so you don’t have to worry about containers, CORS, auth and other infrastructure stuff. 🙂