External REST Endpoint Invocation in SQL Azure Now Generally Available

Microsoft recently announced the general availability (GA) of Azure SQL Database External REST Endpoint Integration – an ability to call a REST endpoint natively from Azure SQL Database.

Earlier, the company released a public preview of the Azure SQL Database feature that provides system-stored procedure sp_invoke_external_rest_endpoint, which invokes an HTTPS REST endpoint provided as an input argument to the procedure. It allows developers to call REST/GraphQL endpoints from other Azure Services from right in the Azure SQL Database. With the GA, the company enabled access to OpenAI, Azure Blob Storage, Azure Files, Azure Queue Services, and Azure Table Services. In addition, the feature also supports header and managed identity authentication, removing the option of passing passwords.

In an Azure Developer blog post, Brian Spendolini, a senior product manager of Azure SQL Database, provides an example of asking ChatGPT, "Why is the sky blue?" in a T-SQL statement that looks like this:

declare @url nvarchar(4000) = 
N'https://openaidemo1.openai.azure.com/openai/deployments/talk-to-me-goose/chat/completions?api-version=2023-03-15-preview';
declare @headers nvarchar(102) = N'{"api-key":"12345,678910,1112"}'
declare @payload nvarchar(max) = N'{"messages":[{"role":"system","content":"Why is the sky blue?"}]}'
declare int, @response nvarchar(max);

exec = sp_invoke_external_rest_endpoint 
@url = @url,
@method = 'POST',
@headers = @headers,
@payload = @payload,
@timeout = 230,
@response = @response output;

select as ReturnCode, @response as Response;

Furthermore, Spendoli outlines several use cases for the feature, such as:

• Activate workflows by sending an email using Azure Logic Apps
• For cache invalidation or update by calling an Azure Function
• Call a Durable Function to kick off some complex process

Overview of leveraging the External REST Endpoint Invocation feature in SQL Azure (Source: Microsoft Developer blog post)

With various possible use cases with the feature, the concept of calling a rest endpoint from a database is not new. For instance, in Oracle databases, it is available for some time, like in version 11g XE:

create or replace
procedure publish_cinema_event
( p_room_id in varchar2
, p_party_size in number
) is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'http://localhost:9002/cinema';
  name varchar2(4000);
  buffer varchar2(4000); 
  content varchar2(4000) := '{"room":"'||p_room_id||'", "partySize":"'||p_party_Size||'"}';
 
begin
  req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
  utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
  utl_http.set_header(req, 'content-type', 'application/json'); 
  utl_http.set_header(req, 'Content-Length', length(content));
 
  utl_http.write_text(req, content);
  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin
    loop
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
end publish_cinema_event;

For developers, the feature brings new options to build data integrations. In a Data Exposed episode, "Calling a REST service from Azure SQL DB," Davide Mauri, principal product manager - Azure SQL DB at Microsoft, concluded:

You can have really full integration from Azure SQL with everything in just one line of code, which makes developers super-efficient.

In addition, in a tech community blog post on the feature, @gmedlin commented in the comment section:

This is awesome! SendGrid for Azure would be my top choice for External REST Endpoint Invocation. With that, we wouldn't have to call a function or logic app to send emails.

With the company responding:

Thanks for the comment, @gmedlin, we will be sure to take a look at that. We are looking at enabling Azure Communication Services soon as well.

Lastly, more examples of leveraging the External REST Endpoint Invocation feature of SQL Azure are available in a GitHub repo.