/azure-functions-sql-extension

Azure Functions trigger and bindings for SQL

Primary LanguageC#MIT LicenseMIT

SQL Server Extension for Azure Functions

This repository contains extension code for SQL Server input and output bindings. Trigger bindings are coming soon.

Input and Output Binding

Quick Start

.NET Function App

  1. Add MyGet package feed.

    dotnet nuget add source https://www.myget.org/F/azure-appservice/api/v3/index.json

  2. Create a function app.

    mkdir MyApp
cd MyApp
func init --worker-runtime dotnet

  3. Install the extension.

    dotnet add package Microsoft.Azure.WebJobs.Extensions.Sql --version 1.0.0-preview1

  4. See samples below for information on how to use the binding.

Input Binding Samples

The input binding takes four arguments

  • CommandText: Passed as a constructor argument to the binding. Represents either a query string or the name of a stored procedure.
  • CommandType: Specifies whether CommandText is a query (System.Data.CommandType.Text) or a stored procedure (System.Data.CommandType.StoredProcedure)
  • Parameters: The parameters to the query/stored procedure. This string must follow the format "@param1=param1,@param2=param2" where @param1 is the name of the parameter and param1 is the parameter value. Each pair of parameter name, parameter value is separated by a comma. Within each pair, the parameter name and value is separated by an equals sign. This means that neither the parameter name nor value can contain "," or "=". To specify a NULL parameter value, do "@param1=null,@param2=param2". To specify an empty string as a value, do "@param1=,@param2=param2", i.e. do not put any text after the equals sign of the corresponding parameter name. This argument is auto-resolvable (see Query String examples).
  • ConnectionStringSetting: Specifies the name of the app setting that contains the SQL connection string used to connect to a database. The connection string must follow the format specified here.

The following are valid binding types for the result of the query/stored procedure execution:

  • IEnumerable: Each element is a row of the result represented by T, where T is a user-defined POCO, or Plain Old C# Object. T should follow the structure of a row in the queried table. See the Query String section for an example of what T should look like.
  • IAsyncEnumerable: Each element is again a row of the result represented by T, but the rows are retrieved "lazily". A row of the result is only retrieved when MoveNextAsync is called on the enumerator. This is useful in the case that the query can return a very large amount of rows.
  • String: A JSON string representation of the rows of the result (an example is provided here).
  • SqlCommand: The SqlCommand is populated with the appropriate query and parameters, but the associated connection is not opened. It is the responsiblity of the user to execute the command and read in the results. This is useful in the case that the user wants more control over how the results are read in. An example is provided here.

The repo contains examples of each of these binding types here. A few examples are also included below.

Query String

The input binding executes the "select * from Products where Cost = @Cost" query, returning the result as an IEnumerable<Product>, where Product is a user-defined POCO. The Parameters argument passes the {cost} specified in the URL that triggers the function, getproducts/{cost}, as the value of the @Cost parameter in the query. CommandType is set to System.Data.CommandType.Text, since the constructor argument of the binding is a raw query.

[FunctionName("GetProducts")]
  public static IActionResult Run(
      [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts/{cost}")]
      HttpRequest req,
      [Sql("select * from Products where Cost = @Cost",
          CommandType = System.Data.CommandType.Text,
          Parameters = "@Cost={cost}",
          ConnectionStringSetting = "SqlConnectionString")]
      IEnumerable<Product> products)
  {
      return (ActionResult)new OkObjectResult(products);
  }

Product is a user-defined POCO that follows the structure of the Products table. It represents a row of the Products table, with field names and types copying those of the Products table schema. For example, if the Products table has three columns of the form

  • ProductID: int
  • Name: varchar
  • Cost: int

Then the Product class would look like

public class Product
{
    public int ProductID { get; set; }

    public string Name { get; set; }

    public int Cost { get; set; }

}

Empty Parameter Value

In this case, the parameter value of the @Name parameter is an empty string.

[FunctionName("GetProductsNameEmpty")]
  public static IActionResult Run(
      [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts-nameempty/{cost}")]
      HttpRequest req,
      [Sql("select * from Products where Cost = @Cost and Name = @Name",
          CommandType = System.Data.CommandType.Text,
          Parameters = "@Cost={cost},@Name=",
          ConnectionStringSetting = "SqlConnectionString")]
      IEnumerable<Product> products)
  {
      return (ActionResult)new OkObjectResult(products);
  }

Null Parameter Value

If the {name} specified in the getproducts-namenull/{name} URL is "null", the query returns all rows for which the Name column is NULL. Otherwise, it returns all rows for which the value of the Name column matches the string passed in {name}

[FunctionName("GetProductsNameNull")]
  public static IActionResult Run(
      [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts-namenull/{name}")]
      HttpRequest req,
      [Sql("if @Name is null select * from Products where Name is null else select * from Products where @Name = name",
          CommandType = System.Data.CommandType.Text,
          Parameters = "@Name={name}",
          ConnectionStringSetting = "SqlConnectionString")]
      IEnumerable<Product> products)
  {
      return (ActionResult)new OkObjectResult(products);
  }

Stored Procedure

SelectsProductCost is the name of a procedure stored in the user's database. In this case, CommandType is System.Data.CommandType.StoredProcedure. The parameter value of the @Cost parameter in the procedure is once again the {cost} specified in the getproducts-storedprocedure/{cost} URL.

[FunctionName("GetProductsStoredProcedure")]
  public static IActionResult Run(
      [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts-storedprocedure/{cost}")]
      HttpRequest req,
      [Sql("SelectProductsCost",
          CommandType = System.Data.CommandType.StoredProcedure,
          Parameters = "@Cost={cost}",
          ConnectionStringSetting = "SqlConnectionString")]
      IEnumerable<Product> products)
  {
      return (ActionResult)new OkObjectResult(products);
  }

IAsyncEnumerable

Using the IAsyncEnumerable binding generally requires that the Run function be async. It is also important to call DisposeAsync at the end of function execution to make sure all resources used by the enumerator are freed.

public static async Task<IActionResult> Run(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts-async/{cost}")]
     HttpRequest req,
    [Sql("select * from Products where cost = @Cost",
         CommandType = System.Data.CommandType.Text,
         Parameters = "@Cost={cost}",
         ConnectionStringSetting = "SqlConnectionString")]
     IAsyncEnumerable<Product> products)
{
    var enumerator = products.GetAsyncEnumerator();
    var productList = new List<Product>();
    while (await enumerator.MoveNextAsync())
    {
        productList.Add(enumerator.Current);
    }
    await enumerator.DisposeAsync();
    return (ActionResult)new OkObjectResult(productList);
}

Output Binding Samples

The output binding takes a list of rows to be upserted into a user table. If the primary key value of the row already exists in the table, the row is interpreted as an update, meaning that the values of the other columns in the table for that primary key are updated. If the primary key value does not exist in the table, the row is interpreted as an insert. The upserting of the rows is batched by the output binding code.

The output binding takes two arguments

  • CommandText: Passed as a constructor argument to the binding. Represents the name of the table into which rows will be upserted.
  • ConnectionStringSetting: Specifies the name of the app setting that contains the SQL connection string used to connect to a database. The connection string must follow the format specified here.

The following are valid binding types for the rows to be upserted into the table:

  • ICollector/IAsyncCollector: Each element is a row represented by T, where T is a user-defined POCO, or Plain Old C# Object. T should follow the structure of a row in the queried table. See the Query String for an example of what T should look like.
  • T: Used when just one row is to be upserted into the table.
  • T[]: Each element is again a row of the result represented by T. This output binding type requires manual instantiation of the array in the function.

The repo contains examples of each of these binding types here. A few examples are also included below.

ICollector/IAsyncCollector

When using an ICollector, it is not necessary to instantiate it. The function can add rows to the ICollector directly, and its contents are automatically upserted once the function exits.

[FunctionName("AddProductsCollector")]
public static IActionResult Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "addproducts-collector")] HttpRequest req,
[Sql("Products", ConnectionStringSetting = "SqlConnectionString")] ICollector<Product> products)
{
   var newProducts = GetNewProducts(5000);
   foreach (var product in newProducts)
   {
       products.Add(product);
   }
   return new CreatedResult($"/api/addproducts-collector", "done");
}

It is also possible to force an upsert within the function by calling FlushAsync() on an IAsyncCollector

[FunctionName("AddProductsAsyncCollector")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "addproducts-asynccollector")] HttpRequest req,
[Sql("Products", ConnectionStringSetting = "SqlConnectionString")] IAsyncCollector<Product> products)
{
    var newProducts = GetNewProducts(5000);
    foreach (var product in newProducts)
    {
        await products.AddAsync(product);
    }
    // Rows are upserted here
    await products.FlushAsync();
    
    newProducts = GetNewProducts(5000);
    foreach (var product in newProducts)
    {
        await products.AddAsync(product);
    }
    return new CreatedResult($"/api/addproducts-collector", "done");
}

Array

This output binding type requires explicit instantiation within the function body. Note also that the Product[] array must be prefixed by out when attached to the output binding

[FunctionName("AddProductsArray")]
public static IActionResult Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "addproducts-array")]
    HttpRequest req,
[Sql("dbo.Products", ConnectionStringSetting = "SqlConnectionString")] out Product[] output)
{
    // Suppose that the ProductID column is the primary key in the Products table, and the 
    // table already contains a row with ProductID = 1. In that case, the row will be updated
    // instead of inserted to have values Name = "Cup" and Cost = 2. 
    output = new Product[2];
    var product = new Product();
    product.ProductID = 1;
    product.Name = "Cup";
    product.Cost = 2;
    output[0] = product;
    product = new Product();
    product.ProductID = 2;
    product.Name = "Glasses";
    product.Cost = 12;
    output[1] = product;
    return new CreatedResult($"/api/addproducts-array", output);
}

Single Row

When binding to a single row, it is also necessary to prefix the row with out

[FunctionName("AddProduct")]
public static IActionResult Run(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "addproduct")]
    HttpRequest req,
[Sql("Products", ConnectionStringSetting = "SqlConnectionString")] out Product product)
{
    product = new Product
    {
        Name = req.Query["name"],
        ProductID = int.Parse(req.Query["id"]),
        Cost = int.Parse(req.Query["cost"])
    };
    return new CreatedResult($"/api/addproduct", product);
}

Trigger Binding

NOTE: THE MYGET PACKAGE DOES NOT CURRENTLY CONTAIN TRIGGER BINDING FUNCTIONALITY

SQL Setup

The trigger binding uses SQL's change tracking functionality to monitor a user table for changes. As such, it is necessary to enable change tracking on the database and table before using the trigger binding.

To enable change tracking on the database, run

ALTER DATABASE AdventureWorks2012  
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

The CHANGE_RETENTION parameter specifies for how long changes are kept in the change tracking table. In this case, if a row in a user table hasn't experienced any new changes for two days, it will be removed from the associated change tracking table. The AUTO_CLEANUP parameter is used to enable or disable the clean-up task that removes stale data. More information about this command is provided here.

To enable change tracking on the table, run

ALTER TABLE Person.Contact  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)

The TRACK_COLUMNS_UPDATED feature being enabled means that the change tracking table also stores information about what columns where updated in the case of an UPDATE. Currently, the trigger binding does not make use of this additional metadata, though that functionality could be added in the future. More information about this command is provided here.

The trigger binding needs to have read access to the table being monitored for changes as well as to the change tracking system tables. It also needs write access to an az_func schema within the database, where it will create additional worker tables to process the changes. Each user table will thus have an associated change tracking table and worker table. The worker table will contain roughly as many rows as the change tracking table, and will be cleaned up approximately as often as the change table.

Samples

The trigger binding takes two arguments

  • TableName: Passed as a constructor argument to the binding. Represents the name of the table to be monitored for changes.
  • ConnectionStringSetting: Specifies the name of the app setting that contains the SQL connection string used to connect to a database. The connection string must follow the format specified here.

The following are valid binding types for trigger binding

  • IEnumerable<SqlChangeTrackingEntry<T>>: Each element is a SqlChangeTrackingEntry, which stores change metadata about a modified row in the user table as well as the row itself. In the case that the row was deleted, only the primary key values of the row are populated. The user table row is represented by T, where T is a user-defined POCO, or Plain Old C# Object. T should follow the structure of a row in the queried table. See the Query String section for an example of what T should look like. The two fields of a SqlChangeTrackingEntry are the Data field of type T which stores the row, and the ChangeType field of type SqlChangeType which indicates the type of operaton done to the row (either an insert, update, or delete).

Any time changes happen to the "Products" table, the function is triggered with a list of changes that occurred. The changes are processed sequentially, so the function will be triggered by the earliest changes first.

[FunctionName("ProductsTrigger")]
public static void Run(
    [SqlTrigger("Products", ConnectionStringSetting = "SqlConnectionString")]
    IEnumerable<SqlChangeTrackingEntry<Product>> changes,
    ILogger logger)
{
    foreach (var change in changes)
    {
        Product product = change.Data;
        logger.LogInformation($"Change occurred to Products table row: {change.ChangeType}");
        logger.LogInformation($"ProductID: {product.ProductID}, Name: {product.Name}, Price: {product.Cost}");
    }
}

Contributing

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.