info

This blog post is a companion to my Say goodbye to building boring APIs with Data API Builder talk! If you are unable to visit a session of this talk, you can read this blog post instead! This way as many people as possible can learn about Data API Builder!

My talk offers some more in-depth information, so if you want to know more, take a look at my Speaking page to see when and where I will be giving this talk again! You can also contact me if you want me to give this talk at your event!

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.

An image describing a client connecting to a CRUD api, which in turn connects to a database.

An overview of a common client -> server -> database setup.

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.

An image describing a client connecting to DAB, which in turn connects to a database.

Thanks to Jerry Nixon, the PM for DAB, for creating the base of this image and allowing me to use it!

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
  • An OpenAPI document with SwaggerUI
  • 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!

An image describing DAB's architecture

A useful overview of DAB’s architecture and inner workings.
Image under MIT license in the DAB repository on GitHub.

Does this look familiar?
Data API Builder is not a new product on the market. Similar tools exist like Supabase, Hasura and PostgREST and all of these have pro’s and con’s, including Data API Builder. However, I really like DAB because it has broad database support, it’s open-source, lacks vendor lock-in, lacks paid features, has tons of features, an impressive security model and it can run anywhere. This sets it apart from other tools making it a great choice for many projects!

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.

An image describing a database model containing a book entity with an id, title, author id, genre, publication date and image url, and an author entity with an id, name, birthdate, bio and image url.

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

info
To follow along, you can use one of my demo projects, which includes the exact database model and test data. I recommend starting with the combined/dab folder and then moving on to combined/react which contains a React app showcasing many of DAB’s features for a rich, interactive demo. The other folders are related to other features based on the content of this post. Have fun!
  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:

Click to view the entire 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:

GraphQL Query:

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

Output:

{
  "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!

Thoughts
What do you think of this approach? Let me know in the comments below!

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:

A diagram showing off how to use serverless functions to execute custom code before creating a new entity

This approach allows us to create a cloud-native CRUD API with custom logic with minimal effort!

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:

A table showing off cloud hosting options for docker.

DAB can truly run anywhere!

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!

A diagram showing off Azure Static Web App's built-in /api endpoint feature, including a call to /data-api which calls a hosted DAB instance for you.

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!

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!

An image of stickers of the DAB logo An image of Jerry Nixon, PM for DAB, holding my stickers of the DAB logo

Did you enjoy this post? Buy Me a Coffee at https://ko-fi.com/stenbrinke.nl