Tedious 20.3.0 streams big SQL Server parameters and table rows from async sources

Tedious 20.3.0 lets you stream a parameter into SQL Server. A varchar(max), nvarchar(max) or varbinary(max) value, or the rows of a table-valued parameter, can now come from an async iterable, such as a Node readable stream. Tedious reads the source while the request is being sent, so the value doesn't have to fit in memory.

Here's what that looks like. You build the request the usual way, and where the value would go, you pass an async iterable of buffers or strings. A readable stream works. A streamed value has no known length, so Tedious declares it as the max form of the type, here varbinary(max). It works with execSql, with callProcedure, and with a prepared statement through execute.

Table-valued parameters get the same treatment. The rows property can now be an async generator instead of an array. Each row is still an array of values, one for each declared column, and Tedious writes the rows as it reads them. The request it sends is byte for byte what the same rows would produce from an array.

A source can fail partway through. Say your generator yields twenty thousand bytes, then throws. The request ends with an InputError that names the parameter, and your original error is on its cause property. A bad row in a streamed table-valued parameter fails the same way, and the cause names the column and the row. The connection is still usable. The next request on it, a plain select of 42, comes back with 42.

A request that carries a streamed value can be executed only once. Each string chunk is encoded on its own, so a chunk can't end in the middle of a UTF-16 surrogate pair. Text that Node decoded from UTF-8 is always safe. A string you sliced by index might not be. And a buffer chunk of 8 kilobytes or more is sent by reference, so don't reuse or change it until the request has completed.

The fix in this release is for bulk loads. Until now, a value the column type couldn't serialize surfaced as the raw error, like this range error for a number that doesn't fit a decimal column. In 20.3.0, it's an InputError that names the column, with the original error kept as its cause. InputError is also exported from the package now, next to ConnectionError and RequestError, so you can check for it directly.

To upgrade, run npm install tedious. Check one thing first. If your bulk load code tests for a range error, that check stops matching, because the error is now an InputError. Read its cause instead. Values you already pass as buffers or strings aren't streamed. Streaming only starts when you hand Tedious an async iterable. So in 20.3.0, you can stream big parameters and table rows into SQL Server.