Working with Azure’s Data API builder

Microsoft’s platform-based approach to cloud development has allowed it to offer managed versions of many familiar elements of the tech stack, especially within its data platform. As well as its own SQL Server (as Azure SQL) and the no-SQL Cosmos DB, it has managed versions of familiar open source databases, including PostgreSQL and MySQL.

Using these familiar databases and APIs makes it easy to migrate data from on premises to Azure, or to build new cloud-native applications without a steep learning curve. Once your data is stored on Azure, you can use familiar tools and techniques to use it from your code, especially if you’re working with .NET and Java which have plenty of official and unofficial data SDKs. But what if you’re taking advantage of newer development models like Jamstack and using tools like Azure Static Web Apps to add API-driven web front ends to your applications?

Although you could use tools such as Azure Functions or App Service to build your own data API layer, it adds inefficiencies and increases your maintenance and testing requirements. Instead, you can now use Microsoft’s own Data API builder tool. It’s simple to configure and gives a database either REST or GraphQL endpoints that can quickly be consumed by JavaScript or any other REST-aware language. It’s also possibly the fastest way to start turning Azure-hosted databases into applications.

Introducing Data API builder

Designed to run on premises, at the edge, and in the cloud, Data API builder is an open source tool targeting five different databases: Azure SQL, SQL Server, PostgreSQL, MySQL, and Cosmos DB. You can work with your own installations as well as with Microsoft’s own managed services, so you can develop and run in your own data center and migrate code to the cloud as needed.

If you’re using Data API builder as part of your own code, it’s a .NET tool that’s available as a Nuget package. You need .NET 6 or 7 to run it, and it runs on any .NET-compatible system, including Linux. Once it’s installed, you can use its CLI to build the appropriate endpoints for your databases, ready for use in your applications. Alternatively, you can use a ready-to-run container image from Microsoft’s container registry. This approach works well if you’re targeting edge container environments, such as the new Azure Kubernetes Service (AKS) Edge Essentials, which gives you a limited managed Kubernetes platform.

Installation is quick and you can use the tool with the dab command from any command line. Help is relatively basic, but as this is a very focused tool, you shouldn’t find it hard to use. Single-purpose command-line tools like this are an increasingly important part of the .NET ecosystem, and it’s worth being familiar with them as they can save a lot of work and time.

Building APIs at the command line

It’s a good idea to be familiar with ADO.NET to use Data API builder. That’s not surprising; it’s the standard way of accessing data services in .NET and, at heart, this is a .NET tool, even if you’re using it to build web applications.

To make a connection, you’ll need to know the structure of your database and which elements you want to expose. At the same time, you also need any ADO connection strings so you can make the initial connection to your database. For Azure resources, these can be found in the Azure Portal as part of your resource properties. You don’t need to store the connection data in the clear; you have the option of using environment variables to hold data outside your code at runtime, so you can use tools like Azure Key Vault to keep any secrets safe.

Data API builder uses a JSON configuration file to store details of any APIs you build. Create this by defining the database type, along with a connection string. Be sure to use an account with appropriate permissions for your application. The configuration file details the supported API types, so you can enable either REST, GraphQL, or both. Other parts of the configuration file specify the mode, whether cross-origin scripting is allowed, and the authentication type used for the connection. While the CLI tool creates and updates configuration data, you can edit it yourself using the GitHub-hosted documentation.

Once you have defined a connection, you can set up the APIs for your data. Using familiar database entities, give the API a name and tie it to a source, like a table or a query, and give it permissions associated with users and database operations. The name is used to build the API path for both REST and GraphQL.

With a connection defined and entities added to the configuration file, you’re now ready to build and serve the API. The Data API builder is perhaps best thought of as a simple broker that takes REST and GraphQL connections, maps them to prebuilt ADO statements, and runs them on the source before returning results and remapping them into the appropriate format. The REST API supports common verbs that map to standard CRUD (create, read, update, delete) operations; for example, GET will retrieve data and POST will write it.

Each REST verb has additional query parameters to help manage your data. You can filter data, order it, and apply select statements. Unfortunately, even though you can pick the first however many items to display, there doesn’t seem to be a way to paginate data at present. Hopefully, this will be added in a future release as it would simplify building web content from the query data.

Using GraphQL with Data API builder

If you’re planning to use GraphQL, it’s worth using a tool such as Postman to help build and test requests. GraphQL can do a lot more than a basic REST query, but it can be hard to build queries by hand. Having a tool to explore the API and test queries can save a lot of time. For more complex GraphQL queries, you will need to build relationships into your configuration. Here it helps to have an entity diagram of your data source with defined relationships that you can describe by the type of relationship, the target entity for the query, and how the relationship is stored in your database.

The process of making an API is the same for all the supported databases, with one difference for Cosmos DB. As it already has a REST API, there’s no need to generate another. However, you can still use it to create a GraphQL API.

If you’re using this approach with Azure Static Web Apps, first use the Azure Portal to add your source database to your site configuration. You then need to import an existing Data API builder configuration file. You can use both the Database API builder and the Azure Static Web Apps CLI to create the files needed. The Static Web Apps CLI creates a stub file for the configuration, which you can either edit by hand or paste in the contents of a Database API builder file.

Being able to add GraphQL support to any database is important; it’s a much more efficient way to query data than traditional APIs, simplifying complex queries. By supporting both REST and GraphQL APIs, Data API builder can help migrate between API types, allowing you to continue using familiar queries at the same time as you learn how to structure GraphQL. As an added bonus, while this is a tool that works for any application framework, it’s well worth using with Azure Static Web Apps to build data-connected Jamstack apps.