Method explainQuery

src/connectors/sqlserver/index.ts:818–861

* Return the estimated execution plan for a query using SHOWPLAN_XML. * * SHOWPLAN_XML compiles the statement and returns its plan without executing * it, but it has two constraints: `SET SHOWPLAN_XML ON` must be the only * statement in its batch, and the setting is session scoped. The s

(innerQuery: string)

   * with SHOWPLAN enabled (which would return a plan instead of its results).
   */
  private async explainQuery(innerQuery: string): Promise<SQLResult> {
    // Validate against comment/string-stripped SQL so comment-only input counts
    // as empty and a SET SHOWPLAN can't hide behind comments.
    const cleaned = stripCommentsAndStrings(innerQuery, "sqlserver").trim();
    if (!cleaned) {
      throw new Error("EXPLAIN requires a statement to analyze");
    }

    // Defense in depth: the SET SHOWPLAN session toggle is what makes EXPLAIN
    // non-executing, so the explained statement must not disable it. SQL Server
    // already rejects `SET SHOWPLAN_* OFF` alongside other statements in a
    // batch, but enforcing it here keeps the read-only guarantee self-contained.
    if (/\bset\s+showplan/i.test(cleaned)) {
      throw new Error("EXPLAIN does not support SET SHOWPLAN statements");
    }

    if (!this.config) {
      throw new Error("Not connected to SQL Server database");
    }

    const explainPool = new sql.ConnectionPool({
      ...this.config,
      pool: { ...this.config.pool, max: 1, min: 1 },
    });

    try {
      await explainPool.connect();
      // max:1 + sequential awaits guarantee both batches hit the same session.
      await explainPool.request().batch("SET SHOWPLAN_XML ON");
      const planResult = await explainPool.request().batch(innerQuery);

      // The plan is returned as the single column of the first row.
      const planRow = planResult.recordset?.[0];
      const planXml = planRow ? Object.values(planRow)[0] : null;
      return {
        rows: planXml != null ? [{ plan: planXml }] : [],
        rowCount: planXml != null ? 1 : 0,
      };
    } catch (error) {
      throw new Error(`Failed to explain query: ${(error as Error).message}`);
    } finally {
      await explainPool.close();
    }
  }
}

// Create and register the connector