The binding source code is available here at https://github.com/davidpallmann/sql-database-binding-azurefunc-v1.
About Bindings
If you're not familiar with Function Bindings, here they are in a nutshell: instead of writing code for common operations like retrieving data or storing data, you just configure a few settings and the cloud does that work for you. There are Input Bindings that retrieve data and pass them to your function; and there are Output Bindings where your function outputs data and the binding takes care of storing it.Here's what an Input Binding does for you: it takes care of writing all the data retrieval code, including handling errors and retries. All that leaves is your code to process the data and return something.
An Output Binding works the same way, but instead of pre-fetching data for your function to work on, it post-stores data your function has generated.
You can even combine input and output bindings in the same function, which allows you to have functions that chain together: the output of function A is the input of function B, the output of B is the input of C, and so on.
Why a SQL Database Binding?
If you review the list of Microsoft-provided bindings, you'll see an impressive list. You can, as of this writing, bind to: blob storage, Cosmos DB, Azure Event Grid, Event Hubs, HTTP, Excel tables, OneDrive files, Outlook email, Mobile Apps, Notification Hubs, queue storage, SendGrid, ServiceBus, SignalR, and table storage, and WebHooks.That's an exensive list—but, relational databases are not in the collection. Maybe that's because serverless functions are often thought of in conjunction with other cloud-native services like NoSQL databases, and relational databases aren't considered a priority. Whatever the reason, I certainly recognize that there are many cloud-hosted applications that make use of relational databases, including a great many legacy applications that have been migrated.
Fortunately, there's nothing stopping you from making use of Azure Functions and accessing your relational databases. It would be nice, however, to be able to take advantage of Input Bindings and Output Bindings since they save you so much code. That's the reason I developed this new SQLDatabase binding: relational database developers also deserve a first-class serverless experience.
How it Works
To use the Azure SQL Database binding, you simply annotate your Azure Function code with the [SQLDatabase] attribute. You set a few parameters, and Voila! you're writing less function code. That may sound overly-magical, but it really is the experience. Let's see it in action.Input Bindings
You'll use an input binding when your function needs to query your database. There are 2 parameters to supply:- ConnectionString: the name of the setting that holds your database connection string, such as "ConnectionString". You'll define this setting either in your project's local.settings.json file (when running locally to test), or in the Azure Portal's Application Settings area for your Function App.
- SQLQuery: a SQL query to run, the results of which will be passed to your function. You're allowed to embed parameter names in your query such as {OrderNo} or {name}. Note: you can't use percent signs in the parameter, which is a bummer for T-SQL LIKE clauses, but you can get around that by expressing % as CHAR(37).
[FunctionName("title")]
public class TitleRequest
{
public string title { get; set; }
}
...
public static HttpResponseMessage title(HttpRequestMessage req,
[HttpTrigger] TitleRequest parameters,
[SQLDatabase(ConnectionString = "ConnectionString",
SQLQuery = "SELECT * FROM Book WHERE Title LIKE CHAR(37)+'{title}'+CHAR(37)")]
DataTable table,
TraceWriter log)
{
// Convert data table to JSON string
var objType = JArray.FromObject(table, JsonSerializer.CreateDefault(new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })); //.FirstOrDefault(); // Get the first row
var js = objType.ToString();
return new HttpResponseMessage(HttpStatusCode.OK)
{
StatusCode = HttpStatusCode.OK,
Content = new StringContent("{ \"Data\": " + js + " }", Encoding.UTF8, "application/json")
};
}
title Function, data passed in a DataTable
https://[function-name].azurewebsites.net/api/title?title=world
The [SQLDatabase] attribute defines our Input Binding. Input Bindings are smart. Notice the SQLQuery argument defines a query that contains an embedded parameter name, {title}. Because our URL had a query parameter also named title, the binding framework connects them. The query that actually executes is this:
SELECT * FROM Book WHERE Title '%world%'
When the above function code starts running, it is handed a DataTable right off the bat, in the variable named table. The only code we need to write is to work with the data and return something. After converting the DataTable to serialized JavaScript, the function returns this:
{ "Data": [
{
"Title": "Proteus in the Underworld",
"Author": "Charles Sheffield",
"Yr": "1995",
"Genre": "Science Fiction"
},
{
"Title": "Quest of the Three Worlds",
"Author": "Cordwainer Smith",
"Yr": "1966",
"Genre": "Science Fiction"
},
{
"Title": "Ringworld",
"Author": "Larry Niven",
"Yr": "1970",
"Genre": "Science Fiction"
},
{
"Title": "The Ringworld Engineers",
"Author": "Larry Niven",
"Yr": "1979",
"Genre": "Science Fiction"
}
] }
title Function Result
If you're a long-time .NET developer, getting passed a DataTable might be right up your alley. Then again, maybe you'd prefer to work with JSON; that is, after all, the most likely format you'll use to pass data around. Well, good news: the binding allows you get the data as a JSON string instead of a DataTable if that's what you want. All you have to do is change the DataTable variable declaration after the [SQLDatabase] attribute to a string parameter. This will serialize the DataTable into a JSON string (actually just a JavaScript array, not formal JSON).
If we make the above-described change, our function code gets even smaller! All that's left is the return statement. That's pretty dramatic.
[FunctionName("title")]
public static HttpResponseMessage title(HttpRequestMessage req,
[HttpTrigger] TitleRequest parameters,
[SQLDatabase(ConnectionString = "ConnectionString",
SQLQuery = "SELECT * FROM Book WHERE Title LIKE CHAR(37)+'{title}'+CHAR(37)")]
string js,
TraceWriter log)
{
return new HttpResponseMessage(HttpStatusCode.OK)
{
StatusCode = HttpStatusCode.OK,
Content = new StringContent("{ \"Data\": " + js + " }", Encoding.UTF8, "application/json")
};
}
title Function, data passed in a string
Output Bindings
Now let's see how the output binding mechanism works. You'll use an output binding when you want to add records to a table. There are 2 parameters to supply:- ConnectionString: just as before, this is the name of your setting for connection string.
- TableName: the name of the database table to add records to.
Let's imagine you need a function to accept a list of books and add records for them. You write a function named addbooks that accepts an HTTP POST body that contains a list of books. You define an output binding that will handle storing the records.
// DataTable edition - function passes a DataTable object with multiple rows to the output binding with records to add
[FunctionName("addbooks")]
public static HttpResponseMessage addbooks(HttpRequestMessage req,
[HttpTrigger] Book[] books,
[SQLDatabase(ConnectionString = "ConnectionString",
TableName = "Book", SQLQuery = "")] ICollector<DataTable> output, TraceWriter log)
{
// Create data table for output
DataTable dt = new DataTable();
dt.TableName = "Book";
dt.Clear();
dt.Columns.Add("Title");
dt.Columns.Add("Author");
dt.Columns.Add("Yr");
dt.Columns.Add("Genre");
DataRow row = null;
if (books != null)
{
foreach (Book book in books)
{
row = dt.NewRow();
row["Title"] = book.title;
row["Author"] = book.author;
row["Yr"] = book.yr;
row["Genre"] = book.genre;
dt.Rows.Add(row);
}
}
output.Add(dt);
return req.CreateResponse(HttpStatusCode.Created);
}
addbooks function
https://[function-name].azurewebsites.net/api/addbooks
[
{
"title": "Rendezvous with Rama",
"author": "Arthur C. Clarke",
"yr": "1973",
"genre": "Science Fiction"
},
{
"title": "Childhood's End",
"author": "Arthur C. Clarke",
"yr": "1953",
"genre": "Science Fiction"
},
{
"title": "2001: A Space Odyssey",
"author": "Arthur C. Clarke",
"yr": "1968",
"genre": "Science Fiction"
},
{
"title": "The Songs of Distant Earth",
"author": "Arthur C. Clarke",
"yr": "1986",
"genre": "Science Fiction"
},
{
"title": "Imperial Earth",
"author": "Arthur C. Clarke",
"yr": "1975",
"genre": "Science Fiction"
},
{
"title": "The Sentinel",
"author": "Arthur C. Clarke",
"yr": "1951",
"genre": "Science Fiction"
}
]
HTTP POST Body
The [HttpTrigger] Book[] books parameter serializes our payload into a .NET Book class for us, which we might find handy. Our code then constructs a DataTable and populates it with DataRows.
The [SQLDatabase(...) ICollector<DataTable> output parameter establishes that our function will be passed as output a collection of DataTable objects (yes, we could output multiple DataTables if we so wished). For this function we only need one. We add it to the output with output.Add(dt);.
And that's it--for our code. The function returns an HTTP Created status code. But first, the output binding will run.
Now that our function code is finished, the output binding is passed our output collection, which consists of one DataTable. Records are added to the specified table (Book), using SqlBulkCopy for good performance. If there are any duplicate key issues (a record in the collection already exists), the record won't be inserted and no error is generated.
We could make this code even smaller if we wanted to. The incoming request body could be passed as a string with JavaScript rather than a .NET object; and we could return a DataTable in a JavaScript string instead of an actual DataTable object. If we did that, our [SqlDatabase] attribute would look like this:
[SQLDatabase(...)] ICollector<string> output,
Limitations
Let's cover a few limitations of the SQLDatabase binding:
- v1 only. This is an Azure Functions v1 runtime binding. In other words, it's for .NET Framework functions and not for .NET Core functions. However, I am hoping to release a v2 .NET Core edition in the future so stay tuned.
- No percent signs in queries. You can't use percent signs in your SQLQuery parameter values; however, CHAR(37) is a work-around.
- Limited functionalty. If you need more elaborate output than adding records to a single table, you'll have to go back to writing discrete data access code.
- No triggers. While input and output bindings are useful, this is not a trigger. This binding will not allow you to trigger a function because your database data has changed.
In Conclusion
In this post, I unveiled a binding for Azure SQL Database. I wrote it so relational database developers could also benefit from bindings in their Azure Functions.While you won't always be able to use bindings—sometimes you have more complex operations that require discrete data access code—you'll find them invaluable when you can use them which will be often. Input Bindings and Output Bindings are remarkable code-savers and time-savers; and now you can use them with Azure SQL Database.
Source code on Github
No comments:
Post a Comment