I wrote a piece on getting started with the Data API Builder (DAB), which looked at how this product works and how to start using it. That was a basic look, but it gives data professionals an idea of how this might help them work more closely with front-end developers who are more comfortable with API access to data and dislike SQL. In this article, I want to continue to dive into DAB with a more detailed look at the REST API and how one might perform queries against the tables you have exposed from your database.

Note that the REST API is lightly documented at MS Learn: https://learn.microsoft.com/en-us/azure/data-api-builder/rest?tabs=http

Pre-requisites

I will assume you know how to

• create a DAB configuration file and add entities to the file.
• start the DAB process and find the REST API base URL
• configure this for SQL Server
• have Postman installed (not required, but nice)

It should be the same for other platforms, though I haven't tested those. I will also be showing examples in Postman as it makes rendering results easier. You can do this from a web browser, but it's harder to read results (and more annoying to me), or you can use any other method you want that supports accessing an http endpoint and rendering JSON results.

The REST API

If you are a data professional, you might not be familiar with REST APIs. These are a common way of accessing data resources, or changing them, in much of software development. These are often http URL addressed resources, with the API being a structured view over the underlying resources. You can read more about REST from Red Hat or AWS, but it's not required. Essentially for us data professionals, we are exposing our database objects through an HTTP endpoint.

As an example, I have DAB running against the Northwind database and the Customers table exposed. The base URL for my DAB endpoint is: http://localhost:5000/api. However, this doesn't provide my anything but an error:

This is because there isn't any data to return. Instead, in my config file, I have added the dbo.Customers table, and it is accessible as http://localhost:5000/api/Customers. Note all the results below, which are hard to read.

This is a JSON document, with lots of elements, arrays, and more. If you are familiar with Northwind, this data looks familiar, but it's hard to consume. In .NET, there are plenty of easy ways to de-serialize this data into a series of rows and columns that could populate a table or a grid or anything the developer wants to fill with data.

That's a simple look at the REST API, now, let's look at what we can do with it.

Note, in the rest of the article I'll be using Postman to submit the URL and get results back.

Selecting Certain Columns

In the example above, for the base URL for Customers, I get all customers from the table, both all rows and all columns. This is essentially a SELECT * with no WHERE clause. Not what we want most developers to do from their applications.

Instead, we would like them to be selective about what is returned. Choose certain columns of data. I'll start to limit data in this section by only picking some columns. In the next section, we will add the filtering of a WHERE clause.

The REST API in DAB includes a $select parameter, which can be used as the column list to be returned. The basic syntax is:

/api/entity?$select=column1,column2,column3

Here we give the entity name, use a ? to indicate parameters are coming and then use $select as the parameter. For my Customers table, I construct a URL like this to get the CustomerID and Company Name: http://localhost:5000/api/Customers?$select=CustomerID,CompanyName

When I run this, I see this data. Note that Postman gives me a table in which I can edit parameters below the URL. All customers are shown in the results, but only those two columns.

If I wanted another column, like ContactName, I add it to my URL. The call is now:

http://localhost:5000/api/Customers?$select=CustomerID,CompanyName,ContactName

Here are the results:

One caveat here is that DAB is case sensitive in the REST API. Your casing must match what's in the DB. Here is a query in SSMS that works. Note that I've lowercased the ContactName field:

In the REST API, the equivalent query is:

http://localhost:5000/api/Customers?$select=CustomerID,CompanyName,contactname

This, however, returns an error:

I haven't tested how many columns can be added, but I assume there isn't a limit in DAB. I've added many, and they seem to work.

I can also rearrange the columns. In this URL, I've changed the order of columns, and I've duplicated one column:

http://localhost:5000/api/Customers?$select=CompanyName,ContactTitle,ContactName,Country,CustomerID,ContactName,ContactTitle,Address,City,Region,Country

In the results, however, I don't see duplicate data. DAB has removed the duplicate.

Behind the scenes, this is an sp_executesql query that looks like this (I've cleaned this up slightly to make it easier to read):

exec sp_executesql N'
SELECT TOP 101 
  [dbo_customers].[CompanyName] AS [CompanyName]
, [dbo_customers].[ContactTitle] AS [ContactTitle]
, [dbo_customers].[ContactName] AS [ContactName]
, [dbo_customers].[Country] AS [Country]
, [dbo_customers].[CustomerID] AS [CustomerID]
, [dbo_customers].[Address] AS [Address]
, [dbo_customers].[City] AS [City]
, [dbo_customers].[Region] AS [Region]
FROM [dbo].[customers] AS [dbo_customers] 
WHERE 1 = 1 
ORDER BY [dbo_customers].[CustomerID] ASC FOR JSON PATH, INCLUDE_NULL_VALUES
',N'@param0 nvarchar(11),@param1 nvarchar(12),@param2 nvarchar(11),@param3 nvarchar(7),@param4 nvarchar(10),@param5 nvarchar(7),@param6 nvarchar(4),@param7 nvarchar(6)',@param0=N'CompanyName',@param1=N'ContactTitle',@param2=N'ContactName',@param3=N'Country',@param4=N'CustomerID',@param5=N'Address',@param6=N'City',@param7=N'Region'

Note that the column list doesn't include the duplicate column.

It's great we can reorder which columns appear, but often we don't want all rows. Let's look at filtering next.

Filtering API Results

The WHERE clause is a filter for our queries. In the REST API, we use the $filter parameter. Like $select, this will allow us to add values to limit results. However, unlike $select, this is an expression, and the format is this:

/api/entity?$filter=column [operator] value

That's the basic structure. The operators are:

• eq - equal
• ne - not equal
• gt - greater than
• lt - less than
• ge - greater than or equal
• le - less than or equal

The value can be a number. If it is a string, enclose it in single quotes. For dates, I'll show that below. One thing to note, for a dev, they need to URLEncode their $filter parameters.

Here are a couple examples to illustrate how this works. First, let's only get one customer. We can do this with a URL that uses the eq operator and the CustomerID column. The URL would be:

http://localhost:5000/api/Customers?$filter=CustomerID eq 'ALFKI'

This returns just this customer:

Every column in dbo.Customers is a string field, so let me switch to dbo.Orders for the next example. If I want just one particular order, I can use the order ID. My URL would be:

 http://localhost:5000/api/Orders?$filter=OrderID eq 10253

Note I have spaces in my expression and URL and it works. Look at my results:

For a date range, I have a slightly different format. I can't use quotes or the OData style (datetime'yyyy-mm-dd'). Instead, I need to use a datetime offset value. I can still use a gt or ge expression. For example, dates for orders in Northwind range from 1996-07-04 to 1998-05-06. Let's get just the 1998 orders with this URL:

http://localhost:5000/api/Orders?$filter=OrderDate ge 1998-01-01T00:00:00Z

The results are here:

I can also combine multiple expressions with AND/OR/NOT, as I would in a WHERE Clause. I can also use parenthesis to group my logical expressions. In the examples below, I'm going to limit what I return and combine a few logical filters. First, let's get a limited set of data from orders that were shipped to France. The URL for this is:

http://localhost:5000/api/Orders?$filter=ShipCountry eq 'France'&$select=CustomerID,OrderID,ShipCountry,OrderDate

I now see just the date, customer, order, and country.

That's still 77 orders. Let's further limit this to just those orders with a number > 11000. I do that by adding values to the URL. Now I query with:

http://localhost:5000/api/Orders?$filter=ShipCountry eq 'France' and OrderID gt 11000&$select=CustomerID,OrderID,ShipCountry,OrderDate

I get three results.

Perhaps I also want to include a few orders from a different customer. Let's group our previous query and add an OR filter. This gives us a URL of:

http://localhost:5000/api/Orders?$filter=(ShipCountry eq 'France' and OrderID gt 11000) or CustomerID eq 'VINET'&$select=CustomerID,OrderID,ShipCountry,OrderDate

Now I get five more rows. There are 5 VINET orders, and you can see the last one with the 3 orders from the previous query.

Just as you can in SQL, you can build up some expressions here. I don't know the limits of DAB, but I would also expect these not to be too complex as this is designed to replace relatively basic CRUD operations from an application.

We have looked at limiting results in a few ways, but what about ordering? Clients can order results, and if the set is small the should, but there is power in letting the server do some of this work. Let's look at adding an ORDER BY clause.

Ordering Results

My previous results were in whatever order the server wanted to return them to me. However, if I wanted to ensure a certain order, there is an $orderby parameter in DAB. As you might expect, this equates to the ORDER BY keywords in T-SQL, and you can add a list of columns (case-sensitive). The structure is

/api/entity?$orderby=column1,column2

For my last query, I have a complex expression, but let's now order all the results by the employee that took the order. To do that, I add a new parameter and then the $orderby keyword. That give me this URL:

http://localhost:5000/api/Orders?$filter=(ShipCountry eq 'France' and OrderID gt 11000) or CustomerID eq 'VINET'&$select=CustomerID,OrderID,ShipCountry,OrderDate&$orderby=EmployeeID

My results show the VINET orders mixed in with the others that are limited by country and date. I've ordered by a column I'm not retrieving, just as I can in T-SQL.

I can add multiple columns, but I'm sure you understand how to use this parameter.

Summary

There are a few more things you can do with DAB to retrieve data, but I'll cover those in another article. In this one, we've seen how to limit the columns returned, the rows, and to order them. These equate to a column list, a WHERE clause, and an ORDER BY clause in T-SQL. Most developers likely understand these concepts well, and the only strange thing might be encoding dates with the datetimeoffset value for the parameter when used in expressions.

DAB is a way to speed up front-end development, and a way that helps DBAs control what they expose to developers. This can be a nice front end for the database, limiting what data certain applications can access without adding any complex RBAC rules with GRANT and REVOKE statements in the database. Of course, users do need to be granted permissions, but this is one further way to keep your security simple and help developers access data in a way that they are comfortable with working.

Give it a try today and experiment with the Data API Builder. It can speed up a lot of front end drudgery with those simple CRUD applications.