OpenAPI for your Azure SQL database

Davide Mauri - Oct 24 '23 - - Dev Community

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

List of endpoints via 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()
Enter fullscreen mode Exit fullscreen mode

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:

Tweet screenshot

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!

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player