I mentioned in my previous article that having native JSON support in Azure SQL it’s a game changer as it profoundly change the way a developer can interact with a relational database, bringing the simplicity and the flexibility needed in today’s Modern Applications.
As Python is becoming immensely popular, one of the most common tasks for a developer is to create REST API using Python. Thanks to JSON support, using Azure SQL as a backend database to support your API is as easy as writing to a text file, with the difference that behind the scenes you have all the peace of mind that your data will be safely stored and made available on request, at scale, with also the option to push as much compute to data as you want, so that you can leverage the powerful query and processing engine while keeping your code simple, elegant and agile, with a clear separation of concerns. All these things will help you immensely once you’ll start to evolve your project to keep it updated with today’s demanding and ever-changing world.
You can see by yourself how simple it is:
A dictionary is created with the CustomerID property. The dictionary is then passed to the stored procedure as a JSON document, which will return a JSON document as a result. The stored procedure is really simple as well:
First line shows how JSON_VALUE help to extract data from JSON when you are looking for a scalar value. Then you get the column you need from the Customers table, also specifying the shape you want the resulting JSON to have by aliasing the column with a name using the common dot notation to represent relationships and hierarchies. Then you use FOR JSON to simply tell Azure SQL that the result must be a JSON document, not a tabular resultset.
That’s it. Minimum effort yet amazing value. Clear separation of concerns, abstraction from database schema via stored procedure usage, efficient query performance via index usage (and you don’t have to do anything special to maintain the index updated, it will be done automatically by Azure SQL behind the scenes), database and application working perfectly together as a team, thanks the JSON being the glue that keep the solution together, and also favor loose coupling and thus scalability.
And while you, as a developer, are working to create a nice UI for the new API just created, someone else can query, process and update the data, even using very powerful and resource consuming processes without affecting your performance at all, as isolation can be guaranteed using read-only copies and geographical distributed database.
Not bad for just a bunch of lines of code, right?
Even better, the whole sample is available here for you to play with, and, especially if you are a new developer, it will also have some interesting link to videos and tutorials on how to use Python, Flask and Visual Studio Code to create the API I just described. Enjoy!
https://github.com/Azure-Samples/azure-sql-db-python-rest-api
Microsoft
