fix(sqlserver): queries returning multiple record sets

[lboonekamp]

Description of change

Fixes #11566, fixes #5051

What?

The change resolves the issue whereby any call to query() for mssql connections, returns only the first recordset, even when a query returns multiple recordsets. This change checks for recordsets and adds it to the QueryResult if useStructuredQuery = true is passed in. This change will allow the passing in of a boolean, anywhere query() is called, which will enable the full list of results to be accessed. This change does not change the default behaviour.

Why?

Without this change, you cannot access all recordsets in the return, you only get the first one.

Verification

Run a stored procedure that returns multiple recordsets i.e. .query('MY_SP'). Run two select statements within a single call to .query('SELECT 1; SELECT 2').

Pull-Request Checklist

• [x] Code is up-to-date with the master branch
• [x] This pull request links relevant issues as Fixes #11566, #5051
• [x] There are new or updated unit tests validating the change
• [x] Documentation has been updated to reflect this change

Summary by CodeRabbit

• New Features

  • Introduced QueryOptions to request structured results from raw queries across data sources, entity managers, and query runners.
  • getRawMany now accepts options to return structured results when requested.
  • Query results now expose recordsets for multi-statement responses.

• Deprecations

  • The boolean overload for requesting structured results is deprecated; use QueryOptions instead.

• Documentation

  • Added guidance and examples for executing raw SQL with structured results, including migration notes and API usage.

[coderabbitai[bot]]

Walkthrough

Introduces QueryOptions with useStructuredResult, updates query methods across DataSource, EntityManager, QueryRunner, and drivers to accept options or boolean, adds QueryResult.recordsets, threads options through query builders, updates docs and tests, and exports QueryOptions. Minor .gitignore addition and Sqljs flush visibility change.

Changes

Cohort / File(s)	Summary
Ignore config \.gitignore	Added comment and ignore pattern /*TestProject.
Public query options and result shape src/query-runner/QueryOptions.ts, src/query-runner/QueryResult.ts, src/index.ts	Added QueryOptions interface (useStructuredResult?: boolean); QueryResult gains recordsets: T[][]. Re-export QueryOptions.
QueryRunner API surface src/query-runner/QueryRunner.ts, src/query-runner/BaseQueryRunner.ts	Updated query signatures to accept options or boolean; added overloads and deprecation note for boolean; BaseQueryRunner abstract signature updated; imported QueryOptions.
Core entry points src/data-source/DataSource.ts, src/entity-manager/EntityManager.ts	DataSource.query and EntityManager.query accept optionsOrUseStructuredResult; normalize and forward to QueryRunner; import QueryOptions.
Driver query runners src/driver/*/*QueryRunner.ts (aurora-mysql, aurora-postgres, better-sqlite3, capacitor, cockroachdb, cordova, expo/...*, mysql, nativescript, oracle, postgres, react-native, sap, spanner, sqlite, sqljs, sqlserver)	Replaced boolean parameter with optionsOrUseStructuredResult (QueryOptions
Query builders src/query-builder/SelectQueryBuilder.ts, src/query-builder/InsertQueryBuilder.ts, src/query-builder/DeleteQueryBuilder.ts, src/query-builder/SoftDeleteQueryBuilder.ts	getRawMany accepts QueryOptions; loadRawResults now takes useStructuredResult; internal calls updated; replaced boolean true with { useStructuredResult: true } in DML builders.
Documentation docs/docs/query-runner.md, docs/docs/working-with-entity-manager/5-entity-manager-api.md, docs/docs/query-builder/1-select-query-builder.md	Documented QueryOptions and structured results; deprecated boolean overload; examples updated to use options; noted structured results via getRawMany.
Tests test/functional/query-runner/structured-result.ts, test/functional/repository/find-options-locking/find-options-locking.ts	Added functional tests for structured results and forwarding; adjusted test casting for query override.

Sequence Diagram(s)

sequenceDiagram
  autonumber
  participant App
  participant DataSource
  participant QueryRunner
  Note over App,QueryRunner: Request structured results using QueryOptions
  App->>DataSource: query(sql, params, { useStructuredResult: true })
  DataSource->>QueryRunner: query(sql, params, { useStructuredResult: true })
  alt useStructuredResult === true
    QueryRunner->>QueryRunner: Execute SQL
    QueryRunner-->>DataSource: QueryResult { raw, records, recordsets?, affected? }
    DataSource-->>App: QueryResult
  else default/raw
    QueryRunner->>QueryRunner: Execute SQL
    QueryRunner-->>DataSource: raw
    DataSource-->>App: raw
  end

sequenceDiagram
  autonumber
  participant App
  participant SelectQB as SelectQueryBuilder
  participant QR as QueryRunner
  App->>SelectQB: getRawMany({ useStructuredResult: true })
  SelectQB->>QR: loadRawResults(..., useStructuredResult=true)
  QR-->>SelectQB: QueryResult { records, recordsets? }
  SelectQB-->>App: QueryResult (structured)

Estimated code review effort

🎯 4 (Complex) | ⏱️ ~60 minutes

Suggested labels

size-s

Suggested reviewers

• sgarner
• michaelbromley
• naorpeled
• gioboa

Poem

I thump my paw—structures bloom anew,
Options sprout where booleans once grew.
Recordsets multiply, hops in a row,
From runners to sources, the currents now flow.
I nibble docs, tests fresh as dew—
Query me carrots, I’ll return them all too! 🥕🐇

Pre-merge checks and finishing touches

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Out of Scope Changes Check	⚠️ Warning	The addition of .gitignore entries for test/github-issues/issue-8975.ts and unrelated modifications to the find-options-locking functional test do not relate to SQL Server multi-recordset support or QueryOptions improvements and fall outside the scope of the linked issues and PR objectives.	Remove or relocate the .gitignore updates and revert the unrelated test modifications to a separate housekeeping PR to keep this changeset focused on SQL Server recordset support and structured-result enhancements.

✅ Passed checks (4 passed)

Check name	Status	Explanation
Description Check	✅ Passed	Check skipped - CodeRabbit’s high-level summary is enabled.
Title Check	✅ Passed	The title “fix(sqlserver): queries returning multiple record sets” clearly and concisely identifies the primary change—enabling SQL Server queries to return all record sets—and follows the conventional “fix(scope): description” format without extraneous detail.
Linked Issues Check	✅ Passed	The PR fully implements the objectives from issues #11566 and #5051 by exposing the driver’s recordsets array in QueryResult when structured mode is requested, preserving backward compatibility via a boolean flag, adding QueryOptions for extensibility, updating DataSource.query and EntityManager.query to accept structured-result requests, and including tests and documentation for multi‐recordset scenarios.
Docstring Coverage	✅ Passed	No functions found in the changes. Docstring coverage check skipped.

✨ Finishing touches

🧪 Generate unit tests (beta)

• [ ] Create PR with unit tests
• [ ] Post copyable unit tests in a comment

---

📜 Recent review details

Configuration used: CodeRabbit UI

Review profile: CHILL

Plan: Pro

📥 Commits

Reviewing files that changed from the base of the PR and between a5baaa34dd108950ec1c0fce4d411d5b77103c55 and c4593a4e48e06d1d0c75aa84fd68c8489a72716c.

⛔ Files ignored due to path filters (1)

• package-lock.json is excluded by !**/package-lock.json

📒 Files selected for processing (2)

• .gitignore (1 hunks)
• docs/docs/query-runner.md (1 hunks)

🚧 Files skipped from review as they are similar to previous changes (1)

• .gitignore

🔇 Additional comments (1)

docs/docs/query-runner.md (1)

56-141: Structured result docs look solid.

Nice job documenting the options flow and the expanded QueryResult shape; the examples line up with the new API behavior.

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

[pkg-pr-new[bot]]

typeorm-sql-js-example

npm i https://pkg.pr.new/typeorm/typeorm@11576

commit: 74d734e

[coveralls]

coverage: 76.442% (+0.002%) from 76.44% when pulling ccddce2d3a7e2cb88109bf665e42630fd531be28 on lboonekamp:11566-sql-recordset-return-fix into d57fe3bd8578b0b8f9847647fd046bccf825a7ef on typeorm:master.

[alumni]

I wonder whether we should make the last parameter of query an object of type QueryOptions containing the property useStructuredResult?: boolean.

Currently, in QueryRunner.query the last parameter is boolean just for this option, but the database clients that TypeORM uses can accept options for executing statements (e.g. to return rows are arrays instead of objects).

[lboonekamp]

I wonder whether we should make the last parameter of query an object of type QueryOptions containing the property useStructuredResult?: boolean.

Currently, in QueryRunner.query the last parameter is boolean just for this option, but the database clients that TypeORM uses can accept options for executing statements (e.g. to return rows are arrays instead of objects).

Great idea. Objects are much more extendable. Let me know if you want me to make the changes.

[lboonekamp]

Super annoying. Almost like I didn't commit properly. I'll push an update shortly to fix this up.

[BerenLongstreet]

Love seeing the movement on this issue! Wondering what's holding up the merge...

[qodo-free-for-open-source-projects[bot]]

PR Code Suggestions ✨

Latest suggestions up to 97826d2

Category	Suggestion	Impact
Possible issue	Handle multiple recordsets correctly In SqlServerQueryRunner, when multiple recordsets are returned, populate result.records by flattening raw.recordsets to ensure it contains all records, not just the first set. src/driver/sqlserver/SqlServerQueryRunner.ts [291-297] -if (raw?.hasOwnProperty("recordset")) { +if (raw?.hasOwnProperty("recordsets")) { + result.recordsets = raw.recordsets + result.records = raw.recordsets.flat() +} else if (raw?.hasOwnProperty("recordset")) { result.records = raw.recordset } -if (raw?.hasOwnProperty("recordsets")) { - result.recordsets = raw.recordsets -} - [To ensure code accuracy, apply this suggestion manually] Suggestion importance[1-10]: 6 __ Why: The suggestion correctly points out that result.records will only contain the first result set when multiple are available, which can be misleading. The proposed change to flatten all recordsets into records provides a more consistent and intuitive data structure.	Low
General	Improve QueryRunner type detection In DataSource.query, improve the type guard for detecting a QueryRunner by checking for multiple properties (e.g., query and release) instead of just query, to prevent potential misidentification. src/data-source/DataSource.ts [540-550] if (queryRunnerOrOptions) { if (typeof queryRunnerOrOptions === "boolean") { options = { useStructuredResult: queryRunnerOrOptions } } else if ( - (queryRunnerOrOptions as QueryRunner).query !== undefined + typeof (queryRunnerOrOptions as QueryRunner).query === "function" && + typeof (queryRunnerOrOptions as QueryRunner).release === "function" ) { queryRunner = queryRunnerOrOptions as QueryRunner } else { options = queryRunnerOrOptions as QueryOptions } } [ ] Apply / Chat Suggestion importance[1-10]: 5 __ Why: The suggestion correctly identifies a weakness in the type guard used to differentiate a QueryRunner from QueryOptions. While the current check is likely sufficient for now, using a more robust check for multiple properties like query and release would make the code more resilient to future changes.	Low
[ ] More

---

Previous suggestions

Suggestions up to commit 9d798e6

Category	Suggestion	Impact
General	Improve QueryRunner type detection Improve the type guard in DataSource.query to more reliably distinguish a QueryRunner from a QueryOptions object by checking for a more specific property like manager instead of query. src/data-source/DataSource.ts [540-550] if (queryRunnerOrOptions) { if (typeof queryRunnerOrOptions === "boolean") { options = { useStructuredResult: queryRunnerOrOptions } } else if ( - (queryRunnerOrOptions as QueryRunner).query !== undefined + (queryRunnerOrOptions as QueryRunner).manager !== undefined ) { queryRunner = queryRunnerOrOptions as QueryRunner } else { options = queryRunnerOrOptions as QueryOptions } } Suggestion importance[1-10]: 5 __ Why: The suggestion correctly points out that checking for a query property is a weak method for type guarding a QueryRunner. While a QueryOptions object is unlikely to have a query property in the current codebase, using a more specific property like manager or connection for the check, as suggested, would make the type detection more robust and future-proof.	Low
Possible issue	Handle multiple recordsets correctly Refactor the logic in SqlServerQueryRunner.ts to prioritize raw.recordsets for populating result.records to ensure consistency when multiple recordsets are returned. src/driver/sqlserver/SqlServerQueryRunner.ts [291-297] -if (raw?.hasOwnProperty("recordset")) { +if (raw?.hasOwnProperty("recordsets")) { + result.recordsets = raw.recordsets + // Use the first recordset for backward compatibility, or flatten all recordsets + result.records = raw.recordsets[0] || [] +} else if (raw?.hasOwnProperty("recordset")) { result.records = raw.recordset } -if (raw?.hasOwnProperty("recordsets")) { - result.recordsets = raw.recordsets -} - Suggestion importance[1-10]: 4 __ Why: The suggestion correctly identifies that raw.recordset is the first of raw.recordsets. The proposed change to use an if/else if structure to populate result.records from raw.recordsets when available is a good practice for robustness, though the original code is not incorrect as the driver provides both properties. This is a minor code quality improvement.	Low

Suggestions up to commit 74d734e

Category	Suggestion	Impact
Possible issue	Handle multiple recordsets correctly Refactor the logic to populate result.records and result.recordsets by checking for raw.recordsets first and deriving result.records from it for consistency. src/driver/sqlserver/SqlServerQueryRunner.ts [291-297] -if (raw?.hasOwnProperty("recordset")) { +if (raw?.hasOwnProperty("recordsets")) { + result.recordsets = raw.recordsets + // Use the first recordset for backward compatibility, or flatten all recordsets + result.records = raw.recordsets[0] || [] +} else if (raw?.hasOwnProperty("recordset")) { result.records = raw.recordset } -if (raw?.hasOwnProperty("recordsets")) { - result.recordsets = raw.recordsets -} - Suggestion importance[1-10]: 6 __ Why: The suggestion improves the logic for handling multiple recordsets from SQL Server by ensuring result.records is consistently derived from result.recordsets when available, making the code more robust.	Low
General	Improve QueryRunner type guard Replace the unsafe query property check with a more robust type guard to reliably identify QueryRunner instances. src/data-source/DataSource.ts [540-550] if (queryRunnerOrOptions) { if (typeof queryRunnerOrOptions === "boolean") { options = { useStructuredResult: queryRunnerOrOptions } } else if ( - (queryRunnerOrOptions as QueryRunner).query !== undefined + typeof (queryRunnerOrOptions as any).query === "function" && + typeof (queryRunnerOrOptions as any).release === "function" ) { queryRunner = queryRunnerOrOptions as QueryRunner } else { options = queryRunnerOrOptions as QueryOptions } } Suggestion importance[1-10]: 5 __ Why: The suggestion correctly identifies a weak type guard for QueryRunner and proposes a more robust check, which improves type safety and prevents potential runtime errors from incorrect type narrowing.	Low

Suggestions up to commit f9141f4

Category	Suggestion	Impact
Possible issue	Handle multiple recordsets correctly In SqlServerQueryRunner, when raw.recordsets is available, derive result.records from the first element of raw.recordsets for consistency, instead of using raw.recordset independently. src/driver/sqlserver/SqlServerQueryRunner.ts [291-297] -if (raw?.hasOwnProperty("recordset")) { +if (raw?.hasOwnProperty("recordsets")) { + result.recordsets = raw.recordsets + // Use the first recordset for backward compatibility + result.records = raw.recordsets[0] || [] +} else if (raw?.hasOwnProperty("recordset")) { result.records = raw.recordset } -if (raw?.hasOwnProperty("recordsets")) { - result.recordsets = raw.recordsets -} - Suggestion importance[1-10]: 6 __ Why: The suggestion correctly identifies that if raw.recordsets is present, result.records should be derived from it to ensure data consistency, making the code more robust against driver-specific behavior.	Low

✅ Suggestions up to commit 09e3944

Category	Suggestion	Impact
Possible issue	✅ Fix redundant ternary operator logic Suggestion Impact: The suggestion was directly implemented in the commit. The redundant ternary operator was corrected to return results.records when useStructuredResult is false, exactly as suggested. code diff: - return useStructuredResult ? results : results + return useStructuredResult ? results : results.records In loadRawResults, correct the redundant ternary operator to return results.records when useStructuredResult is false, ensuring backward compatibility. src/query-builder/SelectQueryBuilder.ts [3907] -return useStructuredResult ? results : results +return useStructuredResult ? results : results.records Suggestion importance[1-10]: 9 __ Why: The suggestion correctly identifies a critical bug where the ternary operator is redundant and returns the full QueryResult object instead of the expected array of records, breaking existing functionality.	High
	Handle multiple SQL Server result sets In SqlServerQueryRunner, when multiple result sets are returned, populate result.records by flattening all result.recordsets to avoid data loss and ensure consistency. src/driver/sqlserver/SqlServerQueryRunner.ts [291-297] -if (raw?.hasOwnProperty("recordset")) { +if (raw?.hasOwnProperty("recordsets")) { + result.recordsets = raw.recordsets + // Flatten all recordsets into records for consistency + result.records = raw.recordsets.flat() +} else if (raw?.hasOwnProperty("recordset")) { result.records = raw.recordset } -if (raw?.hasOwnProperty("recordsets")) { - result.recordsets = raw.recordsets -} - Suggestion importance[1-10]: 7 __ Why: The suggestion correctly identifies that result.records is only populated with the first result set, which could be unexpected. Flattening all recordsets into records is a valid improvement for consistency.	Medium

✅ Suggestions up to commit 3afd257

Category	Suggestion	Impact
Possible issue	✅ Fix redundant ternary operator Suggestion Impact: The commit directly implements the suggestion by changing the ternary operator from 'results : results' to 'results : results.records', fixing the bug where the method always returned the full QueryResult object code diff: - return useStructuredResult ? results : results + return useStructuredResult ? results : results.records Fix a redundant ternary operator in loadRawResults that causes it to always return the full QueryResult object. It should return results.records when useStructuredResult is false. src/query-builder/SelectQueryBuilder.ts [3907] -return useStructuredResult ? results : results +return useStructuredResult ? results : results.records Suggestion importance[1-10]: 9 __ Why: The suggestion correctly identifies a critical copy-paste error in loadRawResults that causes it to always return the full QueryResult object, breaking existing functionality that expects only an array of records.	High
	Handle multiple SQL Server result sets When multiple result sets are returned from SQL Server, flatten them into result.records instead of only using the first result set. This prevents data loss and improves consistency. src/driver/sqlserver/SqlServerQueryRunner.ts [291-297] -if (raw?.hasOwnProperty("recordset")) { +if (raw?.hasOwnProperty("recordsets")) { + result.recordsets = raw.recordsets + // Flatten all recordsets into records for backward compatibility + result.records = raw.recordsets.flat() +} else if (raw?.hasOwnProperty("recordset")) { result.records = raw.recordset } -if (raw?.hasOwnProperty("recordsets")) { - result.recordsets = raw.recordsets -} - Suggestion importance[1-10]: 7 __ Why: The suggestion correctly identifies that result.records would only contain the first result set, and proposes to flatten all recordsets into it for consistency and to prevent potential data loss for consumers of the API.	Medium

[lboonekamp]

@sgarner - what are your thoughts on the last change suggested by coderabbit "Handle multiple SQL Server result sets"? I've fixed the first change (pending commit). If we flatten the array, it defeats the purpose of the recordsets property that was added to QueryResult.ts.

[BerenLongstreet]

Yeah that code rabbit suggestion doesn't make any sense

[lboonekamp]

Yeah that code rabbit suggestion doesn't make any sense

My thoughts exactly.