Writing a simple API in minutes with Azure Functions and SQL

Jun 27, 2023 | Updated: Dec 8, 2023

For a long time, developers using SQL Server have felt forgotten when it comes to Azure Functions. While Cosmos DB bindings have been available since the beginning, the equivalent SQL Server ones have been lagging.

Last month (May 2023), the General Availability of SQL Bindings for Azure functions was finally announced and I could't wait to jump into it. If you have used the CosmosDB bindings, there won’t be many surprises, they have done a good job in keeping things consistent.

To summarize, this is what they have made available:

• Input / Output bindings (General Availability) — You can use Text Commands or Stored Procedures.
• Track Changes Trigger (In Preview)
  Update Dec 6, 2023: Now Generally Available. (annoucement)

I decided to write a simple API using these new capabilities, I am working with a model Listing, and aim to create a RESTful API for it. I am using SQL Server running on a container on my machine, but this should work with Azure SQL as well.

Here we go:

Get All Listings

Using the input binding, it selects all active listings from my table.

[FunctionName("GetListings")]
public static IActionResult Run(
  [HttpTrigger(AuthorizationLevel.Function, "get", Route = "listing")] HttpRequest req,
  [Sql("SELECT * FROM [dbo].[Listings] WHERE IsActive = 1", "SqlConnectionString")] IEnumerable<Object> result,
  ILogger log)
{ 
  return new OkObjectResult(result);
}

Get Single Listing

Using the input binding again, using the route parameter (Id) to query the database.

[FunctionName("GetListing")]
public static IActionResult Run(
  [HttpTrigger(AuthorizationLevel.Function, "get", Route = "listing/{id}")] HttpRequest req,
  [Sql("SELECT * FROM [dbo].[Listings] WHERE Id = @Id", "SqlConnectionString", "@Id={id}")] IEnumerable<Object> result,
  ILogger log)
{
  return result.Count() == 0 ?
      new NotFoundResult() : 
      new OkObjectResult(result.First());
}

Create Listing

Using the output binding with an ASync collector. It's important to notice the AddItem is actually an upsert. We will use it on updates as well.

[FunctionName("CreateListing")]
public static async Task Run(
    [HttpTrigger(AuthorizationLevel.Function, "post", Route = "listing")] HttpRequest req,            
    ILogger log,
    [Sql("[dbo].[Listings]", "SqlConnectionString")] IAsyncCollector<Listing> listings)
{
    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var listing = JsonConvert.DeserializeObject<Listing>(requestBody);
    listing.Id = Guid.NewGuid();
    listing.CreatedDate = listing.UpdatedDate = DateTime.Now;
    listing.IsActive = true;
    
    await listings.AddAsync(listing);
    await listings.FlushAsync();
    
    return new CreatedResult($"/listing", listing);
}

Update Listing

Output binding again, but with some validation on the input to compare to the route param (Id). There are many ways to be creative with the update, let me say this is just a simple example.

[FunctionName("UpdateListing")]
public static async Task Run(
    [HttpTrigger(AuthorizationLevel.Function, "put", Route = "listing/{id}")] HttpRequest req,
    Guid id,
    ILogger log,
    [Sql("[dbo].[Listings]", "SqlConnectionString")] IAsyncCollector<Listing> listings)
{
    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var listing = JsonConvert.DeserializeObject<Listing>(requestBody);
    if (id != listing.Id) {
    return new BadRequestResult();
    
    }
    //prepare
    listing.UpdatedDate = DateTime.Now;
    //Update  
    await listings.AddAsync(listing);
    await listings.FlushAsync();
    
    return new OkObjectResult(listing);
}

Delete Listing

Output binding as well. Output Bindings are flexible, although I am using a SQL Text command, you can also use Stored Procedures for all of these.

[FunctionName("DeleteListing")]
public static IActionResult Run(
    [HttpTrigger(AuthorizationLevel.Function, "delete", Route = "listing/{id}")] HttpRequest req,
    [Sql("DELETE FROM [dbo].[Listings] WHERE Id = @Id", "SqlConnectionString", "@Id={id}")] IEnumerable<Object> result,
    ILogger log)
{
    return new OkResult();
}

Track Changes Trigger

You will also need to enable change tracking on your database:

ALTER DATABASE [Database]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE [dbo].[Table]
ENABLE CHANGE_TRACKING;

The Change Feed trigger basically allows you to get notified when something happens in your database, it is very useful if you are in an event-driven/distributed architecture. Here is what the code looks like:

[FunctionName("ChangeFeedTrigger")]
public static void Run(
    [SqlTrigger("[dbo].[Listings]", "SqlConnectionString")]
    IReadOnlyList> changes,
    ILogger logger)
{
    foreach (SqlChange change in changes)
    {
        Listing listing = change.Item;
        //Do your action
        logger.LogInformation($"Change operation: {change.Operation}");
        logger.LogInformation($"Id: {listing.Id}, Title: {listing.Name}");
    }
}

For this particular code, here is a sample output after I insert and then update a record:

[2023-06-23T17:29:20.743Z] Change operation: Insert
[2023-06-23T17:29:20.744Z] Id: 0e5d476f-f450-4f09-965b-54c4ae5f1c96, Title: Casa Amore - Merida

[2023-06-23T17:31:11.349Z] Change operation: Update
[2023-06-23T17:31:11.350Z] Id: 7e4e7b10-6d5d-4b02-903d-1ffc93333f55, Title: Casa Amore - Merida

That's a Wrap

That is it, pretty simple and straightforward, it took way too long for this functionality to come to Azure Functions but is finally here.

Here is my source code for your reference. Remember is just a playground 😄

Here is the official documentation and more sample code (from MS).

Last thoughts…

Every time I do a RestAPI with Azure Functions I put them behind an API Management (APIM) service. Just saying….

Hope this helps 😎