Microsoft 365 Community Viva Connections, 19 Apr 2023

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Exposing business data in Microsoft 365 using Azure Data API builder and SPFx

Description

In this 16-minute developer-focused demo, Davide Mauri and Paolo Pialorsi integrate a ToDo list into an existing application without writing any code with an open-source tool Microsoft just released called Data API Builder for Azure Database. For a new/existing database, specify a table, and let tool create a REST API or GraphQL endpoint that’s then consumed by your application – an ACE in this case! Step through ACE code for managing security and tasks.

This PnP Community demo is taken from the Viva Connections & SharePoint Framework Bi-weekly sync call recorded on March 23, 2023.

Demo Presenters
• Davide Mauri (Microsoft) | @mauridb
• Paolo Pialorsi (PiaSys) | @PaoloPia

Supporting materials
• Tool - Data API builder for Azure Databases | https://mcr.microsoft.com/product/azure-databases/data-api-builder/about
• Repo - data-api-builder | aka.ms/dab
• Demo - Data API builder and Microsoft 365 | https://github.com/yorek/dab-microsoft365-demo
• Builder - ace-dab-spfx (SPFx consuming DAB Demo) | https://github.com/pnp/sp-dev-fx-aces/tree/main/scenarios/ace-data-api-builder/spfx

Learn more
• Microsoft 365 Unified Sample gallery - https://aka.ms/community/samples
• Microsoft 365 Platform Community in YouTube - https://aka.ms/community/videos
• Microsoft 365 Platform Community - http://aka.ms/community/home

A

B

Okay, I'll give you control just because you are a friend of mine of.

A

Course now that I have control I'm, not sure what to do, because I can't do anything. So maybe just uh but.

B

I will be your demo monkey just let me know what to do and I will do it so.

A

Okay, yeah just uh so a little introduction about myself, I'm, a sequeler I'm, a SQL guy I'm, a PM in the Azure SQL team, and lately we just released something that I think it could be very, very interesting for you. It's called Data Pi Builder! That's what I'm going to to show with the help of Paulo, and so, let's just move to the next slide.

B

Yeah and I'm Paulo I'm a solution, architect I work in a company of my own and you can find all of the info here. But let's focus on the demo so yeah. Let me move on yeah.

A

Yeah, that's that's what we're going to build right! The the idea is that we want to build to do application, or at least integrated to do a list in no existing application, and to do that, you probably need to have a table, and then probably you need to expose some rest API to be able to connect with the SharePoint uh uh framework that that you have at your end and uh and now today you can do it without writing code.

A

Basically, because we just released the last week a tool called Data API builder for Azure database. That's the full name, but you know names are maybe uh not exactly our strong side. Also data API build that is, is something that allows you it's open source. So it's a it's an application that you can download and run offline or even, of course, a use on Azure that will take your existing database or new database.

A

If you want to start fresh, uh specify a table and we automatically create the arrest and even a graphql endpoint, that's what I'm going to do with power, so I will be taking a table, exposing it using a dub and then power will consume it into a nice application. Much nicer, the one that I can build myself.

A

This is how datapia Builder, uh basically the very high level architecture, and you have the link there. If you want to go and and see the source code it's available, you can download it and basically what we do is we just ask you to provide a configuration file because we want to be super, transparent and uh and and absolutely non-intrusive in the database. So we don't touch database at all.

A

We just use it, but of course we need, for example, to understand what is the URL that you want to use to expose some table, what tables so or story procedure or reviews you want to expose, and so we just use a configuration file. Then database Builder starts read the configuration file read the metadata, which is basically the relational schema from Azure SQL in our case, but we also support postgres, MySQL and Cosmos DB for Cosmos DB.

A

You need to provide a graphql schema because we need a schema um and then once we have all this information, we just expose the rest and the graphql schema. Now this is this is going to be, you know, kind of obvious, so you say: okay, that's easy. You know rest and graphql, but what is what was not easy? Anything that adds a lot of value is that we also support security.

A

So we also allow you to specify not only which restaurant point you want to make available, but within the rest, endpoint also which rows of data will be available to someone. So we also implemented something. We call policies which by all means are row level access, uh Securities um right, and that is something I will show you and uh let's go to the next slide and then I think we can go to the demo yeah. This is exactly how you install it.

A

So if you don't care about the open source part in the sense that you don't want to develop with it or at least contribute to the development you just do.net tool install and then it will be installed on your machine and it's a CLI application and then once you um once, you have installed it on your machine.

A

The first thing you have to do is dab init that will initialize the configuration file and then tab add to add whatever you want to add as a to the configuration file and then tab start so three command and you're done, and that's exactly what I'm going to show you um something else I wanted to highlight is that, of course, this is also hosted in Azure. So if you want to sell fast in Azure and that's what I'm going to do is super easy, you just run it in a container and it's done.

A

If you don't even want to take care of Hosting, you can use a static web apps to basically have a full stack uh kind of environment at your render in just a few line of code, because we also integrated with static web app. So if you're familiar with the SWA the static or up CLI, it's all integrated, so you just have one tool to do everything from front and back-end database, which is pretty nice. In my opinion. Okay, let's go to the demo. Let me share my screen here. We go so here.

A

I have a very simple table right, a to-do table and we have the ID the title completed and that's the important part now I kept everything single, very simple, but basically owner says who is the owner of a to-do item? Public means is has been created by an anonymous user and therefore anyone can see it, and if you have an ID, that's belonged to the ID of the user, who created it and of course, I can only see my own stuff.

A

Paulo can only see his own to-do and, of course you know it goes like that. So that's the table I want to expose them. What I I need to do is first of all, dub inita and I already done it and what you get once you do that in it is just this configuration file here which basically says what is the connection string in the database?

A

You want to connect to internet information like do you want to have rest enabled do you want to have graphql enabled- uh and this is the important part for for you- for a sharepointing for SharePoint usage.

A

um The provider that you want to use to authenticate in our case will be Azure directory, and this is uh uh Paolo's tenant and an Azure active directory configuration and then, and then aside, the runtime configuration you need to specify which entities need to be exposed in this case, I am exposing something I will call to do that is based on the table. Dbr table. It will answer to the path to do, and here you have the permission.

A

So if someone comes in and and doesn't present any token because that's rest right, so you have to present a better token. You will be anonymous, and so you will only be able to see the item for which the column of the owner ID is equal to public and you you can do any thing on that. I could just say: okay, I only want you to be able to read, but let's say I I want to fully support public objects.

A

Then, if you are authenticated, we will allow you to see the items that are marked authenticated kind of a semi-private uh thing here, but that's the important one sample rule one. uh So we just generate the roles that comes from in this case: Azure Azure active directory because the authentication provider you configured is azure active directory. So any role that is available there can be used here and we created a sample role, one and if I present my token and the token has a sample or one then I will be identified as a person.

A

With this permission and what I will be able to do is do anything on anything as long as the row on which I'm operating has the same value of my Ed in the claim. So you have with the policy the ability to exactly Define who will be able to see what and and also the action that is uh or are able to do on that. So it's pretty flexible and Powerful.

A

Now to run this, it's super easy. Once I have configured my file which, by the way it is just a Json file. So if you don't like uh dub, add kind of operation, you can just create the configuration file with any tool you want as long as it's a valid against this schema that is published here. So uh if you want to automatically expose all the tables, you can easily create this file on your own, and now all you have to do is tap starter. This will start locally.

A

My um the application, and now it's listening on localhost, 5000 or 5001, which means that with the uh insomnia, arrest, client or Postman I can just go here. Let me zoom in a meter and then I query. So, of course, this is the one on Azure.

A

Let me just use the local, the local one lost five thousand one, and that will be uh you know connecting to my lock a lotabase, and my local database is now connecting to to Azure, and this is my table right and, as you can see, I can only see the public item because right now, I am not providing any um any any better token right. There is no.

A

Actually this is disabled, and probably it is better token is, is expired, so if I try to do that, I will probably get an error uh because of course now the token is expired.

A

um But that's that's to show you that everything first of all can work offline, which is great for a development experience if I want to deploy. It is super easy. So, first of all everything I'm showing you is available on a GitHub repo that I created just for this sample, and here you have an Azure deployee file, I'm I'm, more confident with a shell file than Arma and bicep. So everything here we'll just use AZ to deploy basically what this thing here uh web app that uses a container behind the scene.

A

So if I go to the configuration, uh there is a that API Builder is also published as a container on the Microsoft public registry, so here I'm using a container and and basically what the container is doing behind the scene. It's just using uh dotnet instead of wcli to execute a data Builder engine and that's it. That's that's! Basically what you need to do um once you deploy it is. You will see that we are taking the image from here, so that will be the the latest one and then I can just go here.

A

That's what I already has prepared before so and now I'm I'm going I'm going to the DMR web app, the the basically the same website. You have here yep, that's the same website now again I'm using insomnia. But what will happen right now is that of course, I still get access to all my items here right all the public one. uh But this time I'm I've been served by Asia.

A

So in just a few minutes, you can literally take any table that you have or views or start a procedure and just publish it on Azure in this case, using that API, Builder and everlasting point that is fully working, which this enables you power to do something pretty neat right.

B

Yeah definitely so I'll take this screen sharing. Thank you, David yep. Let me know when you see my screen, please we can do you at least I can okay yeah perfect, so this is an Adaptive card extension that I've built using SharePoint framework in order to consume the API. That David just showed you built with the data API Builder, so just to give you an idea of how it works. Well, I can see a list of tasks which are related to my own account. I can play with them.

B

I can add new tasks, for example a new one with PMP. Let me add it and if I will go back to my list of tasks, I can play with it. I can set it as completed and stuff like that, so you can play with it using SharePoint framework and creating nice adaptive card extension Solutions. How uh can we make it well? First of all, as David showed you, we have an application registered in Azure directory in the Target tenant.

B

This is the application ID that you saw in the configuration file of the dub settings file, and here we have an explosion. Api setting where we have this application, unique URI and we have a permission, which is the endpoint.access permission now from a SharePoint frame or point of view where I created my adaptive card extension I simply have my adapter card extension, which, in the on init method, will rely on the service scope pattern.

B

To give you a clean and well architected example here, using the context of SharePoint framework, I simply create an instance of a custom service that I have defined in my solution. This service implements an interface I2 service which will do all the or will provide all of the activities that we need to have for our to-do list. So we can list it to do items. We can get a single item to the add, update and delete, so the crude queue operations. Every single to do.

B

Item will be made by an idea: title a complete status and the owner ID, as you saw in the SQL table, and then we have the actual implementation of the tutu service in my solution here in the Constructor I simply rely on the ad HTTP client Factory object that we have in SharePoint framework so that this object. This type is provided by atmax of SP, HTTP and I can simply use it to create a client object to consume my unique URI, the one that you saw before in the Azure active directory settings.

B

Okay, now, whenever I want to consume the resting Point provided by David I will simply need to rely on this AED HTTP client instance object and I will simply have to make my request, which can be a get request, for example, to get the whole list of uh to do items or it can be a post or a patch if I want to add or to update an item, but no matter what I want to do against the API.

B

What is really important to know is that, in order to have proper authentication in place, I will always need, while consuming a data. Api Builder exposed apis to use the adhdp client instance, providing a set of options in the options. I need to provide a custom header. Let me go into this method in this get request. Headers I will simply add a custom header which will be called XMS api-roll, in which I will have to specify the role that I need in order to being able to consume DPI.

B

This is precisely the role that we see in the up role so defined here in our Azure active directory application. As such, when I make a request from here, let me show you with F12: I can make or actually better from here. Let me make a request now for the to do API right here in this page, and you will see that there will be a to-do request right here. If we scroll a little bit down, let me make much more room here.

B

We have the hopes most likely if we have the access token right here, the bear access token. This has been inserted in the request by the SharePoint framework infrastructure. We don't need to take care of it because it is provided out of the box by the aadhdp Handler, and here we simply have a request with an access token, with the sample roll. That dub is waiting for in order to grant me the permission to get access to the Target to do items so from a SharePoint framework perspective. It is really really really easy.

B

You simply rely on the adhdp client Factory. You create an instance of the adhdp client object, it's like as it would be. I love you. It is provided by dub and you will simply need to add this custom Adder to have Security in place. One more and last thing. You need package solution, Json file of your SharePoint framework solution that you need to have the permission and point dot access, which is the custom one we defined for our own API register in Azure, active director and, of course, in your solution.

B

This will be your own permission, because the application in is ready will be registered by you with your own custom, permissions and settings. That said, I think that's all, and it is a really powerful capability that you can leverage back to you. You go and thank you, foreign.