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.1-beta.1`
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	Rewritten to an equivalent `SELECT` that previews affected rows or values
BEGIN TRAN / COMMIT / ROLLBACK / SAVE TRAN	Neutralized in place — structure preserved, nothing can commit
Named EXEC	`PRINT` diagnostic stubs
Declared variables	`PRINT` / `RAISERROR` traces at key points
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/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

sql-debug-harness generate -i <file.sql> [-o <out.sql>] [--trace-style print|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 tables are previewed; table-variable DML is left alone. Fixture coverage lives under samples/fixtures/ with Jest in src/engine/__tests__/.

Roadmap

Marketplace publish — VSIX packaging works today; listing still pending for a stable v1.0.
Multi-dialect support — node-sql-parser already spans other dialects.
Optional live transaction-wrapper mode — connect and run inside BEGIN…ROLLBACK.
Richer control-flow handling inside cursors / WHILE (still detect + warn for v1).