Technical design

Architecture & engine

In-process TypeScript transform for T-SQL stored procedures — hybrid AST + text scan, no external runtime beyond VS Code’s Node.js host.

Field	Value
Project	`vscode-sql-debug-harness`
Extension ID	`DeeprajAdhikary.sql-debug-harness`
Package / CLI	`sql-debug-harness`
Version	`0.0.5`
Engine	`src/engine/` (TypeScript)

Architecture

The extension runs entirely on the Node.js runtime that ships with VS Code — there is no external interpreter or package manager involved. Installing the extension from the Marketplace (or a VSIX) is the complete setup.

Why not a separate backend process?

An earlier design shelled out to a Python backend. That required Python on PATH, a first-run pip install that could fail on locked-down machines, and two languages to maintain. Porting to TypeScript removed that dependency class — the engine ships inside the VSIX via esbuild (node-sql-parser bundled).

Parser & AST

SQL parsing uses node-sql-parser’s TransactSQL build as a best-effort AST. On real enterprise procedures (deploy preambles, TRY/CATCH, multi-batch scripts) the AST often fails — so text scan is authoritative for DML detection and rewrites.

import { Parser } from 'node-sql-parser/build/transactsql';
const parser = new Parser();
const ast = parser.astify(sqlText);

Analysis is fully static — no database connection is opened or required to generate a debug script.

Rewrite engine

The engine applies a small set of transform rules (correctness over coverage):

Input construct	Rewrite behavior
INSERT / UPDATE / DELETE (durable tables)	Rewritten to an equivalent `SELECT` that previews affected rows or values
Temp tables / table variables	`#temp`, `##temp`, and `@tableVar` DML left live
BEGIN TRAN / COMMIT / ROLLBACK / SAVE TRAN	Neutralized in place — structure preserved, nothing can commit
Named EXEC	`PRINT` diagnostic stubs
Declared variables	`SELECT` / `PRINT` / `RAISERROR` traces; bare `IF`/`ELSE` bodies wrapped in `BEGIN`…`END`
Dynamic SQL, cursors, WHILE, MERGE, OUTPUT	Flagged as an explicit warning — left unrewritten rather than guessed

For a tool whose value proposition is safety, an incorrect silent rewrite is worse than an honest “I can’t safely handle this.”

Module boundaries

src/engine/parser.ts

Best-effort node-sql-parser wrapper; soft failures.

src/engine/transform.ts

Orchestration — DML previews, TCL, traces, EXEC stubs.

src/engine/inventory.ts

Analyze report — Summary / Warnings / Identified.

src/history.ts

Workspace-scoped recent procedure history for analyzed and debugged files.

src/harnessSidebar.ts

Activity-bar actions, grouped procedure history, and history picker commands.

src/engine/unsupported.ts

Detection rules for constructs the engine won’t guess at.

src/engine/scan.ts

Comment-aware DML / TRY-CATCH line scan (authoritative).

src/cli.ts

npx sql-debug-harness — same engine, outside the editor.

Data flow

.sql file

→

Prepare

→

optional AST text scan

→

Debug script Analyze panel

Both outputs share the same engine pass — harness SQL and Analyze warnings stay in sync.

SQL text
   │
   ▼
prepare (strip GO, deploy preamble, CREATE PROC → DECLARE)
   │
   ├──► node-sql-parser (optional AST)
   │
   └──► text scan (DML / TRY-CATCH / unsupported)
            │
            ├── analyze → Summary / Warnings / Identified
            │
            └── transform → previews, EXEC stubs, TCL, traces

Extension UX & CLI

Command	Behavior
`spDebug.generate`	In-process generate → Workbench with debug script + log
`spDebug.analyze`	In-process analyze → Workbench with analysis + log
`spDebug.openWorkbench`	Open workbench for current file / selection
`spDebug.openInWorkbench`	Load a chosen `.sql` into the workbench
`spDebug.showHistory`	Pick a recently analyzed or debugged procedure without opening the sidebar

sql-debug-harness generate -i <file.sql> [-o <out.sql>] [--trace-style select|print|printCombined|raiserror]
sql-debug-harness analyze  -i <file.sql>
sql-debug-harness version

generate exits with code 2 when warnings are present. Build: npm run compile → out/extension.js, out/cli.js.

Unsupported constructs

Detected and surfaced as warnings rather than silently rewritten:

Dynamic SQL (EXEC(@sql), sp_executesql)
Cursors and WHILE-loop control flow
MERGE (disabled + commented) and OUTPUT clause warnings

Temp-table and table-variable DML are left live; only durable-table DML is previewed. Fixture coverage lives under samples/fixtures/ with Jest in src/engine/__tests__/.

Roadmap

Current package version is 0.0.5. Items below separate what already shipped from near-term and later work.

Shipped (through 0.0.5)

Marketplace listing + VSIX packaging (DeeprajAdhikary.sql-debug-harness).
In-process TypeScript engine (no Python backend).
Workbench: Analyze / Generate, History, Save popover, Clear, Compare (noise-filtered add/remove), leveled Activity Log with filters.
Engine: durable-table DML previews; temp / table-var DML left live; TCL neutralization; EXEC stubs; configurable traces (select default); procedure-header → DECLARE; bare IF/ELSE wrap for traces.
Optional CLI (npx sql-debug-harness) sharing the same engine.

Near term

Stable v1.0 — polish docs/demos, broaden fixture coverage, harden edge cases before calling the release “1.0”.
Richer control-flow handling inside cursors / WHILE (still detect + warn today).
Compare refinements — optional ignore-whitespace / word-level highlight; region-aware maps from the transform (quieter diffs).

Later (v2+)

Multi-dialect support — Postgres / Oracle / MySQL; node-sql-parser already spans other dialects, but rewrites are T-SQL-specific today.
Optional live transaction-wrapper mode — connect and run inside BEGIN…ROLLBACK (credentials + rollback edge cases).
Dynamic SQL assist — safer handling or guided stubs for EXEC(@sql) / sp_executesql (currently flagged only).