S08E03 - Designing APIs Like a Pro: Lessons from Jerry Nixon on Data API Builder and Beyond

Embedded Player

The Modern .NET Show

S08E03 - Designing APIs Like a Pro: Lessons from Jerry Nixon on Data API Builder and Beyond

Supporting The Show

If this episode was interesting or useful to you, please consider supporting the show with one of the above options.

Episode Summary

Jerry Nixon, a representative from Microsoft, discussed the importance of designing APIs for his audience. He emphasized that creating a standard approach to interacting with databases can save developers time and effort, as well as improve security and performance. To achieve this goal, he introduced Data API Builder, an open-source tool that enables users to create data APIs without extensive engineering expertise.

Data API Builder is designed to be used by individuals with varying levels of experience, from those who have never built an API before to seasoned developers. The tool offers a range of features, including cursor-based pagination, which can help prevent the implementation of “fake” pagination in applications. Jerry also highlighted the importance of standards compliance and demonstrated how Data API Builder achieves this through its adherence to Microsoft’s engineering practices.

Jerry stressed that Data API Builder is not just a product, but an open-source project that accepts contributions from the community. This allows users to extend the tool’s capabilities and ensure its security and performance are maintained at high levels. He also emphasized that the tool can be used in conjunction with other Microsoft products, such as Azure, to provide a comprehensive solution for building data APIs.

One of the key benefits of using Data API Builder is its ability to enable developers to create multiple data APIs quickly and efficiently, without requiring extensive engineering expertise. This can be particularly useful for organizations that need to deploy multiple applications or services with varying data requirements. Jerry also noted that Data API Builder can be used in a variety of deployment environments, including cloud-based platforms like AWS.

In terms of getting started with Data API Builder, Jerry recommended using the official Microsoft Learn documentation and samples repository as a starting point. He also suggested taking advantage of the SQL DevPath credential, which provides a formal learning path for developers who want to master data API building skills. Overall, Jerry’s presentation emphasized the value of Data API Builder as a tool for empowering developers to build high-quality, standards-compliant data APIs with ease.

Episode Transcription

Simple is always the better choice, but easy is not always the best. So sometimes you’ll go to graph, it’s a little bit harder for us to write the code for around it, but the bandwidth consumption is considerably smaller. the compute consumption and the ability for it to run on a mobile device is considerably easier.

- Jerry Nixon

Hey everyone, and welcome back to The Modern .NET Show; the premier .NET podcast, focusing entirely on the knowledge, tools, and frameworks that all .NET developers should have in their toolbox. I’m your host Jamie Taylor, bringing you conversations with the brightest minds in the .NET ecosystem.

Today, we’re joined by Jerry Nixon. Jerry is a Principal Product Manager at Microsoft, focussing on the tooling and Developer Experience around Azure SQL Server. Jerry shares his advice for architecting web-based APIs, RESTful design, and using what fits within your team, and of course we talk about Data API Builder.

When you think about what an architect really is and their responsibility, the decisions, architectural decisions are the decisions that are the most expensive to change. That’s kind of like who should be making this decision? Well, how expensive is it to change? It’s very expensive.

- Jerry Nixon

Jerry also covers a very important point when it comes to designing your software: simple is always best. Because simple software is easier to understand, easier to maintain, and easier to implement. And that’s where Data API Builder comes in; but I’m getting a little ahead of myself.

Before we jump in, a quick reminder: if The Modern .NET Show has become part of your learning journey, please consider supporting us through Patreon or Buy Me A Coffee. Every contribution helps us continue bringing you these in-depth conversations with industry experts. You’ll find all the links in the show notes.

So let’s sit back, open up a terminal, type in dotnet new podcast and we’ll dive into the core of Modern .NET.

Jamie : So, Jerry, welcome to the show. It’s fantastic to have you on the show. We’re going to talk about stuff. That I’ve used in production, which is great, but I get the feeling that even though y’all have built something amazing and you’ve done the fault-the-good fight of getting it out there and getting it in front of people, I get the feeling not very many people know it exists. We’ll come on to that in a moment. First, I wonder, firstly, welcome to the show, and how are you?

Jerry : Thanks, Jamie. Yeah, it’s good. You know, the the all of America seems to be going through a hot flash right now, except for where I live. It’s beautiful weather. But I live up in the mountains in Colorado, and it’s sometimes you know, creates a little bit of jealousy or maybe envy when I uh talk about it, but boy, about seventeen degrees. It just it’s it’s crazy. Everybody else is scrambling for air conditioning. And I had my heat on this morning. It’s a bizarre thing.

Jamie : One part of the country can’t cool down, the other part of the country can’t warm up. That’s right.

Jerry : That’s right. Most of it’s nice and warm. Mine’s just a little dot. That’s right. What about you? How’s it going over there? I was in London a few weeks ago and it was stiflingly hot. How’s it how is it these days?

Jamie : Yeah, yeah. So as we record, it’s recently been SQLBits, hasn’t it? It is a lot cooler now. We’ve had our five minutes of summer that we are allowed to have, here in the UK. And you know it it’s been it’s been almost back to normal weather for the UK at this point. I quote there’s a there’s a UK stand-up comedian called Bill Bailey and I sometimes quote him I say, you know, the British rainy season started around 1600 and has continued unsteadily ever since.

Jerry : Nice. Well, speaking of weather, I am a .NET developer, and I think you are obviously a. NET developer. Have you been a. NET developer your whole life?

Jamie : Well, I wasn’t born a. NET developer, but I, yeah, ever since oh my goodness, first time I used any kind of .NET stuff was college. So UK over here would have been about sixteen to eighteen years old, just playing around with. NET framework and just going, “wow, thi is pretty cool!” Because previously, I’d done lots of stuff in C and a little bit in BASIC and stuff like that. So I’m there going, “wait, I don’t have to write all of the memory management code myself? This is awesome.”

Jerry : You start with C# or you start with VB .NET?

Jamie : Oh, it was with C#. Yeah, I remember building WYSIWYG apps with WinForms. In fact, it was part of… I was sort of trying to help myself with my studies, and so I built a Windows Forms app to… like a quiz yo question me on like to help me to remember the stuff that I needed to learn for the exam.

Jerry : Nicce. Very clever. It’s crazy it’s crazy to think that the next version of Windows Forms was just released. Talk about a technology with shelf life. That’s incredible.

Jamie : Absolutely. Absolutely. Yeah, it’s got the real staying power because it is just so simple and easy to use, right? You fire up a new WinForms project and just drag and drop some tools. From the toolbox into the UI, double click to set up the event handlers, and you’re done.

Jerry : Developers love simplicity, that’s for sure. No doubt about it.

Jamie : Absolutely.

Jerry : But we’re talking about what perhaps could be a slightly more sophisticated architecture.

Jamie : Yes, indeed we are.

What I was thinking we could talk about is a little bit about API design and then a wonderful, I want to say tool, but I feel like that reduces it slightly that you guys have all cooked up over at Redmond for interacting with databases over a REST API. I would love to pick your brains about all of that stuff. And since you’re building these APIs for devs, I would love to understand what goes into a good REST API.

So I guess before we get to the REST API stuff, let’s really quickly talk I’ve danced around a little bit. We’re going to talk about Data API Builder, which I think is fantastic. If you haven’t used it, folks, you need to go use it on something. But yeah, well so so before we get to DAB, how do I build a good API? What’s RESTful? Because everybody has their own opinion. And how do we build good ones?

Jerry : Well, let’s remember that simple still is the best architecture regardless of everything that’s going on. And best practices aren’t really best practices for everybody. They’re just the best practices for whoever has adopted those and proven that they work in their enterprise.

So, you know, I don’t know your business or your business model. I don’t know your enterprise or the politics. And so it’s difficult for me to say what’s the “right” thing for you to use and the “right thing” for you to do. I know some things that are problems and worth avoiding. But I don’t necessarily know that the solution that I come up with is the one ideal for you until you tell me more about your solution. So I want to just start with that because there’s not a universal architecture that everybody should follow. There are many patterns that are just super duper and they tend to work most of the time. But you know, you just can’t go visit somebody and sit down in their conference room, have them draw their solution on the whiteboard and draw too many like judgmental conclusions too quickly because there are just so many details you don’t know. And so let me just start by saying that.

That being said, I think there are a handful of things inside an API that every developer should think about and consider before they really consider their architecture complete. And then as you start to see these different problems, you want to start thinking, “how am I going to solve those in a way that’s consistent with the rest of my application? That’s consistent with the skill set of my developer team? That’s consistent with the rest of the applications in my enterprise, and it gives enough shelf life to my application so that I can be confident that The maintenance developer in the future will be able to pick this up easy enough. I’ll be able to find workers and other engineers in the future that know the technology, or at least they understand it or are willing to learn it.” And more importantly, that it’s not going to be a technology that I’m picking up that is constantly moving off from under me like a you know and like quicksand, so that I have to constantly adjust for changes that are happening, backwards compatibility issues or some sort of breaking change that gets introduced. All of that is sort of the context that you want as you start looking at whatever the endpoint is.

And I’ll point out there really are two types of data APIs. In fact, I would like to say there are two types of APIs. The first API is more like a generic app API. That’s the API that any application needs, and it doesn’t necessarily have anything to do with the data. What it might be doing is some sort of interaction with a backend business system. It might be doing a calculation for you. Who knows what it’s doing? Those are the APIs that are very important to your app. And those are not really the APIs that we’ll be talking about with Data API Builder. Those are the ones that differentiate your app, give you the business value that makes it so your app is important. And can’t really be automated and just abstracted away into some sort of dynamically created endpoint.

However, the other type of API is the data API, and that’s the one that is almost solely talking to the database. There might be a little bit of business logic, but the reality is there’s a very limited amount of business logic in this type of API. And it is the type of thing that can be abstracted and automated. It is the type of thing that is typically repetitive from one project to another, and you’ll find yourself sort of copying the code from one place to another and allowing the sort of that copy paste inheritance that brings bugs from one project to another, because this is the type of code that’s so boring. That you tend not to give it too much attention. You just sort of copy it over, change the POCO objects, change the DTOs, whatever they are. And run it, right? And so maybe you’ll have unit tests, maybe you won’t. And if you do, they’re probably copied as well. And so the goal is, “what if we could take those data APIs and be able to remove that code That’s very prone to error, not because it’s sophisticated, but because it’s not sophisticated. And just developers are kind of bored with it."

A couple other things that your API gives your client for free is the ability not to have a SQL driver. So Not having a database driver means that your client not only is smaller when it ships, but it is easier and not is easier to update and doesn’t have so many requirements around it with its update because there are times when a database driver is updated for security or performance reasons, and you just can’t ignore it. You have to update it. Now, all of a sudden, you have to really think about the fact that drivers and libraries are written not necessarily for you, but for them. And so they could introduce some sort of error because they aren’t backwards compatible, and they do introduce some sort of a breaking change. So it’s not necessarily an error, but maybe they’re fixing a bug in a previous iteration, and you rely on that bug discovered it, you wrote around it, and now for them to fix it actually just broke you. And so it’s not always even backwards compatibility issues. Sometimes the fact that they fix something is the problem by itself as well.

And so, if I’m thinking about my API, there’s a handful of things I want to look at. So, First, I want to decide if I’m going to go GraphQL or REST. So, GraphQL is easily one of the most robust and rich ways to build an API, but it does have some. Prerequisites for your client. There are certain libraries that you’ll likely use to make it so it’s easier to use. And enjoy the power of GraphQL. It’s the one that can write nested queries and multiple queries and have many mutations all in one place, where REST really is typically isolated to the object at that endpoint. So if you have a user table. You have a REST endpoint dedicated to that user table. That’s totally normal. Where GraphQL, there’s one endpoint, period. And you call all the different entities or tables, views, store procedures, that sort of thing. You call all of those directly from that one endpoint, and you just build this nice, robust query.

So picking between those is probably your first step. And you don’t necessarily, it’s not one or the other, but one is going to lead. And so is it, “mostly going to be REST, but we’ll use Graph when it’s necessary. Is it mostly going to be Graph, but we’ll use REST when it’s necessary? Or will we isolate it so that there’s only one because we want to simplify for whatever reason?” That’s a decision that you and your team need to make, and we need to really think about… there is some sophistication around each of these technologies. Certainly, REST is the easiest of all. And so you’re like, “well, I’ll choose REST because it’s the easiest of all.” That’s not a guaranteed win. The reason it’s not is because just because it’s the easiest doesn’t mean it’s the best, right? So it’s funny because developers are often drawn towards easy, and we tend to pick things that are easy. And I don’t want to confuse easy and simple. Simple is always the better choice. But easy is not always the best. That so sometimes you’ll go to graph, it’s a little bit harder for us to write the code for around it. But the bandwidth consumption is considerably smaller. The compute consumption and the ability for it to run on a mobile device is considerably easier. And the ability for us to build something that gives a better user experience might be on the graph side. So in that sense, it makes sense to pick it regardless of whether or not it’s easier or not.

That being said, let’s talk about REST particularly because that is the eighty percent, ninety percent of all the APIs out there. It’s one hundred percent of all the REST APIs out there.

So there’s a lot of argument, first of all, about what the methods mean. Like, you know, we have PUT, we have POST, we have PATCH, we have DELETE. We have GET, of course. And what, you know, how each of those are supposed to be used and what you allow to happen in each of them. And, you know, that this is somewhat established because most developers now have acquiesced to the louder voices in the room. And now we can say, “oh, PUT and PATCH , they aren’t the same. However, maybe we never implement PATCH and maybe we only,” and so on. Maybe we PUT everything inside the body or maybe you PUT it in the URL. And that’s an important part as well. And so when you think about the keys in your table, especially if you have compound keys or specific data types that are somewhat sophisticated, putting that in a URL can be very complex. Maybe it makes more sense to put the keys inside the body. Well, you might immediately say, “well, this is you’re already starting to violate some of the tenets of REST."

And I just want to kind of bring it back to the heart of the matter that there is no way to violate the tenets of rest if what you’re doing is better for your enterprise. Because REST is there to serve you. You’re not there to serve it. And so if you find that certain operations belong in certain methods for certain reasons, that is actually very good. Like if you find that you’re going to pass the operation name in the URL and you’re going to let it so that GET can do all operations and there are no other methods in your API If that works for you and you have a solid case of why that matters and it makes things better for your company, then it makes no sense for anybody to sit you down and pull out the specification and say, “yeah, but but did you know this about REST?”” Right. Because we’re just kind of going back to best practices. Best practices are proven generic things that have nothing to do with your company. But if we can move it instead to, “what are the requirements specific that you’re having to deal with? What are the politics of your company? What are the decisions that were made before you even arrived that you have to deal with? That you didn’t realize you would have to if this was freenfield and you were in control of everything?"

So I just want to say all that to say. There are some standards, and I’ll put those in air quotes. There are some standards with REST that are we’ll just call them they’re the best practices, but they are subject to change. And I don’t know, Jamie, does that make sense to be able to have that sort of wiggle room?

Jamie : Yeah, absolutely. And it it fits with… I have this personal thing, right, where folks tend to use the term engineer to describe what we do, and yet they tend to not engineer things. They tend to just hack away and build stuff. Maybe that’s my own experience with the types of people I’ve been working with over the last fifteen years. Whereas my personal opinion is, “an engineer will look at a problem, look at their list of best practices they have in their toolbox and decide which ones fit the problem that they are trying to build a solution for,”" which I guess is what you’re talking about, right? It’s like a best practice exists.

Jerry : Yeah.

Jamie : But just because it’s best practice doesn’t mean that you have to and I’m very cautious of my words. Dogmatically, or apply with enthusiasm. You don’t have to say, “well, Uncle Bob says we have to do this, so we have to do this.” Well, actually. Uncle Bob doesn’t actually ever say in any of his books, “you have to do it this way. He just says, This is what worked for me, and I’ve done it for 20, 30 years.” you know and and the uh the design patterns people you know the gang of four and you know solid and all of these things right they exist because They work in certain instances for people, right?

And one of my own personal bugbears is conference-driven development, where someone will go to a conference, see a technology, and go, “everything we build has to be used with this technology. And we cannot deviate. We have to do this particular technology, this particular framework, this particular paradigm, this particular practice, and we will never deviate because all the problems can be solved like this.” Whereas actually my personal opinion, coming from a long line of engineers, is you see a problem, you look in your toolbox and you go, “I think this pro this this paradigm, this practice will help us solve this. Let’s try it. It’s not working. Let’s back out and try again.”

Jerry : Conference-driven development. That is hilarious. I’ve also heard resume-driven development. That’s similar in no doubt.

Well, good. Yeah, you and I were totally in agreement here. And here’s the thing. When you think about what an architect really is and their responsibility, the decisions, architectural decisions are the decisions that are the most expensive to change. That’s that’s kind of like, “who should be making this decision? Well, how expensive is it to change? It’s very expensive.” Okay, I would call that an architect. Textural decision that belongs to you know the architect or the team of architects depending on the size of the team. And that I, Because of that, because architectural decisions are so expensive and they are so expensive to change, not so much necessarily expensive to implement, they need to be deferred as far as possible, making it so that, I believe, the role of the architect is really to resist almost every decision possible.

So for example, “I know we need some sort of message queue. We’re going to use the message queue in this way, and we’ll be as abstract as possible.” But the decision of which message queue to use, because if you think about it like from an Azure point of view, there’s about fifty five different messaging technologies you could use in Azure. And making the choice in advance is needless. It’s not that it can’t be done and could be forced to be right over time. And you just kind of knuckle down. But the reality is, most of these decisions need to be made conceptually and then put off as far as possible: “Now that we understand what the requirements are. And actually, we decided to use the queue in this way. Now we know that the queue has to be an event hub. Now we know that the queue has to be a service bus.” That makes so much more sense than having everything set out like you’re ordering a meal.

And it’s all ready, and we just waterfall this thing from here on. And so I certainly understand that Agile is a the modern Agile is the combination of waterfall and Agile together. And it seems pretty good. We’ve all kind of Intuitively, sort of, merge these two things together in a way that seems to be working. I think that that’s part of it. The architect, the number one word out of an architect’s mouth needs to be, “no.” Should we no, no, let’s not do it that way until we’re forced to do it, and so that’s pretty nice.

I don’t, you know, obviously, an architect doesn’t say no to things like security. But they may say no to other things like, “oh, yeah, do we implement the CQRS pattern?” So the CQRS pattern is so simple just and straightforward to implement. You just take your API. And you duplicate it. And you make it so that you focus all of your write operations to one of those endpoints. And you focus all your read operations to the other. And maybe you don’t have any code change at all. Maybe it’s just the topological change in your infrastructure that makes it so that all of your write operations are in a specific container or server, so you’re using all of it compute and memory, and then all of your read are isolated away so that you at least can make it so your read operations don’t slow down your write and the opposite of your write not slowing down your read. So that’s nice.

“So do we do the CQRS pattern? Well, we’re going to need to see because we don’t know the size of our data. We don’t know the user the pattern of usage. We also don’t know all the other components as well.” However, What I’m not going to do, what I’m going to make sure as an architect I’m going to do, is make it so that I am allowed to do CQRS. So I don’t get to a point where I’m like, “oh, because of the decision I just made or because of the technologies or libraries I just chose. We no longer can do CQRS.” Well, CQRS is a little bit like a parachute. I definitely want to have the ability to do it. And so part of it is being able to see the future and understand the interconnectedness of different decisions. So that as I make a choice, now I made it so that, “okay, CQRS is not out of the question. I still have it as something I can pull if we need it, but I’m not going to make that decision while we’re still on the whiteboard, while we’re still designing our application because,” and I just You know, I say this again and again, but simple is the best architecture. And so I want to start as simple as possible and add on sophistication or maybe even complexity to my solution only as needed, only as mandated. You know, and so you’re getting to a point where. “Okay, so do I have a static repository where all my images can be sourced from?” Well, you know, that is really nice if you have a lot of static resources. But it may not be necessary if you just have a handful. And so let’s wait and see how many we have before we make that decision. Well, if we’re going to have that, should we? Go ahead and put all of this inside a CDN so we can deploy this geographically. Like, whoa, whoa, whoa. Let’s just see how slow slow is before we start solving a problem that we don’t even know exists. So, that’s really what I mean. You, an architect. Defends the simplicity of the solution as much as possible. Not saying that nothing comes in, because things do come in, but to solve a problem before we know it’s even there doesn’t make any sense.

Now if I’m designing Visa or MasterCard, obviously there are things I can anticipate already. And so I would make it would be far more than simple, even out you know, out of the gate, because I know there are certain things that have to be done. If I’m doing it for a hospital, then I also know that there are going to be regulations that I have to follow. So those sorts of things might introduce complexity to my solution that I wouldn’t have otherwise chosen, but it’s because I understand that the sort of use case or the the you know the the cordoned off industry that I happen to be working in. Those aren’t wrong, we’re just talking about technology for technology’s sake. You know, it just makes sense for all of your uh APIs to be microservices. Does it? Let’s just have I mean, that that’s that’s not The simplest architecture. Microservices aren’t simple because now you have all this integration work that you need to reconsider about how you do it. And so Great, there’s my soapbox on keeping things nice and simple, under the role of the architect as well.

And so, all right. So, first things first, here are the pain points of a data API down the road. That’s really where it really matters. I always joke that the maintenance developer that follows you is the most important person for every project. They’re the ones who receive it. And keep it going. They’re the ones who don’t tell your boss that you’re a jerk. They’re the ones who don’t recommend that everything just get rewritten. That’s because you give them a well architected and designed application that’s both intuitive and does all the things it needs to, without just needless things, so that you have another two or three bullets in your resume when you’re done with the project. That’s important.

I teach computer science. I’m a professor here at a university here in Denver, Colorado Christian University. And I tell my students, I’m like, “listen, the maintenance developer that follows you is an axe murderer. You just need to think of them like that. And if you make them mad, they are going to come find you. Your goal is to give them something that makes their life just as good as your life is right now. So if you have the luxury of a green, or near greenfield, when you’re designing this application, you want to provide to them not a blackfield. But the same sort of thing where the decisions you made are intuitive and obvious. And there are many things that you held back to make sure the complexity was dialed back.” So that I think that’s really good.

One of the things that an endpoint suffers from is versioning. And this may be the number one thing that all endpoints suffer from, is how to handle So here’s the idea: you have a client, it calls to your REST endpoint. It expects a certain payload to come in. It does the operation, and then it expects a certain payload To be returned, we take the input and output payloads, we document those inside our OpenAPI so that you can build clients against them, and everything is beautiful. Because your company has merged with another, or acquired another, or because there was a mistake made, or because we’re expanding the scope of this application to do something else. or because the industry changed and we’re trying to pivot, right? Very valid reasons. It doesn’t make sense for us as the engineers to push back against the business. You know, what? You’re going to make another change? I mean, I can’t tell you how many teams I’ve been on where that’s sort of the attitude, where the business is the problem, instead of seeing us as the thing that’s meant to enable the business. Instead, we see the business almost as they serve us. It’s really a frustrating place.

But that being said, let’s just say that there’s a generic or not generic, but a totally valid reason for your API to change, but it’s a subtle change. It’s, you know, we now require this other field to be passed in, whatever it is. Well, your clients are not going to be passing that in until you update all the code in your client. But you’ve designed it in such a way so that as that field that comes in, if you pass it in, we know that we operate in a certain way, but if you don’t pass it in, it’s the old way. And you’re going to phase out the old way, but you’ve designed it in such a way so that there’s a seam in your application that you can make a change to your app without changing the entire wholesale application. Well, this versioning, so this would be like a 1.1 version, where it’s not backwards compatible and it’s important, and you have to be able to solve it one way. But the only solution is to update all of your clients and to update your API at the same time, unless you’ve thought this through in advance, or you’ve introduced something along the lines of an app gateway or an API manager in front of your API.

And so this is funny because it’s a little bit less around, “what’s the best way to design your API,” but it’s more around what’s overall, what’s your solution, what should you include with your API. And an API manager is paramount. Like if you have an any sort of system without an API manager of some kind, two things. One, I’d like for you to tell me why, because that Ridiculous. Two, you’re not planning for the future because an API front end like that not only does things like load balancing or helps with exotic sort of security and authentication, but it also allows you to rewrite the payloads. That’s the important part. So, I can have my API manager, which would be the front end of my endpoint. It would make, if I had several endpoints or microservices. API manager would make it so that it looks like there’s just one. It’s a nice clean surface area for my application to be able to hit against. But it also means that if I start handing it old payloads for a specific endpoint, I can identify those old payloads, recognize what it’s trying to do, and I can route it to the previous version’s endpoint. And if it’s not, then I can route it to the other. Or If I’m able to infer certain values or just at least make them present even if the value is null, I can manipulate the payload, passing it to the new endpoint so that it gets those values.

So again. I’m just saying one of the hardest things to deal with is versioning inside an endpoint. One of the easiest ways to handle it is to introduce an app gateway or API manager. So I’m not talking about Azure API Manager, which can do all of these things, of course. But I’m just saying an API man(anger). It’s a concept inside your architecture that allows you to maybe get just a library an open source library that does this app gateway processing for you. It’s very good. It is very good. Denial of service attacks and all of these other things can be mitigated because you’ve introduced this one piece.

So that what you should have in your mind is, “I’m going to build an API.” Well, when you watch demos that, you know, for like Visual Studio Code or ASP .NET Core. They usually just say, “file new project.” They jump into Program.cs, they add a minimal API, and they’re like, “look how fast and easy this is.” Well, that’s great. But in your mind, because we’re thinking like architects, we’re saying, “okay, if I’m going to add an API, there are two components to make up a full API. One of them is the app gateway or the API manager on the front end. So I’m not going to have one without the other.” In the same way, you wouldn’t write a method without a unit test, right? So can you have an API without APIM? Yes. Can you have a method without a unit test? Yes. Which one are if you write a method but no unit test, are you proud of that? Are you going to show that off to the team? No, absolutely not. Because we all know this is really a good way to go. There are some reasons. Sometimes not to have unit tests, fine. But on the 99% kind of range, it is always a good idea. In the same way, that some sort of front end gateway is important for your API.

So that handles versioning. The next is around security. So a lot of developers find so we’ll pass a JWT along, right? So that’ll be the way we do it. So we’ll either use EntraID or something like that that will provide back to us a JWT claim with all of our claims that will be signed cryptographically. And then we’ll have that and we’ll pass it along as a really easy way for us to be able to handle not just authentication but authorization as well. Now, here’s the mistake that’s often made as well: that a custom JWT server is not introduced into a solution. Now remember, I’ve just given you this entire speech on keeping things out of it, but I just want to talk about why some of these things are actually very, very valuable. So why would you have a custom JWT server inside your application? And how does it make it so that your endpoint is better because of it?

Well The reason it is, is if you don’t have a custom JWT server and you want to have very detailed claims, which I would recommend always have as detailed claims as you can get. Well, those detailed claims all need to show up back in your directory or in your tenant. So Active Directory tenant or your EntraID tenant. And you have to go in and you have to create these claims, and then you have to go in and you have to assign all of these claims. Well, that’s not what you want to do because the isolated authorization portion of your application doesn’t belong up in your tenant, it actually belongs inside your app. And it’s part of your solution. So what should be happening is, as you come in to authenticate and you do your OAuth authentication into EntraID, for example, it will give you a list of claims wrapped in a JWT that’s signed. That you then break open and you hand it to your own JWT server inside your application. It validates the token and all the things to maintain that keep it secure. But it also looks up you, Jamie, and it says, “okay, Jamie, out of the five things that you that are possible to do, these are the four. But that fifth one, you are allowed to do it, but only in this way.” Well, that sort of level of detail is awesome. It is exactly what you want to enable inside your application. And it’s the last thing you want to put up into your directory or up into your tenant because you can only do so many changes into your tenant until eventually your tenant administrators like, “slow down. You’re clogging and putting all kinds of crap in the tenant that doesn’t belong there, it actually needs to be pushed down to your application."

Now, you know, you could use Azure’s Solution around JWTs, or you just introduce your own JAT server. There are many and they’re open source and they’re very well accepted and they’re in almost every language. That’s an important recognition of the next thing is handling not the authentication, right? That’s going to be handled by EntraID or whatever it is you got, you know, Google or whatever. But really the next step of authorization to be able to extend the claims to be very precise, but to be specific to your application, and to be managed And then re-signed cryptographically again by the JWT server that you’re shipping as part of your overall solution.

So I would say those two things are the first things that should be talked about when you, as your team, sit down? You have your whiteboard, you’ve got the lid off of your pens, you’re about to write, and you’re like, “what should we do with our endpoint?” Well, First of all, let’s just talk about versioning, how we’re going to solve it, because it’s a concept, not a specific problem. And then, as far as authorization, how are we going to solve detailed authorization so every endpoint has the opportunity to be extremely precise. And the way you can do that is with a custom JWT server that’s shipped basically with your solution and is part of your overall and like structure.

So those two things, I mean, it’s funny because we haven’t even gotten down to all the like whether or not it’s cached and all these other pieces, which also are you know, the list is long. But I think these are the things, and the reason I’m bringing them up with you here is these are the things that we as developers sometimes skip, and they cause a lot of heartache as you kind of go down the line.

Jamie : There’s so much that you said there I’d love to sort of unpack and sort of cover again. But some of the really key highlights: one of the things that you said was about, you’ve said this quite a lot, and I’m sure we’ll continue saying it throughout the rest of the episode: Simple is the best architecture, right? There’s something in, I want to say it’s the pragmatic programmer. I feel like it’s the the updated edition that came out a few years ago rather than the original. because of what I’m about to say, which is they say quite plainly, “if you want to build for Netflix scale, go out and get Netflix scale first then build it,” right? So start simple and build up from there. And the sarcastic part of me is thinking, “hey, but like if we live in an agile world, and we’re brilliant developers anyway. We don’t need versioning on APIs because we get it right the first time,” right?

Jerry : Right. I mean, at least I do.

Jamie : Absolutely. But I really like your point about having some kind of management system, not just for all of the other things that you’d said, but but specifically for versioning because you know, we hardly ever… I mean, I’m only speaking from my own experience here, but like we hardly ever get the chance to go back and work on things to upgrade the APIs that we build or whatever, right? You we need we need a real business case for, “why do we need to build a version two?” So putting it behind an APIM, an API manager, to start with, means that that process of migrating from version one to a version two is super trivial, right? Because you’re already behind the API management app or whatever you’re using. You’re already behind that stack. So you just have to go, “oh, API management, here’s version two. And guess what? I’m also using OpenAPI, so you know, that helps you manage that too,”" right?

Jerry : That’s exactly right. You know, it’s funny, Netflix scale. The thing is, these large companies, like, “this is the way Twitter does it, this is the way Microsoft does it, this is the way Amazon does it.” Like, that is so irrelevant to us because these companies, they can buy their way out of mistakes in a way that we don’t have the luxury to do. So when we see that the architect of whatever giant company, of WhatsApp, they have decided to use this database. Well, maybe I should decide to use this database. That approach is so problematic because you tend to think that you are them. And even worse, you tend to think because of their scale, tthey know more than you. But the truth of the matter is, they’re just in a position where their mistakes just don’t matter to them, where it might get you fired or might close the doors on your business if you make a a bad decision to them, it just adds another comma to the price of the budget and then they fix it and they move on from there. So you never see the cover of the magazine saying, “oh, this is how we fixed our gigantic mistake that we were bragging about three years ago.” With the exception of you know, you see a little bit of that about remember HTML5 with Facebook, and Netflix had all of their microservices written in multiple languages and then they unified those to just be Go so they could I mean, that was a nightmare; I can’t even imagine somebody made that decision in the first place.

And then, other things, you know, so we have seen some of that. The reality is that it’s a little bit like mistakes in the news. You know, once they make the mistake. they issue a a retraction or a correction, but that’s not the headline. That’s the back page of the newspaper for sure. And meanwhile, we don’t even see that, and we’re you know, negotiating with our manager to get a different budget and timeline so we can do that one thing that we didn’t realize didn’t even work. So it’s hesitate when your role model is big tech because big tech, they do not operate in a cautious way the way you would.

Jamie : Absolutely. And I guess it’s similar to, say, sports stars or top of their game musicians, right? If you are, say, picking up the guitar and you want to learn how to play your favorite song ever, don’t go out and spend five thousand dollars on you know, a custom made guitar with custom made pedals and massive amplifiers, because you haven’t even picked the guitar up yet, right? Go buy something that’s a little cheap, that if you break it, it’s not going to be a huge problem that maybe you can get started with. And similarly in sports, right? So I’m going to switch to NFL because I know who I’m talking to. You’re more likely to understand an NFL meta metaphor than a soccer metaphor, for instance, right?

You want to start playing NFL? Don’t go, don’t go out and buy the the most sturdy helmet, and under armor gear, and the chest plates that they do. Please do wear safety equipment, of course, but don’t spend thousands upon thousands upon thousands of dollars of doing it before you’ve even tried to do it yet. It fits with every part of our life, right? You want to learn to drive? Don’t go buy a Ferrari, right?

Jerry : Yeah, yeah, well said.

Jamie : Get yourself something way cheaper.

Jerry : Yeah, that’s exactly right.

All right, so let me lean back into a couple of things around APIs. So those are some of the problems. Other problems have to do with scale. And so let me just make one quick little note that do your best to make it so that your application is stateless. When it’s stateless, you just give yourself so many options. When it comes to how you’re going to scale. So, obviously, a stateless server can scale horizontally, that’s the entire point of making things stateless. And by stateless, I mean the second call doesn’t rely on the first. It doesn’t, there’s never anything that follows up. So for example, you might have, you might be tempted to have, an endpoint that starts a transaction, another endpoint that executes inside that transaction, and a final one to complete that transaction. Well, that would not be a stateless server. And the reason then, the problem with that, of course, is if you have two servers that are serving up your API, you got to make sure that you have affinity to the one that you called the first time so that you can use that same transaction. That’s a kind of a weird example, but it’s just one simple example to make sure that everything is as atomic as possible, and everything basically comes and goes and releases from that server with every call. So doing it that way also also gives you vertical scale as well, because you can always, it doesn’t matter what your technology is, you can always scale vertically, just add more RAM to it. So that’s really nice to be able to do that. Just focusing on that so you don’t accidentally introduce some sort of a process that runs for a long time and makes it so that the state of the specific server is super important to the way that you call your API.

Three things that you can do to your API that can really add value that are sometimes pretty low cost. The first would be a retry policy. So many of us have used Poly to do this. Now it’s built into the SQL driver, so you can just enable it. But it’s not enabled by default. And the reason it’s not enabled by default is we don’t really know, and just think about the driver, we don’t really know that a retry makes sense. Like if we don’t know your situation. But certainly we make it as easy as possible to enable it. Or Poly is nice and rich, so you can have a exponential back off. And so all these neat things that you can do, so that you try again and again. But the reality here is we live in a real world, and sometimes there’s just this hiccup. It just happens. It doesn’t have to do with the stability of your system. It doesn’t have to do with the quality of your bandwidth. It’s just something weird, you know. It was a sun flare. You know, something happened. And now, if you just try one more time, then it skips the requirement for you to present to your user an error message, right? You’re like, “oh, failed, sorry. Nope, actually, it would be much nicer if you failed.” Let me try that one more time. Oh, work the second time. Don’t try and explain it. Don’t try and debug the crap out of it. Just understand that a retry policy that tries one more time and it happens within a handful of milliseconds, so it’s transparent to you and the user. You’ve solved the problem. You’ve solved the problem without adding another comma to the budget of your project so that you get totally new fiber or whatever.

Now, there are times when it really matters to be able to chase down these tiny little bugs. I’m not saying not to do that. I’m just saying we’re kind of respect the scope of things. If it turns out that there’s a tiny amount of delay. That tiny amount of delay, you could actually build into the way that you’re writing your application. You could embrace the problem, right, rather than try and fix it. That’s very reasonable, especially with something like a retry policy. And not having a retry policy is very unusual. Deciding not to have a retry policy is unusual. So just enabling it, it’s a really straightforward thing to do, adding something like Poly or using the SQL driver so that you can enable it and just make it so that you try at least once. Sometimes you can try up to five times. The user is still totally transparent and ignorant of the situation. And so you’re still giving them an excellent user experience. While, on the back end, you’ve tried this five times and it took five times for it to work. The user doesn’t care about that. They just want it to work. So that’s nice. One thing that introduces is this idea of eventual consistency as well. Let’s talk about one other thing you could do to an API to make it so that it gives a better experience to the client consumer.

Another would be a cache. So the value of having a cache, level one or level two cache allows it so that your client has a much faster experience when querying the database. If you want to improve the speed of your database, the one thing you need to do to it is query it less. That’s just that’s the trick, like the golden calf here, the holy grail. It is like do not query your database and your database will be faster. However, we know we have to query it from time to time. But caching is an incredible strategy that allows you to query it less. So let’s say you and I were both asking for the same query, and that query took five seconds to run. Now, just start with a simple way that cache works. You run… Let me make it me so that Jamie, you have a great experience. So I run it, it’s a five-second query. So it lasts five seconds and go. Now, the data that I’m querying on the back end, let’s pretend, changes every I don’t know, fifteen minutes. We have a refresh of the data or something like that. So we’ve made it so that the query so that the cache against the query expires automatically, it’s TTL is the time to live. That’s usually the language used for caching. So it expires every 15 minutes. No big deal. Beautiful. Now you come in, it’s a five-second query, you ask for it, and it’s three milliseconds, right? Because That data was basically pulled out of the fastest RAM in the world and just handed to you. No operation against the database.

Well, what’s nice is if you could do a heat map of a normal solution, you’re like, “show me where my costs are.” Well, the red hot Cost of every solution is the database. There’s no getting around it. If it starts to slow down, you ramp it up. If it slows you know, all of these things. Well, you can really make it so that a Small or medium-sized database performs like a medium or large size database, making it so that the cost center of your solution isn’t necessarily the database. Because you’ve used strategies like caching so that you hit your database less and less and less. That’s really nice. Another problem this solves is Stampede. So Stampede would be where you and I ask for the same thing at the exact same time. And that would be two queries that the database receives. And there’s nothing in cache, so you can’t do it. You can’t serve one or the other.

Well, when you implement cache, so inside Data API Builder, we implement it through Fusion Cache. So it’s a little bit like Poly actually. It’s just a really well-known, very loved open source project. And so Fusion Cache handles stampeding as well. So what happens is: I initiate the query, but you wait for me. And so it recognizes identical queries without having to call the database. So even though it still may take five seconds for you to get the data back from the database, or maybe I should say for the data back from the endpoint, you are not actually impacting the performance of the database or even calling it. Let’s say a thousand people asked for the same data all at the same time, right? So think about like Taylor Swift tickets. We all, you know, 10,000 teenage girls all hit F5 at the exact same time over and over and over and over again. But the data hasn’t changed underlined. So that’s 10,000 queries against the database. But in this sense, because that’s a super stampede, it would be one query against the database that performs nice and fast and everybody else… and because 10,000 one second queries, that takes five seconds to run because now you’re competing against each other and consuming so much of your compute power. That you’ve actually made a pretty quick database call, pretty long, because you’re having to share all of this cooperative multitasking across the database.

So that’s really nice. So you can add a retry policy, you can have caching, both just so you can hit your database less, but also so the user has a much faster experience. So, even though it might cost me a second to run it, every time you come, it’s milliseconds. So, the overall experience of your user base is really positive, even though we know we have to have that first kind of patsy that comes in and actually executes the query. A database still provides data. It’s still the right thing to query it. We just want to adopt strategies to query it less. And so that’s the goal of this whole retry policy of caching.

And the final thing that you might introduce into an API to really give it That probably is the best user experience possible for a client is an internal queue of its own. So being able to have a queue means that you can ask the API to do something, and it could recognize based on some sort of thresholds and that it monitors itself that it just can’t do it, right? It turns out the database has returned an error that says it’s busy. That’s not uncommon, right? A database can be busy if you don’t have good strategies around it. And so then you could have your API say, “if the database is busy, then I will take the request and all the data around it. And I will put it into a persistent queue.” So you wouldn’t put this in memory, you’d put it into wherever you would persist something, so probably the file system. And then you would tell the client, “done. I’ve accepted, acknowledged, and I will do it."

Now, this is important because if you’re going to implement a strategy like this, it means the client needs to understand that there’s eventual consistency across the board. Saying that they can rely on that queue being executed eventually, but that they can’t immediately go to and run a report and see the result of what they just did. So you have this retry policy because it’s the real world. You have this caching solution because you want to make it so, for one, the user experience is positive, but also so that your database doesn’t just get beat up when it doesn’t need to be. But then you have this queue. And this the idea around this queue is to say that, “listen, sometimes things really are a problem, but I’m not going to make it a problem for you. Let me handle it from this point forward.” So, right now, so it’s a little bit like I’ve typed all this stuff in, I’ve sent an email, and Outlook can’t even connect to the email server. Well, Outlook doesn’t make you wait. Outlook says, “I accept this email. I’ll put it in the outbox,” right? So that’s its local queue. And it’s waiting then until it finally connects to the Internet, finally can reach the mail server and finally sends your message. That’s the same sort of concept that you want to be able to enable in your API if you know that there’s a possibility of some sort of component in your application not being as reliable as others.

So I would say those three are really big. Like just genuinely, CQRS is a very solid pattern to add to an API. You can also enable CQRS on the database side so that you have a read-only replica that you may be pointing your read-only API to so that you can separate everything and you can really make it so that things are disconnected, but still, you know, I mean, regardless of the amount of money that you pay Amazon or Azure, you are not going to break the speed of light. So the idea like they can’t do it. Like electrons flow at a certain speed. So if you need to, for example, create a read-only replica of SQL. Well, that gap between your primary database and your replica, it could just be on the same rack. It could be sitting right next to each other, in the same hard drive, even. That’s fine. That would make it so that the data replicates to the other as fast as humanly possible. But maybe you make it so that they’re on different devices in the same rack. Maybe you make it on so they’re separate racks. Maybe you say, “make it so they’re in separate zones or separate regions or separate geographies.” All of those are possibilities. And some of them, and they’re all realistic, right? They’re all the right answer if that’s the requirement. But now we have to just understand that eventual consistency means that the read operation that I’m about to do against my replica might actually need to have a second delay or something along those lines before we can really see the operation that we wrote to it.

So I would say those are the big things, you know, easy to solve with APIM so that you can handle versioning, so you can handle exotic authentication. And also some sort of a JWT server so that you can do detailed authorization inside your application or inside your API and your application. This is valuable.

And then if we were just to change direction now and talk about Data API Builder, all of this is part of Data API Builder, with the exception of a native queue, where we don’t handle native queuing because that would be very difficult to do on a generic sense. Everything else is there, you know, being able to split CQRS, being able to handle different databases depending on the API, being able to put an APIM in front of it, being able to parse and deal with custom JWTs. Being able to cache it, a retry policy, it’s all built in. Which means that if you’re thinking, “I want to build this API and I’m going to go in and say file new project and start editing <myproject.cs>,” that’s fine.

But more often than not, what you’re going to be doing is so repetitive and so like standard that you might as well use a tool like Data API Builder, which is open source and free. So it’s not like we’re talking about, “how much is the license?” We’re just talking about reducing the amount of code in your code base and having an API that you would have built this way anyway, using the same libraries. We use FusionCache, you would use FusionCache. We use Poly, you would use Poly. And on and on it goes, right? We use HotChocolate for our Our GraphQL implementation. We use ASP .NET Core for our REST implementation, and on and on we go, right? It’s like we’re not doing something exotic that doesn’t at all look like the kind of code you would write. We’re doing exactly the way you would write the code. We’re just writing it for you. And we’ve been writing it now for almost four years. It’s gone through so many security checks. It’s gone through so much work to make sure that it’s solid and performant and scales well. Why wouldn’t this be at least the beginning of your investigation phase in your application?

“Can we use Data API Builder?” should be one of the questions you ask. Because if you can, you’ve saved yourself a lot of headache and given yourself probably the same sort of behaviour you would have gotten otherwise, and the same sort of, and maybe even more. Maybe your budget, as far as time and hours of your team’s velocity, can’t afford to implement a cache, right? It’s just realistic. We just can’t do it. Well, you can with data API builder. That’s the reason something like that is nice, is because you get all of the features prebuilt and it’s just a matter of enabling them.

Anyway, there you go. A lot of recommendations, Jamie. Can they all be solved by Data API Builder? Most of them can. But again, I don’t know your business. I don’t know what all your requirements are. Can Data API Builder be the right thing for your application? It could be. Yeah, it could be. Is it likely to be? Probably. Is it for sure? No. So I just want to be able to say that, that these are all great ideas, but they may not fit in your architecture.

Jamie : So what does Data API Builder look like then? So we’ve said, you’ve gone through a wonderful amount of advice for things that should be in place for API architecture. For the majority of things if it fits your business process. But like because we haven’t really talked a great deal about Data API Builder, I’m wondering ‘cause I’ve used it. I’m not just saying that because I want to sound clever, and amazing, and hip, and with it and stuff. Wow, I felt really old saying that. But also, for the folks who are listening along, who are like, what, you’ve been working on something for four years and I haven’t heard about it? What the heck is this?

Jerry : Yeah, it’s a Data API Builder. We build it, compile it. And then we package it into a container inside the MCR, the Microsoft container registry, so that you can go to Docker and say, “new Docker from image,” and you can say Microsoft.DataAPIBuilder."

And what’s nice is in a normal distributed application scenario… so let me start by saying this. If you’re listening to this and you’re like, “it’ll be a cold day in hell when I use a container.” Data API Builder will not be the solution for you because we deploy as a container and it we want it to be so that it’s completely isolated. And this isn’t code generation. Instead, this is instead an engine. that you provide a JSON file to that describes the connection string to your database as well as what objects inside your database you want to expose through your API. That’s it. And then we do the rest behind the scenes dynamically to verify everything that you said to us, to check to make sure that the metadata comes from the database matches what you provided. And then we dynamically generate the REST endpoints or the GraphQL endpoints for you. We enable caching and retry policies based on what you’ve provided as well. All of that happens. We have most things defaulted, so you have to do very little.

That’s the upside, right? So you have basically this container image that we keep shipping. And so then you have the option to lock it. You could say, “oh, okay, well. We’re currently at 1. 6. So 1. 6 is what I… that’s what I want to use forever.” Well, you can do that. We’ll always have 1.6. I don’t know how long forever means, but we’ll have it for many, many, many years. But maybe you said it, so it’s just the latest. And so you always get the latest. And so now you’re relying on us and our careful policies to make sure that we’re backwards compatible and that we don’t have introduce breaking changes without significant workarounds to make sure that we have feature flags and things like that to make it work.

So I say that to say you can safely then introduce this into your architecture as just a container over on the side. And You can then build the VNet around your database in that architecture. You can then tell your DBA, “the only thing that will ever talk to your database is from this one container.” You can also tell your DBA, “the queries That are going to be written are only going to be written by the query builder inside Data API Builder, which, by the way, was written by the SQL team.” We know how to write SQL. And so you can, that’s nice, right? Because it’s different than having Entity Framework do it. Every time you get a new version of Entity Framework, you get a new possibility of how the query is going to be written and executed. And you also get a lot of influence over how the query is going to be written inside Entity Framework itself. So the developer can come in and basically, you’re saying to the DBA, “we’re going to run some Dynamic SQL. That’s all there is to it, and you need to allow it."

Now that’s not that big of a deal in most companies because that’s the way applications are written. But sometimes the database just can’t have changes made to it. You can’t introduce security changes. You can’t introduce some of these things and certainly you can’t run ad-hoc queries. But now you can because I can have Data API Builder as a deterministic query builder so that I can go in, I can ask for things at my REST endpoint, I can ask for things at my GraphQL endpoint, and then it’s the same query builder on the other end. that spits out the T-SQL for you, making it so that your developer team doesn’t even need to know about T-SQL. They don’t even need to know like it’s really nice because you can say, “h, this is a pretty data intensive app. So we’re going to need mostly mid and senior engineers.” Well, maybe, unless you use something like Data API Builder. Now, all of a sudden, even a junior developer can call a HTTP endpoint and pass JSON and receive JSON back. So now this sort of changes the entire game and it makes it really appealing to do. All because you’ve just introduced this container with the API builder image in it and provided a configuration file that gives it a connection over to it. So inside that connection, by the way, you could use username and password. I mean, we’ve all done it. Nothing wrong with that. It’s still fully supported. You can use system identities. You can use assigned identity. It’s up to you. So we want to sort of meet the developer and meet the architect, sort of where they are, understanding that every developer and every enterprise has to make certain decisions that can’t be anticipated sometimes. So the more flexible we are, the better.

It also means That you can introduce Data API Builder for our. NET project, but you can also do it for your Node project or your Python project or whatever. And because HTTP and JSON make it so that everything is completely invisible. So it’s just a container that sits in your distributed application. Maybe nothing else is containerized. You just have this one container sits next to your database. And you find that the people responsible for your database are delighted. And that’s the typical type of response because the reason they’re delighted is because now they have a reliable, it’s deterministic, it’s structured in a way that they don’t have to have everything be super dynamic. They can handle the security. They can put off a lot of the changes at the API layer. Rather than having to make changes to their database, it is beautiful all the way around, as well as giving you the ability to do things like migration.

So we earlier we were talking about migrating from one version to another, right? So that’s the reason we introduce APIM. But you can also just migrate from one database to another. Right now, you’re using SQL Server on-prem, you’re thinking about using SQL up in Azure. Well, that’s not going to be a big deal. So all you have to do is just switch your connection string. The API surface area stays exactly the same. And so it’s a beautiful way. Maybe you’re using Postgres today and you’re going to move over to SQL someday soon. Fine. Use Data API Builder in front of Postgres. And when you finally are ready to move over, then all you have to do is change the connection string in Data API Builder, the service area remains the same. So there’s a lot of beauty there. When it comes to we’ve worked really hard to keep things as the back end data sources in parity, so that when you do this sort of migration, the switch is completely transparent. It’s uh, I tell you, we’re ninety-nine percent there.

If there is a superset, it’s SQL Server. You can do quite a bit with SQL Server, including taking the individual claims that come through your JWT token and pass them down into the session context of SQL so that you can write row level security sort of logic inside the engine itself if you want to. So We really want to make it so that the full potpourri of applications out there, we can hit the ninety percentile of we can really make a developer’s life better. And make it so that, frankly, you can build and deploy a data application as fast as possible in the data API part of it is inconsequential. That’s the real goal for it. It’s just it’s absolutely inconsequential. And you can be responsible for that container. If you want that container to be really super big, if you want to horizontally control and or scale it so that they’re all managed and orchestrated across several, that’s up to you. You can totally do that. We want to make it so that for the little app, this is an awesome solution. For the giant app, this is an awesome solution. I think we’ve pretty much gotten there. We’re at one dot seven, but it took us a year and a half to go through all the previous versions as well. We’ve really added a lot of capability. We’ve really added a lot of like flexibility for various architectures as well.

You know that moment when a technical concept finally clicks? That's what we're all about here at The Modern .NET Show.

We can stay independent thanks to listeners like you. If you've learned something valuable from the show, please consider joining our Patreon or BuyMeACoffee. You'll find links in the show notes.

We're a listener supported and (at times) ad supported production. So every bit of support that you can give makes a difference.

Thank you.

Jamie : You know, I love everything that you’ve said there. And before we talk about, real quick how someone could learn about it and implement it in one of their applications, I just kind of want to point out that if Data API Builder is wrapped in a container, that means that technically, and I know I’m speaking to a blue badge, but that technically means that I can go, “hey, I want to host that on AWS,” or “hey, I want to host it on-prem on our Kubernetes cluster.” Or if you’re going with the “let’s not do things simply” or maybe, you know, for my own local dev life I could throw it in a dev container or a. NET Aspire or a Dapper—d-a-p-r— or any other kind of local runtime thing, and it will just spin up the container, right?

Jerry : Yes, that’s one hundred percent right. If you want to run this in AWS or GCP, go ahead. There’s nothing in the license that says you can’t do it, and we support it. I mean, companies are forced to make decisions based on things that are sometimes out of the control of their architects. And so if your company just got incredibly good pricing because they negotiated with AWS, you don’t want to look at Data API Builder and say, “ah, this is the sacrifice.” No, no, not at all. So yes, you’re absolutely right.

By the way, we also support multiple data sources simultaneously. So Earlier, I said you can give it a config file, but you can give it two config files, one that points to an on-prem database and one that points to an Azure database, for example, or one that points to an AWS database and one that points to And then you can have endpoints that point to each one of those independently. It’s really neat. Yeah, it’s really neat stuff.

Jamie : That’s really cool. Yeah, I like that. And yeah, being able to… perhaps, if you want to roll it into your local dev experience, it would be pretty cool too. Hosting agnostic is, I feel, would make, some people more likely to try it out. Because obviously, like you said, you don’t want to be tied down to a single provider or something that is out of your control. So you said you mentioned a few times that, “all you need to do is give it a config file,”" right?

Jerry : That’s right.

Jamie : What, is it just literally like, “use the tooling to make a JSON, point out my database, and whoops, there’s my API?” Like, is it literally that simple?

Jerry : Yes. So, I mean, it’s kind of crazy because it does, it sounds too simple, no doubt about it. So we have a cross-platform CLI that you can use. You can install it as a. NET tool. And you can say dab init and give it the config file and give it your connection string, and it’ll build the initial file for you. Everything except for the list of tables, user store procedures that you want to expose. Then you can say dab add, and you can say what tables and views you want to add. The end, you can literally just take that file then, as long as the connection string is valid. You can just take that file provided to the container. And you’re done. The container will read that, will validate it against the data source, and then it’ll create your endpoint for you.

It sounds stupid, right? But you’re like, “okay, but what else do you have to do?” I mean, I’ve only done this demo five trillion times in the last three years. And it’s mind-numbing to people who see it because we have Visual Studio extensions, a Visual Studio Code extension. Sorry, not in Visual Studio yet. Visual Studio Code extensions, where you can right click a folder, you can say dab init, provide to it the connection string.

We support .env files, so you can have environment variables that are at the scope of the process. So that you don’t overwrite an environment variable that you already have, or another process doesn’t read your environment variables, which is nice. But that’s the way you would also set the connection string, probably, in a container. So you would have that file, you would feed it to your to your container, but in the file you would use @env so that it reads from an environment variable. So then you could have this like every environment uses the same configuration file. But the container, depending on the environment that it’s in, has a different connection string. So that’s nice for dev, and UAT, and production as you move it around as well.

But I mean, I can you know, if we were just sitting here, I could right-click, initialize, add my connection string, right-click, add a table. Right click and start. And now I’ve got an endpoint running. Like all of that could happen inside a 45-second window, if faster if I can click fast enough. And DAB itself spins up insanely fast. So we have customers that have four or five tables, and we have other customers that have six hundred tables. DAB can take milliseconds to start or a couple of seconds to start, depending on how many endpoints it needs to spin up. And depending on the complexity, I suppose, of your configuration file. But in either case, the container starts exceptionally fast. So fast, in fact, that if you had cold start containers, those containers would feel not really like they were instant, but nearly instant, which just gives you another opportunity to be able to impact the budget of your project, right? And that’s another really key aspect of of a really good architect: They’ve come up with very compelling solutions and strategies to be able to deal with things. They’ve been able to meet the expectations and requirements of the business. And they’ve been able to dramatically drive down the budget of their project because of strategies they’ve introduced as well.

So all of those are really valuable things to say. And Data API Builder not only saves you budget and time as far as development, but can also save considerable cost as far as the hosting and orchestration of your containers because it’s such a lightweight solution. I mean, our URL is aka dot ms, so also known as right, aka do ms slash dab, dab Data API Builder. So if you go there, it takes you to the repository in GitHub. It’s open. You can look at it yourself. And you can see what we’re talking about. Like we use the same sort of patterns you would use, and it’s not a bunch of like bloated overweight nonsense. And so I think there you would find that to be very appealing and nice.

And one of the reasons why this container is able to start up so quickly, and you see the engine itself not just churn and churn and churn, even though it has to reach out and get the metadata from your database and validate it, has to build an introspection schema for your GraphQL endpoint. Has to spin up all of ASP. NET Core. Has to do all the things that you would expect, including a bunch of middleware that we use that you would have used as well. It’s still sub-second. And so that’s what you love about it. You want to be able to say dab start and Shazam everything just is as fast as ever. And you have as many endpoints as you want with all the special capabilities that you’ve added in there. And it’s not this constant, you know, nickel and dime sort of cat and mouse game where you’re like, “oh, yeah, but I don’t add that feature because it takes away from this other.” That’s the entire goal of Data API Builder, to make it so that the friction for adoption is as low as possible, making it so that developers aren’t constantly thinking of compromise. They just get as much benefit as they possibly can as fast as they can.

So yeah, I mean, when you describe it and you’re like, “oh, what? You just give it a file?” That’s all you do. It’s pretty insane.

Jamie : It really is pretty insane, especially since, like, you know, I How do I put this? I appreciate that I’ve talked to you, Jerry, not necessarily in person, but in a similar sort of venue, about how long it can take a developer to spin up an API, right? “Oh, we have this database table and we need to do some work exposing some of the stuff from that table. Go spin up a database, a brand new greenfield API."

“Okay, boss, it’ll be three days.” I’ve got a, like you said, maybe I’m using Entity Framework, maybe I’m using ASP. NET Core. Maybe I need to build some security hardening in there, and then I’ve got to do integration tests and unit tests. And it’s got to go through multiple stages of deployment. And then finally, splat is there. “Oh, wait, the ship has sailed. We’re moving on to something else.” Or, hey, “could you speed that up?"

“Well, not really. There’s only so much that I can do, even if I’m working with Copilot,” or Claude or whatever your Gen AI tool of choice is. “There’s only so much that I can build in a 24-hour period or an eight-hour day,” right? So being able to go, “here’s my configuration file. Hey, DAB, you go figure it out.” And then suddenly an API appears and it has all my tables in it. That’s amazing.

Jerry : I mean, there are a couple of other features we haven’t talked about where you can build the relationships between tables that aren’t actually relationships in the database. So, you can still do nested navigation inside your GraphQL queries. That’s unbelievable because if you don’t have the ability to go and actually introduce those constraints in the database for whatever reason. Just do it on the API level, right? I mean, that by itself is really cool.

The permissions that we the granular permissions we have on every entity, so that you don’t have to go into the database and set any sort of sort of permission. Unbelievable. The I mean, we’re all role-based inside, so you can set up permissions anonymous, authenticated, or it was some sort of custom role that you might have, go to town. Every entity can have its own set of permissions, create, read, update, and delete and execute, depending on if it’s a stored procedure or not. You can pass parameters to store procedure. It is unbelievable that The things that you get, you’re like, “oh, yeah, but can it do that?” Almost certainly it can, right? It’s kind of crazy.

Not to say it’s perfect, not to say that our backlog is not gigantic, but you know, in the back of your mind, some people might be thinking about, “I wonder if this can perform in scale.” And so it’s worth just pointing out that Microsoft Fabric is Microsoft’s big data solution. It’s the data lake, it’s the one lake, it’s our version of a lake house. So, when you’re talking about data, it’s Fabric. And Fabric has a GraphQL endpoint. And guess what the engine is behind the scenes that powers the GraphQL endpoint inside of Fabric, right? It’s Data API Builder. So if there’s a suspicion, you know, like, “oh, there’s a lot of features here, Jerry. I wonder if it can scale and perform.” We’ve already gone down that road for you. We’ve made it so that it can scale and perform because we wouldn’t be able to do this inside Fabric if it didn’t.

And so a lot of capabilities that we have, for example, Fabric Insisted that we have the ability to do aggregations. And so that means you can do HAVING clauses, GROUPBY clauses, all the aggregation methods you would expect; all inside a single query so that you can do incredible, like incredible queries that are just beautiful to run. And you don’t have to have a special stored procedure that enables it or something like just do it right there inside GraphQL. So that sometimes drives the decision between REST and GraphQL. Like if you wanted to do multiple queries simultaneously, then obviously you could do that inside GraphQL. If you wanted to take advantage of aggregations, that’s how you would do it. If you want to do nested queries that use relationships, GraphQL supports that as well.

But if you just wanted to do something like PageNation, Pagination is built in. If you want to make it so that somebody queries a fifteen trillion row table, but they only get 100 rows back because you don’t want to kill your database or your API or your client, frankly. Obviously, you have to come up with a paging strategy. Well, we that’s built in as well. We have paging as well. And it’s cursor based paging. It’s the Cadillac of paging because it’s the type of paging where you’re like, “okay, you’re saying if you have a very active table where things are changing under all the time, the user’s experience going page to page is always constant,” right? That’s what you want. You don’t want to make it so that every time you go to the next page, it’s actually you just skip four pages because so many things were inserted, deleted, or updated inside that table. So we’ve really tried to make it so that the types of requirements that developers have against that API, those are the sorts of requirements that we just meet you right out of the box. You don’t have to enable pagination. I mean, it just happens. It’s really, really spectacular.

And we limit it in other ways too. I mean, that’s very important to make sure you’re protecting the container and the API and the overall experience. But we also you know you can also limit it based on the size, like the megabyte size of the payload coming back. Because what if you’re just returning one row? I’m sorry, one column, but that column happens to be a text column, and in SQL, it’s two gigabytes as far as the limit of a text column. which means or think about the new JSON data type, right? So it’s a binary data type, and that’s also two gigabytes. So now you’re like, “oh, I’m going to use SQL as my NoSQL database.” Well, that’s not unreasonable. But now all of a sudden I automatically page to 100 rows. Well, hang on, 100 rows, two gigabytes each, you’re going to blow the socks off of this thing, and everybody’s even though it seems quite small. And so now you can set it up and we’ll monitor as we’ll stream everything and we’ll monitor the size that comes back. And when it hits your threshold, we’ll page in a new way. And so we’ll make it so the pages get even smaller because of the size of the payload.

And on and on it goes, like all these little things we’ve thought about that you would probably build into your own API given enough time and budget. And we’re just trying to make it so that you get it for free. And literally for free. I mean, open source, no license. So, I mean, there is an MIT license, but no special license where you have to pay us whatever. And you can do everything against your saying you could run it in AWS. You could run this in AWS against Postgres. There’s no Microsoft anywhere all of a sudden. I mean, it’s kind of crazy to think about. And that’s fine too. We really are trying to meet developers where they are, making it so that this becomes a standard approach to interacting with the database.

And we have really large customers, like really large we’re Microsoft. And some of our customers, they have internal corporate policies now that don’t even allow anything to touch the database. That’s the way that it works. And so they need to introduce all of these data APIs without apps using them, right? They’re just trying to enable them so that apps can be written. Well, here’s the solution. You don’t need an engineering team to do it. You can have a guy do it over his lunch break and you can have it by the end of the day. I mean, it’s just kind of crazy what you could actually do. Even better is now that there are other situations where because you have so much data and because we live in this new world where data is everywhere and data is so valuable and you can use it to enrich other data, and we have this data discovery going on because of machine learning. You may have like a clearing house where you want to create a marketplace for external customers to be able to access your data. And you don’t want them to be able to just call against your database. You want to give them a data API. And maybe you want Something that’s really small, cheap, delivers in a container, and you want to create a container for every customer so that they can isolate their experience. And you can make it so that they have unique permissions, perhaps, unique objects exposed, but also so that you are almost like CQRS. isolating all the compute into specific areas too. So really neat stuff that you can do that this just enables, partly because it’s so cheap.

If you’re thinking about microservices. You’re like, “oh, okay, now I have 10 microservices. That means 10 small database schemas. Now I need to build a front end to those databases.” Well, you could have 10 data API front ends, and you could have all of your microservices have such a small engineering cost as a result. And you could have the topology that you want. If that’s the way you want to lay it out, you can do it in microservices. If you want a handful of really large ones, you can do that with Data API Builder as well.

So it’s funny because it’s not impossible to come up with scenarios. where you hit a wall. You’re like, “oh, Data API Builder can’t do it.” But there’s not a lot of them. There’s not a lot of them. And we really are trying to introduce features and changing the engine a little bit to kind of meet these low hanging fruit of things that we know that if we could just do that, we could take that ninety percentile of who we kind of meet and we could make it ninety one. And then we make it ninety two. We would never get to one hundred, but we want to get as close as we can and because we can do a small amount of engineering or even a large amount of engineering and save you and your team tons of work and be able to kind of prove it across the industry because you’re not the only one using Data API Builder. Many of us are. And so that allows us to consolidate the issues and consolidate the security issues, consolidate the performance, whatever they are, so that we can resolve those too. Really, really cool.

By the way, This is not a Microsoft product. I mean, in a sense, it is. But because it’s open source, we have to think about it slightly differently. However, because we are the authors, we accept pull requests from you and anybody else in the community that wants to enhance it. And then we’ll kind of process those and make sure they get scrubbed. But we treat it like a product, which means it goes through security reviews, which means it’s constantly being monitored. which means there are certain things we’re not allowed to do. Like, “yo, you can’t do it that way.” Well, it’s going to be twice as hard if we have to do it the hard way. And they’re like, that’s the way it is. But so there’s another advantage that if you’re using Data API Builder, you get the advantage of something that is built to the rigour of Microsoft engineering without the license cost of it. So there’s just an incredible like back to back benefits of doing it.

Meanwhile, I acknowledge you might just write your own API. That’s fine too.

Jamie : I mean, all of that is quite simply amazing. Yeah, you’re absolutely right. You may want to write your own, but you also may want to just Just get something out the door and get something that is standards compliant and has all of those features, Fusion Cache, and all that kind of stuff. And the like you said, the cursor-based pagination, like I’ve seen developers implement what they think is pagination, but it’s actually not. So even like that is a non-trivial thing to solve sometimes.

We’re rapidly running out of time, Jerry. And I was just wondering, you’ve already said aka dot slash dab for to get to Data API Builder’s repo. What are some of the other places where folks can go, “you know what? This sounds amazing. I’ve just listened to Jamie and Jerry talking about Data API Builder, and I don’t want to have to do all of my API software stuff. I don’t want to have to write that.” What am I doing? How am I learning? Like, is it just literally go to aka dot ms slash dab? And, like, or are there Microsoft Learn documents?

Jerry : I think that’s probably the right place to start.

But so if we just use that URL as the base, so aka dot ms slash dab slash docs will take you to MS Learn. So you can see all of our formal documentation. (aka dot ms slash dab) slash samples, that’ll take you to all of the repositories that have pre-built samples and doing different kind of cool things. (aka dot ms slash dab) slash mcp, if you want to see how to use it with an MCP server. slash… let me think of another one. (aka dot ms slash dab) slash registry, if you want to go to the MCR registry and you want to see the image directly, we have quite a few of these slashes.

But there’s also a SQL DevPath [at aka dot ms slash sql slash dev slash path]. The SQL DevPath is a formal credential that comes from MS Learn that you can go through. a series of modules, and then it gives you and then it gives you a kind of an exam and a cordoned-off part of Azure that you can go and prove that you can do it in. And that’s um that’s the SQL dev path. But because it’s an important it’s you know, it I don’t want to say it’s the cornerstone or the jewel of the crown here, but it is an important part of the developer story for SQL. So you’ll see it in many things, including that.

Yeah, that’s the easiest way, aka dot ms slash dab, and slash dab slash docs. I go to those two constantly for sure. If none of that makes sense, then you could just reach out to me. I’m on Twitter @JerryNixon, and I’m happy to help you there as well.

Jamie : Amazing. Thank you ever so much for that, for that, Jerry. And thank you for being on the show as well. Like I said, I’ve used the Dead API Builder. In the past, but I’m always really interested to hear someone who’s super excited about it tell me even more things that I didn’t know. So, yeah, I really appreciate you being on the show. This has been a ton of fun. Thank you so much, Jerry.

Jerry : Thanks for inviting me. I was excited. When you did, and I was glad to be here.

Wrapping Up

Thank you for listening to this episode of The Modern .NET Show with me, Jamie Taylor. I’d like to thank this episode’s guest for graciously sharing their time, expertise, and knowledge.

Be sure to check out the show notes for a bunch of links to some of the stuff that we covered, and full transcription of the interview. The show notes, as always, can be found at the podcast's website, and there will be a link directly to them in your podcatcher.

And don’t forget to spread the word, leave a rating or review on your podcatcher of choice—head over to dotnetcore.show/review for ways to do that—reach out via our contact page, or join our discord server at dotnetcore.show/discord—all of which are linked in the show notes.

But above all, I hope you have a fantastic rest of your day, and I hope that I’ll see you again, next time for more .NET goodness.

I will see you again real soon. See you later folks.

Useful Links

SQLBits
The original definition of REST
Data API Builder documentation
Data API Builder on GitHub
SQL Dev Path
FusionCache
Jerry on X (formerly known as Twitter)
Supporting the show:
Getting in touch:
- via the contact page
- joining the Discord
Podcast editing services provided by Matthew Bliss
Music created by Mono Memory Music, licensed to RJJ Software for use in The Modern .NET Show
Editing and post-production services for this episode were provided by MB Podcast Services

S08E03 - Designing APIs Like a Pro: Lessons from Jerry Nixon on Data API Builder and Beyond

Sponsors

Embedded Player

The Modern .NET Show

S08E03 - Designing APIs Like a Pro: Lessons from Jerry Nixon on Data API Builder and Beyond

Supporting The Show

Episode Summary

Episode Transcription

Sponsor Message

Wrapping Up

Useful Links