Azure / data-api-builder

Data API builder provides modern REST and GraphQL endpoints to your Azure Databases and on-prem stores.
https://aka.ms/dab/docs
MIT License
953 stars 197 forks source link

⭐ [Enhancement]: Stored Procedure Parameters #2365

Open JerryNixon opened 2 months ago

JerryNixon commented 2 months ago

What is it?

Output scenario

This demonstrates an output parameter.

CREATE PROCEDURE [dbo].[GetBooks]
    @count INT OUTPUT
AS
    SELECT *, @count = COUNT(*) OVER()  
    FROM dbo.Books;

Note how the result set from the SELECT statement is combined with the output parameter itself.

Current Configuration

Current configuration supports parameters and their default values - only.

{
  "entities": [
    {
      "<entity-name>": {
        ...
        "source": {
          ...
          "parameters": {
              "<parameter-name>": "<parameter-default>",
              "<parameter-name>": "<parameter-default>",
              "<parameter-name>": "<parameter-default>"
            }
...

Future Configuration

New object type provides more flexibility. This is an additive/alternative change.

{
  "entities": [
    {
      "<entity-name>": {
        ...
        "metadata": {
          "description": "<string>" (optional default: null)
        },
        "source": {
          ...
          "parameters": [ // current hierarchy position 
           {
              "name": "<parameter-name>",
              "alias": "<string>" (optional default: <parameter-name>),
              "description": "<string>" (optional default: null),
              "data-type": "<string>" (optional default: <string>), 
              "required": "true | false" (optional default: true),
              "default": "<string>" (optional default: null)
              "kind": "input | output | input-output | error-provider", (optional default: input)
           }
...

Description

Metadata Description is used to enhance both the OpenAPI and GraphQL Schema.

Maybe this means views and tables get description. We will have to see.

CLI impact

Though this may change to align with ongoing work, this is pretty close.

How do we delete a parameter from the CLI?

Output parameter

This supports both SELECT and OUTPUT params at the same time.

Example (page count)

CREATE PROCEDURE [dbo].[GetBooksPaginated]
    @PageSize INT,
    @PageNumber INT,
    @PageCount INT OUTPUT
AS
BEGIN

    DECLARE @TotalCount INT;
    SELECT @TotalCount = COUNT(*) FROM dbo.Books;
    SET @PageCount = CEILING(@TotalCount * 1.0 / @PageSize);

    SELECT * FROM dbo.Books ORDER BY Id
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;

END;

Output payload (GraphQL)

TBD

Output payload (REST)

{
    "value": [
        {
            "Id": 1,
            "Name": "William Shatner",
            "BirthYear": 1931
        }
    ],
    "output-params": [
        {
            "Name": "PageCount",
            "Value": 123
        }
    ],
    "errors": [] // see below
}

Database parity

Database Supports Output Params
SQL Server Yes
MySQL Yes
PostgreSQL Yes
Cosmos DB (SQL) No (Return values through JavaScript)

Error-Provider

Today, stored procedures cannot raise an error in a controlled way.

Today's error format (REST)

{
    "error": {
        "code": "DatabaseOperationFailed",
        "message": "Invalid column name 'Id'.\r\nInvalid column name 'Id'.",
        "status": 500
    }
}

Today's error format (GQL)

{
  "errors": [
    {
      "message": "Invalid column name 'Id'.\r\nInvalid column name 'Id'.",
      "extensions": {
        "code": "DatabaseOperationFailed"
      }
    }
  ]
}

Future error format (REST)

The original error remains - it will return the First() from the new errors array.

{
    "value": [],
    "output-params": [],
    "errors": [
      {
          "code": "DatabaseOperationFailed", // optional
          "message": "Invalid column name 'Id'.\r\nInvalid column name 'Id'.", // required
          "status": 500 // optional
      }
    ]
}

Future error format (GQL)

No change.

Raising errors versus returning errors

This section shows how returning errors is better than raising them. Reasons:

  1. Allows multiple errors.
  2. Keeps execution flow.
  3. Consistent format for APIs.
  4. Customizable messages.

Raising errors

CREATE PROCEDURE [dbo].[GetBooks]
    @count INT OUTPUT
AS
BEGIN
    SELECT *, @count = COUNT(*) OVER()  
    FROM dbo.Books;

    IF @count < 10
        RAISERROR('The number of books is less than 10. Operation aborted.', 16, 1);
END;

(versus) Returning errors

Effectively, an error-provider is an output parameter with a JSON payload of errors.

CREATE PROCEDURE [dbo].[GetBooks]
    @count INT OUTPUT,
    @ErrorMessage NVARCHAR(MAX) OUTPUT  
AS
BEGIN

    IF @count < 10
    BEGIN

        -- error 
        DECLARE @ErrorTable TABLE (code NVARCHAR(50), message NVARCHAR(255), status INT);

        INSERT INTO @ErrorTable (code, message, status)
        VALUES ('DatabaseOperationFailed', 'The number of books is less than 10. Operation aborted.', 500);

        SELECT @ErrorMessage = (SELECT * FROM @ErrorTable FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);

        RETURN; -- Abort 

    END

    SELECT *, @count = COUNT(*) OVER()  
    FROM dbo.Books;

END;

Error result

{
    "errors": [
      {
          "code": "DatabaseOperationFailed",
          "message": "The number of books is less than 10. Operation aborted.",
          "status": 500
      }
    ]
}

Questions

  1. Can errors be returned WITH data? Yes.
  2. Should status in an error impact the returned HTTP status? Return 200 (REST & GQL)
  3. Let's make sure we log the non-generic error and return the generic error.
  4. Custom errors are not censored.

Related Issues to Close

pingu2k4 commented 2 months ago

I would love to have this. My primary use case currently, is requesting paginated data from a SP for a datagrid, but also wanting to know the total count (also, given filter and sort data).

Am currently achieving this with 2 SP's, and 2 queries - but being able to set an output param with the total count for the given filter would be much more preferable.

JerryNixon commented 1 month ago

Should this be REST only? @michaelstaib GraphQL have output params on the roadmap?

abhishekkumams commented 1 month ago

Good to have a list of validations that needs to be performed with the above CLI commands.

abhishekkumams commented 1 month ago

Note: Individual value returned by the output parameter will always be scalar, so there's no need for us to have an array of value inside output params. No database supports a table type return value in output parameters today.

abhishekkumams commented 1 month ago

@JerryNixon, Can you add some more details on each of these values :

"parameters": [ // current hierarchy position 
{
...
    "kind": "input | output | input-output | error-provider", (optional default: input)
}