MSSQL: stream runner with row cap, cancel and timeout #325

New issue

Closed

opened 2026-11-10 08:20:49 +00:00 by david · 2 comments

david commented

2026-11-10 08:20:49 +00:00

Owner

Summary

Implement the query runner: stream rows with request.stream = true, stop at the row cap, call request.cancel(), discard later rows, and report a truncated flag.

Background

Design decision: stream and cancel. The runner reads rows until MSSQL_MAX_ROWS, then cancels, discards any rows the server sends after the cap, and the tool output says when it truncated. Limits: a query with a sort or aggregate can run a long time before the first row, so the query timeout is the control for that case; cancel is not instant, so extra rows must be discarded; the fake request in tests cannot prove real stream, cancel or timeout behaviour (no live SQL Server in tests, accepted). The runner takes an injected pool so tests use a fake. Blocked until the Stage 1 gate (#318) passes. TDD.

Depends on: #324

Documentation Required

docs/reference/mssql/ — https://raw.githubusercontent.com/tediousjs/node-mssql/master/README.md : node-mssql README, "Streaming" section (request.stream, row/error/done events, request.cancel()) and request timeout. Vendored on main; fetch the URL if absent on your branch.

Implementation Details

Create extensions/mssql/runner.ts exporting runQuery(pool, sql, { maxRows, timeoutMs }) -> Promise<{ rows: object[], truncated: boolean, rowCount: number, durationMs: number }>. Steps: const request = pool.request(); request.stream = true; register row, error, done handlers; push rows until rows.length === maxRows, then set truncated = true, call request.cancel() once and ignore every later row event; resolve on done (or after cancel acknowledges); reject on error unless it is the expected cancellation after truncation. Enforce the timeout both via the driver's request timeout and an overall timer that cancels and rejects with a clear timeout error; clear timers on settle. Resolve or reject exactly once. Detect "more rows exist" correctly: truncated is true only if a row beyond the cap arrived (or capture the (cap+1)th row and discard it), so a result of exactly maxRows rows is not flagged truncated. The runner never alters the SQL.

Acceptance Criteria

Under the cap: all rows returned, truncated: false, cancel() not called.
Exactly at the cap: all rows returned, truncated: false.
Over the cap: exactly maxRows rows, truncated: true, cancel() called once.
Rows arriving after cancel are discarded and never appear in the result.
A timeout cancels the request and rejects with a timeout error; timers are cleared.
The promise settles exactly once in every scenario, including driver errors.

Test Plan

node --test extensions/mssql/runner.test.ts with a fake request (EventEmitter with stream, query, cancel) covering: under cap, exactly at cap, over cap, rows after cancel, timeout, driver error before and after truncation.

## Summary Implement the query runner: stream rows with `request.stream = true`, stop at the row cap, call `request.cancel()`, discard later rows, and report a truncated flag. ## Background Design decision: stream and cancel. The runner reads rows until `MSSQL_MAX_ROWS`, then cancels, discards any rows the server sends after the cap, and the tool output says when it truncated. Limits: a query with a sort or aggregate can run a long time before the first row, so the query timeout is the control for that case; cancel is not instant, so extra rows must be discarded; the fake request in tests cannot prove real stream, cancel or timeout behaviour (no live SQL Server in tests, accepted). The runner takes an injected pool so tests use a fake. **Blocked until the Stage 1 gate (#318) passes.** TDD. **Depends on:** #324 ## Documentation Required - `docs/reference/mssql/` — https://raw.githubusercontent.com/tediousjs/node-mssql/master/README.md : node-mssql README, "Streaming" section (`request.stream`, `row`/`error`/`done` events, `request.cancel()`) and request timeout. Vendored on `main`; fetch the URL if absent on your branch. ## Implementation Details Create `extensions/mssql/runner.ts` exporting `runQuery(pool, sql, { maxRows, timeoutMs }) -> Promise<{ rows: object[], truncated: boolean, rowCount: number, durationMs: number }>`. Steps: `const request = pool.request(); request.stream = true;` register `row`, `error`, `done` handlers; push rows until `rows.length === maxRows`, then set `truncated = true`, call `request.cancel()` once and ignore every later `row` event; resolve on `done` (or after cancel acknowledges); reject on `error` unless it is the expected cancellation after truncation. Enforce the timeout both via the driver's request timeout and an overall timer that cancels and rejects with a clear timeout error; clear timers on settle. Resolve or reject exactly once. Detect "more rows exist" correctly: truncated is true only if a row beyond the cap arrived (or capture the (cap+1)th row and discard it), so a result of exactly `maxRows` rows is not flagged truncated. The runner never alters the SQL. ## Acceptance Criteria - [ ] Under the cap: all rows returned, `truncated: false`, `cancel()` not called. - [ ] Exactly at the cap: all rows returned, `truncated: false`. - [ ] Over the cap: exactly `maxRows` rows, `truncated: true`, `cancel()` called once. - [ ] Rows arriving after cancel are discarded and never appear in the result. - [ ] A timeout cancels the request and rejects with a timeout error; timers are cleared. - [ ] The promise settles exactly once in every scenario, including driver errors. ## Test Plan `node --test extensions/mssql/runner.test.ts` with a fake request (EventEmitter with `stream`, `query`, `cancel`) covering: under cap, exactly at cap, over cap, rows after cancel, timeout, driver error before and after truncation.

david added this to the MSSQL: M6 - Runner milestone

2026-11-10 08:20:49 +00:00

david added the

feature

label

2026-11-10 08:20:49 +00:00

david referenced this issue

2026-11-10 08:21:08 +00:00

MSSQL: mssql_query and mssql_query_one tools #326

david commented

2026-11-10 23:44:17 +00:00

Author

Owner

Update: the "Blocked until the Stage 1 gate (#318) passes" note is superseded. The gate resolved as STOP, and docs/adr/0002-mssql-read-only-credentials-with-minimal-guard.md replaced the parser design. Stage 2 is unblocked. The scope of this issue is unchanged. The row cap and timeout matter more now, because they are the main limit on expensive reads from a read-only login.

Update: the "Blocked until the Stage 1 gate (#318) passes" note is superseded. The gate resolved as STOP, and `docs/adr/0002-mssql-read-only-credentials-with-minimal-guard.md` replaced the parser design. Stage 2 is unblocked. The scope of this issue is unchanged. The row cap and timeout matter more now, because they are the main limit on expensive reads from a read-only login.

david referenced this issue

2026-11-10 23:46:59 +00:00

MSSQL spike: write RESULT.md, apply the gate, open the Stage 1 PR #318

david referenced this issue

2026-11-11 00:17:33 +00:00

325: MSSQL: stream runner with row cap, cancel and timeout #349

david closed this issue

2026-11-11 00:17:35 +00:00

david commented

2026-11-11 00:17:35 +00:00

Author

Owner