Earlier this year I had a PM at Microsoft reach out and ask me what I thought about the Data API Builder. I hadn't looked at it, so I made a note to check it out. I hadn't done that by the time SQL Saturday Denver 2024 occurred, where I saw Jerry Nixon from Microsoft present a session about it and ask me if I'd used it. I hadn't, but remembered I needed to look at it.

I'm finally doing that.

This article will talk lightly about what the Data API Builder is and then look at getting started with it. Future articles will look in more depth on how to use the API and the different ways you can access the API.

What is the Data API Builder?

I wasn't sure what to think of this the first time I saw it. This looked like some sort of way to cover up the database and give developers a simple way to query tables, but as I learned more, I find the idea of this API interesting. There is a "what is" on MS Learn, and it gives a nice overview, but it's high-level.

This is an abstraction tool that creates either a REST (HTTP/HTTPS) endpoint or a GraphQL endpoint that your application can use to submit CRUD queries. The REST API is Swagger compatible and the GraphQL one is Banana Cake Pop. Those names are funny enough and once I stopped chuckling, I could search and learn that Swagger is a way of setting up the API so it can be read by a machine. Essentially a folder-style structure that many developers understand. GraphQL is different but also gives you a way to interact, one which can be over HTTP or Websockets. It's more protocol-neutral. Both Swagger and Banana Cake Pop are IDEs as well, which adds to the confusion, but your developers will know what they are.

In any case, what this means is a service starts running that can reduce the amount of "stuff" that your users need to configure on their side to interact with your database. The service can be running the dab CLI or it can be inside a Docker container, but you essentially configure what the API allows and what users can do, including access to which objects they can see, what rights they need, and what security is required.

Oh, this is also data store independent. The DAB system works with:

• SQL Server
• Azure SQL
• Azure Cosmos DB for NoSQL
• PostgreSQL
• Azure Database for PostgreSQL
• Azure Cosmos DB for PostgreSQL
• MySQL
• Azure Database for MySQL
• Azure SQL Data Warehouse

Not that you're port your database, but it's nice to give developers a simple way to interact across projects with different databases in the same way. Reducing their cognitive load helps, and with something like DAB, developers don't need to know or worry about SQL in many cases. Maybe in all.

Installation

I want to cover two things here, first, get Postman, which will help you experiment with the API without writing a lot of code. Second, we need DAB.

Postman is a desktop tool that lets you submit http post and get requests. It doesn't matter if you know what those are, but this makes life easy and save help you save queries, so grab this. You can also use a browser, but download Postman and give it a try.

For DAB, if you have dotnet installed, then run this. If not, get dotnet as a lot of stuff is buried in this little tool for Windows devs.

dotnet tool install --global Microsoft.DataApiBuilder

This starts the install, but not my CMD window was here for a bit before it finished. Something is happening, but I don't get a status.

You can check it's installed with the dab CLI command shown here. Note, two dashes if you're not a CLI person.

Now that Postman and DAB are installed, let's move on.

Getting my first API

To get started we want to point the API at an existing database. I have a copy of Northwind that I use for various Flyway demos, so I'll point to that. My database is located on my localhost (Aristotle). I'll experiment with Windows auth, and I'll make a place to experiment. As a habit, I do this with a git repo to start, as you can see below:

This is an empty folder. I will start by using dab init in this way. There's a sample connection string in the docs, but I changed it to use Windows Auth. I also set this to point to a small sample database I use for demos, called SimpleTalk:

dab init --database-type "mssql" --host-mode "Development" --connection-string "Server=localhost,1433;TrustedConnection=True;Database=SimpleTalk;TrustServerCertificate=True;Encrypt=True;"

When I run this, I get these results, which say I have a config file.

If I look in the folder, I see this:

This file contains the bare bones of stuff, which is the configuration of the DAB system. There aren't any objects, and nothing I can query in here. The entire file is shown here:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.2.14/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "Server=localhost,1433;TrustedConnection=True;Database=SimpleTalk;TrustServerCertificate=True;Encrypt=True;",
    "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": "development"
    }
  },
  "entities": {}
}

Notice entities are blank, and there are two different APIs presented here. You can alter this stuff as needed, and the complete configuration file spec is available if needed. We won't do that to start, but let's follow the instructions from the CLI output and add an entity. Here is my database.

I'll choose one table, and here's the command I'll run:

dab add Country --source "dbo.CountryCodes" --permissions "anonymous:*"

I'm going to add an API reference of "Country", which will point to the dbo.CountryCodes table. I'm not setting any permissions as I'm testing.

Note: This is PoC code. THIS IS NOT READY FOR PRODUCTION.

When I run this, I see these results:

The JSON file has now data in the entities element:

  "entities": {
    "Country": {
      "source": {
        "object": "dbo.CountryCodes",
        "type": "table"
      },
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "Country",
          "plural": "Countries"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*"
            }
          ]
        }
      ]

I should have an API ready. The next thing is to start DAB and query it.

Checking the API

Before I start DAB, I don't want to run under my credentials. I'll add a login that is a SQL login and is assigned a role in this database that can read the table I've added. I'll adjust the JSON to have a different connection string:

"connection-string": "Server=localhost,1433;User ID=STWebApp;Password=Demo12#4;Database=SimpleTalk;TrustServerCertificate=True;Encrypt=True;",

The way to start DAB is with dab start. Once I do this, I see that it is running on a local port. There's a lot here, but look down near the bottom and you can see it's running on localhost:5000. That's the port we need.

Let's query the API and see if it works. I'll use Postman to do this. I'll create a new query and use the http type. Note there is a GraphQL type as well:

When I get this, I'll now point my query to the localhost on port 5000. The URL I'll use locally for this is: http://localhost:5000

I need to add to this URL. If you look above, you will see in the initial JSON config that the endpoint is /api for the REST endpoint. I'll add that. The next part is that I need the entity I added, which is "Country". The entire URL is this: http://localhost:5000/api/Country

If you look in the image below, you see the Postman query, which is a GET request. When I send this, you can see the data in the results in the lower right. I have a list of countries, as I'd see them in my table, but in JSON format, which is how many APIs return data. Developers also love this and most languages can deserialize this into usable data.

This is the data in my table, as you can see below when I query from SSMS.

If I put that URL in Chrome, I see this, which is less helpful than Postman. However, a REST API works fine here.

I can also query the GraphQL endpoint. I won't explain GraphQL here, but I'll cover it in another article. However, you can see the countries at the bottom.

One Last Thing

In the REST API query, if I scroll to the bottom, I see this image. Notice the last country is Hungary, and the next entry is a "next" URL.

The DAB API only brings back a limited number of results by default. There are 101 returned, which I can see in the CLI output that is logged to the console. Here I've snipped the query.

If I paste that link into the top of Postman, I'll get the next set of countries. You can see those results below, where the first country is Indonesia.

Paging is built in, which is nice, since most developers query select * from table and get everything far too often.

Conclusion

This article shows a quick way to get started with the Data API Builder (DAB) by installing the tool and then configuring it from the CLI. The config file can be edited, but this article showed the basics of adding a table to the API and then starting the API server. We then queried this from REST (Postman and browser) and GraphQL.

There is a lot more that can be done here, and this is more than a simple SELECT query tool. The Data API builder has a lot of possibilities and can be an easy way to present an API to your developers without requiring them to build their own API or data access layer in their application.

In future articles we'll look more at how you can configure and control the DAB server.