Hello and welcome to the let's Talk Azure podcast with your host Sam Foote and Anne Armstrong. If you're new here, we're a pair of Azure and Office 365 focused it security professionals. It's episode 22 of season five. Alan and I recently had a discussion around data API builder. It's a powerful tool designed to streamline the process of creating and deploying APIs for data driven applications. And what did we cover? What are crud APIs and why are they important? How do you use it? What are some of the use cases for the solution and how much does it cost? We've noticed that a large number of you aren't subscribed. If you do enjoy our podcast, please do consider subscribing. It would mean a lot for us for you to show your support to the show. It's a really great episode. So without further delay, let's jump in. Hey, Alan, how are you doing this week?
Hey Sam, not doing too bad. How are you?
Yeah, not doing too bad. Not doing too bad. Are you? Sort of. Are you all product updated out? If that's even a term that I can, that I can use.
Yeah, yeah, definitely. Yeah. Still running through all the updates and checking some of them out that I didn't notice they didn't know about but not tested out yet. What about you?
Yeah, I think I added more to my things to look at list in last month and I don't feel like I'm gonna get through it this month, if that makes sense. Right. So yeah, it's gonna be interesting to see what our end of month update episode is like this month. See if they push out anywhere near as much stuff. I bet you they will. An absolute. Yeah. Machine.
Yeah, it's definitely gonna be some things in there, so. But um. Yeah, just recovering, I say recovering, but just, I guess last week we had the, we had infosec, didn't we? So there was a lot of activity there as well.
Yeah, I think big thing that I've been sort of looking at is the sort of coverage from Computex, like new hardware that's coming out. It's interesting to see more details of these. I think they're snapdragon processors that are in the new surface laptops. Interesting to see their sort of local AI capability that they've got. I know. I don't know. Well, at least Twitter was a bit ablaze with recall concerns around privacy. I think Microsoft actually put a blog, blog post out saying that it's going to be opt in now, I believe so, yeah. Just calm things down, I think a little bit. But also interesting to see Apple's WWDC as well of them pushing onto, they're not calling it AI, they're calling it was Apple intelligence and their sort of viewpoint of on device, you know, models. So it does feel like sort of edge, edge AI at the edge. I don't know if that's a term, probably is what it is now. I suppose that seems to be more and more of a thing to me now. You know, I wonder if that's to also reduce some load server side as well for some of that processing onto the endpoints.
Yeah, I mean, it's not what this episode is all about, but it was interesting because we were talking about this earlier today. But it does look like since Pixel four there's been an MPU present for the Google side or Android side there for Google Pixels at least. So it's been small presence there at least since then.
Yes. It'd be really interesting to see what happens in that space. Obviously changes almost every week.
Yes. Okay, let's get on with this episode. So we are talking about.
Data API builder, a product I never knew existed until. Was it last episode or episode?
Yes. It wasn't. It hasn't been out for a year. That was right, wasn't it? In preview? Yes.
It's like, whoa, this, this is one of those ones that they just put live. That's a bit weird, isn't it? You know, Alan sends me a link going. No, it's been a preview for a year, Sam. I can't believe I missed this one. Yeah, I'm actually quite excited by it.
Okay, so sam, I guess, you know, what are, if we kind of start from the beginning, you know, what are crud? What is crud? And what is crud APIs? And I suppose, you know, what are APIs in general.
Yeah, so let's talk about APIs in general. Just to start off with APIs, application programming interfaces, they come in many different forms, but primarily on the web. The sort of way that people approach APIs is through what's called rest APIs. I can't be certain, but that is the sort of the main delivery mechanism nowadays, I would say. There's lots of other different API technologies. Soap with XML, you've got rest, you've now got graphQl. We'll talk about that a little bit as well. But basically, APIs can also be not just web driven APIs. They can be on your device and lower level APIs. But essentially an API is a way for two different systems to define a contract, let's say, about how they're going to communicate with each other. So you've got word and probatics example, Sharepoint and Dataverse as an example, and they want to talk to each other. An API can be defined and that can sit wherever, but an API and a contract can be defined from a server and a host, and then a client can then connect to that API and transmit and receive data via the API. Why are they important? It allows distributed and disconnected or loosely coupled systems. If you're an organization and you've got, let's use that example, you've got Sharepoint, let's say you've got dataverse and you want to pull some data into power. Bi as an example, you might use an API to go and get that information. Now, they're two different technologies, but they have a common way of communicating with each other. So there's lots of different standards for APIs and the different flavors, but essentially it allows you to bridge systems together. So APIs, sorry, massively popular, in use everywhere, ubiquitous, that's just a pivotal part of modern day computing. Crud APIs stands for create, read, update and delete. Now, a lot of applications, all they are doing is creating data, reading data, updating data, and then deleting that data, potentially. So when we're talking about a crud API, it's just those simple sort of data management actions that you need to take. So let's say you've got a to do list. You might want to add a new item to your to do list. You might want to read the list of to do list items so you can display them to the user. You might want to update one because you've found a typo or something, and you might want to delete one or mark one is complete as an example for a to do list. So these types of sort of crud API, a lot of API interaction and a lot of building of APIs is sort of scaffolding and building that very basic, you know, those four basic actions really. You do have other business logic that you might want to trigger in, so that when somebody deletes an item from their to do list, you send them an email. Silly example, but that's another bit of business logic, really simple sort of process flow. If user deletes item, then send email. So when that delete request goes out, there's another little bit of code. Might not be a little, another bit of code or business logic as we call it, that goes off and actually sends that, that email but a lot of the time it is just data management, you know, updating, moving data. So a lot of time is spent by developers building and there are frameworks out there to accelerate that scaffolding. There are lots of sort of what we call SDKs platforms that you can, you can essentially start to, they're sort of opinionated frameworks so to speak, that allow you to accelerate that development. But building these sort of, these APIs does take a lot of time and developers are scarce and they are expensive. So anything you can do to make them more efficient or make it easier for non developers, as an example, to actually build these APIs, there's some real potential efficiencies that can be gained there and real money saved.
Yeah. Okay. So yeah, I mean I've probably consumed APIs and as you said, kind of this kind of stuff has been around the 365 environment but yeah, creating one from scratch and things like that. I think I'd have no idea how to start that to be fair. I guess if we talk about the data API builder, what are the key things that it's trying to solve?
Yeah, the data part of that name data API builder is really around that sort of those crud actions so to speak. Right. So let's say you've got a database, right? And I'll use the example of like a internal application at a business, let's say, right, let's say you've got a SQL server database that may be part of like a legacy application as an example. That's probably what I'm going to use. And maybe that application is end of life. Maybe it was custom built by somebody potentially years ago. And maybe you want to get access to the data that's in that database now you could use a, let's say you're using SQL Server, you could use the SQL server client, SQL Server management studio as an example and you could connect to that database and sort of read the data from the database. And if you're not really used to databases, imagine a bunch of Excel files which we would call tables, you know, rows and columns of data effectively. So you've got a database with a bunch of tables in it. But imagine a table as an excel sheet. So I've got my database but I want to maybe connect to it in a more modern way. Maybe I want to use a rest API so that I can integrate it with another new more modern part of a system. Maybe I'm building a power automate flow or I'm building a logic app or I've got some I'm using data factory or something like that to build a data flow, but I need to access that database in a way that maybe I don't have access to a SQL client where I can connect to it, and maybe I want an API there. You could then hire somebody to create a new API layer for you. They put all the code in place to build all the actions that you want for all of the tables. You scaffold the code and then you host it somewhere, basically. So that's absolutely fine if you've got an in house development team, somebody that knows how to do that and also secure it as well, because we'll talk about that and that's fine. That's really traditional API building. Now, what data API builder is doing is it's a tool that is effectively a, it's a command line tool, but then it's also an actual process itself that runs. And what you can do is you can create a configuration file, and I'll talk you through how you install it and get started with it. But you basically build a configuration file which points data API builder at your database. And then you say, hey, in this database there is a table called to do list items as an example, and it will go off and it will read what's in that database table and it will automatically create an API for you with those crud attributes. They're ready to go automatically. So there's no, you can do extra configuration for more advanced scenarios. We'll talk about a few of those later. But essentially, if you want create, read, update, delete, rest, API endpoints, it's going to generate that for you ready to go. Then it's going to do things like create you an open API specification. What an open API specification is, it's effectively a schema or specification document for your API. You can plug that into other systems and then those other systems know what they can and can't retrieve and the types of data that are returned. But data API builder gives that to you automatically. So when you run it, you go to a special like URL and it shows you the configuration. So if you want to plug that into other systems, like logic apps as an example, they can take OpenAPI specifications copilot for security. There's loads of other systems that use these specifications. I'm not going to really go into that, but that's another thing you would have to build. And there's SDKs and plugins that allow you to do that, but it's really giving you that accelerated API building now. Okay, it's not going to be as customizable. But let's say you do just want to do, you know, sort of crud work on a database. You might not ever need anything more complicated than that. You might want to keep all of your sort of crud logic, so to speak, as part of this process. And then you make your own separate set of bespoke function apps or logic apps to do anything else that you might want to do if you want to pile on any other business logic. Because when you're actually talking and saving to the database, you can go one way and then you can go another way. It will go directly direct if you also want to. Now the really cool part about this is, and they short, the shortened name is Dab. So that's what I'm going to use going forward. But DAB supports SQL Server, so on premise SQL Server SQL Server Express I assume Azure SQL Assure Cosmos DB Postgres SQL Azure database for postgres Azure Cosmos DB for postgres, MySQL, Azure database for MySQL and Azure SQL data warehouse. So there's lots of different environments that this piece of technology will talk to. And you'll also notice that there's some non Azure prefixed technologies there, just like native SQL Server, native MySQL, native postgres. So I know this is called like Azure Data API builder, but you could in theory connect it to MySQL running anywhere. I don't know what the minimum version that's required, you know, but that's just something to really call out is that it's, it supports a lot of different technologies, which is really positive to see.
Yeah, it does, it does sound very interesting. What was I thinking about? Yeah, I guess kind of like you said, you know, if you, if you're, if you're working in a, an API space or you need to have access to that data and you can't, like you said, use a SQL client to access it, then it's perfect, isn't it, to kind of just bring you that API to you quite quickly as well because I guess in some theory you could at least use it if you really needed to do advanced, maybe more advanced API. At least you could start with this to make sure that the data is in the right format and things like that as well for your API. Because I guess that's a question as well about how you consume it or how it comes out of the API without much effort at least maybe, I.
Don'T know, let's say that you were connecting to new systems. Let's say you had an old legacy application and let's say you are building a new version of that. Now the people that are working on the user interface, you might be building a brand new API to go in between, but this could be a really good way to expose that older legacy data to those developers that are working on building that new user interface. Because you bring data API builder up, they can develop and work against that because they know what types of data they're going to get and I suppose kind of in what format. So you could even use it as just like a temporary solution whilst you're migrating to something else. But it's not marketed marketed as that. There are other features in there which means that it can be run in production as well.
Yeah, well, yeah, I mean, from the sound of it, you know, using Azure CosmoS DB and a sure sequel, that's definitely not legacy capability, is it? So it definitely sounds, you know, definitely more, you know, a, you know, production or you know, more recent sort of, you know, functionality there. But yeah, it's definitely interesting. Okay, so I guess you've talked about what it is, but how do we use it?
Okay, yeah, so the first thing that you have to have is you have to have.net eight installed. And so that's sort of the first prerequisite. It's a tool within, it's A.NET tool which is essentially a. What's the best way of describing it? It's like a plugin or a tool that is installed into.net itself. You get.net installed, that's relatively simple on loads of different platforms. It's really across platform. It's basically everywhere. Then you just do.net tool install and you install it globally and it's just Microsoft data API builder. Just run that command. All the docs are basically really good, really simple. Then you've got to have your database that you're going to connect to. When I worked through this, I just created a docker container with SQL Server in it just quickly to bring something up. Then you create your configuration file. So you do what's called Dabinit. Once you've installed the tool, you get like an actual executable that you can, an application that you can run. It's in your path. So you just do dab init. You tell it what type of database it is, the connection string to that database and some other sort of options. So you do that first and that creates sort of your connection into it. And then what you do is you add each entity so this is basically each table that's in that database. So if you had, what was the example I used to do list items, you would say like dab add todo item, and then you do the source, you know, dbo todo listitems as an example. Once you've run that and let's, you know, let's do a happy path, let's say it's, it's all, you know, sort of gone smoothly, then you just start the service, basically. So you just do dab start and it will spring into life and it will give you a URL to go to. So I think by default it's localhost port 5000. And you go there and then you do the localhost 5000 API, the name of your model that you've just created, and it will just list all of those items that are in the database. There's filtering, there's paging, there's all sorts of options there that you get out of the box. You don't need to build any of that. It's all just built in by default. If you want to get to the open API specification, it uses a system called Swagger, which is an application that hosts and manages that specification. And you can go there and then you get sort of interactive user interface, web interface where you can actually execute some of the API requests direct in the web browser. So as long as you've got your database all ready to go and lots of people will have databases that they want to interface with. Yeah, you just run a, literally a couple of commands and then you can have a fully fledged sort of API development environment ready to go.
Well, there's not much to it, is there, at all. You know, there's a lot of, it's quite, it's powerful, but red is simple to get started. Okay, I've got, I have got some questions, but I think I'm going to wait till after the next section. It might come out of this. So can you take us through how it works?
Yeah, okay, so yeah, I've spoken about sort of by, I suppose by default you get a sort of rest API. So you have, if you're utilizing rest APIs, you get post, get put, patch and delete actions. You get filtering, sorting and pagination built in. There is also an in memory cache as well for the actual data there. And then. Yeah, I've spoken about the support for OpenAPI specifications. You also get a GraphQL endpoint. Now GraphQL is a, a relatively new, I say relatively because GraphQL has been out for a while now, but GraphQL is a different type of API technology. Essentially in graph QL, the person that's consuming the API describes to the API what they want to return. So on a rest API, if your to do list items has got like a title and a description, if you request a list of to do list items, you're going to get titles and descriptions sort of every time. But with GraphQl you can say, oh, I only want to see the, I only want to get the titles, I don't want the description. Why is that? Because I only show the titles on this screen. And GraphQL was sort of driven out of sort of a mobile development perspective, perspective of just requesting the minimal amount of data that you effectively need. So not only is rest supported, GraphQL is also supported. And you can set up your queries. If you're into graphql, some of these things that I'm going to describe will make sense to you. You can do queries and mutations and you can set all that up in as extra config. You get filtering, sorting and pagination as well, but also relationship navigation as well. So you can define the relationships between tables of data and you can navigate them on both the rest side and also the graphql side as well. So what relationships are is where you've got two pieces of data that are linked in some way. So like a user, a user entity might have a list of to do list items as an example. A query that you might want to run is say, get me all of the to do list items for Alan as an example. So having relationships allows you to define sort of the navigation paths to pull back data, essentially. There is also an environments, a way that you can essentially run. You can essentially run different configurations dependent on the actual environment that you're looking at. So different configuration files can be set up for development, production, staging, different connection strings for staging and development, staging and production, that type of thing. So there's a very flexible sort of hierarchical sort of configuration. You can also use environment variables and EMV files as well. So there's lots of, it's very sort of developer orientated. From that perspective. Something I didn't try but is in the documentation is having connections to multiple databases through one single data API builder. So you don't just have to connect to one database. You could connect to multiple databases and also different languages and different dialects databases. So if you maybe you're going through sort of m and a activity, you're pulling in lots of data from your new acquisition. Don't know, random example. You want to start pulling data out of databases to query it in power bi as an example, you can put data API builder in front of it to give you a nice rest API to connect to it. So yeah, very powerful. That type of thing would also lend itself to multi tenancy as well if that's what you're doing. Like a separate database per customer, that type of thing. In database, sort of relational database technology, there's sort of a term called a view, which is a, you write a query to pull back some data and you can sort of save it as a view and that's effectively like a cached query. So you have the ability to bind onto views as well. So views are, I don't know, I haven't used views for a long, long time personally, but they were, I assume they're used lots still. I just haven't used them personally. But if you do have sort of an application or a database that maybe you're inheriting or there's a specific technical requirement for you to have views that is also still supported, the one that I'm really, the piece of functionality that I'm really sort of excited about for sort of legacy data interfaces is stored procedures. Stored procedures are effectively a bunch of SQL commands that are sort of bunched into a specific script, so to speak. So it's a stored procedure. You build your business logic in your database and you execute stored procedures. There was one time that stored procedures were used for all manner of business logic and they probably still are. Again, it's not something I really use today, but you can actually bind on to store procedures because stored procedures, you can pass parameters into them when they execute. So you can map all of that to your API as well via config. So from an actual connecting to databases and pulling data out of it, you've got quite a lot of support there for sort of key data. Yeah, sort of key data technologies, authentication and authorization. So you can do, you can put authorization in front of the API itself. So you can use Azure authentication, JWT, OAuth, those types of technologies. You can also have anonymous if you want, so that sort of anybody can, can access it. You can simulate authentication locally as well. So as you're developing you can sort of build your integrations with those authentication systems but not actually have it linked up. You can put custom user roles in and you can also do row level security as well. And row level security is like quite, I would say an advanced probably not. Now, I don't know, but probably quite an advanced sort of SQL technology where you're actually putting sort of RBAC onto a row level. You can also do that in this system. So that's quite a sort of an advanced part of a lot of frameworks and orms. So it's really good to see that you can do that. Last part I just wanted to touch on was deployment. So this is where it gets probably the most impressive, I would say, is that it's essentially designed to be deployed into modern sort of hosting stacks. So Azure app service is supported. Azure container instances Azure container apps kubernetes. So effectively it can be containerized, but what is also really impressive is Azure static web apps. Now Azure Static web apps is a service where you can host literally static web applications, but it's really, really cheap to do that. It's very cost efficient to do that because when you run this application, it's sort of a static application that runs in people's browsers. So you put all the processing load onto the end user. So it's a lot cheaper than app service, as an example, because there's not a runtime there, users are getting client side applications, things like React Vue angular, and they're running those applications in their browser. But there's an integration with data API builder where you can essentially deploy it to a static web app with a database connection. I didn't go to the lengths of actually trying that, but if you are running a static web application, which a lot of people are now like react as an example, massively popular. There's actually sort of an integration directly into static web apps as well for you there. So that could be another large piece of infrastructure that you don't need to configure, but also because you can essentially run it in a docker container, you can host it anywhere you want. So it is called like Azure data API builder, but you could run it anywhere you want. And the only last bit that I just want to just call out is that it was integrated with application insights as well. So you can monitor it when you push it up to Azure. I don't know about other monitoring solutions, but it does log to like standard out. So wherever you're sort of hosting it, you can pull those logs and yeah, get some insights from that perspective. Whistle stop tour. But if you build APIs, and I've just talked through all those different concepts, a lot of those things are hard to achieve. And this is like literally minutes to configure it.
Yeah. Okay, so you did answer my questions then, which was about the deployment part when you're starting to say, you know, you have to install it with, you know, was it.net? oh, sorry.net. eight and things like that. I was thinking you have to host this on Iaas or something to even do it. But as you said, you can run in AP, you know, API service and things like app service. Yeah. Okay. That makes sense now. It's great. It sounds really great, especially the authorization. I know I'm the security hat or security head, but like you said, doing row based. Yeah, row based access control and things like that. Especially against a legacy database like you said, or maybe an older one that maybe doesn't support it or it's a database you maybe don't want to mess with. Maybe.
Exactly. Exactly, alan. Yeah. Because what you could do is you could give it a read only connection string or account and just pull data for bi through it if you wanted to.
Yeah. And I was thinking about it as well that we talked about migration of data and things like that. And I guess this is kind of the reason, also reason why you'd have you use APIs generally in front of data because you can change that back end quite easily because as long as your schema still fits and I think your application will then just doesn't even need to know what type of connections behind it. So.
Yeah, and just to go back to authentication perspective, the native JWT provider, I'm not joking, like six lines of JSON in the config file provider Azure ad JWT audience pass the app id in the issuer login dot Microsoft.com Azure adtenant id v two done orfed. So it's really powerful from that perspective.
I mean, you're right, that's another thing. You can in effect have enter id authentication against any database that maybe doesn't support it for things like that.
Yeah, and as long as this is hosted in a place that's got connectivity to that environment because you can effectively host it anywhere. So as long as that Docker container is or wherever it's hosted has got like you know, Vnet or networking to somewhere you don't even have to, as long as that connection has got a path to wherever you're, you know, it could be on prem, you know, via VPN if you want to, you know, like it could go anywhere basically.
Yeah. Wow. Okay. The question we always ask, you know, how much does it cost? Because there's a lot of capability here.
And you know, Alan, I've sold this quite a lot in this episode, haven't I? We've been going for 40 minutes and I've been sell, sell, sell. You should use it. Right. And you know what, I'm such a nice guy, I'm going to give this to you. I say I'm going to give it to you. I didn't even make it. I don't even know why I said that. It's completely free, it's open source, it's built, developed on GitHub. It's under an MIT license, which I believe is a, you know, sort of corporation, organization for profit safe license. So yeah, it's just there in the community to be used essentially, which is okay. And that's why I'm so, you know, like excited by it because it accelerates your development. Right. You can host it anywhere you want. It doesn't have to be in Azure because it could be hosted anywhere. Right. I mean, I'm sure Microsoft would want you to host it in Azure. That's probably why they make it. Right. But, yeah, loads of upvotes on nearly 800 stars, 300 issues on GitHub, which might sound like a bad thing, but that shows that a lot of people are actually using it, I would say personally. So, yeah, so I think actually it's, yeah, it's a really good one to keep us part of the toolkit, that's for sure.
Yeah, definitely. I mean, I thought of a few things we could use it for. I think we thought about briefly last week when we, when you found out about it, but, um.
Yeah, I was like, what? What is this? How did I miss this? It's like, it's, yeah, amazing. Really, really good. I think I should just spend an evening going through the git repositories of Microsoft and Azure, you know, see what I've missed, basically.
Cool. Okay. So is there anything else about it apart from its awesomeness and its freeness?
Free and, yeah, I definitely think if you've got a requirement where you're building these things and you could do with some efficiency gains and it works for you, it might not work for you. Right. You know, I don't want to advocate, it definitely doesn't take away from what developers are doing day to day, but it might fit some of your requirements. So yeah, definitely check it out, see if it's something that you want to start using.
Yeah. And I guess as well, it might be one of those things that you start off with. Like we kind of said, to get a connection to an application whilst you continue to develop it. Maybe. So it's a quick thing whilst you either learn or look to get that API developed because again, you might not know what you want. Don't be wrong. Design, you know, you would have designed it, but sometimes you just don't know with the data, do you? Sometimes it is organic, sort of grown sometimes.
Yeah, exactly.
Cool. Okay, so next episode I'm going to talk about managed identities in Azure. This is going to be around kind of the reasons why to use them, what potentially you might have done previously, and the pros around using them and getting away from using certificates and secrets with app registrations where you can at least. So yeah, I think it'll be an okay episode.
Yeah. No important topic in Azure, that's for sure. That, you know, I think does take a little bit of time to get your head around, but once you do, it's, it's then a no brainer, I would say personally.
Yeah, there's some stuff I found out about where you can use it actually elsewhere rather services. Nice. So yeah, we'll talk about that as well. Okay. Did you enjoy this episode? If so, please do consider leaving us a review on Apple or Spotify. This really helps us reach out to people like yourselves. If you do have any specific feedback or suggestions, we have a link in our show notes to get in contact with us.
Yeah. And if you've made it this far, thanks ever so much for listening and we'll catch you on the next one.
Yeah, thanks all. Bye.