Introduction Link to heading

Are you tired of building CRUD APIs? Me too! As developers we prefer to be busy solving creative or technical problems and delivering value. But we still spend a lot of time building standard APIs. Whilst this can be fun, it can also be quite repetitive and sometimes even boring.

Fortunately, this is no longer necessary thanks to a new open-source product from Microsoft called Data API Builder! In this article, you will learn all about this cool product and how to get started with it!

What is a “CRUD” API? Link to heading

Before we dive deep into Data API Builder, I think it’s good to explain the definition of a CRUD API a bit better.

This is also called a Data API; an API that is essentially nothing more than a wrapper around a database. In other words: CRUD, which stands for Create Read Update Delete. Such an API typically exposes REST or GraphQL endpoints which can be used to retrieve, create, edit and delete entities.

There is nothing wrong with this approach, but we actually spend quite a lot of time and effort setting up such an API. Think about setting up the repository, documentation, choosing your programming language and framework, etc. Then you set up a database connection and write endpoints to connect to it and secure them with authentication and authorization. But you’re not done yet! You will probably also implement something like CI/CD so that you can easily release new code, which means you have to take package, SDK, and security updates into account.

And all this for a simple API that is nothing more than a wrapper around your database? What a hassle!

Introducing Data API Builder Link to heading

But luckily Data API Builder can help us out! Data API Builder (DAB) is a zero-code, cross-platform, open-source, MIT-licensed tool that generates an API for you in milliseconds at runtime based on your database (SQL or NoSQL) entities. All DAB needs is a database connection and information about the endpoints that need to be generated for specific tables, views, and/or stored procedures.

Let’s say you have a database with 2 tables: dbo.books and dbo.authors. When you expose these entities via DAB and start it, it will expose /graphql, api/books and api/authors endpoints that your client can use to fetch, create, and update data!

But that’s not all DAB offers! DAB is packed with features that you would normally have to build yourself:

• Support for MySQL, PostgreSQL, SQL Server, Azure SQL, and Azure CosmosDB NoSQL!
• It generates REST and GraphQL endpoints
• It supports OData so common API features are already built for you!
• Caching for high-traffic scenarios
• Many options for authentication and authorization
  • You can find more information in the official documentation.
• An OpenAPI document with SwaggerUI
  • Read my blog post about OpenAPI for more information 😉
• A GraphQL playground thanks to Banana Cake Pop
• Docker support
• Integration with Azure Static Web Apps
  • and with Azure App Service in the future!
• And more!

Under the hood DAB uses a lot of open-source components, such as ASP.NET Core for setting up the endpoints, Hot Chocolate and Banana Cake Pop for the GraphQL part, and FusionCache for caching. This is extremely useful because updates to those projects will also be made available in DAB!

Oh, and maybe the most impressive feature of all? DAB is not tied to a Microsoft platform! You can run it on-premise or in any cloud, for free! So even if you run on-premise with a cloud database or vice versa, DAB can be used to its full potential!

Installing and using DAB Link to heading

You can use DAB in various ways. You can download the .exe on the GitHub releases page, but I recommend using dotnet tool install Microsoft.DataApiBuilder --global. This will expose a dab/dotnet dab command that you can use to configure and run DAB.

Let’s use the data model above to generate endpoints for the dbo.books table running in Microsoft SQL Server.

1. dotnet tool install Microsoft.DataApiBuilder --global
2. dab init --database-type mssql --connection-string "@env('sqldb')"
   1. This generates a dab-config.json file containing DABs configuration settings, more on this later.
   2. The connection string for the database is taken from an environment variable sqldb or a .env file. Safe and secure!
3. dab add Book --source "dbo.books" --permissions "anonymous:*"
   1. DAB will now make changes in the dab-config.json file so it knows to generate endpoints for the Book entity when it starts. Other tables will not be exposed!
   2. With --permissions we tell that everyone can perform all actions (Create, Read, Update, Delete) without needing to be logged in. More information about DAB’s fantastic security systems can be found in the documentation.
4. dab start
   1. DAB will now start and generate the REST and GraphQL endpoints in-memory.
   2. DAB does not output any code. All endpoints are generated at runtime!
5. curl https://localhost:5001/api/Book or navigate to https://localhost:5001/api/Book.

   [
     {
      "id": 1,
      "title": "Pronouncing Azure Correctly: The Definitive Guide",
      "authorId": 4,
      "genre": "Technology",
      "publicationdate": "2024-01-01",
      "imageurl": "https://i.imgur.com/Gx9VJp2.png"
     }
   ]
   

   1. You can easily change this path in the dab-config.json file to /api/books or anything else to be more RESTful!

What else does DAB give us? Link to heading

Not only can you use api/Book to retrieve, create and mutate data, you can also navigate to /graphql for the GraphQL playground, /swagger for SwaggerUI, /api/openapi for the OpenAPI document and / for the health endpoint.

Don’t forget that we can also use OData to filter, sort, and paginate data. For example: /api/Book?$filter=genre eq 'Technology'.

Take a look at the images below:

I think this is very impressive! Building or scaffolding all this yourself is often complex or time consuming, but DAB gives us all of this out of the box.

DAB’s configuration file Link to heading

When you use the DAB CLI, you’re actually editing the dab-config.json file. This means that you can also edit the configuration file yourself. This is a JSON file backed by a JSON Schema that contains settings about the database connection, runtime, entities, security and more. Take a look at how the the Book entity that we just created is represented in dab-config.json:

{
  // The rest of the configuration would be above here
  "entities": {
    "Book": {
      "source": {
        "object": "dbo.books",
        "type": "table"
      },
      "graphql": { "enabled": true },
      "rest":    { "enabled": true },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [ { "action": "*" } ]
        }
      ]
    }
  }
}

Also consider clicking below to view the rest of the dab-config.json file:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.2.10/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "@env('sqldb')",
    "options": {
      "set-session-context": false
    }
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/api",
      "request-body-strict": true
    },
    "graphql": {
      "enabled": true,
      "path": "/graphql",
      "allow-introspection": true
    },
    "host": {
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      },
      "mode": "production"
    }
  },
  "entities": {
    "Book": {
      "source": {
        "object": "dbo.books",
        "type": "table"
      },
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "Book",
          "plural": "Books"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*"
            }
          ]
        }
      ]
    }
  }
}

Relationships between entities Link to heading

I am impressed by what we can achieve with DAB in such a short time. But that’s not all! There is an important feature we haven’t talked about yet, namely the possibility to create relationships between entities to retrieve related data.

A book is related to an author who wrote it. When you show an author to a user, you also want to show which books this author has written. And this can also be done with DAB!

First of all, you could create a View in your database and tell DAB to generate endpoints for this, but this requires you to adjust your database for DAB, which may not be desired. So, let’s use another approach with GraphQL! First, we need to tell DAB that there is a relationship between an Author and Book:

# Instruct DAB to generate endpoints for the authors table
dab add Author --source "dbo.authors" --permissions "anonymous:*"
# An author can have many books
dab update author --relationship books --target.entity Book --cardinality many

To retrieve all authors with their books in GraphQL, you can now execute the following:

query {
  authors {
    items {
      name
      books {
        items { 
          title                             
        }
      }
    }
  }
}

{
  "data": {
    "authors": {
      "items": [
        {
          "name": "Mark Russinovich",
          "books": {
            "items": [
              {
                "title": "Pronouncing Azure Correctly: The Definitive Guide"
              }
            ]
          }
        }
      ]
    }
  }
}

Extending DAB Link to heading

I often get asked: “Is it possible to extend DAB’s endpoints with custom code?” This could be very useful. Picture a scenario where you need to call an external server to verify some details about a new book before adding it to the database.

DAB does not support this natively. DAB really focuses on generating endpoints for CRUD scenario’s where the only validation you need is stored in your database schema. If you’re quite sure you will need to extend DAB’s endpoints with a lot of custom code at some point, I recommend you to build a custom API to prevent a large migration from DAB to a custom API in the future. Otherwise, read on!

However, this native limitation doesn’t mean that we can’t get this to work with a little bit of extra work for projects that need some customization!

Executing API calls in stored procedures Link to heading

DAB supports exposing stored procedures as entities. This would allow us to create a stored procedure for storing a new book entity, which can then be called with REST/GraphQL. This stored procedure can then use your database provider’s HTTP support to execute an API call during query execution! This can then be used to validate the new entity before storing or rejecting the new entity. I’m most familiar with Azure SQLs version, which is called sp_invoke_external_rest_endpoint. Take a look at some samples.

Storing business logic in your SQL queries used to seem like a bad idea to me, but nowadays I do understand the appeal for applications that are data-focused and don’t really need custom-built software to drive the database, like DAB! However, I do not recommend combining this approach with custom-built software as this would muddle the waters between the source of truth for your business logic!

Executing serverless functions Link to heading

Instead of executing business logic in SQL, you could also use a different approach! We could route calls that require custom logic away from DAB with an API management service, and execute some serverless code like an Azure Function or AWS Lambda instead. This would allow us to write a few lines of code for the custom logic and then redirect the request to the DAB endpoint to store the new entity, removing the need for a full-blown custom API project. You could even consider storing the entity in the database yourself if you need some more control:

Hosting Link to heading

So, you have now generated an API in milliseconds with DAB! But how do you host it? There are several ways to host DAB, which we’ll cover below.

I quickly want to mention hosting DAB using the .exe I mentioned earlier. This can be used when playing around with DAB or running on-premise, for example. While possible, I do recommend you use Docker instead because this makes managing the start-up and deployment of DAB’s configuration file way less complicated.

Docker Link to heading

DAB has built-in support for Docker. All you need to do is use DAB’s docker image and mount your dab-config.json, which is perfect for self-hosting with docker-compose or even your on-premise Kubernetes cluster:

docker run \
    --name dab \
    --publish 5000:5000 \
    --detach \
    --mount type=bind,source=$(pwd)/dab-config.json,target=/App/dab-config.json,readonly \
    mcr.microsoft.com/azure-databases/data-api-builder

My previous link contains more information about DAB’s container deployment process, so instead I want to dedicate this section to all the ways you can host DAB in the cloud by showing you the most popular cloud platforms and how you can host DAB on them by using Docker:

Azure Static Web Apps Link to heading

As I mentioned earlier, DAB is not tied to a Microsoft platform. However, it does integrate well with Azure Static Web Apps. This is a serverless hosting platform for web apps like Hugo and Jekyll but also React, Angular, Vue, Blazor, etc.

In most cases a web application will also communicate with a server. For this you can easily link Azure Static Web Apps to an Azure Compute service such as Azure App Service, Azure Functions, Azure Container Apps and more. Azure Static Web Apps will then expose an /api endpoint that forwards all requests to the configured Azure service. We could use this by hosting DAB in an Azure Container App thanks to the Docker support!

However, this is not very practical, considering the alternative option you’ll learn about now! Setting up an Azure Container App to host DAB is extra work and needs to be maintained. That’s why Azure Static Web Apps has direct integration with Data API Builder! You can link a Static Web App to a database in Azure and then create a Data API Builder configuration file that will be used by Azure Static Web Apps. When you deploy a Static Web App, requests to /data-api will automatically be sent to Data API Builder! Very useful!

With this setup you can build a web app without having to manage a server at all! And you can still use the /api endpoint with a full-blown custom API or small Azure Function if you need to execute custom logic!

Finishing up Link to heading

Thanks for reading to the end! I hope you enjoyed this post!

DAB contains way more features that I won’t cover in this post to avoid it becoming way too long ( again ). I recommend you to read the documentation or contact me if you want to know more! Lastly, I would like to thank you for reading this article and I hope you are just as excited about Data API Builder as I am!

Let me know if you have any questions or feedback in the comments below, and get in contact with me if you want me to speak about Data API Builder at your event!

Conclusion Link to heading

By using Data API Builder you can stop building CRUD APIs and focus on delivering value! DAB generates an API at runtime for you in milliseconds based on your database entities. This way you can quickly and securely expose your data to your clients without having to write a lot of boilerplate code.

DAB runs everywhere, and with its integration with Azure Static Web Apps it becomes even easier to build a full-stack application without having to manage a server!

Links to the demo Link to heading

A lot of the code examples in this blog post are based on the demo I give in my related session. You can find the files here.

Get yourself a DAB sticker! Link to heading

If this post has piqued your interest, great! I have ordered custom DAB stickers to give away to people who are interested in DAB! If you want one, get in contact with me when I’m attending or speaking at a conference, meetup or local event, and I’ll give you one!

Update: Right after I published this post, Jerry Nixon, the PM for DAB, received the stickers I sent to him! That’s one happy customer already! 😉 I’m grateful to have been able to send him these stickers, as DAB means a lot to me!