[Feature]: Support for table-valued parameters

[niphlod]

Hello all,
would you be interested in supporting table-valued parameters in stored procedures ?

Right now only "default" IN parameters are supported, and if the stored procedure definition has a TVP parameter, DAB can't build the proper type.

I traced the culprit till Azure.DataApiBuilder.Core.Services.FillSchemaForStoredProcedureAsync, which ends up (at least for SQL source) to SqlToCLRType erroring out.

I have some code that can build a DataTable definition out of a tvp dinamically, but I'm unsure on how to proceed further as ParameterDefinition points to a Type which ... gets then used to build the interface ?

[JerryNixon]

In all reality, this is already possible by simply passing JSON to a string parameter. No?

Procedure

CREATE OR ALTER PROCEDURE dbo.ProcessCrewJson
    @crewJson NVARCHAR(MAX)
AS
BEGIN
    DECLARE 
        @firstName NVARCHAR(100),
        @lastName NVARCHAR(100),
        @rank NVARCHAR(50);

    SET @firstName = JSON_VALUE(@crewJson, '$.firstName');
    SET @lastName  = JSON_VALUE(@crewJson, '$.lastName');
    SET @rank      = JSON_VALUE(@crewJson, '$.rank');

    SELECT 
        @firstName AS FirstName,
        @lastName AS LastName,
        @rank AS Rank;
END;

dab-config.json (fragment)

{
  "entities": {
    "ProcessCrewJson": {
      "source": {
        "object": "dbo.ProcessCrewJson",
        "type": "stored-procedure"
        "parameters": {
          "crewJson": "string"
        }
      }
    }
  }
}

Call it

curl -X POST https://your-api-host/api/ProcessCrewJson \
  -H "Content-Type: application/json" \
  -d '{"crewJson": "{\"firstName\":\"James\", \"lastName\":\"Kirk\", \"rank\":\"Captain\""}'

[niphlod]

Hi @JerryNixon , thanks for the reply and sorry for the delay.

Yes, this is "doable" via json, but it's ... "suboptimal" ?

Maybe your example is too contrived, and a better example is to use a LIST of "records", e.g.

CREATE OR ALTER PROCEDURE dbo.ProcessCrewJson
    @crewJson NVARCHAR(MAX)
AS
BEGIN
    
	SELECT
		FirstName,
		LastName,
		[Rank]
	FROM OPENJSON(@crewJson, '$') WITH (
		FirstName	VARCHAR(100)	'$.firstName',
		LastName	VARCHAR(100)	'$.lastName',
		[Rank]		VARCHAR(100)	'$.rank'
	)

END;

vs

--DROP TYPE IF EXISTS dbo.Crew
CREATE TYPE dbo.Crew AS TABLE (
	firstName	VARCHAR(100),
	lastName	VARCHAR(100),
	[rank]		VARCHAR(100)
)

CREATE OR ALTER PROCEDURE dbo.ProcessCrewJsonWithTVP
    @crew dbo.Crew READONLY
AS
BEGIN
	SELECT
		FirstName = c.firstName,
		LastName = c.lastName,
		[Rank] = c.[rank]
	FROM @crew c;
END

Lemme also try and explain what are pros and cons here, and my own personal opinion on what DAB does (and should do).

I see Data Api Builder as "the thing" that turns my backend into an interface.
It does already a tremendously good job in creating interfaces at runtime for tables and views.
It's an "accelerator": it provides the cross-cutting concern piece of the code of exposing data sets via HTTP, turning tables into "json structures" (json-ifyed) ready to be consumed.

Tables define a "contract" that gets both ONE input and ONE output: the input gets "declined" into "getters" and "setters" and the output is a "list of records" that matches the table structure.

Views define a "similar" contract, but it lacks the "setter" interface on the input that table have.

Stored procedures are the "real API" of the backend:

• they define the contract for the input ("in" parameters)
• they can define also additional output "things" ("out" parameters)
• they can define more than one resultset (and DAB already picks up the first one)

Back to the aforementioned example, the "problem" of "reducing" dbo.ProcessCrewJson to work shredding json WITHIN the sproc is that you "loose" the "input" contract: when exposed by DAB (let's talk REST just to make it simpler) you get this for the input

for the output, you get a richer

This "doesn't accelerate" (read: let the people using DAB, call them "the frontend team") hooking the sproc via DAB: you get a "crewJson" string in there, and the "frontend team" now needs to encode the payload as a string, e.g. turning

[
	{
		"firstName" : "James",
		"lastName" : "Kirk",
		"rank" : "Captain"
	},
	{
		"firstName" : "Harry",
		"lastName" : "Kim",
		"rank" : "Ensign"
	}
]

into

{"crewJson": "[{\"firstName\":\"James\",\"lastName\":\"Kirk\",\"rank\":\"Captain\"},{\"firstName\":\"Harry\",\"lastName\":\"Kim\",\"rank\":\"Ensign\"}]"

Someone else (read "not DAB") needs to tell them that crewJson accepts an array of objects whose properties are "firstName, lastName, rank".

If support for TVP is introduced, this would turn dbo.ProcessCrewJsonWithTVP input into

and the usual output as

The "frontend team" now knows "more" about the input param (directly via DAB), and they can "natively" pass down a proper list, without special encoding

{
    "crew": [
        {
            "firstName": "James",
            "lastName": "Kirk",
            "rank": "Captain"
        },
        {
            "firstName": "Harry",
            "lastName": "Kim",
            "rank": "Ensign"
        }
    ]
}

Lets keep this issue strictly about in parameters defined as TVPs, but the same shortcomings are there for "output" parameters, too (DAB "could do better" and add to the "input contract" the parameters (at least for mssql, "output" are "inputoutput") and add to the "output contract" the relevant properties).

edit: I see output params are already addressed in #2365

[JerryNixon]

You make an interesting observation. We could handle this a few ways. With the ultimate goal being contract validation at the API level, one interesting and entirely feasible approach would be to expand how parameters are provided to procedures—something we are already planning—to support a JSON data type as input—something we are not planning—along with an optional JSON Schema for validation before downstream processing.

Current procedure syntax

A parameter is a single value, which is quite limiting.

{
  "entities": {
    "getCustomerOrders": {
      "parameters": {
        "customerId": "number"
      }
    }
  }
}

Current direction we're going

We're changing each parameter to be an object so we can include metadata.

{
  "entities": {
    "getCustomerOrders": {
      "parameters": {
        "customerId": {
          "type": "number",
          "required": true,
          "description": "This is the customer ID"
        }
      }
    }
  }
}

What we might do to enable the input you're suggesting

We could introduce a special type called json that supports an optional json-schema property for validation.

{
  "entities": {
    "getCustomerOrders": {
      "parameters": {
        "payload": {
          "type": "json", // special
          "required": true,
          "description": "JSON payload with filter criteria",
          "json-schema": "./filename.json or http://server/filename.json"
        }
      }
    }
  }
}

Because Data API builder is intended to support parity across data sources including SQL Server, Postgres, MySQL, and CosmosDB, this design enables a flexible solution that does not constrain implementation to one specific backend. This also allows far more capability than simply a table type. Not sure about how we would prioritize this, but certainly see potential value. You are, however, the only customer to request this.

Could you tell me more about your specific use case? You can email me at jerry dot nixon at microsoft dot come if you want to include a lot of detail and not post it here. But discussion on the feature itself, not your use case should stay here since DAB is open source and getting community input is important to us.

By the way, serializing objects to JSON is far too easy to let that "work" motivate more complexity.

One more thing

I acknowledge there is a possible solution specific to table-valued parameters that would work only for SQL Server (as you know, SQL is the only database that supports this advanced type), and it might look like this:

{
  "entities": {
    "getCustomerOrders": {
      "parameters": {
        "payload": {
          "type": "tvp", // special
          "required": true,
          "description": "JSON payload with filter criteria",
          "tvp-row-schema": {
            "Id": "number",
            "Name": "string",
            "Date": "string",
            "Enabled": "boolean"
          }
        }
      }
    }
  }
}

Don't get me wrong, I understand the simplicity and appeal of this approach—it wouldn’t require the cognitive load of creating and managing a full JSON Schema. That said, this would only work with SQL Server, and introducing it would conflict with our goals for cross-source parity.

Still, I see the possibility.

We'll see if anyone else comments.

[niphlod]

Hi @JerryNixon , thanks for the reply.

I must admit I'm no Oracle, MySQL or CosmosDB guy, but Postgres has "composite arrays" that may express the same "need".

As for the usecase, let's restart from the basics:

• DAB is "an extension" that lets us turn "Database Dialects" into "HTTP calls", potentially avoiding dealing with db drivers and SDKs in "higher level apps"
• DAB should strive to let people working on "Database" export readily-available "things" to "HTTP calls"
• "Database people" are already used to define an "Application Programming Interface" via stored procedures/functions
• DAB should "reuse" all the information available to generate its own "Application Programming Interface"s
• potentially one "API" (stored proc on the database) and the other (HTTP exposed via DAB) are supposed to be generated without repeating the same information, allowing for faster "time to button" scenarios AND avoiding remembering to change "both contracts" in two places (one contract is the definition of the sproc, the other is the definition of the parameter on DAB's config file)
• TVPs (or arrays in general) shouldn't require to be "double quoted" as a string to make things work
• DAB supports "relational" and "document" dbs: relational ones often describe data as a two dimensional-matrix ("set")
• Right now (situation is improving with recent releases) turning relational dbs into spitting hierarcal data is still difficult and support is spotty at best (e.g. Azure SQL and 2025 have a "json" data type but is far from being easily resurfaced, and once you turn json, you basically "loose" the "contract", allowing for basically any shape)

tl;dr : I see DAB as a contract resolver between APIs at a db layer and APIs at the HTTP one.

Your idea to "augment" the config file describing "the contract/data shape" for inputs could be extended to outputs (both resultsets and parameters) too, e.g. solving #444 .

Remaining "DRY" could be assured in two phases (didn't think about that at first):

• at runtime (which is what I was trying to implement, and I reached a viable way to generate just the REST interface dynamically, sadly not yet the real execution which threw me off a bit with ADO which I'm not very well versed into ^_^ )
• at "config time" e.g. via dab add, inspecting the stored procedure at "add time" and generating a verbose(r) json config file that could help the runtime generator (and the executor) to "translate" http to db calls

One could also then manually edit the config file for "things hard or impossible to reverse-engineer from the backend", like e.g. a full blown json schema that is impossible to derive from the backend at runtime.

I think the "really valid point" in being able to specify "arrays" (TVPs/"composite arrays"/etc) as inputs enable to turn RBAR (Row By Agonizing Row) approaches to set based ones.

I'm thinking to the usual app that needs to enable CRUD on the usual "grid".
Let's also assume one may want to edit 3 records and save them as a batch.
Right now I'm "forced" to do a little dance:

• prepare a "staging table" which is a copy of the "real one" (with the same columns or just the ones needed to be updated)
• expose the "staging table" via DAB
• re-forge/re-invent constraints on the "staging table" according to business rules stored into "the real one"
• the app sends one GET to fill the grid
• the app caches on the client the grid, lets the user edit 3 records
• the app needs to send 3 HTTP calls to DAB (one-by-one)
• if all goes okay (e.g. no constraints errored out on each of the three rows) data is now "ready" on the "staging table"
• via Change Feed/Data Capture/Change Tracking/RowId/Timestamp someone else figures out "the staging has been updated" and transfers the data to the "real one". Or I need to publish a "data is now ready" stored procedure to be pinged by the client when its sure all 3 records have been submitted successfully
• compensations may need to be developed (e.g. what happens if 2 records went okay and 1 errored out ?)

The alternative is to publish for the client app dev a procedure with an "obscure" parameter to document "separately" that needs to be a string-ified representation of an array in json, and doing the validation within the stored procedure (e.g. validating that "age" of a crew member is not a guid ^_^), and find a way to resurface either an error message or an exception back to the client app.
So, far from a "native"/"natural" approach that DAB has for other entities.

If DAB had the possibility to expose a sproc whose input is a TVP instead:

• DAB exposes the "real table" in readonly (or a view)
• DAB exposes the "stored proc" with a clear interface
• the app sends one GET to fill the grid
• the app lets the user edit 3 records
• the app sends an array of 3 records in one http call to DAB
• the schema is "enforced" (yeah, "age" could still be invalid, like a negative value, but at least is not a guid)
• DAB could enforce the schema before calling the db, but the db is able to "validate" the data against the TVP definition and bomb out before executing any piece of the stored procedure
• atomically the sproc updates the data on the "real table", enforcing "more" constraints where needed (positive age), they either pass as a whole or nothing changes
• if all goes okay everything is already in the correct place, if something goes wrong "real table" is still in a consistent state

[JerryNixon]

Lots there—let’s continue.

You might benefit from GraphQL’s ability to insert multiple records in a single mutation, wrapped in a single database transaction. This means all three records must succeed for any of them to be committed.

That’s something you can do today.

Next, we should talk about bulk insert. This is a REST feature that allows you to pass an array of objects in the request payload instead of a single object. It’s not exactly what you're asking for, but it’s similar.

To make it work in your case, we would need the bulk insert interface with a flag or parameter to indicate that it should not use the optimized bulk insert operation but instead wrap all the inserts in a single transaction and execute them in series.

We don’t have this on our backlog today, but outside contributors often look for small features to take on, and I bring this one up regularly. So while it’s not planned, it could show up at any time.

Then again, you’re a developer, right? Interested in contributing? If you get it working, our engineers can take it across the finish line (with a security review, tests, and everything else). Let me know. If you're interested, we can spec out a design to guide your work.

Outside of that, I still like the idea of adding JSON schema to input parameters. Like you said, applying schema to outbound fields is interesting too, though I’m not 100% sure on that yet.

Now back to TVPs. The complexity grows when you consider how to reference a custom type in SQL. Defining a parameter as an array of types is simple in the config file, but much more complex in the engine.

But here’s a question: would accepting an array payload on a single REST input to perform multiple inserts solve your problem?

And, if so, why not use GraphQL today and just get it done? Or wanna submit a PR?

[niphlod]

You might benefit from GraphQL’s ability to insert multiple records in a single mutation, wrapped in a single database transaction. This means all three records must succeed for any of them to be committed.

That’s something you can do today.

Sure, albeit never tried on DAB it should work out of the box. From the docs I gather that even this (tagged as "multi-mutation") right now is only valid for mssql (I'm bringing this up because you mentioned "parity accross data sources").
Before opening this issue I checked ( here ) and I see even now stored procedures and sp parameters are only available for mssql, that's why "parity accross data sources" wasn't in my mind originally

Next, we should talk about bulk insert

Did I miss a feature ?
I don't see anything ready-to-use so, should we talk about bulk 😉 ?
Bulk insert (mssql), copy (postgresql/oracle?), load (mysql), bulk execution (for comosdb) expose IMHO wildly different APIs.
I don't see DAB being the correct tool to do heavy data ingestion: how large can a body be before it begins to look "too much to handle" ? json is rather verbose, even minified 100k records (which is a drop into the ocean for a proper bulk) account for a 10MB body.

Maybe a better term for this "new feature" would be "batch inserts", which is more or less "execute this n times, each time with a different set of parameters".

"Batch inserts" could solve the issue on the REST side of things when GraphQL "Multi-Mutation" are not available, or at least achieve "feature parity" to avoid an RBAR approach. I think by design they should be wrapped in a single transaction, as I don't see why going for separate ones when you can do an RBAR approach "as-is".

Edit: one point against "batch inserts" would be latency. Each insert with a different set of "single-row parameter" that DAB multiplexes even in a single transaction need to do a round-trip while a "proper bulk" approach or a TVP/json-parameter storedprocedure need to do a SINGLE roundtrip.

Now back to TVPs. The complexity grows when you consider how to reference a custom type in SQL

I wasn't suggesting supporting any user defined type within TVPs, and to be fair I don't see any support for user defined type in "simple" parameters anywhere so ... I was shooting for a middle ground where you can use TVPs only with "standard" types

would accepting an array payload on a single REST input to perform multiple inserts solve your problem?

It solves the issue of avoiding an RBAR approach but doesn't solve another side, which is the need to expose "staging tables" in order to do validation plus a "signal" sproc to inform that the modification has been done (and takes charge of doing "heavy validation" plus moving data from "staging" to "real" table).
It'll probably solve 20% of the usecases (small, unrelated, "simple" tables) but leave 80% out of the picture... for better wording I wouldn't use DAB as an accelerator, rather than "yet another tool that does the job". Scaffolding out a REST API on a fixed table is a job well done by most frameworks.
Exposing a stored procedure with TVPs not so much but it takes maybe a 200-line library to account for 90% of the scenarios (granted, for just mssql and without "parity accross data sources", but you asked for a "solving my problem" point of view).
Contributing with this "addition" was my initial goal but, as aforementioned, I never considered "parity accross data sources" .

All-in-all I see the only viable options with "parity accross data sources" and also "feature parity across GraphQL and REST" as:

• for tables
  • GraphQL
    • enable the feature "multi-mutation" for all data sources (mssql is there already)
  • REST
    • scaffold three new operations "multi-insert", "multi-update", "multi-delete" that accept a json "array" of the "base object" single operation expose already for the inputs. For consistency, reuse what output already looks like supporting, which is

    •  {
            "value": [
                    {
                        "col1": "string",
                        "col2": "string"
                        ....
                    }
                ]
       }
      

    • For the outputs already dab exposes a "value" : "array" so it shouldn't change that part
• for stored procedures
  • GraphQL and REST
    • enable via config the possibility to describe "complex parameters" that I guess can be serialized as json and turn the sproc input to accept a string whose "unpacking" is left to the procedure code itself and not on dab's shoulders